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
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
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
SELECT Name FROM Customers
UNION
SELECT Name FROM Suppliers;
UNION ALL
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
CREATE TABLE Orders
(
OrderId INT PRIMARY KEY,
CustomerId INT FOREIGN KEY REFERENCES Customers(CustomerId)
);
6. What Is the Difference Between CHAR and VARCHAR?
CHAR
Name CHAR(20)
VARCHAR
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
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
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.