Post #2683

Real-world SQL Questions with Answers 🔥 Let's dive into some real-world SQL questions with a mini dataset. 📊 Dataset: employees id name department salary manager_id 1 Aditi HR 30000 5 2 Rahul IT 50000 6 3 Neha IT 60000 6 4 Aman Sales 40000 7 5 Kiran HR 70000 NULL 6 Mohit IT 80000 NULL 7 Suresh Sales 65000 NULL 8 Pooja HR 30000 5 1. Find average salary per department SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; 2. Find employees earning above department average SELECT name, department, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department ); 3. Find highest salary in each department SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department; 4. Find employees who earn more than their manager SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary; 5. Count employees in each department SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department; 6. Find departments with more than 2 employees SELECT department, COUNT(*) AS total FROM employees GROUP BY department HAVING COUNT(*) > 2; 7. Find second highest salary SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); 8. Find employees without managers SELECT name FROM employees WHERE manager_id IS NULL; 9. Rank employees by salary SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; 10. Find duplicate salaries SELECT salary, COUNT(*) FROM employees GROUP BY salary HAVING COUNT(*) > 1; 11. Top 2 highest salaries SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 2; Double Tap ❤️ For More