Comprehensive SQL Interview Questions

(300+ Questions)
1. SQL Basics and Fundamentals (30 Questions)
1.​ What is SQL? What does it stand for?
2.​ What are the different types of SQL commands? Name them with examples.
3.​ What is DDL (Data Definition Language)? Give examples of DDL commands.
4.​ What is DML (Data Manipulation Language)? List DML commands.
5.​ What is DCL (Data Control Language)? What commands does it include?
6.​ What is TCL (Transaction Control Language)? Name its commands.
7.​ What is a database? How is it different from DBMS?
8.​ What is RDBMS? How does it differ from DBMS?
9.​ What is a table in SQL? What are its components?
10.​What is a record/row in a database table?
11.​What is a field/column in a database table?
12.​What is a primary key? What are its characteristics?
13.​What is a foreign key? How does it work?
14.​What is the difference between primary key and unique key?
15.​What is a composite key? When would you use it?
16.​What is a candidate key? How is it different from primary key?
17.​What is a super key? Provide an example.
18.​What is an alternate key?
19.​What are the different data types in SQL?
20.​What is NULL in SQL? How is it different from zero or empty string?
21.​What is the difference between CHAR and VARCHAR?
22.​What is the difference between DELETE and TRUNCATE?
23.​What is the difference between DROP and DELETE?
24.​What is SQL injection? How can you prevent it?
25.​What are SQL comments? How do you write them?
26.​What is case sensitivity in SQL?
27.​What are SQL operators? Name different types.
28.​What is the difference between = and == in SQL?
29.​What are aggregate functions? Give examples.
30.​What are scalar functions? Provide examples.

2. Basic Queries and SELECT Statements (25 Questions)

31.​Write the basic syntax of SELECT statement.
32.​How do you select all columns from a table?
33.​How do you select specific columns from a table?
34.​What is the WHERE clause? How is it used?
35.​What are comparison operators in SQL? List them.
36.​What are logical operators in SQL? Explain with examples.
37.​What is the LIKE operator? How do you use wildcards with it?
38.​What are wildcards in SQL? Explain % and _ wildcards.
39.​What is the BETWEEN operator? How does it work?
 40.​What is the IN operator? When would you use it?
41.​What is the NOT operator? Provide examples.
42.​How do you handle NULL values in WHERE clause?
43.​What is IS NULL and IS NOT NULL?
44.​What is the ORDER BY clause? How does it work?
45.​What is the difference between ASC and DESC in ORDER BY?
46.​How do you sort by multiple columns?
47.​What is the LIMIT clause? How is it used?
48.​What is the TOP clause in SQL Server?
49.​What is the DISTINCT keyword? When do you use it?
50.​What is the difference between DISTINCT and GROUP BY?
51.​How do you use aliases in SQL? What are column aliases?
52.​What are table aliases? Why are they useful?
53.​What is the difference between single quotes and double quotes in SQL?
54.​How do you search for a pattern in SQL?
55.​What is the difference between WHERE and HAVING clause?

3. Functions and Operators (35 Questions)

56.​What are string functions in SQL? Name 10 commonly used ones.
57.​What does the CONCAT function do? How do you use it?
58.​What is the difference between CONCAT and concatenation operator (||)?
59.​What does the SUBSTRING function do?
60.​What does the LENGTH or LEN function do?
61.​What does the UPPER function do? What about LOWER?
62.​What does the TRIM function do? What are LTRIM and RTRIM?
63.​What does the REPLACE function do?
64.​What does the LEFT and RIGHT function do?
65.​What are date functions in SQL? Name 10 important ones.
66.​What does the NOW() or GETDATE() function return?
67.​What does the DATEADD function do?
68.​What does the DATEDIFF function do?
69.​What does the EXTRACT function do?
70.​How do you get the current date without time?
71.​How do you format dates in SQL?
72.​What are numeric functions in SQL? List 10 of them.
73.​What does the ROUND function do? What about CEILING and FLOOR?
74.​What does the ABS function do?
75.​What does the MOD function do?
76.​What does the POWER function do?
77.​What does the SQRT function do?
78.​What are conditional functions? Explain CASE statement.
79.​What is the difference between CASE and IF statement?
80.​What does the COALESCE function do?
81.​What does the NULLIF function do?
82.​What does the ISNULL function do?
83.​What are conversion functions? Name some.
84.​What does the CAST function do?
85.​What does the CONVERT function do?
86.​What is the difference between CAST and CONVERT?
87.​What are system functions? Give examples.
 88.​What does the USER function return?
