Real-world SQL Questions with Answers 🔥

Let's dive into some real-world SQL questions using a practical 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 their 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 the 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;

🔟 Find duplicate salary values

SELECT salary, COUNT(*) 
FROM employees 
GROUP BY salary 
HAVING COUNT(*) > 1;

1️⃣1️⃣ Top 2 highest unique salaries

SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 2;

1️⃣2️⃣ Find the total salary payout per department

SELECT department, SUM(salary) AS total_payout 
FROM employees 
GROUP BY department;

1️⃣3️⃣ Find employees whose names start with 'A'

SELECT name 
FROM employees 
WHERE name LIKE 'A%';

1️⃣4️⃣ Find the manager's name for each employee (Self-Join)

SELECT e.name AS employee, m.name AS manager 
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.id;

1️⃣5️⃣ Find the department with the highest total salary expenditure

SELECT department 
FROM employees 
GROUP BY department 
ORDER BY SUM(salary) DESC 
LIMIT 1;

👉 Follow @datascience_bds for more.