Top 15 SQL Interview Questions and Answers for Freshers and Experienced
Structured Query Language (SQL) is one of the most in-demand skills for data analysts, software developers, and database administrators. Whether you are a fresher or an experienced professional, these SQL interview questions with simple answers and examples will help you prepare confidently.
1. What is SQL?
Answer:
SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases.
It helps you store, retrieve, update, and delete data efficiently.
Example:
SELECT * FROM Employees;
This command retrieves all records from the Employees table.
2. What is the difference between DELETE, TRUNCATE, and DROP commands?
Answer:
- DELETE: Removes specific rows from a table. (Can use WHERE clause)
- TRUNCATE: Removes all rows but keeps the table structure.
- DROP: Deletes the entire table (data + structure).
Example:
DELETE FROM Employees WHERE Department='HR';
TRUNCATE TABLE Employees;
DROP TABLE Employees;
3. What are Primary Key and Foreign Key?
Answer:
- Primary Key: Uniquely identifies each record in a table.
- Foreign Key: Links one table to another using a reference to the primary key.
Example:
CREATE TABLE Department(
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);
CREATE TABLE Employee(
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
DeptID INT FOREIGN KEY REFERENCES Department(DeptID)
);
4. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT JOIN: Returns all records from the left table and matched records from the right table.
Example:
SELECT E.EmpName, D.DeptName
FROM Employee E
INNER JOIN Department D ON E.DeptID = D.DeptID;
5. What is a Subquery?
Answer:
A Subquery is a query inside another SQL query. It helps to fetch data based on the result of another query.
Example:
SELECT EmpName
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
This query retrieves employees whose salary is above the company average.
6. What are Constraints in SQL?
Answer:
Constraints are rules applied on data columns to ensure accuracy and integrity.
Types include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.
Example:
CREATE TABLE Students(
ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18)
);
7. What is Normalization?
Answer:
Normalization is the process of organizing data to reduce redundancy and improve integrity.
Example:
- 1NF: Remove repeating groups.
- 2NF: Remove partial dependency.
- 3NF: Remove transitive dependency.
A table with student and course data split into separate tables follows normalization.
8. What is the difference between WHERE and HAVING?
Answer:
- WHERE filters rows before grouping.
- HAVING filters groups after aggregation.
Example:
SELECT Department, COUNT(*)
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 5;
9. Explain GROUP BY and ORDER BY with example.
Answer:
- GROUP BY: Groups rows based on a column.
- ORDER BY: Sorts the result in ascending or descending order.
Example:
SELECT Department, COUNT(EmpID)
FROM Employee
GROUP BY Department
ORDER BY COUNT(EmpID) DESC;
10. What are SQL Joins and their types?
Answer:
Joins combine rows from two or more tables based on related columns.
Types:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
11. What is a View in SQL?
Answer:
A View is a virtual table that displays data from one or more tables without storing it physically.
Example:
CREATE VIEW HighSalary AS
SELECT EmpName, Salary
FROM Employee
WHERE Salary > 80000;
12. What is the difference between CHAR and VARCHAR?
Answer:
- CHAR(n): Fixed-length string.
- VARCHAR(n): Variable-length string.
Example:
CHAR(10) always uses 10 bytes, while VARCHAR(10) uses only what’s needed.
13. How can you find duplicate records in a table?
Answer:
You can use GROUP BY with HAVING to identify duplicates.
Example:
SELECT EmpName, COUNT(*)
FROM Employee
GROUP BY EmpName
HAVING COUNT(*) > 1;
14. What are Indexes in SQL and why are they used?
Answer:
Indexes are used to speed up data retrieval.
However, they slow down INSERT and UPDATE operations.
Example:
CREATE INDEX idx_employee_name ON Employee(EmpName);
15. What are SQL Aggregate Functions?
Answer:
Aggregate functions perform calculations on multiple rows and return a single value.
Common functions: COUNT(), SUM(), AVG(), MIN(), MAX().
Example:
SELECT AVG(Salary) AS AvgSalary FROM Employee;
🏁 Conclusion
SQL is the foundation of database management and an essential skill for every IT professional. These 15 SQL interview questions and answers cover the most asked topics for both freshers and experienced candidates.
Practice them regularly, and you’ll be confident to handle any SQL-related interview question with ease.