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 intermediate-level SQL exercises covering joins, subqueries, CTEs, window functions, transactions, indexes, and optimization.
Welcome to Intermediate SQL Practice.
At this stage, you should already know:
text example
SELECT
WHERE
GROUP BY
HAVING
JOINS
CRUD Operations
Views
Aggregate FunctionsNow we focus on:
text example
Subqueries
Advanced JOINs
CTEs
Window Functions
Transactions
Indexes
OptimizationThese exercises are commonly asked in:
text example
Technical Interviews
Coding Assessments
Backend Development Roles
Data Analyst PositionsDepartments
| DepartmentID | DepartmentName |
|---|---|
| 1 | HR |
| 2 | IT |
| 3 | Finance |
Exercise 1
Find Employees Earning More Than Average Salary
sql example
SELECT *
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);Exercise 2
Find Second Highest Salary
sql example
SELECT MAX(Salary)
FROM Employees
WHERE Salary <
(
SELECT MAX(Salary)
FROM Employees
);Exercise 3
Find Third Highest Salary
sql example
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 example
SELECT
DepartmentID,
AVG(Salary)
FROM Employees
GROUP BY DepartmentID;Exercise 5
Find Employees With Highest Salary 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 = 1;Exercise 6
Find Employees Without Department
sql example
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 example
SELECT *
FROM Departments D
LEFT JOIN Employees E
ON D.DepartmentID =
E.DepartmentID
WHERE E.EmployeeID IS NULL;Exercise 8
Find Duplicate Salaries
sql example
SELECT
Salary,
COUNT(*)
FROM Employees
GROUP BY Salary
HAVING COUNT(*) > 1;Exercise 9
Use INNER JOIN
sql example
SELECT
EmployeeName,
DepartmentName
FROM Employees E
INNER JOIN Departments D
ON E.DepartmentID =
D.DepartmentID;Exercise 10
Use LEFT JOIN
sql example
SELECT
EmployeeName,
DepartmentName
FROM Employees E
LEFT JOIN Departments D
ON E.DepartmentID =
D.DepartmentID;Exercise 11
Find Top 3 Highest Paid Employees
sql example
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 3;Exercise 12
Find Bottom 3 Salaries
sql example
SELECT *
FROM Employees
ORDER BY Salary ASC
LIMIT 3;Exercise 13
Assign Row Numbers
sql example
SELECT
EmployeeName,
ROW_NUMBER()
OVER
(
ORDER BY Salary DESC
)
AS RowNum
FROM Employees;Exercise 14
Assign Rank
sql example
SELECT
EmployeeName,
RANK()
OVER
(
ORDER BY Salary DESC
)
AS RankNum
FROM Employees;Exercise 15
Assign Dense Rank
sql example
SELECT
EmployeeName,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
)
AS DenseRankNum
FROM Employees;Exercise 16
Calculate Running Total
sql example
SELECT
EmployeeName,
Salary,
SUM(Salary)
OVER
(
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Exercise 17
Department Wise Running Salary
sql example
SELECT
EmployeeName,
DepartmentID,
Salary,
SUM(Salary)
OVER
(
PARTITION BY DepartmentID
ORDER BY EmployeeID
)
AS RunningTotal
FROM Employees;Exercise 18
Use CTE
sql example
WITH HighSalary AS
(
SELECT *
FROM Employees
WHERE Salary > 60000
)
SELECT *
FROM HighSalary;Exercise 19
Use Recursive CTE
sql example
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 example
SELECT
E.EmployeeName,
M.EmployeeName AS Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID =
M.EmployeeID;Exercise 21
Create Index
sql example
CREATE INDEX IX_Salary
ON Employees(Salary);Exercise 22
Create Composite Index
sql example
CREATE INDEX IX_DepSalary
ON Employees
(
DepartmentID,
Salary
);Exercise 23
Create Unique Index
sql example
CREATE UNIQUE INDEX IX_Email
ON Employees(Email);Exercise 24
Create View
sql example
CREATE VIEW ITEmployees AS
SELECT *
FROM Employees
WHERE DepartmentID = 2;Exercise 25
Create Materialized View
(PostgreSQL)
sql example
CREATE MATERIALIZED VIEW EmployeeSummary AS
SELECT
DepartmentID,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID;Exercise 26
Start Transaction
sql example
BEGIN;Exercise 27
Commit Transaction
sql example
COMMIT;Exercise 28
Rollback Transaction
sql example
ROLLBACK;Exercise 29
Create Savepoint
sql example
SAVEPOINT SP1;Exercise 30
Rollback To Savepoint
sql example
ROLLBACK TO SP1;Exercise 31
Use CASE Expression
Example
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 example
SELECT *
FROM Employees E
WHERE EXISTS
(
SELECT 1
FROM Departments D
WHERE D.DepartmentID =
E.DepartmentID
);Exercise 33
Use NOT EXISTS
sql example
SELECT *
FROM Departments D
WHERE NOT EXISTS
(
SELECT 1
FROM Employees E
WHERE E.DepartmentID =
D.DepartmentID
);Exercise 34
Use UNION
sql example
SELECT EmployeeName
FROM Employees
UNION
SELECT CustomerName
FROM Customers;Exercise 35
Use UNION ALL
sql example
SELECT EmployeeName
FROM Employees
UNION ALL
SELECT CustomerName
FROM Customers;Exercise 36
Use INTERSECT
sql example
SELECT Email
FROM Employees
INTERSECT
SELECT Email
FROM Customers;Exercise 37
Use EXCEPT
sql example
SELECT Email
FROM Employees
EXCEPT
SELECT Email
FROM Customers;Exercise 38
Analyze Query Using EXPLAIN
sql example
EXPLAIN
SELECT *
FROM Employees
WHERE Salary > 60000;Exercise 39
Find Latest Employee
sql example
SELECT *
FROM Employees
ORDER BY EmployeeID DESC
LIMIT 1;Exercise 40
Find Highest Salary Department
sql example
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
Output
✓ Subqueries ✓ Joins ✓ Self Joins ✓ CTE ✓ Recursive CTE ✓ Window Functions ✓ Transactions ✓ Indexes ✓ Views ✓ Materialized Views ✓ EXISTS ✓ UNION ✓ EXPLAIN
Summary
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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, practice, exercises, intermediate
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 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.Interview QuestionsSQL Interview Questions (Intermediate)Learn the most important intermediate-level SQL interview questions with detailed answers, examples, joins, subqueries, indexing, transactions, and optimization concepts.