Most Important SQL Queries That Will Surprise You!

1. Finding the Second Highest Salary 

Have you ever struggled to find the runner-up salary in a table? Here’s how to do it without using LIMIT or OFFSET:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Explanation:

• The inner query finds the highest salary.
• The outer query finds the maximum salary that is less than the highest.

2. Detecting Missing Gaps in a Sequence 🔍

To find missing IDs in a sequence:

SELECT t1.id + 1 AS missing_id
FROM table_name t1
LEFT JOIN table_name t2
ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;

Explanation:

• Join each row to the next expected row.
• Filter out rows where the next ID exists.

3. Finding the Nth Highest Value 

If you need the nth highest value, here’s a general approach:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET n - 1;

Explanation:

• Use ORDER BY to sort salaries.
• Skip n-1 rows using OFFSET.

4. Calculating a Running Total 

To compute a cumulative sum in SQL:

SELECT employee_id, salary,
       SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM employees;

Explanation:

• The SUM() function with the OVER clause adds up salaries in order.
• ORDER BY defines the sequence for the running total.

5. Pivoting Data Dynamically 

Transform rows into columns:

SELECT employee_id,
       MAX(CASE WHEN month = 'January' THEN sales END) AS january_sales,
       MAX(CASE WHEN month = 'February' THEN sales END) AS february_sales
FROM sales
GROUP BY employee_id;

Explanation:

• Use CASE to pivot specific rows into columns.
• MAX ensures unique values per column.

6. Finding Duplicates 

To identify duplicate entries:

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

Explanation:

• Group rows by the column you’re checking.
• Use HAVING to filter groups with multiple rows.

7. Finding Overlapping Date Ranges 

When you need to detect overlapping time periods:

SELECT a.*, b.*
FROM reservations a
JOIN reservations b
ON a.start_date < b.end_date
AND a.end_date > b.start_date
AND a.id <> b.id;

Explanation:

• Check if the date ranges intersect.
• Exclude the same row from matching itself.

8. Hierarchical Queries (Finding Manager Trees) 

To get the hierarchy of employees and their managers:

WITH RECURSIVE employee_tree AS (
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, et.level + 1
    FROM employees e
    JOIN employee_tree et
    ON e.manager_id = et.employee_id
)
SELECT * FROM employee_tree;

Explanation:

• Use a recursive CTE to traverse the hierarchy.
• The base case includes top-level managers, and recursion builds levels.

9. Generating Random Data 

To populate a table with random values:

INSERT INTO random_data (value)
SELECT FLOOR(1 + (RAND() * 100))
FROM numbers_table
LIMIT 10;

Explanation:

• RAND() generates random numbers.
• Multiply and floor values to get integers in a range.

10. Finding the Median 

Calculating the median value in SQL can be tricky:

SELECT AVG(salary) AS median
FROM (
    SELECT salary
    FROM employees
    ORDER BY salary
    LIMIT 2 - (SELECT COUNT(*) FROM employees) % 2
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employees)
) subquery;

Explanation:

• Find the middle values using LIMIT and OFFSET.
• Average them if necessary to compute the median.