SQL Topics
HubAdvantages and Disadvantages of SQLFeatures of SQLHistory of SQLSQL vs NoSQLWhat is SQL?SQL CommentsSQL Data TypesSQL KeywordsSQL OperatorsSQL SyntaxCHECKComposite KeysDEFAULTFOREIGN KEYNOT NULLPRIMARY KEYSQL ConstraintsUNIQUEDELETE DataINSERT DataSELECT DataSQL AliasesUPDATE DataBETWEEN OperatorDISTINCT KeywordGROUP BY ClauseHAVING ClauseIN OperatorLIKE OperatorLIMIT ClauseORDER BY ClauseTOP ClauseWHERE ClauseWildcard CharactersEXCEPT Exclusion Rows OperatorINTERSECT Mutual Rows OperatorMINUS Difference Operator DatabaseUNION Deduplicated Set OperatorUNION ALL Combined Stack OperatorCROSS JOINFULL OUTER JOININNER JOINJOIN vs UNIONLEFT JOINRIGHT JOINSELF JOINSQL Joins OverviewANY and ALL OperatorsCASE ExpressionCorrelated SubqueryEXISTS OperatorNested SubqueriesScalar SubqueriesSubquery IntroductionAggregate FunctionsAVG FunctionConversion FunctionsCOUNT FunctionDate FunctionsMIN and MAX FunctionsNumeric FunctionsString FunctionsSUM FunctionALTER TABLECreate TableDROP TABLERename TableTemporary TableTRUNCATE TABLEBackup DatabaseCreate DatabaseDrop DatabaseRestore DatabaseCREATE VIEW Saved SELECT StatementsDROP VIEW Discard virtual tablesMaterialized Views Cash & StorageUPDATE VIEW ProceduresCreate ProcedureExecute ProcedureParameters in ProcedureProcedure vs FunctionStored Procedure IntroductionAFTER TriggerBEFORE TriggerINSTEAD OF TriggerTrigger ExamplesTrigger IntroductionACID Properties in SQLCOMMIT in SQLIsolation Levels in SQLROLLBACK in SQLSAVEPOINT in SQLRelational Transactions Control BasicsBoyce-Codd Normal Form BCNFDatabase NormalizationDenormalizationFirst Normal Form 1NFSecond Normal Form 2NFThird Normal Form 3NFClustered Indexes Storage Physical LayoutIndex PerformanceNon-Clustered IndexSQL Indexing Strategies IntroductionUnique IndexCommon Table Expressions CTEDynamic SQLFunctions in SQLMaterialized ViewsMERGE StatementPIVOT and UNPIVOTRANK, DENSERANK, and ROWNUMBERRecursive CTEStored ProceduresTriggers in SQLViews in SQLWindow FunctionsSQL Interview Questions AdvancedSQL Interview Questions BeginnerSQL Interview Questions IntermediateSQL Output-Based Interview QuestionsSQL Scenario-Based Interview QuestionsAnalyzing Execution Plan DiagramsEXPLAIN PlanIndexing StrategiesPartitioningQuery OptimizationSQL Performance TipsSQL ChallengesSQL Exercises AdvancedSQL Exercises BeginnerSQL Exercises IntermediateSQL ProjectsCreating Your First Database ConnectionInstall MySQLInstall PostgreSQLInstall SQLiteSQL Online CompilersSQL Roadmap
SQL Scenario-Based Interview Questions
title: SQL Scenario-Based Interview Questions
In beginner and intermediate interviews, you are often asked:
text exampleWoHoTech
Definitions
Concepts
Differences
TheoryHowever, in real-world interviews for:
text exampleWoHoTech
Software Engineer
Backend Developer
SQL Developer
Database Engineer
Data Analystinterviewers usually focus on:
text exampleWoHoTech
Scenarios
Problem Solving
Query Writing
OptimizationThese questions test your ability to apply SQL knowledge in practical situations.
Scenario 1
Find the Second Highest Salary
Table:
text exampleWoHoTech
Employees| EmployeeID | Salary |
|---|---|
| 1 | 80000 |
| 2 | 70000 |
| 3 | 60000 |
Solution
sql exampleWoHoTech
SELECT MAX(Salary)
FROM Employees
WHERE Salary <
(
SELECT MAX(Salary)
FROM Employees
);Alternative
sql exampleWoHoTech
SELECT Salary
FROM
(
SELECT Salary,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
) AS RankNum
FROM Employees
) X
WHERE RankNum = 2;Scenario 2
Find the Third Highest Salary
Solution
sql exampleWoHoTech
SELECT Salary
FROM
(
SELECT Salary,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
) AS RankNum
FROM Employees
) X
WHERE RankNum = 3;Scenario 3
Find Employees Earning More Than Department Average
Solution
sql exampleWoHoTech
SELECT *
FROM Employees E
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID =
E.DepartmentID
);Scenario 4
Find Duplicate Records
Table:
text exampleWoHoTech
CustomersSolution
sql exampleWoHoTech
SELECT
Email,
COUNT(*) AS Total
FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;Scenario 5
Delete Duplicate Records
Keep only one record.
Solution
sql exampleWoHoTech
WITH Duplicates AS
(
SELECT *,
ROW_NUMBER()
OVER
(
PARTITION BY Email
ORDER BY CustomerID
) AS RN
FROM Customers
)
DELETE
FROM Duplicates
WHERE RN > 1;Scenario 6
Find Employees Without Department
Solution
sql exampleWoHoTech
SELECT *
FROM Employees E
LEFT JOIN Departments D
ON E.DepartmentID =
D.DepartmentID
WHERE D.DepartmentID IS NULL;Scenario 7
Find Departments Without Employees
Solution
sql exampleWoHoTech
SELECT *
FROM Departments D
LEFT JOIN Employees E
ON D.DepartmentID =
E.DepartmentID
WHERE E.EmployeeID IS NULL;Scenario 8
Find Top 3 Highest Salaries
Solution
sql exampleWoHoTech
SELECT *
FROM
(
SELECT EmployeeName,
Salary,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
) AS Rnk
FROM Employees
) X
WHERE Rnk <= 3;Scenario 9
Find Nth Highest Salary
Solution
sql exampleWoHoTech
SELECT Salary
FROM
(
SELECT Salary,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
) AS Rnk
FROM Employees
) X
WHERE Rnk = N;Scenario 10
Find Employees Joined in Last 30 Days
Solution
sql exampleWoHoTech
SELECT *
FROM Employees
WHERE JoinDate >=
CURRENT_DATE - 30;Scenario 11
Find Customers Who Never Placed Orders
Solution
sql exampleWoHoTech
SELECT *
FROM Customers C
LEFT JOIN Orders O
ON C.CustomerID =
O.CustomerID
WHERE O.OrderID IS NULL;Scenario 12
Find Most Frequently Ordered Product
Solution
sql exampleWoHoTech
SELECT ProductID,
COUNT(*) AS TotalOrders
FROM OrderItems
GROUP BY ProductID
ORDER BY TotalOrders DESC
LIMIT 1;Scenario 13
Find Running Total
Solution
sql exampleWoHoTech
SELECT
EmployeeID,
Salary,
SUM(Salary)
OVER
(
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Scenario 14
Rank Employees By Salary
Solution
sql exampleWoHoTech
SELECT
EmployeeName,
Salary,
RANK()
OVER
(
ORDER BY Salary DESC
)
AS RankNum
FROM Employees;Scenario 15
Find Consecutive Login Days
Interview favorite.
Solution Idea
Use:
text exampleWoHoTech
ROW_NUMBER()
DATE Difference
CTEsto identify consecutive sequences.
Scenario 16
Find Monthly Sales Report
Solution
sql exampleWoHoTech
SELECT
YEAR(OrderDate),
MONTH(OrderDate),
SUM(Amount)
FROM Orders
GROUP BY
YEAR(OrderDate),
MONTH(OrderDate);Scenario 17
Pivot Sales Data
Input:
text exampleWoHoTech
Month
SalesOutput:
text exampleWoHoTech
Jan Feb MarSolution
Use:
sql exampleWoHoTech
PIVOToperator.
Scenario 18
Find Employees Reporting To Manager
Solution
sql exampleWoHoTech
SELECT
E.EmployeeName,
M.EmployeeName AS Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID =
M.EmployeeID;Scenario 19
Find Manager With Most Employees
Solution
sql exampleWoHoTech
SELECT
ManagerID,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY ManagerID
ORDER BY TotalEmployees DESC;Scenario 20
Find Missing IDs
IDs:
text exampleWoHoTech
1
2
4
5Missing:
text exampleWoHoTech
3Solution
Use:
sql exampleWoHoTech
Numbers Table
CTEto identify gaps.
Scenario 21
Find Latest Order Per Customer
Solution
sql exampleWoHoTech
SELECT *
FROM
(
SELECT *,
ROW_NUMBER()
OVER
(
PARTITION BY CustomerID
ORDER BY OrderDate DESC
) AS RN
FROM Orders
) X
WHERE RN = 1;Scenario 22
Find Highest Paid Employee In Each Department
Solution
sql exampleWoHoTech
SELECT *
FROM
(
SELECT *,
DENSE_RANK()
OVER
(
PARTITION BY DepartmentID
ORDER BY Salary DESC
) AS Rnk
FROM Employees
) X
WHERE Rnk = 1;Scenario 23
Find Duplicate Emails
Solution
sql exampleWoHoTech
SELECT Email
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;Scenario 24
Find Customers With More Than 5 Orders
Solution
sql exampleWoHoTech
SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 5;Scenario 25
Find Percentage Contribution
Solution
sql exampleWoHoTech
SELECT
DepartmentID,
SUM(Salary) * 100.0 /
SUM(SUM(Salary))
OVER()
AS Percentage
FROM Employees
GROUP BY DepartmentID;Scenario 26
Delete Records Older Than 1 Year
Solution
sql exampleWoHoTech
DELETE
FROM Logs
WHERE LogDate <
CURRENT_DATE - 365;Scenario 27
Find Products Never Sold
Solution
sql exampleWoHoTech
SELECT *
FROM Products P
LEFT JOIN OrderItems O
ON P.ProductID =
O.ProductID
WHERE O.ProductID IS NULL;Scenario 28
Calculate Moving Average
Solution
sql exampleWoHoTech
SELECT
SalesDate,
Amount,
AVG(Amount)
OVER
(
ORDER BY SalesDate
)
AS MovingAverage
FROM Sales;Scenario 29
Find Top Customer By Revenue
Solution
sql exampleWoHoTech
SELECT CustomerID,
SUM(Amount) AS Revenue
FROM Orders
GROUP BY CustomerID
ORDER BY Revenue DESC
LIMIT 1;Scenario 30
Synchronize Two Tables
Solution
Use:
sql exampleWoHoTech
MERGEstatement.
Example:
sql exampleWoHoTech
MERGE Target T
USING Source S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE ...
WHEN NOT MATCHED THEN
INSERT ...;Most Asked Scenario Questions
text exampleWoHoTech
Second Highest Salary
Nth Highest Salary
Duplicate Records
Running Total
Top N Records
Latest Record Per Group
Department-wise Highest Salary
Customers Without Orders
Employees Without Departments
Pivot DataInterview Tips
Prefer Window Functions
Use:
sql exampleWoHoTech
ROW_NUMBER()
RANK()
DENSE_RANK()for ranking problems.
Use CTEs
Improve readability.
Avoid Nested Queries When Possible
Window functions are often cleaner.
Consider Performance
Interviewers may ask:
text exampleWoHoTech
Can This Query Be Optimized?Explain Your Logic
Correct explanation matters as much as the query.
Quick Revision
text exampleWoHoTech
✓ Nth Highest Salary
✓ Duplicate Records
✓ Running Totals
✓ Ranking
✓ Latest Record Per Group
✓ Missing Records
✓ Pivoting
✓ Aggregation
✓ Window Functions
✓ MERGESummary
Scenario-based SQL questions test practical database skills rather than theoretical knowledge. They focus on real-world business problems involving joins, aggregations, ranking, window functions, data cleanup, reporting, and optimization.
In this lesson, you learned:
- 30 Real-World SQL Scenarios
- Ranking Problems
- Duplicate Handling
- Aggregation Problems
- Window Functions
- Latest Record Queries
- Data Synchronization
- Reporting Queries
- Optimization Considerations
Next Step
Continue to the final interview section:
SQL Output-Based Interview Questions →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQL Scenario-Based Interview Questions.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, interview, questions
Related SQL Topics
Continue learning this concept
Interview QuestionsSQL Interview Questions Advancedtitle: SQL Interview Questions AdvancedInterview QuestionsSQL Interview Questions Beginnertitle: SQL Interview Questions BeginnerInterview QuestionsSQL Interview Questions Intermediatetitle: SQL Interview Questions IntermediateInterview QuestionsSQL Output-Based Interview Questionstitle: SQL Output-Based Interview QuestionsAdvanced SQLTriggers in SQLtitle: Triggers in SQLAdvanced SQLCommon Table Expressions CTEtitle: Common Table Expressions CTE