89.​What does the DATABASE() function return?
90.​What does the VERSION() function return?

4. Joins and Relationships (40 Questions)

91.​What are joins in SQL? Why are they important?
92.​What are the different types of joins? Explain each.
93.​What is INNER JOIN? How does it work?
94.​What is LEFT JOIN (LEFT OUTER JOIN)? Explain with example.
95.​What is RIGHT JOIN (RIGHT OUTER JOIN)? When would you use it?
96.​What is FULL OUTER JOIN? How does it differ from other joins?
97.​What is CROSS JOIN? What does it produce?
98.​What is SELF JOIN? Provide a practical example.
99.​What is NATURAL JOIN? How does it work?
100.​ What is the difference between INNER JOIN and OUTER JOIN?
101.​ What is the difference between LEFT JOIN and RIGHT JOIN?
102.​ What is the difference between JOIN and INNER JOIN?
103.​ When would you use CROSS JOIN?
104.​ How do you join three or more tables?
105.​ What is the performance difference between different types of joins?
106.​ What are join conditions? Why are they important?
107.​ What happens when you don't specify join conditions?
108.​ How do you handle NULL values in joins?
109.​ What is the difference between WHERE and ON clause in joins?
110.​ Can you use multiple conditions in ON clause?
111.​ What is equi join and non-equi join?
112.​ What is theta join?
113.​ What are semi joins and anti joins?
114.​ What is the difference between EXISTS and IN with joins?
115.​ How do you optimize join queries?
116.​ What is the join order? Does it matter?
117.​ What are join algorithms? Name different types.
118.​ What is nested loop join?
119.​ What is hash join?
120.​ What is merge join?
121.​ How do indexes affect join performance?
122.​ What is join elimination?
123.​ What are some common join pitfalls?
124.​ How do you troubleshoot slow join queries?
125.​ What is the maximum number of tables you can join?
126.​ Can you join tables from different databases?
127.​ What is federated join?
128.​ How do you join tables with different column names?
129.​ What is implicit vs explicit join syntax?
130.​ Why is explicit join syntax preferred?

5. Aggregate Functions and GROUP BY (25 Questions)

131.​ What are aggregate functions? List all of them.
 132.​ What does the COUNT function do? What are its variations?
133.​ What is the difference between COUNT(*) and COUNT(column)?
134.​ What does COUNT(DISTINCT column) do?
135.​ What does the SUM function do? Can it work with non-numeric data?
136.​ What does the AVG function do? How does it handle NULL values?
137.​ What does the MAX function do? Can it work with text data?
138.​ What does the MIN function do? How does it compare dates?
139.​ What is the GROUP BY clause? How does it work?
140.​ What is the relationship between GROUP BY and aggregate functions?
141.​ What is the HAVING clause? How is it different from WHERE?
142.​ Can you use WHERE and HAVING together? Provide example.
143.​ What is the order of execution: WHERE, GROUP BY, HAVING?
144.​ Can you group by multiple columns? Show example.
145.​ Can you use column aliases in GROUP BY?
146.​ Can you use aggregate functions without GROUP BY?
147.​ What happens when you use GROUP BY with non-aggregated columns?
148.​ What are window functions? How do they differ from aggregate functions?
149.​ What does PARTITION BY do in window functions?
150.​ What is OVER clause in SQL?
151.​ What are ranking functions? Name them.
152.​ What does ROW_NUMBER() do?
153.​ What does RANK() do? How is it different from ROW_NUMBER()?
154.​ What does DENSE_RANK() do?
155.​ What are the common errors with GROUP BY?

6. Subqueries and Nested Queries (30 Questions)

