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 Challenges
title: SQL Challenges
🎉 Congratulations!
You have completed:
text exampleWoHoTech
Introduction
Setup
Basics
CRUD
Filtering
Functions
Joins
Subqueries
Set Operators
Constraints
Indexing
Views
Stored Procedures
Triggers
Transactions
Normalization
Advanced SQL
Optimization
Interview Questions
Exercises
ProjectsNow comes the final section:
text exampleWoHoTech
SQL ChallengesThese challenges simulate:
text exampleWoHoTech
Real Interviews
Production Problems
Data Analytics Tasks
Database Engineering ScenariosMost companies use similar questions during:
text exampleWoHoTech
SQL Interviews
Online Assessments
Data Analyst Tests
Backend Developer InterviewsChallenge 1
Find Second Highest Salary
Table:
text exampleWoHoTech
Employees| EmployeeID | Salary |
|---|---|
| 1 | 80000 |
| 2 | 70000 |
| 3 | 60000 |
Expected Output
text exampleWoHoTech
70000Solution
sql exampleWoHoTech
SELECT MAX(Salary)
FROM Employees
WHERE Salary <
(
SELECT MAX(Salary)
FROM Employees
);Challenge 2
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;Challenge 3
Find Duplicate Emails
Solution
sql exampleWoHoTech
SELECT
Email,
COUNT(*)
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;Challenge 4
Delete Duplicate Rows
Keep only one row.
Solution
sql exampleWoHoTech
WITH Duplicates AS
(
SELECT *,
ROW_NUMBER()
OVER
(
PARTITION BY Email
ORDER BY UserID
) AS RN
FROM Users
)
DELETE
FROM Duplicates
WHERE RN > 1;Challenge 5
Find Customers Who Never Ordered
Solution
sql exampleWoHoTech
SELECT *
FROM Customers C
LEFT JOIN Orders O
ON C.CustomerID =
O.CustomerID
WHERE O.OrderID IS NULL;Challenge 6
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;Challenge 7
Highest Salary 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;Challenge 8
Second Highest Salary 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 = 2;Challenge 9
Running Total
Solution
sql exampleWoHoTech
SELECT
EmployeeID,
Salary,
SUM(Salary)
OVER
(
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Challenge 10
Moving Average
Solution
sql exampleWoHoTech
SELECT
EmployeeID,
Salary,
AVG(Salary)
OVER
(
ORDER BY EmployeeID
ROWS BETWEEN
2 PRECEDING
AND CURRENT ROW
)
AS MovingAverage
FROM Employees;Challenge 11
Find Missing IDs
Example:
text exampleWoHoTech
1
2
4
5Missing:
text exampleWoHoTech
3Solution
sql exampleWoHoTech
WITH Numbers AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Numbers
WHERE Num < 100
)
SELECT Num
FROM Numbers
EXCEPT
SELECT EmployeeID
FROM Employees;Challenge 12
Consecutive Login Days
Interview Favorite.
Hint
Use:
text exampleWoHoTech
ROW_NUMBER()
Date Difference
CTEChallenge 13
Find Top 10% Employees By Salary
Solution
sql exampleWoHoTech
SELECT *
FROM
(
SELECT *,
NTILE(10)
OVER
(
ORDER BY Salary DESC
) AS SalaryGroup
FROM Employees
) X
WHERE SalaryGroup = 1;Challenge 14
Latest Record Per Group
Solution
sql exampleWoHoTech
SELECT *
FROM
(
SELECT *,
ROW_NUMBER()
OVER
(
PARTITION BY DepartmentID
ORDER BY JoinDate DESC
) AS RN
FROM Employees
) X
WHERE RN = 1;Challenge 15
Most Frequently Ordered Product
Solution
sql exampleWoHoTech
SELECT
ProductID,
COUNT(*) AS Orders
FROM OrderItems
GROUP BY ProductID
ORDER BY Orders DESC
LIMIT 1;Challenge 16
Top Customer By Revenue
Solution
sql exampleWoHoTech
SELECT
CustomerID,
SUM(Amount) AS Revenue
FROM Orders
GROUP BY CustomerID
ORDER BY Revenue DESC
LIMIT 1;Challenge 17
Customer Retention Analysis
Task
Find customers who ordered in:
text exampleWoHoTech
Month 1
and
Month 2Hint
Use:
text exampleWoHoTech
CTE
JOIN
GROUP BYChallenge 18
Year-over-Year Growth
Task
Calculate:
text exampleWoHoTech
Current Year Revenue
Previous Year Revenue
Growth %Hint
Use:
text exampleWoHoTech
LAG()
Window FunctionsChallenge 19
Department Salary Contribution %
Solution
sql exampleWoHoTech
SELECT
DepartmentID,
SUM(Salary) * 100.0 /
SUM(SUM(Salary))
OVER()
AS Contribution
FROM Employees
GROUP BY DepartmentID;Challenge 20
Pivot Monthly Sales
Input:
text exampleWoHoTech
Jan
Feb
MarOutput:
text exampleWoHoTech
Jan Feb Marcolumns.
Hint
Use:
sql exampleWoHoTech
PIVOTChallenge 21
Build Employee Hierarchy
Solution
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;Challenge 22
Detect Circular Manager Relationships
Hint
Use:
text exampleWoHoTech
Recursive CTE
Hierarchy TraversalChallenge 23
Find Gaps And Islands
Classic SQL Interview Problem.
Example
text exampleWoHoTech
1
2
3
7
8
12
13
14Find continuous ranges.
Challenge 24
Detect Duplicate Transactions
Hint
Check:
text exampleWoHoTech
Customer
Amount
Timestampcombinations.
Challenge 25
Rank Products By Revenue
Solution
sql exampleWoHoTech
SELECT
ProductID,
SUM(Amount) AS Revenue,
RANK()
OVER
(
ORDER BY SUM(Amount) DESC
)
AS ProductRank
FROM Sales
GROUP BY ProductID;Challenge 26
Optimize Slow Query
Given:
sql exampleWoHoTech
SELECT *
FROM Orders
WHERE CustomerEmail =
'abc@gmail.com';Task
Improve performance.
Answer
Create index:
sql exampleWoHoTech
CREATE INDEX IX_Email
ON Orders(CustomerEmail);Challenge 27
Analyze Execution Plan
Task
Identify:
text exampleWoHoTech
Table Scan
Index Scan
Index Seek
Sort
Hash Joinfrom an execution plan.
Challenge 28
Design Banking Transfer Transaction
Requirements:
text exampleWoHoTech
Money Transfer
Rollback On Failure
ConsistencyHint
Use:
text exampleWoHoTech
BEGIN
COMMIT
ROLLBACKChallenge 29
Design E-Commerce Schema
Tables:
text exampleWoHoTech
Products
Customers
Orders
PaymentsTask
Create normalized schema.
Challenge 30
Build Complete Sales Dashboard Query
Metrics:
text exampleWoHoTech
Revenue
Orders
Customers
Top Products
Growth %Hint
Combine:
text exampleWoHoTech
CTE
Window Functions
GROUP BY
JoinsUltimate SQL Challenge
Build a complete:
text exampleWoHoTech
E-Commerce Analytics Databaseincluding:
text exampleWoHoTech
Schema Design
CRUD Operations
Indexes
Views
Stored Procedures
Triggers
Reports
Window Functions
Optimization
PartitioningSQL Master Checklist
After finishing all modules you should know:
text exampleWoHoTech
✓ SQL Syntax
✓ CRUD Operations
✓ Filtering
✓ Joins
✓ Subqueries
✓ Set Operators
✓ Constraints
✓ Indexes
✓ Views
✓ Stored Procedures
✓ Triggers
✓ Transactions
✓ Normalization
✓ CTEs
✓ Window Functions
✓ MERGE
✓ Dynamic SQL
✓ Optimization
✓ Partitioning
✓ Interview Questions
✓ Real Projects
✓ Advanced ChallengesSQL Roadmap Completed ✅
sql/
├── introduction/
├── setup/
├── basics/
├── database-operations/
├── table-operations/
├── crud/
├── filtering/
├── functions/
├── joins/
├── subqueries/
├── set-operators/
├── constraints/
├── indexing/
├── views/
├── stored-procedures/
├── triggers/
├── transactions/
├── normalization/
├── advanced-sql/
├── optimization/
├── interview-questions/
├── practice/
└── sql-roadmap.mdx
Congratulations 🎉
You have now completed a Full SQL Learning Path covering:
- SQL Fundamentals
- Intermediate SQL
- Advanced SQL
- Query Optimization
- Database Design
- Interview Preparation
- Real Projects
- Enterprise-Level Concepts
The only remaining file in your structure is:
text exampleWoHoTech
sql-roadmap.mdxwhich should serve as the master navigation page linking all folders and lessons.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQL Challenges.
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, challenges
Related SQL Topics
Continue learning this concept
PracticeSQL Exercises Advancedtitle: SQL Exercises AdvancedPracticeSQL Exercises Beginnertitle: SQL Exercises BeginnerPracticeSQL Exercises Intermediatetitle: SQL Exercises IntermediatePracticeSQL Projectstitle: SQL ProjectsAdvanced SQLTriggers in SQLtitle: Triggers in SQLAdvanced SQLCommon Table Expressions CTEtitle: Common Table Expressions CTE