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 Exercises Advanced
title: SQL Exercises Advanced
Welcome to Advanced SQL Practice.
At this level, interviewers expect you to solve:
text exampleWoHoTech
Complex Business Problems
Performance Challenges
Analytics Queries
Window Functions
Recursive Queries
Optimization TasksThese exercises are commonly asked for:
text exampleWoHoTech
Senior SQL Developer
Backend Engineer
Database Engineer
Data Engineer
DBAExercise 1
Find Second Highest Salary In Each Department
sql exampleWoHoTech
SELECT *
FROM
(
SELECT
EmployeeName,
DepartmentID,
Salary,
DENSE_RANK()
OVER
(
PARTITION BY DepartmentID
ORDER BY Salary DESC
) AS Rnk
FROM Employees
) X
WHERE Rnk = 2;Exercise 2
Find Top 3 Salaries In Each Department
sql exampleWoHoTech
SELECT *
FROM
(
SELECT *,
DENSE_RANK()
OVER
(
PARTITION BY DepartmentID
ORDER BY Salary DESC
) AS Rnk
FROM Employees
) X
WHERE Rnk <= 3;Exercise 3
Find Employees Earning Above Department Average
sql exampleWoHoTech
SELECT *
FROM Employees E
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID =
E.DepartmentID
);Exercise 4
Find Highest Paid Employee In Every Department
sql exampleWoHoTech
SELECT *
FROM
(
SELECT *,
ROW_NUMBER()
OVER
(
PARTITION BY DepartmentID
ORDER BY Salary DESC
) AS RN
FROM Employees
) X
WHERE RN = 1;Exercise 5
Running Total By Department
sql exampleWoHoTech
SELECT
DepartmentID,
EmployeeName,
Salary,
SUM(Salary)
OVER
(
PARTITION BY DepartmentID
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Exercise 6
Moving Average Salary
sql exampleWoHoTech
SELECT
EmployeeID,
Salary,
AVG(Salary)
OVER
(
ORDER BY EmployeeID
ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW
)
AS MovingAverage
FROM Employees;Exercise 7
Find Salary Difference From Department Average
sql exampleWoHoTech
SELECT
EmployeeName,
Salary,
AVG(Salary)
OVER
(
PARTITION BY DepartmentID
)
AS AvgSalary,
Salary -
AVG(Salary)
OVER
(
PARTITION BY DepartmentID
)
AS Difference
FROM Employees;Exercise 8
Generate Numbers 1–100 Using Recursive CTE
sql exampleWoHoTech
WITH Numbers AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Numbers
WHERE Num < 100
)
SELECT *
FROM Numbers;Exercise 9
Employee Hierarchy
sql exampleWoHoTech
WITH EmployeeHierarchy AS
(
SELECT
EmployeeID,
EmployeeName,
ManagerID
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT
E.EmployeeID,
E.EmployeeName,
E.ManagerID
FROM Employees E
JOIN EmployeeHierarchy EH
ON E.ManagerID =
EH.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;Exercise 10
Find Missing Employee IDs
sql exampleWoHoTech
WITH Numbers AS
(
SELECT 1 AS ID
UNION ALL
SELECT ID + 1
FROM Numbers
WHERE ID < 100
)
SELECT ID
FROM Numbers
EXCEPT
SELECT EmployeeID
FROM Employees;Exercise 11
Detect Duplicate Employees
sql exampleWoHoTech
SELECT
EmployeeName,
COUNT(*)
FROM Employees
GROUP BY EmployeeName
HAVING COUNT(*) > 1;Exercise 12
Delete Duplicate Rows
sql exampleWoHoTech
WITH Duplicates AS
(
SELECT *,
ROW_NUMBER()
OVER
(
PARTITION BY Email
ORDER BY EmployeeID
) AS RN
FROM Employees
)
DELETE
FROM Duplicates
WHERE RN > 1;Exercise 13
Find Latest Employee Per Department
sql exampleWoHoTech
SELECT *
FROM
(
SELECT *,
ROW_NUMBER()
OVER
(
PARTITION BY DepartmentID
ORDER BY JoinDate DESC
) AS RN
FROM Employees
) X
WHERE RN = 1;Exercise 14
Department With Maximum Employees
sql exampleWoHoTech
SELECT
DepartmentID,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID
ORDER BY TotalEmployees DESC
LIMIT 1;Exercise 15
Customer With Highest Revenue
sql exampleWoHoTech
SELECT
CustomerID,
SUM(Amount) AS Revenue
FROM Orders
GROUP BY CustomerID
ORDER BY Revenue DESC
LIMIT 1;Exercise 16
Monthly Revenue Report
sql exampleWoHoTech
SELECT
YEAR(OrderDate),
MONTH(OrderDate),
SUM(Amount)
FROM Orders
GROUP BY
YEAR(OrderDate),
MONTH(OrderDate);Exercise 17
Top Selling Product
sql exampleWoHoTech
SELECT
ProductID,
COUNT(*) AS TotalSales
FROM OrderItems
GROUP BY ProductID
ORDER BY TotalSales DESC
LIMIT 1;Exercise 18
Pivot Sales Data
sql exampleWoHoTech
SELECT *
FROM Sales
PIVOT
(
SUM(Amount)
FOR Month
IN
(
Jan,
Feb,
Mar
)
) P;Exercise 19
Unpivot Data
sql exampleWoHoTech
SELECT *
FROM Sales
UNPIVOT
(
Amount
FOR Month
IN
(
Jan,
Feb,
Mar
)
) U;Exercise 20
Dynamic Ranking
sql exampleWoHoTech
SELECT
EmployeeName,
Salary,
RANK()
OVER
(
ORDER BY Salary DESC
)
AS RankNum
FROM Employees;Exercise 21
Analyze Query With EXPLAIN
sql exampleWoHoTech
EXPLAIN
SELECT *
FROM Employees
WHERE DepartmentID = 1;Exercise 22
Create Composite Index
sql exampleWoHoTech
CREATE INDEX IX_DepSalary
ON Employees
(
DepartmentID,
Salary
);Exercise 23
Create Covering Index
sql exampleWoHoTech
CREATE INDEX IX_Covering
ON Employees
(
DepartmentID,
EmployeeID,
EmployeeName
);Exercise 24
Create Partitioned Table
sql exampleWoHoTech
CREATE TABLE Transactions
(
TransactionID INT,
TransactionDate DATE
)
PARTITION BY RANGE
(
YEAR(TransactionDate)
);Exercise 25
Partition Pruning Test
sql exampleWoHoTech
SELECT *
FROM Transactions
WHERE TransactionDate
BETWEEN '2025-01-01'
AND '2025-01-31';Exercise 26
MERGE Two Tables
sql exampleWoHoTech
MERGE Target T
USING Source S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE
SET T.Name = S.Name
WHEN NOT MATCHED THEN
INSERT
(
ID,
Name
)
VALUES
(
S.ID,
S.Name
);Exercise 27
Dynamic SQL
sql exampleWoHoTech
DECLARE @SQL VARCHAR(500);
SET @SQL =
'SELECT *
FROM Employees';
EXEC(@SQL);Exercise 28
Materialized View
sql exampleWoHoTech
CREATE MATERIALIZED VIEW EmployeeSummary AS
SELECT
DepartmentID,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID;Exercise 29
Transaction With Savepoint
sql exampleWoHoTech
BEGIN;
UPDATE Accounts
SET Balance =
Balance - 100
WHERE AccountID = 1;
SAVEPOINT SP1;
UPDATE Accounts
SET Balance =
Balance + 100
WHERE AccountID = 2;
COMMIT;Exercise 30
Deadlock Analysis
Scenario:
text exampleWoHoTech
Transaction A locks Row 1
Transaction B locks Row 2
A waits for Row 2
B waits for Row 1Task:
text exampleWoHoTech
Identify Deadlock
Suggest PreventionAdvanced Challenge Questions
Challenge 1
Find the 5th highest salary.
Challenge 2
Find consecutive login days.
Challenge 3
Find employees whose salary is above 90% of employees.
Challenge 4
Find customers who purchased every product.
Challenge 5
Find longest employee tenure.
Challenge 6
Build department hierarchy using Recursive CTE.
Challenge 7
Identify gaps and islands problem.
Challenge 8
Find top 10% customers by revenue.
Challenge 9
Calculate year-over-year growth.
Challenge 10
Build monthly retention report.
Advanced Practice Checklist
text exampleWoHoTech
✓ Window Functions
✓ Recursive CTE
✓ Ranking
✓ Running Totals
✓ Moving Averages
✓ Hierarchies
✓ Pivot & Unpivot
✓ MERGE
✓ Dynamic SQL
✓ Materialized Views
✓ Partitioning
✓ Optimization
✓ Transactions
✓ DeadlocksSummary
These advanced SQL exercises simulate real-world database problems involving analytics, reporting, optimization, scalability, and enterprise-level query writing. Completing them will prepare you for senior-level interviews and production database environments.
Next Step
Continue to:
SQL Projects →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQL Exercises Advanced.
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, practice, exercises
Related SQL Topics
Continue learning this concept
PracticeSQL Exercises Beginnertitle: SQL Exercises BeginnerPracticeSQL Exercises Intermediatetitle: SQL Exercises IntermediatePracticeSQL Challengestitle: SQL ChallengesPracticeSQL Projectstitle: SQL ProjectsAdvanced SQLTriggers in SQLtitle: Triggers in SQLInterview QuestionsSQL Interview Questions Advancedtitle: SQL Interview Questions Advanced