156.​ What is a subquery? What are its types?
157.​ What is a nested query? How is it different from subquery?
158.​ What is a correlated subquery? How does it work?
159.​ What is a non-correlated subquery?
160.​ What is a scalar subquery? When do you use it?
161.​ What is a multi-row subquery?
162.​ What is a multi-column subquery?
163.​ Where can you use subqueries in SQL?
164.​ Can you use subquery in SELECT clause?
165.​ Can you use subquery in WHERE clause?
166.​ Can you use subquery in HAVING clause?
167.​ Can you use subquery in FROM clause?
168.​ What is derived table or inline view?
169.​ What is the difference between EXISTS and IN?
170.​ When should you use EXISTS vs IN?
171.​ What is NOT EXISTS? How does it work?
172.​ What is NOT IN? What are its pitfalls with NULL values?
173.​ What is ANY operator in subqueries?
174.​ What is ALL operator in subqueries?
175.​ What is SOME operator? Is it same as ANY?
176.​ How do you optimize subqueries?
177.​ What is the difference between correlated and non-correlated subquery performance?
178.​ Can you nest subqueries? What's the limit?
179.​ What is common table expression (CTE)?
 180.​ How is CTE different from subquery?
181.​ What is recursive CTE? Provide example.
182.​ What are the advantages of using CTE?
183.​ Can you use multiple CTEs in one query?
184.​ What is the WITH clause?
185.​ How do you convert subquery to join and vice versa?

7. Data Modification (INSERT, UPDATE, DELETE) (25

Questions)
186.​ What is INSERT statement? Show its basic syntax.
187.​ How do you insert single row into a table?
188.​ How do you insert multiple rows in one statement?
189.​ How do you insert data from another table?
190.​ What is INSERT INTO SELECT statement?
191.​ How do you handle duplicate keys during insert?
192.​ What is ON DUPLICATE KEY UPDATE?
193.​ What is UPSERT operation?
194.​ What is MERGE statement? How does it work?
195.​ What is UPDATE statement? Show its syntax.
196.​ How do you update single column?
197.​ How do you update multiple columns at once?
198.​ How do you update with conditions?
199.​ How do you update using data from another table?
200.​ What is DELETE statement? How is it different from DROP?
201.​ How do you delete specific rows?
202.​ How do you delete all rows from a table?
203.​ What is the difference between DELETE and TRUNCATE?
204.​ What is CASCADE DELETE? How does it work?
205.​ Can you undo DELETE, UPDATE, INSERT operations?
206.​ What are the safety measures for data modification?
207.​ What is transaction in context of data modification?
208.​ How do you handle errors during bulk operations?
209.​ What is batch processing for large data modifications?
210.​ What are the performance considerations for data modification?

8. Database Design and Normalization (25 Questions)

211.​ What is database design? What are its principles?
212.​ What is normalization? Why is it important?
213.​ What are the different normal forms? Explain each.
214.​ What is 1NF (First Normal Form)? What are its rules?
215.​ What is 2NF (Second Normal Form)? How do you achieve it?
216.​ What is 3NF (Third Normal Form)? Provide example.
217.​ What is BCNF (Boyce-Codd Normal Form)?
218.​ What is 4NF (Fourth Normal Form)?
219.​ What is 5NF (Fifth Normal Form)?
220.​ What is denormalization? When would you use it?
221.​ What are the advantages and disadvantages of normalization?
222.​ What is functional dependency?
 223.​ What is partial dependency?
224.​ What is transitive dependency?
225.​ What is multivalued dependency?
226.​ What is join dependency?
227.​ What is referential integrity?
228.​ What are integrity constraints?
229.​ What is entity integrity?
230.​ What is domain integrity?
231.​ What is key integrity?
232.​ What is ER diagram? What are its components?
233.​ What is entity in ER model?
234.​ What is attribute in ER model?
235.​ What is relationship in ER model?

9. Indexes and Performance (30 Questions)

