Top 100 SQL Interview Questions and Answers (2025)
Top 100 SQL Interview Questions and Answers (2025)
1. What is SQL?
2. What is a database?
A foreign key is a column (or set of columns) in one table that refers to the
primary key in another table. It establishes and enforces a relationship
between the two tables, ensuring data integrity.
Arithmetic Operators: +, -, *, /, %
Comparison Operators: =, !=, <>, >, <, >=, <=
Logical Operators: AND, OR, NOT
Set Operators: UNION, INTERSECT, EXCEPT
Special Operators: BETWEEN, IN, LIKE, IS NULL
The UNIQUE constraint ensures that all values in a column (or combination
of columns) are distinct. This prevents duplicate values and helps maintain
data integrity.
INNER JOIN: Returns rows that have matching values in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table,
and matching rows from the right table.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right
table, and matching rows from the left table.
FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a
match in either table.
CROSS JOIN: Produces the Cartesian product of two tables.
15. What is the difference between INNER JOIN and OUTER JOIN?
INNER JOIN: Returns only rows where there is a match in both tables.
OUTER JOIN: Returns all rows from one table (LEFT, RIGHT, or FULL),
and the matching rows from the other table. If there is no match, NULL
values are returned for the non-matching side.
DELETE: Removes rows one at a time and records each deletion in the
transaction log, allowing rollback. It can have a WHERE clause.
TRUNCATE: Removes all rows at once without logging individual row
deletions. It cannot have a WHERE clause and is faster than DELETE for
large data sets.
The ORDER BY clause sorts the result set of a query in either ascending
(default) or descending order, based on one or more columns. This helps
present the data in a more meaningful or readable sequence.
23. What are the differences between SQL and NoSQL databases?
SQL Databases:
Use structured tables with rows and columns.
Rely on a fixed schema.
Offer ACID properties.
NoSQL Databases:
Use flexible, schema-less structures (e.g., key-value pairs,
document stores).
Are designed for horizontal scaling.
Often focus on performance and scalability over strict
consistency.
The SELECT statement retrieves data from one or more tables. It is the
most commonly used command in SQL, allowing users to filter, sort, and
display data based on specific criteria.
Example:
Example:
Example:
The UNION operator combines the result sets of two or more SELECT queries
into a single result set, removing duplicate rows. This is useful when we
need a consolidated view of data from multiple tables or queries that
have similar structure.
Example:
UNION: Removes duplicate rows from the result set, ensuring only
unique rows are returned.
UNION ALL: Includes all rows from each query, including duplicates.
Performance-wise, UNION ALL is faster because it doesn’t require an
additional step to remove duplicates.
Example:
SELECT ID,
CASE
WHEN Salary > 100000 THEN 'High'
WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low'
END AS SalaryLevel
FROM Employees;
Example:
The COALESCE function returns the first non-NULL value from a list of
expressions. It’s commonly used to provide default values or handle
missing data gracefully.
Example:
41. What are the differences between SQL’s COUNT() and SUM()
functions?
1. COUNT(): Counts the number of rows or non-NULL values in a column.
Example:
Example:
Databases
SELECTSQL MySQL PostgreSQL
SUM(TotalAmount) FROMPL/SQL MongoDB
Orders; SQL Cheat Sheet SQL Interview Questions
42. What is the difference between the NVL and NVL2 functions?
Example:
RANK(): Assigns a rank to each row, with gaps if there are ties.
DENSE_RANK(): Assigns consecutive ranks without any gaps.
Example:
If two employees have the same salary, they get the same rank, but RANK()
will skip a number for the next rank, while DENSE_RANK() will not.
44. What is the difference between ROW_NUMBER() and RANK()?
Example:
Example:
WITH TopSalaries AS (
SELECT Name, Salary
FROM Employees
WHERE Salary > 50000
)
SELECT * FROM TopSalaries WHERE Name LIKE 'A%';
46. What are window functions, and how are they used?
1. Index
2. Key
Indexing allows the database to locate and access the rows corresponding
to a query condition much faster than scanning the entire table. Instead of
reading each row sequentially, the database uses the index to jump
directly to the relevant data pages. This reduces the number of disk I/O
operations and speeds up query execution, especially for large tables.
Example:
The index on LastName lets the database quickly find all rows matching
‘Smith’ without scanning every record.
Disadvantages:
1. Clustered Index:
Organizes the physical data in the table itself in the order of the
indexed column(s).
A table can have only one clustered index.
Improves range queries and queries that sort data.
Example: If EmployeeID is the clustered index, the rows in the table are
stored physically sorted by EmployeeID.
2. Non-Clustered Index:
51. What are temporary tables, and how are they used?
Temporary tables are tables that exist only for the duration of a session or
a transaction. They are useful for storing intermediate results, simplifying
complex queries, or performing operations on subsets of data without
modifying the main tables.
Example:
Standard View:
A virtual table defined by a query.
Does not store data; the underlying query is executed each
time the view is referenced.
A standard view shows real-time data.
Materialized View:
A physical table that stores the result of the query.
Data is precomputed and stored, making reads faster.
Requires periodic refreshes to keep data up to date.
materialized view is used to store aggregated sales data,
updated nightly, for fast reporting.
Example:
1. Greater Flexibility:
Example:
Example:
WITH CTE AS (
SELECT Column1, Column2, ROW_NUMBER() OVER (PARTITION BY Column1
ORDER BY Column2) AS RowNum
FROM TableName
)
SELECT * FROM CTE WHERE RowNum = 1;
Example:
SELECT Name,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID) AS
OrderCount
FROM Customers;
60. What are partitioned tables, and when should we use them?
1. Atomicity:
2. Consistency:
A transaction must take the database from one valid state to another,
maintaining all defined rules and constraints.
This ensures data integrity is preserved throughout the transaction
process.
3. Isolation:
4. Durability:
1. Read Uncommitted:
Allows reading uncommitted changes from other transactions.
Can result in dirty reads, where a transaction reads data that might
later be rolled back.
2. Read Committed:
3. Repeatable Read:
Ensures that if a transaction reads a row, that row cannot change until
the transaction is complete.
Prevents dirty reads and non-repeatable reads but not phantom reads.
4. Serializable:
SELECT *
FROM Orders WITH (NOLOCK);
This query fetches data from the Orders table without waiting for other
transactions to release their locks.
Deadlocks occur when two or more transactions hold resources that the
other transactions need, resulting in a cycle of dependency that prevents
progress. Strategies to handle deadlocks include:
Use indexes and optimized queries to minimize the duration and scope
of locks.
Break transactions into smaller steps to reduce the likelihood of
conflicts.
Example:
66. What are the differences between OLTP and OLAP systems?
67. What is a live lock, and how does it differ from a deadlock?
1. Live Lock
2. Deadlock
Occurs when transactions are blocked waiting for each other to release
locks.
No progress can be made unless one of the transactions is terminated
The EXCEPT operator is used to return rows from one query’s result set that
are not present in another query’s result set. It effectively performs a set
difference, showing only the data that is unique to the first query.
Example:
Use Case:
Performance Considerations:
69. How do you implement dynamic SQL, and what are its
advantages and risks?
Syntax:
Advantages:
Risks:
Horizontal Partitioning:
Divides the rows of a table into multiple partitions based on
values in a specific column.
Example: Splitting a customer table into separate partitions by
geographic region or by year.
Use Case: When dealing with large datasets, horizontal
partitioning can improve performance by limiting the number
of rows scanned for a query.
Vertical Partitioning:
Divides the columns of a table into multiple partitions.
Example: Storing infrequently accessed columns (e.g., large
text or binary fields) in a separate table or partition.
Use Case: Helps in optimizing storage and query performance
by separating commonly used columns from less frequently
accessed data.
Key Difference:
Horizontal partitioning is row-based, focusing on distributing
the dataset’s rows across partitions.
Vertical partitioning is column-based, aiming to separate less-
used columns into different partitions or tables.
71. What are the considerations for indexing very large tables?
1. Indexing Strategy:
2. Index Types:
Use clustered indexes for primary key lookups and range queries.
Use non-clustered indexes for filtering, ordering, and covering specific
queries.
3. Partitioned Indexes:
4. Maintenance Overhead:
1. Sharding
Sharding involves splitting a database into multiple smaller,
independent databases (shards). Each shard operates on a subset of
the overall data and can be hosted on separate servers.
Sharding is a horizontal scaling strategy that distributes data across
multiple databases, typically to handle massive data volumes and high
traffic.
Purpose: Horizontal scaling to handle large volumes of data and high
query loads.
Example: A global user database might be divided into shards by
region, such as a shard for North America, Europe, and Asia.
Key Benefit: Each shard can be queried independently, reducing the
load on any single server.
2. Partitioning
73. What are the best practices for writing optimized SQL queries?
3. **Avoid SELECT *:
Retrieve only the columns needed. This reduces I/O and improves
performance.
Choose INNER JOIN, LEFT JOIN, or OUTER JOIN based on the data
relationships and performance requirements.
8. Optimize Aggregations:
1. Indexing
Advantages:
Speeds up read operations and improves query performance
without changing the data structure.
Can be applied incrementally and is reversible if not effective.
Consider indexing when you need faster lookups without
altering the data model.
Disadvantages:
Slows down write operations as indexes need to be
maintained.
Requires additional storage.
2. Denormalization
Advantages:
Simplifies query logic by storing pre-joined or aggregated data.
Can improve performance for read-heavy workloads where
complex joins are frequent.
Consider denormalization when complex joins or repeated
aggregations significantly slow down queries
Disadvantages:
Introduces data redundancy, which can lead to inconsistencies.
Increases storage requirements.
Makes updates more complex, as redundant data must be
synchronized.
Key Components:
Example:
1. Transactional Queries:
2. Analytical Queries:
3. Key Differences:
Example:
Converting a dataset that lists monthly sales into a format that displays
each month as a separate column.
80. What is a bitmap index, and how does it differ from a B-tree
index?
1. Bitmap Index:
2. B-tree Index:
3. Key Difference:
Explanation:
82. Write a query to retrieve employees who earn more than the
average salary.
SELECT *
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
Explanation:
This query fetches details of employees whose salary exceeds the average
salary. The subquery calculates the average salary, and the main query
filters rows based on that result.
Explanation:
The query uses GROUP BY to group identical values and HAVING COUNT(*) > 1 to
identify values that appear more than once in the specified column.
84. Write a query to find the employees who joined in the last 30
days.
SELECT *
FROM Employee
WHERE JoiningDate > DATE_SUB(CURDATE(), INTERVAL 30 DAY);
Explanation:
By comparing the JoiningDate to the current date minus 30 days, this query
retrieves all employees who joined within the last month.
SELECT *
FROM Employee
ORDER BY Salary DESC
LIMIT 3;
Explanation:
The query sorts employees by salary in descending order and uses LIMIT 3
Explanation:
This query retains only one row for each set of duplicates by keeping the
row with the smallest EmployeeID. It identifies duplicates using GROUP BY and
removes rows not matching the minimum ID.
SELECT *
FROM TableA
INNER JOIN TableB ON TableA.ID = TableB.ID;
Explanation:
88. Write a query to fetch employees whose names start and end
with ‘A’.
SELECT *
FROM Employee
WHERE Name LIKE 'A%' AND Name LIKE '%A';
Explanation:
The query uses LIKE with wildcard characters to filter rows where the Name
column starts and ends with the letter ‘A’.
89. Write a query to display all departments along with the
number of employees in each.
Explanation:
SELECT *
FROM Employee
WHERE ManagerID IS NULL;
Explanation:
91. Write a query to fetch the 3rd and 4th highest salaries.
SELECT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 2 OFFSET 2;
Explanation:
Using ORDER BY and the combination of LIMIT and OFFSET, the query skips the
top two salaries and retrieves the next two highest.
Explanation:
This query converts specific row values into columns using conditional
aggregation with CASE. Each column’s value is determined based on a
condition applied to rows.
93. Write a query to fetch records updated within the last hour.
SELECT *
FROM TableName
WHERE UpdatedAt >= NOW() - INTERVAL 1 HOUR;
Explanation:
By comparing the UpdatedAt timestamp to the current time minus one hour,
the query retrieves rows updated in the last 60 minutes.
SELECT *
FROM Employee
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Employee
GROUP BY DepartmentID
HAVING COUNT(*) < 5
);
Explanation:
The subquery counts employees in each department, and the main query
uses those results to find employees working in departments with fewer
than 5 members.
SELECT CASE
WHEN EXISTS (SELECT * FROM TableName) THEN 'Has Records'
ELSE 'No Records'
END AS Status;
Explanation:
The query uses EXISTS to determine if any rows exist in the table, returning
a status of ‘Has Records’ or ‘No Records’ based on the result.
96. Write a query to find employees whose salaries are higher than
their managers.
Explanation:
This query joins the Employee table with itself to compare employee salaries
to their respective managers’ salaries, selecting those who earn more.
SELECT *
FROM Employee
WHERE MOD(RowID, 2) = 0;
Explanation:
By applying a modulo operation on a unique identifier (RowID), the query
returns rows with even IDs, effectively fetching every other row.
SELECT DepartmentID
FROM Employee
GROUP BY DepartmentID
ORDER BY AVG(Salary) DESC
LIMIT 1;
Explanation:
SELECT *
FROM Employee
LIMIT 1 OFFSET (n-1);
Explanation:
Using LIMIT 1 OFFSET (n-1), this query retrieves a single row corresponding
to the specified position (nth) in the ordered result set.
SELECT *
FROM Employee
WHERE MONTH(JoiningDate) = MONTH(CURDATE());
Explanation:
By comparing the month of JoiningDate to the current month, the query
selects all employees who were hired in that month regardless of the year.
Conclusion
A solid understanding of SQL is essential for anyone aspiring to work in
data analysis, database administration, or software development. By
reviewing and practicing these 100 SQL interview questions, we will gain
the confidence and knowledge needed to answer even the toughest
queries during our next interview. Remember, preparation is the key
because each question we master brings us closer to securing that dream
job in the tech industry.
Dreaming of M.Tech in IIT? Get AIR under 100 with our GATE 2026 CSE
& DA courses! Get flexible weekday/weekend options, live mentorship,
and mock tests. Access exclusive features like All India Mock Tests, and
Doubt Solving—your GATE success starts now!
Similar Reads
API Testing Interview Questions for 2024: Top 50 Interview Questions
API testing checks if application programming interfaces (APIs) work as
expected in terms of functionality, performance, security, and reliability.…
7 min read
SQL vs NO SQL vs NEW SQL
SQL stands for Structured Query Language. Which is based on relational
algebra and schema is fixed in this which means data is stored in the form o…
2 min read
2 min read
3 min read
12 min read
11 min read
PL/SQL Interview Questions and Answers
PL/SQL (Procedural Language for SQL) is one of the most widely used
database programming languages in the world, known for its robust…
14 min read
3 min read
Registered Address:
K 061, Tower K, Gulshan Vivante
Apartment, Sector 137, Noida, Gautam
Buddh Nagar, Uttar Pradesh, 201305
Advertise with us
Company Explore
About Us Job-A-Thon Hiring Challenge
Legal Hack-A-Thon
Privacy Policy GfG Weekly Contest
Careers Offline Classes (Delhi/NCR)
In Media DSA in JAVA/C++
Contact Us Master System Design
GFG Corporate Solution Master CP
Placement Training Program GeeksforGeeks Videos
Geeks Community
Languages DSA
Python Data Structures
Java Algorithms
C++ DSA for Beginners
PHP Basic DSA Problems
GoLang DSA Roadmap
SQL DSA Interview Questions
R Language Competitive Programming
Android Tutorial
DSA/Placements Development/Testing
DSA - Self Paced Course JavaScript Full Course
DSA in JavaScript - Self Paced Course React JS Course
DSA in Python - Self Paced React Native Course
C Programming Course Online - Learn C with Data Structures Django Web Development Course
Complete Interview Preparation Complete Bootstrap Course
Master Competitive Programming Full Stack Development - [LIVE]
Core CS Subject for Interview Preparation JAVA Backend Development - [LIVE]
Mastering System Design: LLD to HLD Complete Software Testing Course [LIVE]
Tech Interview 101 - From DSA to System Design [LIVE] Android Mastery with Kotlin [LIVE]
DSA to Development [HYBRID]
Placement Preparation Crash Course [LIVE]
Clouds/Devops GATE
DevOps Engineering GATE CS & IT Test Series - 2025
AWS Solutions Architect Certification GATE DA Test Series 2025
Salesforce Certified Administrator Course GATE CS & IT Course - 2025
GATE DA Course 2025
GATE Rank Predictor