Top 100 SQL Interview Questions and Answers for Freshers
Top 100 SQL Interview Questions and Answers for Freshers
SQL (Structured Query Language) is one of the most important skills for Software Engineers, Full Stack Developers, Data Analysts, Data Engineers, AI/ML Engineers, and Database Developers. Almost every technical interview includes SQL questions because organizations store and analyze data using relational databases.
This guide covers real SQL interview questions asked in companies such as Amazon, IBM, Microsoft, Accenture, Deloitte, Infosys, TCS, Wipro, Cognizant, Capgemini, Oracle, SAP, and many startups.
Top OOP Interview Questions and Answers for Freshers
Top Associate Software Engineer Interview Questions and Answers for Freshers
Basic SQL Interview Questions and Answers
1. What is SQL?
Answer:
SQL (Structured Query Language) is a standard language used to manage, store, retrieve, modify, and manipulate data in relational databases. SQL enables users to interact with databases efficiently through commands such as SELECT, INSERT, UPDATE, and DELETE. It is widely used in applications, websites, reporting systems, and enterprise software. SQL helps organizations manage large volumes of structured data securely and efficiently.
Example:
SELECT * FROM Employees;
This query retrieves all employee records from the Employees table.
2. What is a Database?
Answer:
A database is an organized collection of data stored electronically so that it can be easily accessed, managed, and updated. Databases help businesses store customer information, transactions, product details, employee records, and much more. Modern applications rely heavily on databases for data management. Relational databases store data in tables consisting of rows and columns.
Example:
An e-commerce website stores customer details, orders, products, and payments in a database.
3. What is DBMS?
Answer:
DBMS (Database Management System) is software that allows users to create, manage, and interact with databases. It provides functionalities like data storage, retrieval, security, backup, and recovery. DBMS acts as an interface between users and databases. Examples include MySQL, PostgreSQL, Oracle, and SQL Server.
Example:
MySQL is a popular DBMS used in many web applications.
4. Difference Between DBMS and RDBMS?
Answer:
DBMS stores data without necessarily maintaining relationships between records, while RDBMS (Relational Database Management System) stores data in related tables using keys. RDBMS follows normalization principles and supports ACID properties. Most modern enterprise systems use RDBMS because of better consistency and scalability.
Examples:
- DBMS → File-based database systems
- RDBMS → MySQL, Oracle, PostgreSQL
5. What is a Table in SQL?
Answer:
A table is the fundamental structure used to store data in a relational database. It consists of rows and columns where each row represents a record and each column represents an attribute. Tables help organize data logically for efficient retrieval and management.
Example:
| Emp_ID | Name | Salary |
|---|---|---|
| 101 | John | 50000 |
6. What is a Primary Key?
Answer:
A Primary Key is a column or set of columns used to uniquely identify each row in a table. It cannot contain duplicate values or NULL values. Primary keys ensure data integrity and help establish relationships between tables. Every table should ideally have a primary key.
Example:
Employee_ID can uniquely identify employees.
7. What is a Foreign Key?
Answer:
A Foreign Key is a column that creates a relationship between two tables. It references the primary key of another table. Foreign keys maintain referential integrity and ensure that relationships between tables remain consistent.
Example:
Department_ID in Employees table referencing Department table.
8. What is the Difference Between Primary Key and Foreign Key?
Answer:
A Primary Key uniquely identifies records within the same table, while a Foreign Key establishes relationships between different tables. Primary keys cannot contain duplicates or NULL values, whereas foreign keys can contain duplicate values depending on the relationship structure.
9. What is a NULL Value?
Answer:
NULL represents missing, unknown, or unavailable information in a database. It is different from zero, blank space, or empty string. Proper handling of NULL values is important because comparisons involving NULL behave differently from regular values.
Example:
An employee whose joining date has not yet been recorded may have NULL in the Joining_Date column.
10. What is a Constraint?
Answer:
Constraints are rules applied to database columns to ensure data accuracy and integrity. They prevent invalid data from entering the database. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.
Example:
Salary > 0
This can be enforced using a CHECK constraint.
11. What is the SELECT Statement?
Answer:
The SELECT statement is used to retrieve data from one or more tables. It is the most frequently used SQL command. Developers use SELECT to fetch specific columns, filter records, sort results, and perform calculations.
Example:
SELECT Name, Salary FROM Employees;
12. What is the WHERE Clause?
Answer:
The WHERE clause filters records based on specified conditions. It helps retrieve only relevant data instead of the entire table. Conditions can include comparison operators, logical operators, and pattern matching.
Example:
SELECT * FROM Employees WHERE Salary > 50000;
13. Difference Between DELETE, DROP, and TRUNCATE?
Answer:
DELETE removes selected rows and can be rolled back. TRUNCATE removes all rows quickly while keeping table structure intact. DROP completely removes the table structure and data. Understanding the difference is important because each command has different impacts.
14. What is the ORDER BY Clause?
Answer:
ORDER BY is used to sort query results in ascending or descending order. It improves readability and helps generate reports. Multiple columns can also be used for sorting.
Example:
SELECT * FROM Employees ORDER BY Salary DESC;
15. What is DISTINCT?
Answer:
DISTINCT removes duplicate records from query results. It is useful when only unique values are required. However, using DISTINCT on large datasets can impact performance if not optimized properly.
Example:
SELECT DISTINCT Department FROM Employees;
16. What is a Unique Constraint?
Answer:
A UNIQUE constraint ensures that values in a column remain unique. Unlike Primary Keys, UNIQUE columns may allow one NULL value depending on the database system. It helps maintain data consistency.
17. What is the NOT NULL Constraint?
Answer:
NOT NULL ensures that a column cannot store NULL values. It is commonly applied to mandatory fields such as customer names, email addresses, or employee IDs.
18. What is a Default Constraint?
Answer:
A DEFAULT constraint automatically assigns a value when no value is provided during insertion. It helps reduce manual effort and ensures consistency.
Example:
Default status = ‘Active’.
19. What is the BETWEEN Operator?
Answer:
BETWEEN retrieves records within a specified range. It is commonly used for numeric values, dates, and salaries. The range is inclusive of both endpoints.
Example:
SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 60000;
20. What is the IN Operator?
Answer:
The IN operator allows multiple values to be checked within a single condition. It improves readability compared to using multiple OR conditions.
Example:
SELECT *
FROM Employees
WHERE Department IN ('HR','IT','Finance');
Top 75 Java Full Stack Interview Questions and Answers for Freshers
Intermediate SQL Interview Questions and Answers (21–40)
This section covers intermediate-level SQL concepts frequently asked in Software Engineer, Full Stack Developer, Data Analyst, Data Engineer, Business Analyst, AI/ML Engineer, and Database Developer interviews. Interviewers often focus on Joins, Group By, Normalization, Views, Indexes, and Aggregate Functions because these concepts are heavily used in real-world projects.
21. What is an INNER JOIN?
Answer:
INNER JOIN returns only the matching records from both tables based on a common column. Rows that do not have matching values in either table are excluded from the result set. It is the most commonly used join in SQL and is frequently used when retrieving related information stored across multiple tables.
Example:
SELECT E.Name, D.Department_Name FROM Employees E INNER JOIN Departments D ON E.Department_ID = D.Department_ID;
This query returns employees along with their department names.
22. What is a LEFT JOIN?
Answer:
LEFT JOIN returns all records from the left table and only matching records from the right table. If no match exists, NULL values are returned for columns from the right table. LEFT JOIN is useful when you need all records from the primary table regardless of matching data.
Example:
Retrieving all employees even if they are not assigned to a department.
23. What is a RIGHT JOIN?
Answer:
RIGHT JOIN works opposite to LEFT JOIN. It returns all records from the right table and matching records from the left table. If no match exists, NULL values are returned for columns from the left table. Although less commonly used, it can be useful depending on data retrieval requirements.
Example:
Showing all departments even if no employees belong to them.
24. What is a FULL OUTER JOIN?
Answer:
FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN. It returns all matching records as well as unmatched records from both tables. When no matching value exists, NULL values are displayed for missing columns.
Example:
Displaying all employees and all departments, including unmatched entries.
25. Difference Between INNER JOIN and OUTER JOIN?
Answer:
INNER JOIN returns only matching records between tables, while OUTER JOIN returns matching records along with unmatched records from one or both tables depending on the join type. INNER JOIN is used when matching data is required, whereas OUTER JOIN helps identify missing relationships.
Example:
- INNER JOIN → Employees assigned to departments.
- LEFT JOIN → All employees, even without departments.
26. What is GROUP BY?
Answer:
GROUP BY is used to group rows that share common values into summary rows. It is typically used with aggregate functions such as COUNT, SUM, AVG, MAX, and MIN. GROUP BY is widely used in reporting and analytics.
Example:
SELECT Department, COUNT(*) FROM Employees GROUP BY Department;
This query counts employees in each department.
27. What is HAVING Clause?
Answer:
HAVING filters grouped data after the GROUP BY operation is performed. While WHERE filters rows before grouping, HAVING filters groups after aggregation. It is useful when conditions depend on aggregate values.
Example:
SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;
This query returns departments having more than 5 employees.
28. Difference Between WHERE and HAVING?
Answer:
WHERE filters individual rows before grouping takes place, while HAVING filters grouped results after aggregation. WHERE cannot directly use aggregate functions, whereas HAVING is specifically designed for aggregate-based filtering.
Example:
- WHERE Salary > 50000
- HAVING AVG(Salary) > 50000
29. What are Aggregate Functions?
Answer:
Aggregate functions perform calculations on multiple rows and return a single result. They are extensively used in reporting, analytics, dashboards, and business intelligence applications.
Common aggregate functions include:
- COUNT()
- SUM()
- AVG()
- MAX()
- MIN()
30. What is COUNT() Function?
Answer:
COUNT() returns the number of rows matching specified criteria. It is commonly used in reports and dashboards to measure records, users, transactions, or orders.
Example:
SELECT COUNT(*) FROM Employees;
This query returns the total number of employees.
31. What is AVG() Function?
Answer:
AVG() calculates the average value of a numeric column. It is commonly used for salary analysis, performance metrics, sales reports, and business analytics.
Example:
SELECT AVG(Salary) FROM Employees;
32. What is SUM() Function?
Answer:
SUM() adds all values in a numeric column and returns the total. It is frequently used in financial reports, revenue calculations, inventory management, and business analytics.
Example:
SELECT SUM(Salary) FROM Employees;
33. What is MAX() and MIN() Function?
Answer:
MAX() returns the highest value in a column, while MIN() returns the lowest value. These functions help identify top performers, highest sales, lowest prices, and other business metrics.
Example:
SELECT MAX(Salary), MIN(Salary) FROM Employees;
34. What is a View in SQL?
Answer:
A View is a virtual table created from one or more SQL queries. Views do not store data themselves but display data dynamically from underlying tables. They improve security, simplify complex queries, and provide abstraction.
Example:
CREATE VIEW Employee_View AS SELECT Name, Salary FROM Employees;
35. What is an Index?
Answer:
An Index is a database object that improves query performance by allowing faster data retrieval. Without indexes, the database often performs full table scans. Indexes significantly improve performance for frequently searched columns.
Example:
Creating an index on Employee_ID improves search speed for employee lookups.
36. Advantages and Disadvantages of Indexes?
Answer:
Advantages:
- Faster query execution
- Improved search performance
- Better reporting speed
Disadvantages:
- Consumes storage space
- Slows INSERT, UPDATE, DELETE operations
- Requires maintenance
37. What is Normalization?
Answer:
Normalization is the process of organizing database tables to reduce redundancy and improve data integrity. It divides large tables into smaller related tables while maintaining relationships using keys.
Benefits:
- Reduces duplicate data
- Improves consistency
- Simplifies maintenance
- Enhances data integrity
38. What is First Normal Form (1NF)?
Answer:
1NF ensures that each column contains atomic values and eliminates repeating groups. Every row must be unique and each field should store only one value.
Bad Example:
| ID | Phones |
|---|---|
| 1 | 123,456 |
Good Example:
Store each phone number separately.
39. What is Second Normal Form (2NF)?
Answer:
2NF eliminates partial dependency. A table must first satisfy 1NF, and all non-key columns should depend on the entire primary key rather than part of it. This reduces redundancy and improves consistency.
Example:
Separating course details from student enrollment records.
40. What is Third Normal Form (3NF)?
Answer:
3NF eliminates transitive dependency. Non-key attributes should depend only on the primary key and not on other non-key attributes. This creates cleaner database designs and improves maintainability.
Example:
Instead of storing Department_Name with every employee, create a separate Department table and link it using Department_ID.
Software Development Engineer Interview Guide for Freshers
Advanced SQL Interview Questions and Answers (41–60)
This section covers advanced SQL concepts frequently asked in product-based companies, MNCs, software engineering roles, data engineering positions, business intelligence roles, and database administration interviews. These topics help interviewers evaluate your ability to work with complex queries, optimize database performance, and handle real-world enterprise applications.
41. What is a Subquery?
Answer:
A Subquery is a query written inside another SQL query. It helps retrieve data that will be used by the main query for filtering, comparison, or calculations. Subqueries improve flexibility and are commonly used when data depends on results from another query. They can be placed inside SELECT, FROM, WHERE, or HAVING clauses.
Example:
SELECT Name FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees );
This query returns employees earning more than the average salary.
42. What are the Types of Subqueries?
Answer:
SQL supports several types of subqueries based on usage and output. Understanding these types helps developers write efficient and readable queries.
- Single Row Subquery
- Multiple Row Subquery
- Correlated Subquery
- Nested Subquery
- Scalar Subquery
Example:
Finding employees whose salary is greater than department average using a correlated subquery.
43. What is a Correlated Subquery?
Answer:
A Correlated Subquery depends on values from the outer query and executes once for each row processed by the outer query. Unlike normal subqueries, it cannot run independently because it references columns from the main query. Correlated subqueries are powerful but can be slower on large datasets.
Example:
SELECT Name FROM Employees E1 WHERE Salary > ( SELECT AVG(Salary) FROM Employees E2 WHERE E1.Department_ID = E2.Department_ID );
44. What is a Common Table Expression (CTE)?
Answer:
A Common Table Expression (CTE) is a temporary named result set that exists only during query execution. CTEs improve query readability and simplify complex SQL logic. They are especially useful when dealing with multiple joins, recursive operations, and large analytical queries.
Example:
WITH AvgSalary AS ( SELECT AVG(Salary) AS AvgSal FROM Employees ) SELECT * FROM Employees WHERE Salary > ( SELECT AvgSal FROM AvgSalary );
45. What are the Advantages of CTEs?
Answer:
CTEs improve code readability, maintainability, and debugging. They allow developers to break large queries into smaller logical blocks. Recursive CTEs can also solve hierarchical data problems such as organizational structures or category trees.
Benefits:
- Cleaner code
- Easier maintenance
- Reusable query logic
- Supports recursion
46. What is a Window Function?
Answer:
Window Functions perform calculations across a set of rows while preserving individual row details. Unlike GROUP BY, they do not collapse rows into summary records. They are commonly used in reporting, ranking, analytics, and dashboard applications.
Example:
Calculating employee salary rankings within each department.
47. What is ROW_NUMBER()?
Answer:
ROW_NUMBER() assigns a unique sequential number to each row within a result set. It is often used for pagination, ranking, and identifying duplicate records. The numbering starts from 1 and follows the specified ordering.
Example:
SELECT Name, Salary, ROW_NUMBER() OVER ( ORDER BY Salary DESC ) AS Rank FROM Employees;
48. Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()?
Answer:
All three functions assign rankings but behave differently when duplicate values exist.
- ROW_NUMBER() → Unique ranking
- RANK() → Skips rankings after ties
- DENSE_RANK() → No skipped rankings
Example:
If two employees have the same salary:
- RANK → 1,1,3
- DENSE_RANK → 1,1,2
49. What is LEAD() Function?
Answer:
LEAD() accesses data from the next row without requiring a self-join. It is useful for trend analysis, comparing records, and reporting. Analysts often use LEAD() to compare current values with future values.
Example:
SELECT Employee_ID, Salary, LEAD(Salary) OVER (ORDER BY Employee_ID) AS NextSalary FROM Employees;
50. What is LAG() Function?
Answer:
LAG() retrieves data from a previous row within the result set. It is useful for comparing historical values, calculating growth rates, and analyzing trends over time.
Example:
Comparing current month sales with previous month sales.
51. What is a Stored Procedure?
Answer:
A Stored Procedure is a precompiled collection of SQL statements stored in the database. It can be executed whenever required, improving performance and reducing code duplication. Stored procedures are commonly used in enterprise applications to encapsulate business logic.
Advantages:
- Better performance
- Code reusability
- Enhanced security
- Easier maintenance
52. What is a Trigger?
Answer:
A Trigger is a special database object that automatically executes when specific events occur on a table. Triggers are commonly used for auditing, logging, validation, and enforcing business rules.
Example:
Automatically recording changes whenever employee salaries are updated.
53. Difference Between Stored Procedure and Trigger?
Answer:
A Stored Procedure is executed explicitly by users or applications, while a Trigger executes automatically in response to database events. Stored procedures are used for reusable business logic, whereas triggers enforce automatic actions.
Example:
- Stored Procedure → Generate monthly payroll.
- Trigger → Log salary changes automatically.
54. What is a Transaction in SQL?
Answer:
A Transaction is a sequence of one or more SQL operations treated as a single unit of work. Transactions ensure that all operations succeed together or fail together. They are critical for maintaining data consistency in banking, e-commerce, and enterprise systems.
Example:
Transferring money from one bank account to another.
55. What are ACID Properties?
Answer:
ACID properties ensure reliable transaction processing and database consistency.
- Atomicity → All or nothing execution
- Consistency → Maintains valid data state
- Isolation → Transactions do not interfere
- Durability → Changes remain permanent
ACID properties are essential in banking and financial applications.
56. What is COMMIT?
Answer:
COMMIT permanently saves all changes made during the current transaction. Once committed, the changes become visible to other users and cannot be rolled back. It marks successful completion of a transaction.
Example:
COMMIT;
57. What is ROLLBACK?
Answer:
ROLLBACK reverses changes made during a transaction if an error occurs. It restores the database to its previous consistent state. ROLLBACK is crucial for preventing incomplete or incorrect updates.
Example:
ROLLBACK;
58. What is Query Optimization?
Answer:
Query Optimization is the process of improving query performance while minimizing resource consumption. Database engines automatically optimize queries, but developers should also write efficient SQL. Optimized queries improve application speed and scalability.
Techniques:
- Use indexes
- Avoid SELECT *
- Filter data early
- Optimize joins
59. Why Should We Avoid SELECT *?
Answer:
SELECT * retrieves all columns regardless of necessity, increasing network traffic and processing time. It can negatively impact performance, especially in large tables. Selecting only required columns improves efficiency and readability.
Bad Practice:
SELECT * FROM Employees;
Better Practice:
SELECT Name, Salary FROM Employees;
60. What is Denormalization?
Answer:
Denormalization is the process of intentionally adding redundancy to improve read performance. While normalization reduces duplication, denormalization reduces the number of joins required for complex queries. It is often used in reporting systems, data warehouses, and analytics applications.
Example:
Storing customer and order information together to speed up reporting queries.
Top AI/ML Engineer Interview Questions and Answers for Freshers
Hands-On SQL Interview Questions and Answers (61–80)
This section covers practical SQL questions frequently asked in Amazon, IBM, Infosys, TCS, Wipro, Accenture, Deloitte, Cognizant, Oracle, SAP, Capgemini, and product-based companies. These questions test whether candidates can write SQL queries to solve real business problems. Interviewers often focus on query-writing ability rather than theoretical knowledge.
61. How Do You Retrieve All Employee Records?
Answer:
This is usually one of the first SQL questions asked during interviews. The SELECT statement retrieves data from a table. Although simple, it verifies whether a candidate understands basic SQL syntax and table querying. In real projects, developers often retrieve specific columns instead of all columns for better performance.
Query:
SELECT * FROM Employees;
Example:
Retrieve all employee details such as ID, Name, Salary, Department, and Joining Date.
62. How Do You Retrieve Specific Columns from a Table?
Answer:
Instead of retrieving every column, developers usually select only required columns. This reduces data transfer, improves performance, and makes queries easier to understand. It is considered a best practice in enterprise applications.
Query:
SELECT Employee_ID,
Name,
Salary
FROM Employees;
63. Find Employees Earning More Than ₹50,000.
Answer:
This question tests filtering using the WHERE clause. Filtering is one of the most common database operations because applications rarely display all available data. Conditions can be applied to numeric, text, and date fields.
Query:
SELECT * FROM Employees WHERE Salary > 50000;
64. Find Employees Working in the IT Department.
Answer:
Interviewers often test filtering on string values. This demonstrates understanding of comparison operations and business-oriented data retrieval. Similar queries are used in HR, payroll, and reporting systems.
Query:
SELECT * FROM Employees WHERE Department = 'IT';
65. Find the Total Number of Employees.
Answer:
COUNT() is commonly used in dashboards, reports, and analytics systems. Companies frequently ask candidates to calculate total records because it demonstrates familiarity with aggregate functions.
Query:
SELECT COUNT(*) AS TotalEmployees FROM Employees;
66. Find the Highest Salary in the Employee Table.
Answer:
MAX() helps identify top values in a dataset. Organizations use similar queries to find highest sales, highest revenue, best-performing employees, or maximum transaction amounts.
Query:
SELECT MAX(Salary) FROM Employees;
67. Find the Lowest Salary in the Employee Table.
Answer:
MIN() returns the smallest value in a column. It is useful for payroll analysis, pricing systems, and identifying minimum thresholds within datasets.
Query:
SELECT MIN(Salary) FROM Employees;
68. Find the Average Salary of Employees.
Answer:
AVG() calculates average values and is commonly used in business reports. Managers often use average salary reports to evaluate compensation structures across departments.
Query:
SELECT AVG(Salary) FROM Employees;
69. Find the Total Salary Paid to Employees.
Answer:
This question tests the SUM() aggregate function. Organizations use similar calculations for payroll, revenue, expenses, and financial reporting systems.
Query:
SELECT SUM(Salary) FROM Employees;
70. Find Number of Employees in Each Department.
Answer:
This is one of the most frequently asked GROUP BY questions. It combines aggregation and grouping, which are heavily used in business intelligence and reporting systems.
Query:
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
Wipro WILP Interview Questions and Answers for Freshers
71. Find Departments Having More Than 10 Employees.
Answer:
This question checks knowledge of GROUP BY combined with HAVING. It is often used in managerial reporting and workforce analytics.
Query:
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;
72. Retrieve Employee Names and Department Names Using JOIN.
Answer:
JOIN questions are extremely common because relational databases store data across multiple tables. Candidates must understand how relationships work between tables.
Query:
SELECT E.Name,
D.Department_Name
FROM Employees E
INNER JOIN Departments D
ON E.Department_ID = D.Department_ID;
73. Find Employees Without a Department.
Answer:
This question tests understanding of LEFT JOIN and NULL filtering. It is commonly used for data quality audits and identifying incomplete records.
Query:
SELECT E.* FROM Employees E LEFT JOIN Departments D ON E.Department_ID = D.Department_ID WHERE D.Department_ID IS NULL;
74. Find Duplicate Records in a Table.
Answer:
Duplicate data can cause reporting issues and business inconsistencies. Interviewers frequently ask this question because it tests GROUP BY, HAVING, and aggregation skills.
Query:
SELECT Email,
COUNT(*)
FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;
75. Delete Duplicate Records from a Table.
Answer:
This is a common real-world data-cleaning question. Many companies ask candidates how they would retain one record while removing duplicates.
Example Approach:
Use ROW_NUMBER() to identify duplicates and delete records where row number is greater than 1.
76. Find the Second Highest Salary.
Answer:
This is one of the most famous SQL interview questions. It tests subqueries and ranking logic. Variations include third, fourth, or Nth highest salary.
Query:
SELECT MAX(Salary) FROM Employees WHERE Salary < ( SELECT MAX(Salary) FROM Employees );
77. Find the Nth Highest Salary.
Answer:
Modern interviews often expect ROW_NUMBER(), RANK(), or DENSE_RANK() solutions. These approaches are more scalable than nested subqueries.
Query:
SELECT Salary
FROM
(
SELECT Salary,
DENSE_RANK() OVER
(ORDER BY Salary DESC) AS Rnk
FROM Employees
) T
WHERE Rnk = 3;
This query returns the third highest salary.
78. Find Employees Who Joined in the Last 30 Days.
Answer:
Date-based filtering is common in HR, payroll, and reporting systems. Interviewers often ask candidates to work with date functions.
Query:
SELECT * FROM Employees WHERE Joining_Date >= CURRENT_DATE - INTERVAL '30' DAY;
79. Find Top 5 Highest Paid Employees.
Answer:
This question checks sorting and limiting result sets. Top-N queries are extremely common in dashboards and business reports.
Query:
SELECT * FROM Employees ORDER BY Salary DESC LIMIT 5;
80. Find the Employee with the Highest Salary in Each Department.
Answer:
This is a favorite advanced hands-on interview question. It combines grouping, subqueries, and business logic. Similar questions are asked in Amazon, Oracle, Deloitte, and IBM interviews.
Query:
SELECT Department,
MAX(Salary)
FROM Employees
GROUP BY Department;
Advanced Version:
Retrieve the actual employee details along with department-wise highest salaries using window functions or subqueries.
Real-Time & Scenario-Based SQL Interview Questions and Answers (81–100)
This section contains real-world SQL interview questions frequently asked in Amazon, IBM, Microsoft, Oracle, Deloitte, Accenture, TCS, Infosys, Cognizant, Capgemini, Wipro, Optum, and product-based companies. These questions evaluate how candidates think when solving business problems using SQL rather than simply writing queries.
Interviewers often prefer scenario-based questions because they reflect actual challenges encountered in production environments, banking systems, healthcare applications, e-commerce platforms, payroll systems, and analytics projects.
81. A Customer Complains That Their Order Is Missing. How Would You Investigate Using SQL?
Answer:
I would first identify the customer’s order using Order_ID or Customer_ID. Then I would verify whether the order exists in the Orders table, check payment status, shipment status, and inventory allocation records. I would also review timestamps to identify where the process failed. SQL helps trace the complete lifecycle of the order.
Example Query:
SELECT * FROM Orders WHERE Customer_ID = 101;
82. How Would You Identify the Top 10 Customers by Revenue?
Answer:
This is a common analytics question. I would calculate total purchase value for each customer using SUM() and GROUP BY, then sort the results in descending order. Such reports help businesses identify valuable customers and improve retention strategies.
Query:
SELECT Customer_ID,
SUM(Order_Amount) AS Revenue
FROM Orders
GROUP BY Customer_ID
ORDER BY Revenue DESC
LIMIT 10;
83. How Would You Find Customers Who Have Never Placed an Order?
Answer:
This scenario frequently appears in e-commerce interviews. I would use LEFT JOIN to identify customers without matching orders. Such analysis helps marketing teams target inactive users with promotions.
Query:
SELECT C.* FROM Customers C LEFT JOIN Orders O ON C.Customer_ID = O.Customer_ID WHERE O.Order_ID IS NULL;
84. How Would You Detect Fraudulent Transactions?
Answer:
I would search for unusual transaction patterns such as multiple transactions within a short time, abnormal transaction amounts, or activities from unexpected locations. SQL can be used to identify suspicious behavior before ML models are applied.
Example:
Detect customers performing more than 20 transactions within one hour.
85. Your Query Takes 30 Seconds to Execute. How Would You Optimize It?
Answer:
I would first examine the execution plan to identify bottlenecks. Then I would check indexes, remove unnecessary columns, optimize joins, reduce subqueries, and filter data earlier using WHERE clauses. Performance tuning should always focus on root causes rather than assumptions.
Optimization Techniques:
- Create indexes
- Avoid SELECT *
- Optimize joins
- Reduce full table scans
86. A Table Contains Millions of Records. How Would You Improve Search Performance?
Answer:
The first approach would be creating indexes on frequently searched columns. Depending on usage patterns, partitioning, query optimization, caching, and denormalization may also be considered. Large-scale databases require careful balancing between read and write performance.
Example:
Create an index on Customer_ID for faster customer lookups.
87. How Would You Design a Database for an E-Commerce Website?
Answer:
I would create separate tables for Customers, Products, Orders, Order_Items, Payments, and Shipments. Primary keys would uniquely identify records, while foreign keys would maintain relationships. Normalization would reduce redundancy while ensuring data integrity.
Example Tables:
- Customers
- Products
- Orders
- Payments
- Shipments
88. How Would You Calculate Monthly Revenue?
Answer:
This is a common reporting requirement. I would group orders by month and calculate total revenue using SUM(). Business teams use such reports to analyze growth trends and performance.
Query:
SELECT MONTH(Order_Date),
SUM(Order_Amount)
FROM Orders
GROUP BY MONTH(Order_Date);
89. How Would You Find Repeat Customers?
Answer:
Repeat customers have placed more than one order. Identifying such users helps businesses measure customer loyalty and retention effectiveness.
Query:
SELECT Customer_ID,
COUNT(*) AS Orders
FROM Orders
GROUP BY Customer_ID
HAVING COUNT(*) > 1;
90. How Would You Find the Most Popular Product?
Answer:
I would calculate total sales quantity for each product and sort results in descending order. Product popularity analysis helps inventory planning and marketing strategies.
Query:
SELECT Product_ID,
SUM(Quantity) AS TotalSold
FROM Order_Items
GROUP BY Product_ID
ORDER BY TotalSold DESC;
91. A Banking Transaction Fails Midway. How Does SQL Handle It?
Answer:
SQL databases use transactions and ACID properties. If any step fails during money transfer, ROLLBACK restores the database to its previous state. This prevents partial updates and ensures consistency.
Example:
If ₹10,000 is deducted from one account but not credited to another, the transaction is rolled back.
92. What Happens If Two Users Update the Same Record Simultaneously?
Answer:
Databases use locking mechanisms and transaction isolation levels to manage concurrent access. These controls prevent data corruption and maintain consistency when multiple users access the same records.
Example:
Two cashiers updating the same inventory quantity simultaneously.
93. How Would You Generate a Running Total Report?
Answer:
Running totals are frequently used in finance and sales reporting. Window functions provide an efficient solution without requiring complex self-joins.
Query:
SELECT Order_Date,
Sales,
SUM(Sales)
OVER (ORDER BY Order_Date)
AS RunningTotal
FROM Sales_Data;
94. How Would You Find Missing Employee IDs?
Answer:
This question tests logical thinking and data validation skills. Missing IDs often indicate deleted or corrupted records. Various approaches include self-joins, recursive CTEs, or sequence tables.
Example:
Employee IDs: 101, 102, 104 → Missing ID = 103.
95. How Would You Audit Changes Made to Employee Salaries?
Answer:
I would use database triggers to automatically log salary updates into an audit table. This approach helps organizations maintain accountability and track historical changes.
Example:
Store old salary, new salary, timestamp, and updated-by user.
96. What SQL Concepts Are Most Important for Data Analysts?
Answer:
Data Analysts primarily work with SELECT statements, joins, aggregate functions, GROUP BY, HAVING, subqueries, window functions, and data cleaning queries. Strong SQL skills enable analysts to generate business insights efficiently.
Example:
Analyzing monthly sales performance across different regions.
97. What SQL Concepts Are Most Important for Software Engineers?
Answer:
Software Engineers frequently use joins, indexing, normalization, transactions, stored procedures, query optimization, and database design concepts. Understanding these topics helps build scalable and efficient applications.
Example:
Building a user authentication system with optimized database queries.
98. What SQL Concepts Are Most Important for AI/ML Engineers?
Answer:
AI and ML Engineers often retrieve and prepare datasets from databases. Therefore, joins, filtering, aggregations, window functions, data cleaning, and feature extraction queries are highly important. Efficient SQL reduces preprocessing time significantly.
Example:
Extracting customer transaction history for fraud detection model training.
99. What Are the Most Frequently Asked SQL Topics in Interviews?
Answer:
Based on interviews at Amazon, IBM, Deloitte, Infosys, TCS, Accenture, Wipro, Cognizant, Oracle, and Microsoft, the most common SQL topics include:
- Joins
- Primary Key & Foreign Key
- Normalization
- Aggregate Functions
- GROUP BY & HAVING
- Subqueries
- Window Functions
- Indexes
- Transactions & ACID
- Nth Highest Salary
- Duplicate Records
- Query Optimization
100. How Should Freshers Prepare for SQL Interviews?
Answer:
A structured preparation approach works best. Start with SQL basics, then learn joins, normalization, aggregate functions, subqueries, and window functions. Practice real-world query writing daily. Understanding why a query works is more important than memorizing syntax.
Recommended Roadmap:
- SQL Basics
- Keys & Constraints
- Joins
- Aggregate Functions
- GROUP BY & HAVING
- Normalization
- Subqueries
- CTEs
- Window Functions
- Indexes
- Transactions
- Hands-On Query Practice
- Scenario-Based Questions
Final SQL Interview Preparation Tips
- Practice SQL daily on LeetCode, HackerRank, and SQLBolt.
- Master Joins because they are asked in almost every interview.
- Learn Nth Highest Salary and Duplicate Records queries thoroughly.
- Understand normalization and indexing concepts clearly.
- Focus on business scenarios, not just syntax.
- Practice explaining query logic verbally during mock interviews.
- Be comfortable with both theoretical and hands-on SQL questions.
By mastering these 100 SQL interview questions and answers, freshers can confidently prepare for Software Engineer, Full Stack Developer, Data Analyst, Data Engineer, AI/ML Engineer, Business Analyst, and Associate Software Engineer interviews.
