SQL Interview Question and Answers
Find the second highest salary from an Employee table:
SELECT MAX(salary) AS second_highest_salary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
List all employees who are also managers:
SELECT e.*
FROM Employee e
JOIN Employee m ON e.manager_id = m.employee_id;
Find the nth highest salary from an Employee table:
SELECT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET n-1;
Count the number of employees in each department:
SELECT department_id, COUNT(*) AS num_employees
FROM Employee
GROUP BY department_id;
List departments with more than 10 employees:
SELECT department_id, COUNT() AS num_employees FROM Employee GROUP BY department_id HAVING COUNT() > 10;
Find duplicate records in a table:
SELECT column1, column2, COUNT() FROM table_name GROUP BY column1, column2 HAVING COUNT() > 1;
List the top 3 departments with the highest average salary:
SELECT department_id, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department_id
ORDER BY avg_salary DESC
LIMIT 3;
Find the employee(s) with the highest salary in each department:
SELECT employee_id, department_id, salary
FROM (
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM Employee
) AS ranked_employees
WHERE rank = 1;