1. Find the Second Highest Salary
SELECT MAX(e1.salary)
FROM employee e1
WHERE e1.salary < (
SELECT MAX(e2.salary)
FROM employee e2
);
2. Find the Nth Highest Salary
Option 1: Using LIMIT and OFFSET
SELECT DISTINCT e.salary
FROM employee e
ORDER BY e.salary DESC
LIMIT 1 OFFSET n-1; -- Replace 'n-1' with the calculated offset number
Option 2: Using a Correlated Subquery
SELECT DISTINCT e1.salary
FROM employee e1
WHERE n-1 = ( -- Replace 'n-1' with your target ranking index
SELECT COUNT(DISTINCT e2.salary)
FROM employee e2
WHERE e2.salary > e1.salary
);
3. Find Duplicate Rows in a Table
SELECT e.salary, COUNT(e.salary)
FROM employee e
GROUP BY e.salary
HAVING COUNT(*) > 1;
4. Find Employees Who Earn More Than Their Manager
SELECT e.*
FROM employee e
JOIN employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;
5. Count the Number of Employees in Each Department
SELECT
d.id,
CASE
WHEN d.department_name IS NULL THEN 'No department'
ELSE d.department_name
END AS department,
COUNT(*)
FROM employee e
LEFT JOIN department d ON e.department_id = d.id
GROUP BY d.id, d.department_name
ORDER BY d.id;
6. Find the Department with the Highest Number of Employees