✅ SQL Real-world Interview Questions with Answers 🖥️

📊 TABLE: employees

id | name | department | salary
1 | Rahul | IT | 50000
2 | Priya | IT | 70000
3 | Amit | HR | 60000
4 | Neha | HR | 70000
5 | Karan | IT | 80000
6 | Simran | HR | 60000

🎯 1️⃣ Find the 2nd highest salary
🧠 Logic: Get highest salary Then find max salary below that

✅ Query:
SELECT MAX(salary) FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees );

🎯 2️⃣ Find employees earning more than average salary
🧠 Logic: Calculate overall average salary Compare each employee

✅ Query:
SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );

🎯 3️⃣ Find highest salary in each department
🧠 Logic: Group by department Use MAX

✅ Query:
SELECT department, MAX(salary) AS highest_salary FROM employees GROUP BY department;

🎯 4️⃣ Find top 2 highest salaries in each department
🧠 Logic: Use ROW_NUMBER Partition by department Filter top 2

✅ Query:
SELECT * FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER( PARTITION BY department ORDER BY salary DESC ) r
FROM employees
) t WHERE r <= 2;

🎯 5️⃣ Find employees earning more than their department average
🧠 Logic: Use correlated subquery Compare with department avg

✅ Query:
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) FROM employees WHERE department = e.department
);

⭐ What Interviewer Checks Here
These 5 questions test:
✔ Subqueries
✔ GROUP BY
✔ Window functions
✔ Correlated queries
✔ Real business logic

SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Double Tap ♥️ For More