SQL interview questions
1. What is SQL?
SQL (Structured Query Language) is used to interact with relational databases for querying,
inserting, updating, and deleting data, as well as managing schema and permissions.
2. Difference between WHERE and HAVING?
WHERE filters rows before aggregation, while HAVING filters results after aggregation.
SELECT dept, COUNT(*)
FROM emp
WHERE salary > 5000
GROUP BY dept
HAVING COUNT(*) > 5;
3. What is a Primary Key?
A Primary Key uniquely identifies each row in a table and does not allow NULL or duplicate
values.
4. What is a Foreign Key?
A Foreign Key creates a relationship between two tables and enforces referential integrity.
5. Difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching records from both tables.
LEFT JOIN returns all records from the left table and matching rows from the right table (NULL if
no match).
6. What is normalization?
Normalization organizes data to reduce redundancy and improve integrity (1NF, 2NF, 3NF).
7. What is denormalization?
Denormalization intentionally introduces redundancy to improve read performance, often used
in reporting systems.
8. What is an index?
An index improves query performance by enabling faster data retrieval, similar to an index in a
book.
9. Difference between DELETE, TRUNCATE, and DROP?
DELETE removes specific rows and can be rolled back.
TRUNCATE removes all rows quickly and usually cannot be rolled back.
DROP deletes the entire table including its structure.
10. What is a View?
A View is a virtual table based on a SQL query. It does not store data physically unless
materialized.
11. What is a Stored Procedure?
A Stored Procedure is a precompiled SQL block stored in the database for reuse and better
performance.
12. What is a Trigger?
A Trigger is automatically executed when an event like INSERT, UPDATE, or DELETE occurs on a
table.
13. What is a Subquery?
A subquery is a query nested inside another query.
SELECT name
FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp);
14. What is a CTE (Common Table Expression)?
A CTE is a temporary result set defined using WITH to improve query readability and structure.
WITH AvgSal AS (
SELECT AVG(salary) AS avg_sal FROM emp
)
SELECT * FROM emp WHERE salary > (SELECT avg_sal FROM AvgSal);
15. What are window functions?
Window functions perform calculations across a set of rows without collapsing them into
groups.
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM emp;
16. Difference between RANK() and ROW_NUMBER()?
RANK() assigns the same rank for ties and skips numbers.
ROW_NUMBER() assigns a unique sequential number without skipping.
17. What is a composite key?
A composite key is a combination of two or more columns used to uniquely identify a row.
18. What is ACID property?
Atomicity ensures all operations complete or none.
Consistency ensures valid data state.
Isolation prevents interference between transactions.
Durability ensures changes are permanent.
19. What is a transaction?
A transaction is a group of SQL operations executed as a single unit.
BEGIN;
UPDATE acc SET balance = balance - 100 WHERE id = 1;
UPDATE acc SET balance = balance + 100 WHERE id = 2;
COMMIT;
20. What are indexing disadvantages?
Indexes consume storage and can slow down INSERT, UPDATE, and DELETE operations.
21. Difference between UNION and UNION ALL?
UNION removes duplicates.
UNION ALL keeps duplicates and is faster.
22. What is a self join?
A self join is when a table is joined with itself.
SELECT [Link], [Link]
FROM emp A
JOIN emp B ON A.manager_id = [Link];
23. What is a correlated subquery?
A correlated subquery depends on values from the outer query and executes once per row.
SELECT name
FROM emp e1
WHERE salary > (
SELECT AVG(salary)
FROM emp e2
WHERE [Link] = [Link]
);
24. What is SQL execution order?
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
25. How to optimize SQL queries?
Use indexes efficiently, avoid SELECT *, filter early using WHERE, prefer joins over unnecessary
subqueries, and analyze execution plans.