1
What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?
Short Answer
INNER: Only matching rows from both tables. LEFT: All from left + matching from right (NULL if no match). RIGHT: All from right + matching from left. FULL OUTER: All rows from both, NULLs where no match.
-- Find all employees with their department (include employees without dept)
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- Find employees who have no department
SELECT e.name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;
💡 Memory Trick: "INNER = intersection | LEFT = all left + matching right | FULL = everything"
2
Explain window functions with ROW_NUMBER, RANK, and DENSE_RANK.
Short Answer
Window functions compute values across rows related to the current row without collapsing groups. ROW_NUMBER: unique sequential (1,2,3). RANK: ties get same rank, gaps after (1,1,3). DENSE_RANK: same rank for ties, no gaps (1,1,2).
-- Top 3 highest paid employees per department
SELECT * FROM (
SELECT name, dept, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rnk
FROM employees
) ranked
WHERE rnk <= 3;
💡 Memory Trick: "ROW_NUMBER = race bib (all unique) | RANK = Olympic medals (tie=skip next) | DENSE_RANK = no gaps"
3
What is a CTE and when would you use it?
Short Answer
A Common Table Expression (CTE) is a named temporary result set defined with WITH. It improves readability, enables recursion, and can be referenced multiple times in the same query.
-- Recursive CTE: Find all managers in hierarchy
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 1 as level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;
💡 Memory Trick: "CTE = Named subquery at the top. Makes complex queries readable like paragraphs"
Questions 4-50 continue with the same detailed format.
Topics covered: GROUP BY + HAVING, Indexing strategies, Query optimization (EXPLAIN), Transactions & ACID, Normalization (1NF-3NF), Stored Procedures, Triggers, Views, Temporary Tables, PIVOT/UNPIVOT, and real interview SQL puzzles.
View Full Documentation →