SQL Interview Questions and Answers (100 Q&A;)

■ Basic SQL Questions

1. What is SQL?
Answer: SQL (Structured Query Language) is a standard language used to communicate with
relational databases for storing, manipulating, and retrieving data.

2. What are the different types of SQL commands?

Answer: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control
Language), TCL (Transaction Control Language), and DQL (Data Query Language).

3. What is the difference between DDL, DML, DCL, and TCL?

Answer: DDL defines database structure (e.g., CREATE, ALTER, DROP). DML manipulates data
(e.g., SELECT, INSERT, UPDATE, DELETE). DCL controls access (e.g., GRANT, REVOKE). TCL
manages transactions (e.g., COMMIT, ROLLBACK, SAVEPOINT).

4. What is a primary key?

Answer: A column or a combination of columns that uniquely identifies each row in a table. It must be
unique and NOT NULL.

5. What is a foreign key?

Answer: A column in one table that references the primary key (or unique key) of another table to
enforce referential integrity.

6. What is a unique key?

Answer: A constraint ensuring all values in a column (or set of columns) are distinct. Tables can have
multiple UNIQUE constraints.

7. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before aggregation; HAVING filters groups after aggregation (used with
GROUP BY).

8. What is the difference between CHAR and VARCHAR?

Answer: CHAR is fixed-length (pads with spaces); VARCHAR is variable-length (stores only the
actual characters).

9. What are constraints in SQL?

Answer: Rules that enforce data integrity: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY,
CHECK, DEFAULT.

10. What is normalization?

Answer: A process of structuring tables and relationships to reduce redundancy and improve data
integrity (e.g., 1NF, 2NF, 3NF, BCNF).

■ Table and Data Manipulation

11. How do you create a table in SQL?

Page 1
Answer: Use CREATE TABLE with column definitions.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
department VARCHAR(50)
);

12. How do you insert data into a table?

Answer: Use INSERT INTO with column list and VALUES.
INSERT INTO employees (id, name, age, department)
VALUES (1, 'Alice', 30, 'HR');

13. How do you update a record in a table?

Answer: Use UPDATE with a WHERE clause to limit affected rows.
UPDATE employees
SET department = 'IT'
WHERE id = 1;

14. How do you delete a record from a table?

Answer: Use DELETE with a WHERE clause.
DELETE FROM employees WHERE id = 1;

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

Answer: DELETE removes selected rows and can be rolled back (if within a transaction). TRUNCATE
removes all rows, is minimally logged, and usually cannot be rolled back. DROP removes the table
and its structure entirely.

16. How to alter a table structure?

Answer: Use ALTER TABLE to add/modify/drop columns or constraints.
ALTER TABLE employees ADD salary DECIMAL(10,2);

17. How to rename a table?

Answer: Use a RENAME or ALTER statement depending on the RDBMS.
-- MySQL / MariaDB
RENAME TABLE employees TO employees_old;

-- PostgreSQL / SQL Server (via ALTER)

ALTER TABLE employees RENAME TO employees_old;

18. How to add a column to an existing table?

Answer: Use ALTER TABLE ... ADD COLUMN.
ALTER TABLE employees ADD COLUMN joined_on DATE;

19. How to remove a column from a table?

Answer: Use ALTER TABLE ... DROP COLUMN (supported in most modern RDBMS).
ALTER TABLE employees DROP COLUMN joined_on;

20. How to change the data type of a column?

Answer: Use ALTER TABLE ... ALTER/MODIFY COLUMN depending on RDBMS.
-- MySQL

Page 2
 ALTER TABLE employees MODIFY name VARCHAR(100);

-- PostgreSQL
ALTER TABLE employees ALTER COLUMN name TYPE VARCHAR(100);

■ SQL Queries (SELECT)

21. What is the syntax for a basic SELECT query?
Answer: Use SELECT with a column list (or *), plus FROM.
SELECT * FROM employees;

22. How do you select distinct records?

Answer: Use DISTINCT to remove duplicate rows.
SELECT DISTINCT department FROM employees;

23. How to use the LIKE operator?

