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
Solve advanced SQL challenges that test query writing, optimization, analytics, window functions, recursive CTEs, and real-world database problem-solving skills.
🎉 Congratulations!
You have completed:
text example
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 example
SQL ChallengesThese challenges simulate:
text example
Real Interviews
Production Problems
Data Analytics Tasks
Database Engineering ScenariosMost companies use similar questions during:
text example
SQL Interviews
Online Assessments
Data Analyst Tests
Backend Developer InterviewsExpected Output
text example
70000Solution
sql example
SELECT MAX(Salary)
FROM Employees
WHERE Salary <
(
SELECT MAX(Salary)
FROM Employees
);Challenge 2
Find Nth Highest Salary
Solution
sql example
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 example
SELECT
Email,
COUNT(*)
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;Challenge 4
Delete Duplicate Rows
Keep only one row.
Solution
sql example
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 example
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 example
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 example
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 example
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 example
SELECT
EmployeeID,
Salary,
SUM(Salary)
OVER
(
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Challenge 10
Moving Average
Solution
sql example
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:
Output
1 2 4 5
Missing:
text example
3Solution
sql example
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:
Output
ROW_NUMBER() Date Difference CTE
Challenge 13
Find Top 10% Employees By Salary
Solution
sql example
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 example
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 example
SELECT
ProductID,
COUNT(*) AS Orders
FROM OrderItems
GROUP BY ProductID
ORDER BY Orders DESC
LIMIT 1;Challenge 16
Top Customer By Revenue
Solution
sql example
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:
Output
Month 1 and Month 2
Hint
Use:
text example
CTE
JOIN
GROUP BYChallenge 18
Year-over-Year Growth
Task
Calculate:
text example
Current Year Revenue
Previous Year Revenue
Growth %Hint
Use:
text example
LAG()
Window FunctionsChallenge 19
Department Salary Contribution %
Solution
sql example
SELECT
DepartmentID,
SUM(Salary) * 100.0 /
SUM(SUM(Salary))
OVER()
AS Contribution
FROM Employees
GROUP BY DepartmentID;Challenge 20
Pivot Monthly Sales
Input:
Output
Jan Feb Mar
Output:
text example
Jan Feb Marcolumns.
Hint
Use:
sql example
PIVOTChallenge 21
Build Employee Hierarchy
Solution
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;Challenge 22
Detect Circular Manager Relationships
Hint
Use:
Output
Recursive CTE Hierarchy Traversal
Challenge 23
Find Gaps And Islands
Classic SQL Interview Problem.
Example
Output
1 2 3 7 8 12 13 14
Find continuous ranges.
Challenge 24
Detect Duplicate Transactions
Hint
Check:
text example
Customer
Amount
Timestampcombinations.
Challenge 25
Rank Products By Revenue
Solution
sql example
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 example
SELECT *
FROM Orders
WHERE CustomerEmail =
'abc@gmail.com';Task
Improve performance.
Answer
Create index:
sql example
CREATE INDEX IX_Email
ON Orders(CustomerEmail);Challenge 27
Analyze Execution Plan
Task
Identify:
Output
Table Scan Index Scan Index Seek Sort Hash Join
from an execution plan.
Challenge 28
Design Banking Transfer Transaction
Requirements:
text example
Money Transfer
Rollback On Failure
ConsistencyHint
Use:
text example
BEGIN
COMMIT
ROLLBACKChallenge 29
Design E-Commerce Schema
Tables:
text example
Products
Customers
Orders
PaymentsTask
Create normalized schema.
Challenge 30
Build Complete Sales Dashboard Query
Metrics:
text example
Revenue
Orders
Customers
Top Products
Growth %Hint
Combine:
text example
CTE
Window Functions
GROUP BY
JoinsUltimate SQL Challenge
Build a complete:
text example
E-Commerce Analytics Databaseincluding:
text example
Schema Design
CRUD Operations
Indexes
Views
Stored Procedures
Triggers
Reports
Window Functions
Optimization
PartitioningSQL Master Checklist
After finishing all modules you should know:
text example
✓ 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 ✅
Example
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 example
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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, practice, challenges, sql challenges
Related SQL Complete Guide Topics
Continue learning this concept
PracticeSQL Exercises (Advanced)Practice advanced-level SQL exercises covering window functions, recursive CTEs, optimization, partitioning, transactions, analytics, and real-world SQL challenges.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 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.