Top 30 SQL Interview Questions and Answers (Beginner to Advanced)

Whether you're a fresh graduate, a data analyst, or a software developer, SQL is one of the most critical skills to master for database-related roles. This guide will walk you through 30 essential SQL interview questions and answers, grouped by difficulty level: Beginner, Intermediate, and Advanced.

---

Beginner SQL Interview Questions with Answers

These questions focus on basic SQL syntax, concepts, and simple queries.

1. What is SQL and why is it used?
   Answer: SQL (Structured Query Language) is used to interact with relational databases. It allows users to query, insert, update, and delete data.

2. What are the different types of SQL statements?
   Answer:

   • DDL (Data Definition Language): CREATE, ALTER, DROP

   • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE

   • DCL (Data Control Language): GRANT, REVOKE

   • TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3. What is the difference between WHERE and HAVING?
   Answer:

   • WHERE filters rows before grouping.

   • HAVING filters groups after aggregation.

   SELECT department, COUNT(*) 
   FROM employees 
   GROUP BY department 
   HAVING COUNT(*) > 5;
   

4. What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?
   Answer:

   • INNER JOIN: Returns only matching rows.

   • LEFT JOIN: Returns all from left table + matches.

   • RIGHT JOIN: Returns all from right table + matches.

5. What is a Primary Key? Can a table have more than one?
   Answer:
   A primary key uniquely identifies each row in a table. A table can have only one primary key (which can be composite—more than one column).

6. What is a Foreign Key?
   Answer:
   A foreign key is a column that links to the primary key in another table, ensuring referential integrity.

7. What are NULL values in SQL?
   Answer:
   NULL represents a missing or unknown value. It's not the same as 0 or an empty string. Use IS NULL to check for nulls.

8. What is the purpose of the GROUP BY clause?
   Answer:
   It groups rows sharing a value, often used with aggregation functions like SUM(), COUNT(), AVG(), etc.

   SELECT department, AVG(salary) FROM employees GROUP BY department;
   

9. What is the difference between UNION and UNION ALL?
   Answer:

   • UNION: Removes duplicates.

   • UNION ALL: Keeps all rows including duplicates.

10. How do you use the LIKE operator?
    Answer:
    Used for pattern matching with % and _.

    SELECT * FROM customers WHERE name LIKE 'A%'; -- Names starting with A

---

Intermediate SQL Interview Questions with Answers (11–20)

11. What is a subquery? Provide an example.
    Answer: A subquery is a query nested inside another query.

    SELECT name FROM employees 
    WHERE salary > (SELECT AVG(salary) FROM employees);
    

12. What is a correlated subquery?
    Answer: A correlated subquery depends on the outer query for its values.

    SELECT e1.name 
    FROM employees e1 
    WHERE salary > (SELECT AVG(salary) 
                    FROM employees e2 
                    WHERE e1.department = e2.department);
    

13. What are the different types of joins in SQL?
    Answer:

    • INNER JOIN: Matching rows in both tables

    • LEFT JOIN: All rows from left + matches

    • RIGHT JOIN: All rows from right + matches

    • FULL JOIN: All rows when there is a match in either

    • CROSS JOIN: Cartesian product of both tables

    • SELF JOIN: Table joined with itself

14. How do you find duplicate records in a table?

    SELECT column1, COUNT(*) 
    FROM table_name 
    GROUP BY column1 
    HAVING COUNT(*) > 1;
    

15. How do you get the second highest salary from a table?

    SELECT MAX(salary) 
    FROM employees 
    WHERE salary < (SELECT MAX(salary) FROM employees);
    

16. What is normalization? Explain 1NF, 2NF, 3NF.
    Answer:

    • 1NF: No repeating groups, atomic values

    • 2NF: 1NF + no partial dependency on composite keys

    • 3NF: 2NF + no transitive dependencies

17. What is denormalization and when is it used?
    Answer: Denormalization combines tables to reduce joins and improve read performance, typically in reporting systems.

18. What is the difference between DELETE, TRUNCATE, and DROP?

    • DELETE: Deletes rows (can be rolled back)

    • TRUNCATE: Deletes all rows quickly (cannot be rolled back in some DBs)

    • DROP: Deletes entire table or object

19. How can you change a column's data type in a table?

    ALTER TABLE table_name 
    MODIFY column_name NEW_DATATYPE;
    -- or in some DBs
    ALTER TABLE table_name 
    ALTER COLUMN column_name TYPE NEW_DATATYPE;
    

20. What are constraints in SQL?
    Answer: Rules enforced on data columns:

    • NOT NULL: Disallows nulls

    • UNIQUE: No duplicates

    • CHECK: Enforces conditions

    • DEFAULT: Sets default value

    • PRIMARY KEY and FOREIGN KEY: Integrity constraints

---

Advanced SQL Interview Questions with Answers (21–30)

21. What is a window function? Provide an example using ROW_NUMBER().
    Answer: Window functions perform calculations across a set of rows related to the current row.

SELECT name, department, 
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

22. What is a Common Table Expression (CTE)? How is it different from a subquery?
    Answer: A CTE is a temporary result set used within a query. It improves readability and can be recursive.

WITH DeptAvg AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
)
SELECT * FROM DeptAvg WHERE avg_sal > 60000;

23. How do you perform pagination in SQL?
    Answer (MySQL/SQLite):

SELECT * FROM employees LIMIT 10 OFFSET 20; -- Rows 21 to 30

Answer (PostgreSQL/SQL Server):

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM employees
) sub WHERE rn BETWEEN 21 AND 30;

24. What are indexes? How do they improve performance?
    Answer: Indexes are data structures that speed up search queries by allowing fast lookups, especially on large tables. However, they slow down insert/update operations.

25. What is the difference between clustered and non-clustered indexes?

• Clustered Index: Determines the physical order of data (1 per table).

• Non-clustered Index: Separate from data, contains pointers to the actual rows (can have multiple).

26. What is a transaction? What are the ACID properties?
    Answer: A transaction is a sequence of operations treated as a single logical unit.

• Atomicity: All or none

• Consistency: DB remains valid

• Isolation: Transactions don't interfere

• Durability: Changes persist after commit

27. What is the difference between ROLLBACK and COMMIT?

• COMMIT: Saves changes permanently

• ROLLBACK: Reverts changes to the last SAVEPOINT or transaction start

28. How do you handle deadlocks in SQL?
    Answer:

• Keep transactions short

• Access tables in consistent order

• Use lower isolation levels if possible

• Use database logs or monitoring tools to detect deadlocks

29. What are stored procedures and how are they used?
    Answer: Stored procedures are precompiled SQL code blocks that perform actions. They improve performance and encapsulate logic.

CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
BEGIN
  SELECT * FROM employees WHERE department = dept_name;
END;

30. How do you optimize a slow SQL query?
    Answer:

• Use proper indexes

• Avoid SELECT *

• Use joins wisely

• Filter early with WHERE

• Check execution plan

• Avoid functions in WHERE clauses

• Break complex queries into CTEs or temp tables

---

Conclusion

Mastering SQL is essential for technical interviews. Understanding these 30 questions and their answers will help you tackle both theoretical and practical challenges confidently.