Answer: LIKE does pattern matching with % (wildcard for many) and _ (single character).
SELECT * FROM employees WHERE name LIKE 'A%';

24. How do you filter records using WHERE?

Answer: Add conditions with comparison operators.
SELECT * FROM employees WHERE age > 30;

25. How to use logical operators AND, OR, NOT?

Answer: Combine multiple conditions logically.
SELECT * FROM employees WHERE age > 25 AND department = 'HR';

26. How to sort data using ORDER BY?

Answer: Specify ORDER BY with ASC (default) or DESC.
SELECT * FROM employees ORDER BY name ASC;

27. How to limit rows returned (LIMIT or TOP)?

Answer: Use LIMIT in MySQL/PostgreSQL and TOP in SQL Server.
-- MySQL/PostgreSQL
SELECT * FROM employees LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM employees;

28. How do you use BETWEEN, IN, and NOT IN?

Answer: Use BETWEEN for ranges, IN for set membership, NOT IN for exclusions.
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
SELECT * FROM employees WHERE department IN ('HR', 'IT');
SELECT * FROM employees WHERE id NOT IN (1,2,3);

29. What is the IS NULL and IS NOT NULL usage?

Answer: Check for NULLs explicitly with IS NULL / IS NOT NULL.
SELECT * FROM employees WHERE salary IS NULL;

30. How do you alias a column or table?

Page 3
Answer: Use AS to rename columns/tables in the result for readability.
SELECT name AS employee_name FROM employees e;

■ Aggregate Functions
31. What are aggregate functions in SQL?
Answer: Functions that operate on multiple rows and return a single value: COUNT, SUM, AVG, MIN,
MAX.

32. How to use COUNT()?

Answer: COUNT returns the number of rows (or non-NULL values for a column).
SELECT COUNT(*) FROM employees;

33. How to use SUM()?

Answer: SUM adds numeric values.
SELECT SUM(salary) FROM employees;

34. How to use AVG()?

Answer: AVG returns the average of numeric values.
SELECT AVG(age) FROM employees;

35. How to use MAX() and MIN()?

Answer: MAX returns the largest value; MIN returns the smallest.
SELECT MAX(salary), MIN(salary) FROM employees;

36. What is the difference between COUNT(*) and COUNT(column)?

Answer: COUNT(*) counts all rows, including those with NULLs. COUNT(column) counts only
non-NULL values in that column.

37. How to group results using GROUP BY?

Answer: GROUP BY aggregates rows sharing a column value.
SELECT department, COUNT(*) FROM employees GROUP BY department;

38. What is the use of HAVING with GROUP BY?

Answer: HAVING filters groups after aggregation.
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

39. Can you use WHERE and GROUP BY in the same query?
Answer: Yes. WHERE filters rows before grouping; GROUP BY groups them.
SELECT department, AVG(salary)
FROM employees
WHERE age > 30
GROUP BY department;

40. Can aggregate functions be used with DISTINCT?

Answer: Yes, to aggregate only unique values.

Page 4
 SELECT COUNT(DISTINCT department) FROM employees;

■ Joins
41. What is a JOIN in SQL?
Answer: A JOIN combines rows from two or more tables based on related columns.

42. Explain INNER JOIN.

Answer: Returns rows that have matching values in both tables.
SELECT *
FROM A INNER JOIN B
ON A.id = B.id;

43. Explain LEFT JOIN.

Answer: Returns all rows from the left table and matching rows from the right table; unmatched right
rows are NULL.
SELECT *
FROM A LEFT JOIN B
ON A.id = B.id;

44. Explain RIGHT JOIN.

Answer: Returns all rows from the right table and matching rows from the left table; unmatched left
rows are NULL.
SELECT *
FROM A RIGHT JOIN B
ON A.id = B.id;

45. Explain FULL OUTER JOIN.

Answer: Returns all rows when there is a match in either table; unmatched parts are NULL.
SELECT *
FROM A FULL OUTER JOIN B
ON A.id = B.id;

46. What is a CROSS JOIN?

Answer: Returns the Cartesian product of both tables (all combinations).
SELECT * FROM A CROSS JOIN B;

