SQL Interview Questions
10 questions with detailed answers
Q1. What is the difference between INNER JOIN and LEFT JOIN?
Answer: INNER JOIN returns matching rows from both tables. LEFT JOIN returns all rows from left table and matching from right.
Q2. What is normalization?
Answer: Organizing database to reduce redundancy: 1NF, 2NF, 3NF, BCNF eliminate duplicate data and anomalies.
Q3. Difference between DELETE and TRUNCATE?
Answer: DELETE removes rows one by one (can rollback). TRUNCATE removes all rows quickly (resets identity, harder to rollback).
Q4. What is an index?
Answer: A data structure that improves query speed by creating quick lookup paths, at the cost of write performance and storage.
Q5. What are window functions?
Answer: SQL functions like ROW_NUMBER(), RANK(), LEAD() that perform calculations across related rows without grouping.
Q6. What is the difference between WHERE and HAVING?
Answer: WHERE filters rows before grouping and aggregation. HAVING filters grouped results after GROUP BY, so it can use aggregate expressions such as COUNT(*) or SUM(amount).
Q7. What is a correlated subquery?
Answer: A correlated subquery references columns from the outer query and is logically evaluated for each outer row. It can often be rewritten as a join or window function for better performance.
Q8. What is a transaction and what does ACID mean?
Answer: A transaction groups operations as one unit. ACID means atomicity, consistency, isolation, and durability, which together protect data correctness during failures and concurrent access.
Q9. What is a common table expression (CTE)?
Answer: A CTE is a named temporary result introduced with WITH and used by the following statement. It improves readability and recursive CTEs can traverse hierarchies.
Q10. How do UNION and UNION ALL differ?
Answer: UNION combines result sets and removes duplicates. UNION ALL keeps every row and is usually faster because it avoids the duplicate-elimination step.