SQL Interview Questions and Answers (2025 Edition)

August 12, 2025

1. What is SQL, and what is it used for?
SQL (Structured Query Language) is used to store, retrieve, manipulate, and manage data in relational databases.
Example:
SELECT * FROM employees;

2. Difference between DBMS and RDBMS

DBMS: General database management software, may store data in files or tables, does not enforce relationships.
RDBMS: Stores data in tables with relationships (keys/constraints). Examples: MySQL, PostgreSQL, SQL Server.

3. What is a primary key? Can a table have more than one?
A primary key uniquely identifies each row in a table. A table can have only one primary key, but it can be composite (multiple columns).
Example:
CREATE TABLE students (student_id INT PRIMARY KEY, name VARCHAR(50));

4. What is a foreign key?
A foreign key links a column in one table to the primary key of another, ensuring referential integrity.
Example:
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

5. What are tables and columns in SQL?

Table: Collection of related data stored as rows and columns.
Column: Defines the data type and attribute for each field.

6. What is a constraint in SQL? Types?
Constraints enforce rules on data. Types include: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

7. What is a candidate key?
A column or set of columns that can uniquely identify a row. The primary key is chosen from candidate keys.

8. What is a composite key?
A primary key made from two or more columns.
Example:
PRIMARY KEY (order_id, product_id)

9. Difference between CHAR and VARCHAR

CHAR: Fixed-length storage, padded with spaces if shorter.
VARCHAR: Variable-length storage, saves space.

10. Difference between NULL and 0

NULL: Unknown or missing value.
0: Numeric zero. NULL is not equal to 0.

11. Types of SQL statements

DDL: CREATE, ALTER, DROP
DML: SELECT, INSERT, UPDATE, DELETE
DCL: GRANT, REVOKE
TCL: COMMIT, ROLLBACK

12. Difference between DELETE, TRUNCATE, and DROP

DELETE: Removes rows, can use WHERE, can be rolled back.
TRUNCATE: Removes all rows, resets identity, faster, cannot be rolled back in some DBs.
DROP: Removes table structure and data.

13. What does SELECT do?
Retrieves data from one or more tables.
Example:
SELECT name, salary FROM employees WHERE department = 'Sales';

14. Difference between WHERE and HAVING

WHERE: Filters rows before aggregation.
HAVING: Filters after aggregation.

15. Difference between GROUP BY and ORDER BY

GROUP BY: Groups rows for aggregation.
ORDER BY: Sorts the result set.

16. Use of DISTINCT keyword
Removes duplicate values.
Example:
SELECT DISTINCT department FROM employees;

17. Order of SQL execution
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

18. Types of JOINs
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN.

19. Difference between INNER JOIN and OUTER JOIN

INNER JOIN: Returns only matching rows.
OUTER JOIN: Returns matches plus non-matching rows.

20. Difference between LEFT JOIN and RIGHT JOIN

LEFT JOIN: All rows from left table + matches from right.
RIGHT JOIN: All rows from right table + matches from left.

21. What is a self join?
A join where a table is joined with itself.
Example:
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;

22. What is a CROSS JOIN?
Returns the Cartesian product of two tables (all combinations).

23. What is an index?
A data structure that speeds up data retrieval. Uses extra storage.

24. Clustered vs. Non-clustered index

Clustered: Defines physical order of rows. One per table.
Non-clustered: Separate structure with pointers to rows. Multiple allowed.

25. How do indexes improve performance?
Indexes allow faster lookups and reduce full table scans.

26. What is an execution plan?
A detailed strategy of how a database will execute a query.

27. Query optimization steps
Use indexes, avoid SELECT *, limit rows, check execution plan, optimize joins.

28. What is a subquery?
A query inside another query.
Example:
SELECT name FROM employees WHERE dept_id = (SELECT id FROM departments WHERE name = 'Sales');

29. Correlated vs. Nested subquery

Nested: Can run independently.
Correlated: References outer query, runs per row.

