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