🔥 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

🔥 Now, let’s move to the next topic:

✅ Window Functions

🧠 1. What are Window Functions?

Window functions perform calculations without grouping rows

👉 Difference:
• GROUP BY → reduces rows
• Window Function → keeps all rows + adds extra column

📊 Example Table

name → Amit, Ravi, Neha
department → IT, IT, HR
salary → 60000, 70000, 40000

⚡ 2. Basic Syntax
SELECT column,
FUNCTION() OVER (PARTITION BY column ORDER BY column)
FROM table;

🔥 3. ROW_NUMBER()

Assigns unique rank to each row

SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

✔ Rank employees within each department

🥇 4. RANK() vs DENSE_RANK()

👉 RANK() (skips numbers)

SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

👉 DENSE_RANK() (no skipping)

SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

📊 Visual Difference

If salaries are 100, 90, 90, 80:
• RANK() gives: 1, 2, 2, 4
• DENSE_RANK() gives: 1, 2, 2, 3

⚡ 5. PARTITION BY (Very Important)

👉 Splits data into groups (like GROUP BY but without collapsing rows)

SELECT department, name, salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

✔ Shows avg salary per department for each row

🎯 6. Practice Tasks
1. Rank employees by salary
2. Rank employees within each department
3. Find highest salary per department
4. Add average salary column per department
5. Find second highest salary using window function

✅ Practice Tasks Solution

✅ 1. Rank employees by salary

SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

✅ 2. Rank employees within each department

SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

✅ 3. Find highest salary per department

SELECT name, department, salary
FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn = 1;

✅ 4. Add average salary column per department

SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

✅ 5. Find second highest salary using window function

SELECT name, salary
FROM (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2;

⚡ Mini Challenge 🔥
👉 Get top 2 highest paid employees in each department

⚡ Mini Challenge Solution 🔥

SELECT name, department, salary
FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn <= 2;

🔥 Pro Tip:
• Use ROW_NUMBER() → unique ranking
• Use DENSE_RANK() → handle ties

Double Tap ❤️ For More

🔥 Now, let’s move to the next topic:

✅ CTE (Common Table Expressions)

🧠 1. What is a CTE?

A CTE (Common Table Expression) is a temporary result set
👉 defined using WITH
👉 used to simplify complex queries

Think like this 👇
👉 “Create a temporary table → use it in your query”

⚡ 2. Basic Syntax

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

🎯 3. Simple Example

👉 Get employees with salary > 50k

WITH high_salary AS (
    SELECT * FROM employees
    WHERE salary > 50000
)
SELECT * FROM high_salary;

✔ Makes query more readable

🔥 4. CTE with Aggregation

👉 Average salary per department

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

⚡ 5. CTE vs Subquery

CTE ->
More readable, Reusable Better for complex queries

Subquery -> Hard to read Not reusable

🎯 6. Real Example (Interview Level)

👉 Employees earning above department average

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

SELECT e.name, e.salary, e.department
FROM employees e
JOIN dept_avg d
ON e.department = d.department
WHERE e.salary > d.avg_salary;

🎯 7. Practice Tasks

1. Create CTE for employees with salary > 40k
2. Find average salary using CTE
3. Get employees above average salary using CTE
4. Count employees per department using CTE
5. Find highest salary per department using CTE

🔥 Here are the solutions for CTE practice tasks

✅ 1. Create CTE for employees with salary > 40k

WITH high_salary AS (
    SELECT * FROM employees
    WHERE salary > 40000
)
SELECT * FROM high_salary;

✅ 2. Find average salary using CTE

WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT * FROM avg_sal;

✅ 3. Get employees above average salary using CTE

WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT e.*
FROM employees e, avg_sal a
WHERE e.salary > a.avg_salary;

👉 Alternative (JOIN style):

WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT e.*
FROM employees e
JOIN avg_sal a
ON e.salary > a.avg_salary;

✅ 4. Count employees per department using CTE

WITH dept_count AS (
    SELECT department, COUNT(*) AS total_emp
    FROM employees
    GROUP BY department
)
SELECT * FROM dept_count;

✅ 5. Find highest salary per department using CTE

WITH max_sal AS (
    SELECT department, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM max_sal;

⚡ Mini Challenge 🔥

👉 Find top 2 highest salary employees per department using CTE

⚡ Mini Challenge Solution 🔥

WITH ranked_emp AS (
    SELECT name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked_emp
WHERE rn <= 2;

🔥 Pro Tip:
Whenever query looks messy:
👉 Replace subquery with CTE

Double Tap ❤️ For More

Now, let’s move to the next topic:

Views (Virtual Tables)

🧠 1. What is a VIEW?

A VIEW is a virtual table based on a SQL query

👉 It does NOT store data
👉 It stores the query

Think like this 👇
👉 “Saved SQL query → reuse anytime”

⚡ 2. Why Use Views?

- Simplify complex queries
- Reuse logic
- Hide sensitive data
- Improve readability

⚡ 3. Create a VIEW

CREATE VIEW high_salary_emp AS
SELECT name, salary
FROM employees
WHERE salary > 50000;

🔍 4. Use a VIEW

SELECT FROM high_salary_emp;

✔ Works like a normal table

🔄 5. Update a VIEW

CREATE OR REPLACE VIEW high_salary_emp AS
SELECT name, salary, department
FROM employees
WHERE salary > 50000;

❌ 6. Drop a VIEW

DROP VIEW high_salary_emp;

🎯 7. Real Example

👉 Create view for department-wise average salary

CREATE VIEW dept_avg_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

👉 Use it:
SELECT FROM dept_avg_salary;

⚡ 8. Important Points

- View does NOT store data
- Changes in table → reflect in view
- Can be used like a table

🎯 9. Practice Tasks

1. Create view for employees with salary > 40k
2. Create view for IT department employees
3. Create view for avg salary per department
4. Query data using created views
5. Drop a view

🔥 Here are the solutions for VIEW practice tasks

✅ 1. Create view for employees with salary > 40k
CREATE VIEW high_salary_emp AS
SELECT
FROM employees
WHERE salary > 40000;

✅ 2. Create view for IT department employees
CREATE VIEW it_employees AS
SELECT
FROM employees
WHERE department = 'IT';

✅ 3. Create view for avg salary per department
CREATE VIEW dept_avg_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

✅ 4. Query data using created views
SELECT FROM high_salary_emp;

SELECT FROM it_employees;

SELECT FROM dept_avg_salary;

✅ 5. Drop a view
DROP VIEW high_salary_emp;

⚡ Mini Challenge 🔥

👉 Create a view to show top 3 highest salary employees

⚡ Mini Challenge Solution

CREATE VIEW top_3_salary AS
SELECT
FROM employees
ORDER BY salary DESC
LIMIT 3;

👉 Use the view:

SELECT FROM top_3_salary;

🔥 Pro Tip:
Views are heavily used in:
👉 Dashboards
👉 Reporting systems
👉 Data analytics projects

Because they simplify complex SQL 💯

👉 Table → stores data
👉 View → stores query

Double Tap ❤️ For More