Below is the sample dataset script for practice.

Script for sample dataset

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

SELECT
    e.department_id,
    d.department_name,
    COUNT(*) AS emp_count
FROM employee e
LEFT JOIN department d ON e.department_id = d.id
GROUP BY e.department_id, d.department_name
ORDER BY emp_count DESC
LIMIT 1;

7. Find Employees Who Do Not Belong to Any Department

SELECT *
FROM employee e
WHERE e.department_id NOT IN (
    SELECT d.id FROM department d
);

8. Fetch the Top 3 Highest Paid Employees in Each Department