1. Finding the N-th Highest Salary
This is the single most frequently asked query in fresher database interviews.
sql
-- Approach 1: Using DENSE_RANK() Window Function (Best Industry Standard)
WITH RankedEmployees AS (
SELECT
emp_id,
emp_name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) as rank_num
FROM employees
)
SELECT emp_id, emp_name, salary
FROM RankedEmployees
WHERE rank_num = 2; -- 2nd Highest Salary
-- Approach 2: Using LIMIT & OFFSET (PostgreSQL / MySQL)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- 2nd highest (OFFSET = N - 1)2. Finding & Removing Duplicate Records
sql
-- Query 1: Find duplicate emails and their frequency
SELECT email, COUNT(*) as occurrence_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Query 2: Delete duplicate records while keeping the smallest ID
DELETE FROM users
WHERE id NOT IN (
SELECT MIN(id)
FROM users
GROUP BY email
);3. SQL Joins & Employee-Manager Hierarchy (Self Join)
sql
-- Self Join to find Employee Name along with their Manager Name
SELECT
e.emp_name AS Employee,
COALESCE(m.emp_name, 'Top Executive / CEO') AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;Visual Summary of SQL Joins:
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
- RIGHT (OUTER) JOIN: Returns all records from the right table, and matched records from the left table.
- FULL OUTER JOIN: Returns all records when there is a match in either left or right table.
4. Essential Window Functions (ROW_NUMBER, RANK, DENSE_RANK)
sql
-- Find the Highest Paid Employee in EACH Department
WITH DepartmentRanks AS (
SELECT
emp_id,
emp_name,
department_id,
salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank
FROM employees
)
SELECT emp_id, emp_name, department_id, salary
FROM DepartmentRanks
WHERE dept_rank = 1;ROW_NUMBER(): Assigns a unique sequential integer to each row within a partition without duplicates (1, 2, 3, 4).RANK(): Assigns rank with gaps on tied values (1, 2, 2, 4).DENSE_RANK(): Assigns rank without gaps on tied values (1, 2, 2, 3).
5. ACID Properties and Indexing Optimization
- Atomicity: All operations in a database transaction succeed or all rollback.
- Consistency: Database transitions from one valid state to another valid state.
- Isolation: Concurrent transactions execute without dirty reads or race conditions.
- Durability: Committed data is safely written to disk and survives system crashes.
sql
-- Creating B-Tree Index for faster query lookups
CREATE INDEX idx_users_email ON users(email);