236.​ What is an index? Why are indexes important?
237.​ What are the different types of indexes?
238.​ What is clustered index? How does it work?
239.​ What is non-clustered index?
240.​ What is the difference between clustered and non-clustered index?
241.​ What is unique index? When do you create it?
242.​ What is composite index? When is it useful?
243.​ What is partial index?
244.​ What is filtered index?
245.​ What is covering index?
246.​ What is index scan vs index seek?
247.​ What is table scan? When does it occur?
248.​ How do indexes improve query performance?
249.​ What are the disadvantages of having too many indexes?
250.​ How do indexes affect INSERT, UPDATE, DELETE operations?
251.​ What is index fragmentation?
252.​ How do you maintain indexes?
253.​ What is index rebuild vs index reorganize?
254.​ What are index statistics? Why are they important?
255.​ How do you analyze query performance?
256.​ What is execution plan? How do you read it?
257.​ What are query hints? When should you use them?
258.​ What is cost-based optimization?
259.​ What is rule-based optimization?
260.​ What are some common performance tuning techniques?
261.​ What is query optimization?
262.​ What are some common causes of slow queries?
263.​ How do you identify bottlenecks in database performance?
264.​ What is database monitoring?
265.​ What are some key performance metrics for databases?

10. Constraints and Data Integrity (20 Questions)

266.​ What are constraints in SQL? Why are they important?
 267.​ What are the different types of constraints?
268.​ What is NOT NULL constraint?
269.​ What is UNIQUE constraint? How is it different from primary key?
270.​ What is PRIMARY KEY constraint?
271.​ What is FOREIGN KEY constraint? How does it maintain referential integrity?
272.​ What is CHECK constraint? Provide examples.
273.​ What is DEFAULT constraint?
274.​ How do you add constraints to existing table?
275.​ How do you remove constraints from a table?
276.​ What happens when constraint violation occurs?
277.​ What is CASCADE in foreign key constraints?
278.​ What is SET NULL in foreign key constraints?
279.​ What is SET DEFAULT in foreign key constraints?
280.​ What is RESTRICT in foreign key constraints?
281.​ Can you have multiple constraints on a single column?
282.​ What is the difference between column-level and table-level constraints?
283.​ What is deferred constraint checking?
284.​ What are assertion constraints?
285.​ How do constraints affect performance?

11. Transactions and Concurrency (20 Questions)

286.​ What is a transaction? What are ACID properties?
287.​ What does Atomicity mean in ACID properties?
288.​ What does Consistency mean in ACID properties?
289.​ What does Isolation mean in ACID properties?
290.​ What does Durability mean in ACID properties?
291.​ What are the transaction control commands?
292.​ What does COMMIT do?
293.​ What does ROLLBACK do?
294.​ What does SAVEPOINT do?
295.​ What is autocommit mode?
296.​ What are isolation levels? Name all of them.
297.​ What is READ UNCOMMITTED isolation level?
298.​ What is READ COMMITTED isolation level?
299.​ What is REPEATABLE READ isolation level?
300.​ What is SERIALIZABLE isolation level?
301.​ What are the problems in concurrent transactions?
302.​ What is dirty read?
303.​ What is non-repeatable read?
304.​ What is phantom read?
305.​ What are locks in database? What types exist?

12. Advanced SQL Topics (25 Questions)

306.​ What are views? How do you create them?
307.​ What are the advantages and disadvantages of views?
308.​ What is materialized view? How is it different from regular view?
309.​ Can you update data through views?
310.​ What are stored procedures? How do you create them?
311.​ What are the advantages of stored procedures?
312.​ What is the difference between stored procedure and function?
313.​ What are triggers? What are different types?
314.​ What is BEFORE trigger?
315.​ What is AFTER trigger?
316.​ What is INSTEAD OF trigger?
317.​ When should you use triggers?
318.​ What are cursors? When do you use them?
319.​ What is the difference between implicit and explicit cursors?
320.​ What are user-defined functions?
321.​ What is the difference between scalar and table-valued functions?
322.​ What is dynamic SQL? When do you use it?
323.​ What are the security concerns with dynamic SQL?
324.​ What is prepared statement?
325.​ What is connection pooling?
326.​ What is database partitioning?
327.​ What is horizontal vs vertical partitioning?
328.​ What is sharding?
329.​ What is replication?
330.​ What is backup and recovery?