1.Find the Second Highest Salary in a Table SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Find Duplicate Records in a Table
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
Find the Nth Highest Salary in a Table. SELECT DISTINCT TOP 1 salary
FROM (
SELECT DISTINCT TOP N salary
FROM employees
ORDER BY salary DESC
) AS sub
ORDER BY salary ASC;
Calculate the Running Total SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;
Find Missing Numbers in a Range WITH Numbers AS (
SELECT 1 AS num
UNION ALL
SELECT num + 1 FROM Numbers WHERE num < 100
)
SELECT num
FROM Numbers
LEFT JOIN your_table ON Numbers.num = your_table.column
WHERE your_table.column IS NULL;
Get the Top N Records from Each Group SELECT *
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
FROM products
) ranked
WHERE rank <= N;
Find the Longest Consecutive Sequence of Numbers SELECT MIN(num) AS start_range,
MAX(num) AS end_range,
COUNT(*) AS sequence_length
FROM (
SELECT num,
num - ROW_NUMBER() OVER (ORDER BY num) AS grp
FROM your_table
) sub
GROUP BY grp;
Find the Third Highest Salary in a Table SELECT DISTINCT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM employees
ORDER BY salary DESC
) AS sub
ORDER BY salary ASC;
Find the Median of a Dataset WITH RankedValues AS (
SELECT val,
ROW_NUMBER() OVER (ORDER BY val) AS row_num,
COUNT(*) OVER () AS total_count
FROM your_table
)
SELECT AVG(val) AS median
FROM RankedValues
WHERE row_num IN (total_count / 2, (total_count + 1) / 2);
