SQL Queries That Will Surprise You! 🚀💡

SQL is the backbone of data manipulation, but some queries are not your everyday SELECT *. 🤯 Whether you’re a beginner or a seasoned developer, these mind-bending SQL queries will leave you astonished. Let’s dive in! 🌊

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…