Post #2753

🔥 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