Tech Courses & Job Updates

Subqueries and Common Table Expressions (CTEs) are important PostgreSQL concepts used to write readable queries, filter data based on other query results, and solve real-world business problems. In this tutorial, you will learn how to use subqueries, correlated subqueries, and CTEs with practical SQL examples. Sample Table We will use an employees table with the following data: employee_id employee_name department salary 1 Rahul IT 60000 2 Priya HR 40000 3 Arjun IT 75000 4 Sneha Finance 50000 5 Kiran HR 45000 6 Divya Finance 65000 What Is a Subquery? A subquery is a SQL query written inside another SQL query. It is also called an inner query or nested query. Subqueries can be used inside SELECT , FROM , WHERE , and HAVING clauses. Example 1: Find Employees Earning More Than the Average Salary SELECT employee_name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); The inner query calculates the average salary. The outer query returns employees whos...

In this tutorial, you will learn how to use PostgreSQL aggregate functions, GROUP BY, and HAVING with practical SQL examples. These concepts are useful for database reporting, data analytics, and SQL interviews. What You Will Learn COUNT(), SUM(), AVG(), MIN(), and MAX() GROUP BY with practical examples WHERE vs HAVING Common SQL mistakes Practice questions and interview questions 1. What Are Aggregate Functions in PostgreSQL? Aggregate functions perform calculations on multiple rows and return a single result. They help calculate totals, averages, counts, minimum values, and maximum values. For example, a company may want to calculate its total salary expense or find the average salary of employees. 2. Create a Sample Table We will use an employees table for all examples in this tutorial. CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50), department VARCHAR(30), salary NUMERIC(10,2) ); INSERT INTO employees (employee_id, employee_name, de...