10 Common SQL Interview Questions Every Developer Should Know

Introduction

SQL is one of the most important skills for developers, especially those working with databases and backend technologies. During SQL interviews, employers often assess your understanding of database concepts, query writing, and optimization techniques.

In this article, we'll explore 10 commonly asked SQL interview questions with clear explanations and practical examples.

1. What Is the Difference Between DELETE, TRUNCATE, and DROP?

DELETE

Removes specific rows.
Supports the WHERE clause.
Can be rolled back within a transaction (database-dependent).

DELETE FROM Employees
WHERE EmployeeId = 5;

TRUNCATE

Removes all rows from a table.
Faster than DELETE for removing all records.
Does not support a WHERE clause.

TRUNCATE TABLE Employees;

DROP

Deletes the entire table, including its structure and data.

DROP TABLE Employees;

Interview Tip: DROP removes both the table definition and its data, while DELETE and TRUNCATE preserve the table structure.

2. What Is the Difference Between WHERE and HAVING?

WHERE

Filters individual rows before grouping.

SELECT *
FROM Employees
WHERE Salary > 50000;

HAVING

Filters grouped results after the GROUP BY operation.

SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

3. What Is the Difference Between INNER JOIN and LEFT JOIN?

INNER JOIN

Returns only matching records from both tables.

SELECT *
FROM Orders O
INNER JOIN Customers C
ON O.CustomerId = C.CustomerId;

LEFT JOIN

Returns all rows from the left table and matching rows from the right table. If no match exists, the right-side columns contain NULL.

SELECT *
FROM Orders O
LEFT JOIN Customers C
ON O.CustomerId = C.CustomerId;

4. What Is the Difference Between UNION and UNION ALL?

UNION

Combines results from multiple queries.
Removes duplicate rows.

SELECT Name FROM Customers
UNION
SELECT Name FROM Suppliers;

UNION ALL

Combines results from multiple queries.
Keeps duplicate rows.
Generally performs faster than UNION.

SELECT Name FROM Customers
UNION ALL
SELECT Name FROM Suppliers;

Performance Tip: Use UNION ALL when duplicate removal is unnecessary.

5. What Are Primary Key and Foreign Key?

Primary Key

Uniquely identifies each row in a table.
Cannot contain NULL values.
Ensures entity integrity.

Foreign Key

Creates a relationship between two tables.
Enforces referential integrity.

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT FOREIGN KEY REFERENCES Customers(CustomerId)
);

6. What Is the Difference Between CHAR and VARCHAR?

CHAR

Fixed-length data type.
Pads unused characters with spaces.

Name CHAR(20)

VARCHAR

Variable-length data type.
Stores only the actual characters entered.

Name VARCHAR(20)

Best Practice: Use VARCHAR unless a fixed-length field is specifically required.

7. What Is an Index?

An index improves query performance by allowing the database engine to locate rows more efficiently.

CREATE INDEX IX_Employee_Name
ON Employees(Name);

Benefits

Faster searches
Faster filtering
Improved sorting performance

Note: Too many indexes can slow down INSERT, UPDATE, and DELETE operations because indexes must also be maintained.

8. What Is the Difference Between COUNT(*) and COUNT(column)?

COUNT(*)

Counts all rows, including rows containing NULL values.

SELECT COUNT(*)
FROM Employees;

COUNT(column)

Counts only rows where the specified column is not NULL.

SELECT COUNT(Email)
FROM Employees;

9. What Is a Stored Procedure?

A stored procedure is a precompiled collection of SQL statements stored in the database that can be executed repeatedly.

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT *
    FROM Employees;
END;

Execute the stored procedure:

EXEC GetEmployees;

Advantages

Improved code reusability
Better maintainability
Enhanced security
Potential performance benefits

10. What Is Normalization?

Normalization is the process of organizing data to reduce redundancy and improve data integrity.

Common Normal Forms

First Normal Form (1NF): Remove repeating groups.
Second Normal Form (2NF): Remove partial dependencies.
Third Normal Form (3NF): Remove transitive dependencies.

Proper normalization minimizes duplicate data and keeps the database consistent.

Quick Revision Table

Question	Key Takeaway
DELETE vs TRUNCATE vs DROP	Row deletion vs table deletion
WHERE vs HAVING	Filter rows vs filter groups
INNER JOIN vs LEFT JOIN	Matching rows vs all left rows
UNION vs UNION ALL	Remove duplicates vs keep duplicates
Primary Key vs Foreign Key	Unique identifier vs relationship
CHAR vs VARCHAR	Fixed length vs variable length
Index	Improves query performance
COUNT(*) vs COUNT(column)	All rows vs non-NULL values
Stored Procedure	Reusable SQL code
Normalization	Reduce redundancy and improve data integrity

Conclusion

These SQL interview questions cover concepts that are frequently discussed during technical interviews for backend, full-stack, and database developer roles. Rather than memorizing answers, focus on understanding why each SQL feature exists, when to use it, and the trade-offs involved.

Regular practice with SQL queries, execution plans, indexing strategies, and real-world databases will help strengthen your SQL skills and improve your confidence during interviews.