47. What is a SELF JOIN?

Answer: A table is joined to itself, often for hierarchical relationships.
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

48. Difference between INNER JOIN and OUTER JOIN?

Answer: INNER JOIN returns only matching rows. OUTER JOIN returns matching rows plus the
non-matching rows from one (LEFT/RIGHT) or both (FULL) sides.

49. How to join more than two tables?

Answer: Chain multiple JOIN clauses with appropriate ON conditions.
SELECT *

Page 5
 FROM A
JOIN B ON A.id = B.id
JOIN C ON B.cid = C.cid;

50. What is the difference between JOIN and UNION?

Answer: JOIN combines columns horizontally (adds columns). UNION combines result sets vertically
(adds rows).

■ Subqueries and Nested Queries

51. What is a subquery?
Answer: A query nested inside another SQL statement (SELECT/INSERT/UPDATE/DELETE).

52. What are the types of subqueries?

Answer: Single-row, multi-row, correlated, and nested subqueries.

53. Can a subquery return multiple rows?

Answer: Yes—used with IN, ANY, ALL, or EXISTS operators.

54. How to use subquery in SELECT?

Answer: As a scalar subquery in the SELECT list.
SELECT name,
(SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;

55. How to use subquery in WHERE clause?

Answer: Compare a column to a subquery result.
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

56. How to use subquery in FROM clause?

Answer: Place a subquery in FROM and alias it, then query it like a table.
SELECT avg_salary
FROM (
SELECT AVG(salary) AS avg_salary FROM employees
) AS sub;

57. What is a correlated subquery?

Answer: A subquery that references columns from the outer query; it runs once per outer row.
SELECT e.name
FROM employees e
WHERE salary > (
SELECT AVG(salary) FROM employees WHERE department = e.department
);

58. What’s the difference between a correlated and a non-correlated subquery?

Answer: Correlated subqueries depend on the outer query and execute per outer row; non-correlated
subqueries run once and return a standalone result.

59. Can we use GROUP BY in a subquery?

Page 6
Answer: Yes. GROUP BY can be used inside subqueries for intermediate aggregation.

60. Can we use LIMIT in a subquery?

Answer: Yes, if the database supports it (e.g., MySQL, PostgreSQL).

■ Set Operations
61. What is the use of UNION?
Answer: UNION combines the results of two queries and removes duplicates.

62. What is the difference between UNION and UNION ALL?

Answer: UNION removes duplicates; UNION ALL keeps duplicates and is typically faster.

63. What is the INTERSECT operator?

Answer: INTERSECT returns only the rows common to both queries.

64. What is the EXCEPT or MINUS operator?

Answer: Returns rows from the first query that are not present in the second query.

65. Can you use ORDER BY with UNION?

Answer: Yes—apply ORDER BY once at the end of the combined query.

66. Can you perform set operations on tables with different columns?
Answer: No. The number of columns and their data types/positions must match.

67. How to handle duplicates in UNION?

Answer: Use UNION ALL to retain duplicates or apply DISTINCT/aggregation to deduplicate.

68. When should you use UNION ALL over UNION?

Answer: When duplicates are acceptable or for performance reasons (avoiding duplicate-elimination
overhead).

69. Explain use case for INTERSECT.

Answer: Find common entities across queries, e.g., customers who bought both Product A and
Product B.

70. What are the limitations of set operations?

Answer: Matching column count/types required; some databases may not support all operators.

■ Indexes and Views

71. What is an index?
Answer: A data structure that speeds up data retrieval at the cost of additional storage and write
overhead.

72. How do indexes improve performance?

Answer: They allow the engine to locate rows quickly without scanning the entire table.

73. What are the types of indexes?

Page 7
Answer: Clustered, non-clustered, unique, composite (multi-column), and full-text (varies by
RDBMS).

74. What is a clustered vs. non-clustered index?

Answer: A clustered index defines the physical order of data rows; a non-clustered index stores key
pointers to data rows.

75. What is a view?

Answer: A virtual table defined by a SELECT query.

76. How to create a view?

Answer: Use CREATE VIEW with a defining SELECT.
CREATE VIEW dept_summary AS
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

77. Can a view be updated?

Answer: Yes if it is simple (single table, no aggregation/distinct), otherwise it may be read-only.

78. What are materialized views?

Answer: Views whose results are stored physically and refreshed periodically or on demand.

79. When to use a view?

Answer: For abstraction, security (column/row restriction), and simplifying complex queries.

80. Difference between a view and a table?

Answer: A view is virtual and stores no data (unless materialized); a table physically stores rows.

■ Stored Procedures and Functions

81. What is a stored procedure?
Answer: A named, precompiled set of SQL statements stored in the database.

82. How to create a stored procedure?

Answer: Syntax varies by RDBMS; define a name and block of statements.
-- SQL Server
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT * FROM employees;
END;
GO

83. How to execute a stored procedure?

Answer: Use EXEC/EXECUTE or CALL depending on RDBMS.
-- SQL Server
EXEC GetEmployees;

-- MySQL
CALL GetEmployees();

Page 8
84. What is a function in SQL?
Answer: A routine that accepts input parameters and returns a value; can be used inside queries.

85. Difference between a stored procedure and a function?

Answer: Procedures may not return values and can perform transactions; functions must return a
value and are used in expressions.

86. What are input/output parameters in stored procedures?

Answer: Parameters used to pass values into a procedure and receive values back out.
-- SQL Server
CREATE PROCEDURE AddTwo
@a INT,
@b INT,
@sum INT OUTPUT
AS
BEGIN
SET @sum = @a + @b;
END;

87. Can you call a function from a procedure?

Answer: Yes. Functions are commonly called within procedures and other SQL statements.

88. What are triggers?

Answer: Database objects that automatically execute in response to DML events
(INSERT/UPDATE/DELETE).

89. How are triggers used in SQL?

Answer: To enforce business rules, maintain audit logs, or validate data automatically.

90. What is the difference between a trigger and a procedure?

Answer: Triggers fire automatically on data changes; procedures execute only when explicitly called.

■ Advanced Concepts
91. What is a CTE (Common Table Expression)?
Answer: A named temporary result set defined within a single statement using WITH; improves
readability and supports recursion.
WITH cte AS (
SELECT * FROM employees
)
SELECT * FROM cte;

92. Difference between CTE and subquery?

Answer: CTEs improve readability and can be referenced multiple times; subqueries are nested and
not reusable.

93. What is window function in SQL?

Answer: A function that performs calculations across a set of rows related to the current row without
collapsing them (OVER clause).

94. How to use ROW_NUMBER() in SQL?

Page 9
Answer: Apply ROW_NUMBER() OVER an ORDER BY to assign sequential numbers.
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn, name
FROM employees;

95. What is RANK() and DENSE_RANK()?

Answer: RANK() assigns the same rank to ties leaving gaps; DENSE_RANK() assigns same rank to
ties without gaps.

96. Explain LEAD() and LAG() functions.

Answer: LEAD accesses a subsequent row's value; LAG accesses a previous row's value.
SELECT name, salary,
LAG(salary) OVER (ORDER BY id) AS prev_sal,
LEAD(salary) OVER (ORDER BY id) AS next_sal
FROM employees;

97. What is recursion in SQL?

Answer: Implemented via recursive CTEs to traverse hierarchies such as org charts or parent-child
trees.
WITH RECURSIVE org AS (
SELECT id, manager_id, name, 0 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name, o.level+1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

98. What is dynamic SQL?

Answer: SQL constructed as text at runtime and executed via EXEC/EXECUTE (or prepared
statements).

99. How do you handle NULL values in SQL?

Answer: Use IS NULL/IS NOT NULL to test; COALESCE/IFNULL/NVL to substitute defaults in
expressions.
SELECT COALESCE(middle_name, '') AS middle_name FROM people;

100. How to optimize slow SQL queries?

Answer: Create/select appropriate indexes; avoid SELECT *; filter early; examine execution plans;
rewrite subqueries/joins; limit result sets; batch writes; and ensure up-to-date statistics.

Page 10