Data Analytics

🔥 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