✅ Advanced SQL (Subqueries & CTEs) 🗄️🔥

👉 Now we move to advanced SQL concepts heavily used in:
✔ Data Analysis
✔ Reporting
✔ Dashboards
✔ Interviews

🔹 1. What is a Subquery?
A subquery is a query written inside another query.

👉 Also called:
✅ Nested Query

🔥 2. Example of Subquery
👉 Find employees earning above average salary.

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

How it works:
1️⃣ Inner query calculates average salary
2️⃣ Outer query filters employees

🔹 3. Types of Subqueries
✔ Single-row subquery
✔ Multiple-row subquery
✔ Correlated subquery

🔹 4. Correlated Subquery ⭐
👉 Inner query depends on outer query.

SELECT e1.name
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e1.department = e2.department
);

🔥 5. What is a CTE?
CTE = Common Table Expression

👉 Temporary result set used inside a query.

Defined using:
WITH

🔹 6. Example of CTE ⭐
WITH avg_salary AS (
SELECT AVG(salary) AS avg_sal
FROM employees
)

SELECT *
FROM employees
WHERE salary > (
SELECT avg_sal FROM avg_salary
);

🔹 7. Why Use CTEs?
✔ Makes queries readable
✔ Simplifies complex logic
✔ Easier debugging

🔹 8. Difference Between Subquery & CTE
Subquery : Nested inside query
CTE : Defined separately

Subquery : Harder to read
CTE : More readable

Subquery : Repeated logic possible
CTE : Reusable

🔹 9. Why This is Important?
✔ Frequently asked in interviews
✔ Used in dashboards & analytics
✔ Important for real-world SQL projects

🎯 Today’s Goal
✔ Understand subqueries
✔ Learn correlated subqueries
✔ Understand CTEs
✔ Write cleaner SQL queries

👉 SQL Notes: https://whatsapp.com/channel/0029VbCyzS02ZjCwoShXXc2j

💬 Tap ❤️ for more!