Common SQL Queries and Real-World Problems: Complete Solutions Guide

1. Find Duplicate Records

Duplicate records appear when tables lack unique constraints or when upstream ingestion pipelines re-deliver webhook payloads.

To detect duplicate records across single or composite keys, group by the business key and filter using HAVING COUNT(*) > 1:

-- Finding duplicate email addresses
SELECT email, COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

2. Delete Duplicate Rows

Deleting duplicate rows requires preserving exactly one record (typically the oldest or newest) while purging redundant copies.

The standard technique uses a Common Table Expression (CTE) with ROW_NUMBER() partitioned by the business key:

-- De-duplicating records while preserving the earliest ID
WITH NumberedRows AS (
    SELECT id, email,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY id ASC
        ) AS row_num
    FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM NumberedRows WHERE row_num > 1);

3. Second Highest Salary

A classic technical interview question. There are three standard methods to solve it:

Method 1 (Subquery): SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
Method 2 (LIMIT & OFFSET): SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
Method 3 (DENSE_RANK): Assigns ranks and filters where rank equals 2.

Finding the Second Highest Salary

SQL

Retrieve second-tier compensation using DENSE_RANK

4. Nth Highest Salary

Generalizing to the N-th rank requires handling tied compensation figures correctly. Using ROW_NUMBER() fails when two employees tie for first place because the next employee receives rank 3.

DENSE_RANK() guarantees that tied salaries receive identical rank numbers without creating gaps in subsequent tiers:

-- General Nth highest salary pattern (replace N with desired integer)
WITH Ranked AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
FROM employees
)
SELECT DISTINCT salary FROM Ranked WHERE rk = 3;

5. Top N Records per Group

Finding the top N records within each category (such as the top 2 highest earners in every department) requires partitioning by group and filtering in an outer query:

Top 2 Earners per Department

SQL

Partition rankings by department and filter top two compensation tiers

6. Find Employees Earning More Than Their Manager

When hierarchical relationships exist inside a single table (via manager_id), join the table to itself using distinct aliases:

Self-Join Salary Comparison

SQL

Identify team members whose compensation exceeds their direct manager

7. Running Total (Cumulative Sum)

A running total calculates progressive accumulation across an ordered sequence:

Cumulative Payroll Timeline

SQL

Accumulate total compensation expenditure chronologically

8. Find Missing Numbers in a Sequence

To detect missing sequence identifiers (such as gaps in consecutive invoice numbers), generate an expected sequence using a recursive CTE and perform an anti-join:

Detecting Sequence Gaps

SQL

Generate a continuous number range to locate missing course IDs

9. Compare Two Tables for Differences

To verify data parity between production and staging replicas, use the EXCEPT set operator:

-- Rows present in table_a but missing from table_b
(SELECT * FROM table_a EXCEPT SELECT * FROM table_b)
UNION ALL
-- Rows present in table_b but missing from table_a
(SELECT * FROM table_b EXCEPT SELECT * FROM table_a);

10. Select Rows with the Max Value in a Column

To retrieve complete record attributes for rows containing the maximum column value:

SELECT id, name, category, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

11. Count Rows in Every Table

In PostgreSQL and MySQL, query database metadata catalogs rather than running individual count queries:

-- PostgreSQL row estimates from statistics catalog
SELECT relname AS table_name, n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

12. Swap Values of Two Columns

Relational databases update columns in a single atomic step. You can swap column values directly without a temporary variable:

-- Swap column_a and column_b in one statement
UPDATE coordinates SET col_x = col_y, col_y = col_x;

-- Swap status codes using conditional CASE
UPDATE users SET status = CASE WHEN status = 'active' THEN 'paused' ELSE 'active' END;

13. Find Records That Exist in One Table but Not Another

The anti-join pattern identifies orphan records:

-- Customers who have never placed an order
SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

14. Calculate Median in SQL

While PostgreSQL and SQL Server support PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col), the universal method averages the middle rows using window functions:

Universal Median Computation

SQL

Calculate exact 50th percentile by averaging middle ranking rows

Knowledge Check: Common SQL Queries & Problems

1. Which clause detects duplicate business keys in a table?

2. Why is DENSE_RANK preferred when finding the Nth highest salary?

3. Which join finds employees earning more than their manager?

4. How do you swap values of two columns in a single row?

5. What is the most efficient anti-join pattern in SQL?

6. How does SQL calculate the median without PERCENTILE_CONT?