SQL Notes
HubAdvantages and Disadvantages of SQLFeatures of SQLHistory of SQLSQL vs NoSQLWhat is SQL?SQL CommentsSQL Data TypesSQL KeywordsSQL OperatorsSQL SyntaxCHECK Constraint in SQLComposite Keys in SQLDEFAULT Constraint in SQLFOREIGN KEY Constraint in SQLNOT NULL Constraint in SQLPRIMARY KEY Constraint in SQLSQL ConstraintsUNIQUE Key Constraint in SQLDELETE DataINSERT DataSELECT DataSQL AliasesUPDATE DataBETWEEN OperatorDISTINCT KeywordGROUP BY ClauseHAVING ClauseIN OperatorLIKE OperatorLIMIT ClauseORDER BY ClauseTOP ClauseWHERE ClauseWildcard CharactersEXCEPT Operator in SQLINTERSECT Operator in SQLMINUS Operator in SQLUNION Operator in SQLUNION ALL Operator in SQLCROSS JOIN in SQLFULL OUTER JOININNER JOINJOIN vs UNIONLEFT JOINRIGHT JOINSELF JOINSQL Joins OverviewANY and ALL OperatorsCASE Expression in SQLCorrelated SubqueryEXISTS OperatorNested Subqueries in SQLScalar SubqueriesSubquery IntroductionAggregate FunctionsAVG FunctionConversion FunctionsCOUNT FunctionDate FunctionsMIN and MAX FunctionsNumeric FunctionsString FunctionsSUM FunctionALTER TABLECreate TableDROP TABLERename TableTemporary TableTRUNCATE TABLEBackup DatabaseCreate DatabaseDrop DatabaseRestore DatabaseCREATE VIEW in SQLDROP VIEW in SQLMaterialized Views in SQLUpdate and Modify Views in SQLCreate Stored Procedure in SQLExecute Stored Procedures in SQLParameters in Stored ProceduresStored Procedure vs Function in SQLStored Procedure IntroductionAFTER TriggerBEFORE TriggerINSTEAD OF TriggerTrigger ExamplesTrigger IntroductionACID Properties in SQLCOMMIT in SQLIsolation Levels in SQLROLLBACK in SQLSAVEPOINT in SQLIntroduction to SQL TransactionsBoyce-Codd Normal Form (BCNF)Database NormalizationDenormalizationFirst Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQLIndex PerformanceNon-Clustered IndexIntroduction to SQL IndexesUnique IndexSQL Execution PlansEXPLAIN PlanIndexing StrategiesPartitioningQuery OptimizationSQL Performance TipsCommon Table Expressions (CTE)Dynamic SQLFunctions in SQLMaterialized ViewsMERGE StatementPIVOT and UNPIVOTRANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTEStored ProceduresTriggers in SQLViews in SQLWindow FunctionsSQL ChallengesSQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL ProjectsSQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview QuestionsSQL Scenario-Based Interview QuestionsCreate Your First DatabaseInstall MySQL on Your ComputerInstall PostgreSQLInstall SQLiteSQL Online CompilersSQL Roadmap
Practice advanced-level SQL exercises covering window functions, recursive CTEs, optimization, partitioning, transactions, analytics, and real-world SQL challenges.
Welcome to Advanced SQL Practice.
At this level, interviewers expect you to solve:
text example
Complex Business Problems
Performance Challenges
Analytics Queries
Window Functions
Recursive Queries
Optimization TasksThese exercises are commonly asked for:
text example
Senior SQL Developer
Backend Engineer
Database Engineer
Data Engineer
DBAExercise 2
Find Top 3 Salaries In Each Department
sql example
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 example
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 example
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 example
SELECT
DepartmentID,
EmployeeName,
Salary,
SUM(Salary)
OVER
(
PARTITION BY DepartmentID
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Exercise 6
Moving Average Salary
sql example
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 example
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 example
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 example
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 example
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 example
SELECT
EmployeeName,
COUNT(*)
FROM Employees
GROUP BY EmployeeName
HAVING COUNT(*) > 1;Exercise 12
Delete Duplicate Rows
sql example
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 example
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 example
SELECT
DepartmentID,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID
ORDER BY TotalEmployees DESC
LIMIT 1;Exercise 15
Customer With Highest Revenue
sql example
SELECT
CustomerID,
SUM(Amount) AS Revenue
FROM Orders
GROUP BY CustomerID
ORDER BY Revenue DESC
LIMIT 1;Exercise 16
Monthly Revenue Report
sql example
SELECT
YEAR(OrderDate),
MONTH(OrderDate),
SUM(Amount)
FROM Orders
GROUP BY
YEAR(OrderDate),
MONTH(OrderDate);Exercise 17
Top Selling Product
sql example
SELECT
ProductID,
COUNT(*) AS TotalSales
FROM OrderItems
GROUP BY ProductID
ORDER BY TotalSales DESC
LIMIT 1;Exercise 18
Pivot Sales Data
sql example
SELECT *
FROM Sales
PIVOT
(
SUM(Amount)
FOR Month
IN
(
Jan,
Feb,
Mar
)
) P;Exercise 19
Unpivot Data
sql example
SELECT *
FROM Sales
UNPIVOT
(
Amount
FOR Month
IN
(
Jan,
Feb,
Mar
)
) U;Exercise 20
Dynamic Ranking
sql example
SELECT
EmployeeName,
Salary,
RANK()
OVER
(
ORDER BY Salary DESC
)
AS RankNum
FROM Employees;Exercise 21
Analyze Query With EXPLAIN
sql example
EXPLAIN
SELECT *
FROM Employees
WHERE DepartmentID = 1;Exercise 22
Create Composite Index
sql example
CREATE INDEX IX_DepSalary
ON Employees
(
DepartmentID,
Salary
);Exercise 23
Create Covering Index
sql example
CREATE INDEX IX_Covering
ON Employees
(
DepartmentID,
EmployeeID,
EmployeeName
);Exercise 24
Create Partitioned Table
sql example
CREATE TABLE Transactions
(
TransactionID INT,
TransactionDate DATE
)
PARTITION BY RANGE
(
YEAR(TransactionDate)
);Exercise 25
Partition Pruning Test
sql example
SELECT *
FROM Transactions
WHERE TransactionDate
BETWEEN '2025-01-01'
AND '2025-01-31';Exercise 26
MERGE Two Tables
sql example
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 example
DECLARE @SQL VARCHAR(500);
SET @SQL =
'SELECT *
FROM Employees';
EXEC(@SQL);Exercise 28
Materialized View
sql example
CREATE MATERIALIZED VIEW EmployeeSummary AS
SELECT
DepartmentID,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID;Exercise 29
Transaction With Savepoint
sql example
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:
Output
Transaction A locks Row 1 Transaction B locks Row 2 A waits for Row 2 B waits for Row 1
Task:
text example
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 example
✓ 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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, practice, exercises, advanced
Related SQL Complete Guide Topics
Continue learning this concept
PracticeSQL Exercises (Beginner)Practice beginner-level SQL exercises covering SELECT, WHERE, ORDER BY, GROUP BY, JOINs, aggregate functions, and basic database operations.PracticeSQL Exercises (Intermediate)Practice intermediate-level SQL exercises covering joins, subqueries, CTEs, window functions, transactions, indexes, and optimization.PracticeSQL ChallengesSolve advanced SQL challenges that test query writing, optimization, analytics, window functions, recursive CTEs, and real-world database problem-solving skills.PracticeSQL ProjectsBuild real-world SQL projects to practice database design, CRUD operations, joins, transactions, reporting, optimization, and enterprise-level SQL concepts.Advanced SQLTriggers in SQLLearn SQL Triggers in detail, understand automatic database actions, BEFORE and AFTER triggers, event-driven execution, auditing, validation, and real-world applications.Advanced SQLCommon Table Expressions (CTE)Learn Common Table Expressions (CTE) in SQL in detail, understand CTE syntax, recursive CTEs, advantages, performance considerations, and real-world examples.