SQL exercises help learners improve their skills in querying and managing relational databases through hands-on practice.

• Cover topics ranging from basic queries to advanced SQL techniques.
• Improve problem-solving skills and query-writing speed.
• Provide practical experience in working with relational databases.

Employees Table

The employees table contains details about employees, including their names, departments, salaries, manager IDs, department IDs and hire dates.

Projects Table

The projects table contains details about projects, including project names, the employee leading each project, budgets and statuses.

Beginner-Level SQL Exercises

This section offers beginner-friendly SQL exercises for data analysis, covering SELECT, filtering, sorting, aggregation, joins and data manipulation using tables like employees, sales and products to analyze business data and extract meaningful insights.

1. Retrieve All Records from a Table

SELECT * FROM employees;

Output:

Explanation: This query retrieves all records from the "employees" table, displaying every column for each row.

2. Select Specific Columns

SELECT first_name, last_name FROM employees;

Output:

• This query selects only the "first_name" and "last_name" columns from the "employees" table.

3. Filter Data Using WHERE Clause

SELECT * FROM employees WHERE department = 'Sales';

Output:

• This query filters and retrieves only the employees who belong to the "Sales" department.

4. Sort Data Using ORDER BY

SELECT * FROM employees ORDER BY salary DESC;

Output:

• This query sorts employees in descending order based on their salary.

5. Use GROUP BY and Aggregate Functions

SELECT department, COUNT(*) AS employee_count
 FROM employees GROUP BY department;

Output:

• This query groups employees by department and counts the number of employees in each department.

6. Retrieve Unique Values Using DISTINCT

SELECT DISTINCT department
FROM employees;

Output:

7. Filter Data Using BETWEEN

SELECT *
FROM employees
WHERE salary BETWEEN 30000 AND 60000;

Output:

8. Search for Patterns Using LIKE

SELECT *
FROM employees
WHERE first_name LIKE 'A%';

Output:

Medium-Level SQL Exercises

1. Find Employees with a Salary Greater than the Average Salary

SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

Output:

Explanation: The subquery calculates the company-wide average salary, and the outer query returns only employees who earn more than that.

2. Retrieve Employees Who Have the Same Manager

SELECT * FROM employees WHERE manager_id = 3;

Output:

3.Show Each Employee with Their Projects

SELECT e.first_name, p.project_name, p.status
FROM employees e
JOIN projects p ON e.employee_id = p.employee_id;

Output:

4. Find the Second Highest Salary

SELECT DISTINCT salary AS second_highest_salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Output:

• DISTINCT removes duplicate salary values.
• ORDER BY salary DESC sorts salaries from highest to lowest.
• LIMIT 1 OFFSET 1 skips the highest salary and returns the next one.

5. Retrieve Employees Hired in the Last 5 Years

SELECT * FROM employees
WHERE hire_date > DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

Output (run on 2026-10-29, so the cutoff is 2021-10-29):

• CURDATE() returns today's date and DATE_SUB(..., INTERVAL 5 YEAR) subtracts five years from it. The result changes depending on the day you run it.

6. Count the Projects Each Employee Leads

SELECT e.first_name, COUNT(p.project_id) AS project_count
FROM employees e
JOIN projects p ON e.employee_id = p.employee_id
GROUP BY e.employee_id, e.first_name
ORDER BY project_count DESC;

Output:

7. Find Employees Who Have No Projects

SELECT e.first_name, e.last_name
FROM employees e
LEFT JOIN projects p ON e.employee_id = p.employee_id
WHERE p.project_id IS NULL;

Output:

Advanced-Level SQL Exercises

1. Find Employees Earning More Than Their Department's Average Salary

SELECT employee_id, first_name, last_name, department, salary
FROM employees e
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department = e.department);

Output:

2. Retrieve the Top 2 Highest Paid Employees from Each Department

SELECT employee_id, first_name, last_name, department, salary
FROM (SELECT employee_id, first_name, last_name, department, salary,
             ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
      FROM employees) ranked
WHERE rn <= 2;

Output:

Explanation:

• ROW_NUMBER() assigns a sequential number to employees within each department based on their salary.
• PARTITION BY department restarts the numbering for each department.
• ORDER BY salary DESC places the highest-paid employee first.
• The outer query returns employees whose row number is 1 or 2.

3. Rank Employees by Total Project Budget

SELECT e.first_name,
       SUM(p.budget) AS total_budget,
       RANK() OVER (ORDER BY SUM(p.budget) DESC) AS budget_rank
FROM employees e
JOIN projects p ON e.employee_id = p.employee_id
GROUP BY e.employee_id, e.first_name;

Output:

4. Find Each Employee’s Highest-Budget Project

SELECT first_name, last_name, project_name, budget
FROM (SELECT e.first_name, e.last_name, p.project_name, p.budget,
             ROW_NUMBER() OVER (PARTITION BY e.employee_id
                                ORDER BY p.budget DESC) AS rn
      FROM employees e
      JOIN projects p ON e.employee_id = p.employee_id) ranked
WHERE rn = 1;

Output:

5. Optimize a Query Using Indexes

CREATE INDEX idx_employee_salary ON employees(salary);

Output:

Query OK, 0 rows affected