Top 20 Interview SQL Questions and
Answers:
Freshers Edition
1. What is SQL, and why is it used?
Answer: SQL (Structured Query Language) is the standard programming language used to
manage and manipulate relational databases. It is used for tasks such as retrieving, updating,
and managing data stored in a database.
Example:
SELECT * FROM Employees;
2. What are the different types of SQL commands?
Answer: SQL commands are categorized as:
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
• DCL (Data Control Language): GRANT, REVOKE.
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.
Example (DML):
INSERT INTO Employees (Name, Age) VALUES ('John', 30);
3. What is the difference between DELETE and
TRUNCATE?
Answer:
• DELETE: Removes specific rows based on a condition and can be rolled back.
• TRUNCATE: Removes all rows in a table and cannot be rolled back. It is faster than
DELETE.
Example (DELETE):
DELETE FROM Employees WHERE Age > 50;
Example (TRUNCATE):
Connect/Follow – Ms. Pratiksha Meshram
TRUNCATE TABLE Employees;
4. What is a primary key?
Answer: A primary key is a column (or combination of columns) that uniquely identifies
each row in a table. It cannot contain NULL values.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100)
);
5. What is a foreign key?
Answer: A foreign key is a column that establishes a relationship between two tables by
referencing the primary key of another table.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EmployeeID INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
6. What are SQL joins? Name the types.
Answer: Joins are used to combine rows from two or more tables based on a related column.
• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN: Returns all rows from the left table and matching rows from the right
table.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left
table.
• FULL OUTER JOIN: Returns all rows when there is a match in either table.
Example (INNER JOIN):
SELECT Employees.Name, Orders.OrderID
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;
7. What is the difference between WHERE and HAVING?
Connect/Follow – Ms. Pratiksha Meshram
Answer:
• WHERE: Filters rows before grouping.
• HAVING: Filters groups after aggregation.
Example (WHERE):
SELECT * FROM Employees WHERE Age > 30;
Example (HAVING):
SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;
8. Write a query to fetch the second-highest salary from
the Employee table.
Answer:
SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
9. What is a subquery?
Answer: A subquery is a query nested within another query. It is used to perform operations
dynamically.
Example:
SELECT Name FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
10. What is a CTE (Common Table Expression)?
Answer: A CTE is a temporary result set defined within a SQL query using the WITH
keyword. It improves query readability.
Example:
WITH EmployeeCTE AS (
SELECT Name, Salary FROM Employees WHERE Age > 30
)
SELECT * FROM EmployeeCTE;
Connect/Follow – Ms. Pratiksha Meshram
11. What are indexes in SQL?
Answer: Indexes are used to speed up the retrieval of rows by creating a data structure that
allows quick lookups.
Example:
CREATE INDEX idx_name ON Employees (Name);
12. What is the difference between UNION and UNION
ALL?
Answer:
• UNION: Combines result sets and removes duplicates.
• UNION ALL: Combines result sets and retains duplicates.
Example:
SELECT Name FROM Employees
UNION
SELECT Name FROM Managers;
13. Explain normalization and its types.
Answer: Normalization is the process of organizing data to reduce redundancy and improve
integrity.
• 1NF: Eliminate duplicate columns.
• 2NF: Remove partial dependencies.
• 3NF: Remove transitive dependencies.
14. What are aggregate functions in SQL?
Answer: Functions that perform calculations on a set of values.
• COUNT(), SUM(), AVG(), MIN(), MAX()
Example:
SELECT AVG(Salary) FROM Employees;
15. What are window functions?
Connect/Follow – Ms. Pratiksha Meshram
Answer: Window functions perform calculations across a set of rows related to the current
row without collapsing the data.
Example:
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;
16. What is the difference between DROP and DELETE?
Answer:
• DROP: Deletes the entire table or database and its structure.
• DELETE: Deletes specific rows but retains the table structure.
Example (DROP):
DROP TABLE Employees;
17. What are ACID properties in a database?
Answer: ACID stands for:
• Atomicity: Transactions are all or nothing.
• Consistency: Data integrity is maintained.
• Isolation: Concurrent transactions do not interfere.
• Durability: Once committed, changes are permanent.
18. What is a stored procedure?
Answer: A stored procedure is a precompiled SQL code that can be executed repeatedly.
Example:
CREATE PROCEDURE GetEmployeeSalary
AS
BEGIN
SELECT Name, Salary FROM Employees;
END;
19. What is a trigger in SQL?
Answer: A trigger is a stored procedure that is executed automatically when a specified event
occurs.
Connect/Follow – Ms. Pratiksha Meshram
Example:
CREATE TRIGGER trgAfterInsert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO LogTable(EmployeeID, Action) VALUES (NEW.EmployeeID,
'INSERT');
END;
20. What are scalar functions?
Answer: Scalar functions operate on a single value and return a single value.
Example:
SELECT UPPER(Name) FROM Employees;
Connect/Follow – Ms. Pratiksha Meshram
