✅ SQL Subqueries & Nested Queries 🧠🔍

Subqueries help you write powerful queries inside other queries. They're useful when you need intermediate results.

1️⃣ What is a Subquery?
A subquery is a query inside () that runs first and passes its result to the outer query.

Example: Get employees who earn above average salary.

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

✅ Subquery calculates average salary → main query finds those above it.

2️⃣ Subquery in SELECT Clause
You can use subqueries to return values in each row.

Example: Show employee names with department name.

SELECT name,  
  (SELECT dept_name FROM departments d WHERE d.id = e.dept_id) AS department  
FROM employees e;

3️⃣ Subquery in FROM Clause
Use when you want to filter or group temporary results.

Example: Get department-wise highest salary.

SELECT dept_id, MAX(salary)  
FROM (SELECT * FROM employees WHERE active = 1) AS active_emps  
GROUP BY dept_id;

4️⃣ Correlated Subquery
A subquery that uses a value from the outer query row.

Example: Get employees with highest salary in their department.

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

✅ Subquery runs for each row using outer query value.

💡 Real Use Cases:
• Filter rows based on dynamic conditions
• Compare values across groups
• Fetch related info in SELECT

🎯 Practice Tasks:
• Write a query to find 2nd highest salary
• Use subquery to get customers who placed more than 3 orders
• Create a nested query to show top-selling product per category


✅ Solution for Practice Tasks 👇

1️⃣ Find 2nd Highest Salary

SELECT MAX(salary) AS second_highest_salary  
FROM employees  
WHERE salary < (SELECT MAX(salary) FROM employees);

▶️ Finds the highest salary less than the max salary → gives the 2nd highest.


2️⃣ Customers Who Placed More Than 3 Orders

SELECT customer_id  
FROM orders  
GROUP BY customer_id  
HAVING COUNT(order_id) > 3;

▶️ Groups orders by customer and filters those with more than 3.

You can join to get customer names:

SELECT name  
FROM customers  
WHERE id IN (
  SELECT customer_id  
  FROM orders  
  GROUP BY customer_id  
  HAVING COUNT(order_id) > 3
);

3️⃣ Top-Selling Product Per Category

SELECT p.name, p.category_id, p.sales  
FROM products p  
WHERE p.sales = (
  SELECT MAX(sales)  
  FROM products  
  WHERE category_id = p.category_id
);

▶️ Correlated subquery finds the highest sales within each category.

💬 Tap ❤️ for more!