SQL Programming Resources on Telegram: “⚡ Subqueries CTEs After mastering JOINS, the nex…”

⚡ Subqueries CTEs

After mastering JOINS, the next important concept is Subqueries and CTEs. These are used when queries become complex and you need intermediate results.

👉 Very common in data analyst interviews and real analytics queries.

🔹 1️⃣ What is a Subquery?

A subquery is a query inside another SQL query. It is executed first, and its result is used by the main query.

🎯 Basic Syntax

SELECT column
FROM table
WHERE column = (SELECT column FROM table);

🧠 Example 1 — Find Employees with Highest Salary

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Explanation:
1️⃣ Inner query finds maximum salary
2️⃣ Outer query finds employee with that salary

🔹 2️⃣ Subquery in WHERE Clause
Most common use.

Example — Employees earning more than average salary

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Used heavily in analytics queries.

🔹 3️⃣ Subquery in FROM Clause
Also called Derived Table.

SELECT department, AVG(avg_salary)
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_salary
GROUP BY department;

Used when intermediate results are required.

🔹 4️⃣ EXISTS / NOT EXISTS

Used to check if a record exists in another table.

Example — Customers who placed orders

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE c.customer_id = o.customer_id
);

👉 Returns customers with orders.

Example — Customers with no orders

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE c.customer_id = o.customer_id
);

⭐ 5️⃣ Common Table Expressions (CTEs)

CTEs improve query readability and structure. Defined using WITH clause.

Basic Syntax

WITH cte_name AS (
    SELECT column
    FROM table
)
SELECT *
FROM cte_name;

🧠 Example — Average Salary by Department

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT *
FROM dept_avg;

🔹 6️⃣ Multiple CTEs

You can chain multiple CTEs.

WITH total_sales AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
),
top_customers AS (
    SELECT *
    FROM total_sales
    WHERE total > 1000
)
SELECT *
FROM top_customers;

Used often in complex analytics queries.

🧠 Real Analyst Examples

Customers with above average purchases

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (
    SELECT AVG(total)
    FROM (
        SELECT SUM(amount) AS total
        FROM orders
        GROUP BY customer_id
    ) AS totals
);

Complex analytics often uses subqueries or CTEs.

🚀 Mini Practice Tasks

🎯 Task 1 — Find employees earning more than average salary

✔ Solution

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

💡 Explanation: Subquery calculates average salary, Outer query filters employees above average.

🎯 Task 2 — Find customers who placed orders

✔ Solution (Using EXISTS ⭐)

SELECT name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE c.customer_id = o.customer_id
);

💡 Explanation: Checks if an order exists for the customer.

🎯 Task 3 — Find departments with salary greater than company average

✔ Solution

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

💡 Explanation: Subquery finds company average salary, HAVING filters departments above that average.

🎯 Task 4 — Use a CTE to calculate total sales per customer

✔ Solution

WITH customer_sales AS (
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM customer_sales;

💡 Explanation: CTE calculates total sales for each customer, Main query retrieves the result.

Double Tap ♥️ For More