30. What is a view?
A virtual table based on a query.
Example:
CREATE VIEW active_customers AS SELECT * FROM customers WHERE active = 1;

31. What is a materialized view?
A materialized view stores the result of a query physically and refreshes it periodically.
It improves performance for complex queries but uses extra storage.

32. Difference between UNION and UNION ALL

UNION: Combines results and removes duplicates.
UNION ALL: Combines results and keeps duplicates (faster).

33. Difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)

COUNT(*): Counts all rows.
COUNT(column): Counts only non-NULL values in that column.
COUNT(DISTINCT column): Counts unique non-NULL values.

34. What are aggregate functions?
Aggregate functions return a single value after performing a calculation on a set of values.
Examples: SUM, AVG, MIN, MAX, COUNT.

35. What are window functions?
Window functions perform calculations across a set of rows without collapsing them.
Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER (…).

36. What is a CTE (Common Table Expression)?
A CTE is a temporary result set that can be referenced within a SQL statement using the WITH keyword.
Example:
WITH dept_avg AS (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) SELECT * FROM dept_avg;

37. What is a transaction in SQL?
A transaction is a sequence of SQL operations that are executed as a single unit of work.

38. What are ACID properties?

Atomicity: All or nothing execution.
Consistency: Maintains database validity.
Isolation: Transactions do not interfere with each other.
Durability: Committed changes persist even after a crash.

39. Difference between COMMIT and ROLLBACK

COMMIT: Saves all changes made in a transaction.
ROLLBACK: Undoes all changes since the transaction started.

40. What is a savepoint?
A savepoint is a point in a transaction to which you can roll back without affecting the entire transaction.

41. What is a deadlock?
A deadlock occurs when two transactions wait for each other to release resources, causing both to stop.
Prevention: Keep transactions short, lock resources in a consistent order.

42. What is a stored procedure?
A stored procedure is a precompiled set of SQL statements stored in the database.
Benefits: Performance improvement, reusability, security.

43. What is a trigger?
A trigger is a special procedure that runs automatically in response to certain events on a table (INSERT, UPDATE, DELETE).

44. Types of triggers

BEFORE triggers
AFTER triggers
INSTEAD OF triggers (in some DBMS)

45. Difference between a stored procedure and a function

Stored procedure: Can return multiple results, may not return a value.
Function: Must return a single value and can be used in SQL expressions.

46. What is a CASE statement in SQL?
The CASE statement allows conditional logic inside queries.
Example:
SELECT name, CASE WHEN salary > 100000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;

47. What is normalization? Why is it important?
Normalization is the process of organizing data to reduce redundancy and improve integrity.
It involves dividing large tables into smaller related tables.

48. What is denormalization?
Denormalization is the process of combining normalized tables to improve query performance at the cost of redundancy.

49. Difference between OLTP and OLAP databases

OLTP: Handles day-to-day transactions (Insert, Update, Delete).
OLAP: Handles analytical queries and reporting.

50. How to find duplicate records in a table?
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

51. How to get the second highest salary?
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

52. How to find employees earning more than the average salary?
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

53. How to find employees with no manager?
SELECT name FROM employees WHERE manager_id IS NULL;

54. Find the department with the maximum employees
SELECT dept_id, COUNT(*) AS emp_count FROM employees GROUP BY dept_id ORDER BY emp_count DESC LIMIT 1;

55. Delete all records without deleting the table
DELETE FROM table_name;
or
TRUNCATE TABLE table_name;

56. Limit results to top N rows
MySQL/PostgreSQL: SELECT * FROM employees LIMIT N;
SQL Server: SELECT TOP N * FROM employees;

57. Find employees who joined in the last 6 months
MySQL:
SELECT * FROM employees WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
SQL Server:
SELECT * FROM employees WHERE hire_date >= DATEADD(month, -6, GETDATE());

58. How to partition a table?
Partitioning divides a table into smaller parts for performance.
Example in MySQL:
CREATE TABLE orders (order_id INT, order_date DATE) PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025));

SQL Interview Questions and Answers (2025 Edition)

Related Jobs