SQL Interview Questions with Answers

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table + matching from right (NULLs for non-matches). RIGHT JOIN is the opposite. FULL OUTER JOIN returns all rows from both tables, with NULLs where there's no match.

Window functions perform calculations across a set of rows related to the current row, without collapsing them. Example: `SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank FROM employees;` — This ranks employees by salary within each department.

WHERE filters rows before grouping (works on individual rows). HAVING filters groups after GROUP BY (works on aggregated results). Example: `SELECT dept, AVG(salary) FROM emp WHERE status='active' GROUP BY dept HAVING AVG(salary) > 50000;`

A correlated subquery references columns from the outer query and executes once for each row of the outer query. Example: `SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);` — finds employees earning above their department average.

Multiple approaches: 1) `SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);` 2) Using DENSE_RANK: `SELECT salary FROM (SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) as rnk FROM emp) WHERE rnk = 2;` 3) Using LIMIT/OFFSET: `SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET 1;`

1NF: Atomic values, no repeating groups. 2NF: 1NF + no partial dependencies (every non-key column depends on the entire primary key). 3NF: 2NF + no transitive dependencies (non-key columns don't depend on other non-key columns). Example: A table with student_id, course_id, student_name, course_name violates 2NF because student_name depends only on student_id.

An index is a data structure (usually B-tree) that speeds up data retrieval. Avoid indexes on: (1) Small tables, (2) Columns with high write frequency, (3) Columns with low cardinality (e.g., boolean), (4) Columns rarely used in WHERE/JOIN/ORDER BY.

`SELECT email, COUNT(*) as cnt FROM users GROUP BY email HAVING COUNT(*) > 1;` To find all columns: `SELECT * FROM users WHERE email IN (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1);`