SQL Interview Questions

Common SQL Interview Questions

1. JOIN Types

• INNER JOIN: Only matching rows from both tables
• LEFT JOIN: All from left + matches from right
• RIGHT JOIN: All from right + matches from left
• FULL OUTER JOIN: All rows from both sides

2. WHERE vs HAVING

• WHERE: Filters before grouping
• HAVING: Filters after grouping

3. Subquery Example

SELECT name
FROM employees
WHERE emp_id IN (
  SELECT emp_id FROM departments WHERE dept_name = 'HR'
);

4. GROUP BY and ORDER BY

SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY COUNT(*) DESC;

5. DISTINCT

SELECT DISTINCT department FROM employees;

6. Normalization

• 1NF: Atomic columns
• 2NF: Full functional dependency
• 3NF: No transitive dependency
• BCNF: Stronger version of 3NF
• 4NF: No multi-valued dependency

7. UNION vs UNION ALL

SELECT name FROM employees
UNION
SELECT name FROM contractors;

8. Indexes

Indexes improve performance for lookups, but slow down writes.

9. ACID Properties

• Atomicity: All-or-nothing transactions
• Consistency: Data remains valid
• Isolation: No interference between transactions
• Durability: Permanent changes after commit

10. Primary Key

Uniquely identifies each record, no NULLs allowed.

11. Foreign Key

Refers to a primary key in another table to ensure referential integrity.

12. CASE Statement

SELECT name,
  CASE
    WHEN salary > 50000 THEN 'High'
    WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_level
FROM employees;

13. Transactions

Ensure ACID compliance using BEGIN, COMMIT, ROLLBACK.

14. JOIN Types Explained

Covered in Question 1 with examples of INNER, LEFT, RIGHT, FULL joins.

15. SELF JOIN

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;

16. LIMIT

-- MySQL/Postgres
SELECT * FROM employees LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM employees;

17. TRUNCATE vs DELETE

• DELETE: Row-by-row, can use WHERE, rollback supported
• TRUNCATE: Faster, no WHERE, can't rollback

18. Stored Procedures vs Functions

• Procedure: No return value required, can do INSERT/UPDATE
• Function: Always returns a value, can be used in SELECT

19. CHAR vs VARCHAR

• CHAR: Fixed length, padded with spaces
• VARCHAR: Variable length, more efficient

20. Views

Virtual tables based on SQL queries, used for abstraction and simplification.