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 Intermediate
title: SQL Exercises Intermediate
Welcome to Intermediate SQL Practice.
At this stage, you should already know:
text exampleWoHoTech
SELECT
WHERE
GROUP BY
HAVING
JOINS
CRUD Operations
Views
Aggregate FunctionsNow we focus on:
text exampleWoHoTech
Subqueries
Advanced JOINs
CTEs
Window Functions
Transactions
Indexes
OptimizationThese exercises are commonly asked in:
text exampleWoHoTech
Technical Interviews
Coding Assessments
Backend Development Roles
Data Analyst PositionsSample Tables
Employees
| EmployeeID | EmployeeName | DepartmentID | Salary |
|---|---|---|---|
| 1 | Rahul | 1 | 50000 |
| 2 | Priya | 2 | 70000 |
| 3 | Amit | 1 | 60000 |
| 4 | Neha | 3 | 80000 |
| 5 | Raj | 2 | 65000 |
Departments
| DepartmentID | DepartmentName |
|---|---|
| 1 | HR |
| 2 | IT |
| 3 | Finance |
Exercise 1
Find Employees Earning More Than Average Salary
sql exampleWoHoTech
SELECT *
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);Exercise 2
Find Second Highest Salary
sql exampleWoHoTech
SELECT MAX(Salary)
FROM Employees
WHERE Salary <
(
SELECT MAX(Salary)
FROM Employees
);Exercise 3
Find Third Highest Salary
sql exampleWoHoTech
SELECT Salary
FROM
(
SELECT Salary,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
) AS Rnk
FROM Employees
) X
WHERE Rnk = 3;Exercise 4
Find Department Wise Average Salary
sql exampleWoHoTech
SELECT
DepartmentID,
AVG(Salary)
FROM Employees
GROUP BY DepartmentID;Exercise 5
Find Employees With Highest Salary 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 = 1;Exercise 6
Find Employees Without Department
sql exampleWoHoTech
SELECT *
FROM Employees E
LEFT JOIN Departments D
ON E.DepartmentID =
D.DepartmentID
WHERE D.DepartmentID IS NULL;Exercise 7
Find Departments Without Employees
sql exampleWoHoTech
SELECT *
FROM Departments D
LEFT JOIN Employees E
ON D.DepartmentID =
E.DepartmentID
WHERE E.EmployeeID IS NULL;Exercise 8
Find Duplicate Salaries
sql exampleWoHoTech
SELECT
Salary,
COUNT(*)
FROM Employees
GROUP BY Salary
HAVING COUNT(*) > 1;Exercise 9
Use INNER JOIN
sql exampleWoHoTech
SELECT
EmployeeName,
DepartmentName
FROM Employees E
INNER JOIN Departments D
ON E.DepartmentID =
D.DepartmentID;Exercise 10
Use LEFT JOIN
sql exampleWoHoTech
SELECT
EmployeeName,
DepartmentName
FROM Employees E
LEFT JOIN Departments D
ON E.DepartmentID =
D.DepartmentID;Exercise 11
Find Top 3 Highest Paid Employees
sql exampleWoHoTech
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 3;Exercise 12
Find Bottom 3 Salaries
sql exampleWoHoTech
SELECT *
FROM Employees
ORDER BY Salary ASC
LIMIT 3;Exercise 13
Assign Row Numbers
sql exampleWoHoTech
SELECT
EmployeeName,
ROW_NUMBER()
OVER
(
ORDER BY Salary DESC
)
AS RowNum
FROM Employees;Exercise 14
Assign Rank
sql exampleWoHoTech
SELECT
EmployeeName,
RANK()
OVER
(
ORDER BY Salary DESC
)
AS RankNum
FROM Employees;Exercise 15
Assign Dense Rank
sql exampleWoHoTech
SELECT
EmployeeName,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
)
AS DenseRankNum
FROM Employees;Exercise 16
Calculate Running Total
sql exampleWoHoTech
SELECT
EmployeeName,
Salary,
SUM(Salary)
OVER
(
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Exercise 17
Department Wise Running Salary
sql exampleWoHoTech
SELECT
EmployeeName,
DepartmentID,
Salary,
SUM(Salary)
OVER
(
PARTITION BY DepartmentID
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Exercise 18
Use CTE
sql exampleWoHoTech
WITH HighSalary AS
(
SELECT *
FROM Employees
WHERE Salary > 60000
)
SELECT *
FROM HighSalary;Exercise 19
Use Recursive CTE
sql exampleWoHoTech
WITH Numbers AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Numbers
WHERE Num < 10
)
SELECT *
FROM Numbers;Exercise 20
Find Employees Reporting To Managers
sql exampleWoHoTech
SELECT
E.EmployeeName,
M.EmployeeName AS Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID =
M.EmployeeID;Exercise 21
Create Index
sql exampleWoHoTech
CREATE INDEX IX_Salary
ON Employees(Salary);Exercise 22
Create Composite Index
sql exampleWoHoTech
CREATE INDEX IX_DepSalary
ON Employees
(
DepartmentID,
Salary
);Exercise 23
Create Unique Index
sql exampleWoHoTech
CREATE UNIQUE INDEX IX_Email
ON Employees(Email);Exercise 24
Create View
sql exampleWoHoTech
CREATE VIEW ITEmployees AS
SELECT *
FROM Employees
WHERE DepartmentID = 2;Exercise 25
Create Materialized View
(PostgreSQL)
sql exampleWoHoTech
CREATE MATERIALIZED VIEW EmployeeSummary AS
SELECT
DepartmentID,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID;Exercise 26
Start Transaction
sql exampleWoHoTech
BEGIN;Exercise 27
Commit Transaction
sql exampleWoHoTech
COMMIT;Exercise 28
Rollback Transaction
sql exampleWoHoTech
ROLLBACK;Exercise 29
Create Savepoint
sql exampleWoHoTech
SAVEPOINT SP1;Exercise 30
Rollback To Savepoint
sql exampleWoHoTech
ROLLBACK TO SP1;Exercise 31
Use CASE Expression
SELECT
EmployeeName,
CASE
WHEN Salary > 70000
THEN 'High'
WHEN Salary > 60000
THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM Employees;
Exercise 32
Use EXISTS
sql exampleWoHoTech
SELECT *
FROM Employees E
WHERE EXISTS
(
SELECT 1
FROM Departments D
WHERE D.DepartmentID =
E.DepartmentID
);Exercise 33
Use NOT EXISTS
sql exampleWoHoTech
SELECT *
FROM Departments D
WHERE NOT EXISTS
(
SELECT 1
FROM Employees E
WHERE E.DepartmentID =
D.DepartmentID
);Exercise 34
Use UNION
sql exampleWoHoTech
SELECT EmployeeName
FROM Employees
UNION
SELECT CustomerName
FROM Customers;Exercise 35
Use UNION ALL
sql exampleWoHoTech
SELECT EmployeeName
FROM Employees
UNION ALL
SELECT CustomerName
FROM Customers;Exercise 36
Use INTERSECT
sql exampleWoHoTech
SELECT Email
FROM Employees
INTERSECT
SELECT Email
FROM Customers;Exercise 37
Use EXCEPT
sql exampleWoHoTech
SELECT Email
FROM Employees
EXCEPT
SELECT Email
FROM Customers;Exercise 38
Analyze Query Using EXPLAIN
sql exampleWoHoTech
EXPLAIN
SELECT *
FROM Employees
WHERE Salary > 60000;Exercise 39
Find Latest Employee
sql exampleWoHoTech
SELECT *
FROM Employees
ORDER BY EmployeeID DESC
LIMIT 1;Exercise 40
Find Highest Salary Department
sql exampleWoHoTech
SELECT
DepartmentID,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
ORDER BY AvgSalary DESC
LIMIT 1;Intermediate Challenge Questions
Try solving these yourself.
Challenge 1
Find Nth highest salary.
Challenge 2
Find duplicate employee names.
Challenge 3
Find employees earning above department average.
Challenge 4
Find latest employee from each department.
Challenge 5
Find department with maximum employees.
Challenge 6
Find employees whose salary ranks in top 10%.
Challenge 7
Generate numbers from 1 to 100 using Recursive CTE.
Challenge 8
Find gaps in EmployeeID sequence.
Challenge 9
Find employee with second highest salary in each department.
Challenge 10
Create a monthly salary report.
Intermediate Practice Checklist
text exampleWoHoTech
✓ Subqueries
✓ Joins
✓ Self Joins
✓ CTE
✓ Recursive CTE
✓ Window Functions
✓ Transactions
✓ Indexes
✓ Views
✓ Materialized Views
✓ EXISTS
✓ UNION
✓ EXPLAINSummary
These intermediate SQL exercises focus on real-world querying, reporting, ranking, transactions, and performance concepts. Completing them will significantly improve your SQL problem-solving ability and prepare you for developer interviews.
Next Step
Continue to:
SQL Exercises (Advanced) →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQL Exercises Intermediate.
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 Advancedtitle: SQL Exercises AdvancedPracticeSQL Exercises Beginnertitle: SQL Exercises BeginnerPracticeSQL Challengestitle: SQL ChallengesPracticeSQL Projectstitle: SQL ProjectsInterview QuestionsSQL Interview Questions Intermediatetitle: SQL Interview Questions IntermediateAdvanced SQLTriggers in SQLtitle: Triggers in SQL