🔥 Now, let’s move to the next topic of SQL Roadmap

✅ Subqueries (Nested Queries)

🧠 1. What is a Subquery?

A subquery is a query inside another query

👉 Think like this:
“First get some data → then use that result in another query”

📊 Basic Example

👉 Find employees earning above average salary
SELECT FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);

👉 Inner query → gives average salary
👉 Outer query → filters employees

⚡ 2. Types of Subqueries

🔹 Single Row Subquery

Returns only one value

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

🔹 Multiple Row Subquery

Returns multiple values
SELECT FROM employees
WHERE dept_id IN (
SELECT dept_id FROM departments
);

🎯 3. Subquery with IN

SELECT name
FROM employees
WHERE dept_id IN (
SELECT dept_id FROM departments WHERE dept_name = 'IT'
);

✔ Finds employees in IT department

⚡ 4. Subquery with EXISTS

SELECT name
FROM employees e
WHERE EXISTS (
SELECT 1 FROM departments d
WHERE e.dept_id = d.dept_id
);

✔ Checks if matching record exists

🚨 5. Important Difference

IN ->
Compares values & Slower with large data

EXISTS -> Checks existence & Faster with large data

🎯 6. Practice Tasks

1. Find employees with salary > average salary
2. Find employees in IT department using subquery
3. Get departments that have employees
4. Find employees with max salary
5. Get employees not in HR department

🔥 Here are the solutions for Subqueries practice tasks

✅ 1. Find employees with salary > average salary

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

✅ 2. Find employees in IT department using subquery

SELECT FROM employees
WHERE dept_id IN (
SELECT dept_id FROM departments
WHERE dept_name = 'IT'
);

✅ 3. Get departments that have employees

SELECT FROM departments
WHERE dept_id IN (
SELECT dept_id FROM employees
);

👉 Alternative (using EXISTS):

SELECT FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e
WHERE d.dept_id = e.dept_id
);

✅ 4. Find employees with max salary

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

✅ 5. Get employees not in HR department

SELECT FROM employees
WHERE dept_id NOT IN (
SELECT dept_id FROM departments
WHERE dept_name = 'HR'
);


⚡ Mini Challenge 🔥

👉 Find employees earning second highest salary using subquery

⚡ Mini Challenge Solution 🔥

SELECT FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary) FROM employees
)
);

🔥 Pro Tip:
If question says:
👉 “above average”, “max”, “second highest”
→ Think Subquery instantly 💯

Double Tap ❤️ For More