SQL Exercises - SQL Mastery

SQL Practice Exercises — 40 Hands-On Problems

All exercises use the consistent employees, departments, jobs, job_history, salaries, and transactions tables. Each exercise shows the problem, the SQL solution, and the learning outcome. All answers are collapsed by default — click the header to reveal.

How to practice

Try to write the query yourself before expanding the solution. Use the Copy button to test in your own database or the live SQL panel at the home page.

Basic SQL Exercises (1-8)

Master the fundamentals — SELECT, filtering, sorting, and aggregation. Click each exercise to reveal the solution (collapsed by default).

Select All Columns

Beginner SELECT

▶

Problem: Retrieve all columns from the employees table.

SELECT *
FROM employees;

Practice Goal

Learn how to retrieve all columns from a table.

Select Specific Columns

Beginner SELECT

▶

Problem: Retrieve the first_name and last_name columns from the employees table.

SELECT first_name, last_name
FROM employees;

Practice Goal

Learn how to retrieve only the required columns.

Filter Rows Using WHERE

Beginner WHERE

▶

Problem: Retrieve employees whose age is greater than 30.

SELECT *
FROM employees
WHERE age > 30;

Practice Goal

Learn how to filter rows using conditions.

Sort Data Using ORDER BY

Beginner ORDER BY

▶

Problem: Retrieve all employees sorted by last_name in ascending order.

SELECT *
FROM employees
ORDER BY last_name ASC;

Practice Goal

Learn how to sort query results.

Limit the Number of Rows

Beginner LIMIT

▶

Problem: Retrieve the first 5 employees from the table.

SELECT *
FROM employees
LIMIT 5;

Practice Goal

Learn how to restrict the number of rows returned.

Calculate Average Salary

Beginner Aggregate Functions

▶

Problem: Calculate the average salary from the salaries table.

SELECT AVG(salary) AS average_salary
FROM salaries;

Practice Goal

Learn how to use the AVG() aggregate function.

Average Salary by Department

Beginner GROUP BY

▶

Problem: Calculate the average salary for each department.

SELECT
    department_id,
    AVG(salary) AS average_salary
FROM salaries
GROUP BY department_id;

Practice Goal

Learn how GROUP BY works with aggregate functions.

Filter Groups Using HAVING

Beginner → Intermediate HAVING

▶

Problem: Retrieve departments whose average salary is greater than 50,000.

SELECT
    department_id,
    AVG(salary) AS average_salary
FROM salaries
GROUP BY department_id
HAVING AVG(salary) > 50000;

Practice Goal

Understand the difference between WHERE and HAVING.

Intermediate SQL Exercises (9-20)

Practice JOINs, subqueries, set operations, and conditional logic with real employees/departments data.

INNER JOIN

Intermediate JOIN

▶

Problem: Retrieve employees along with their department names.

SELECT
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Practice Goal

Learn how to combine related data from two tables.

#10

LEFT JOIN

Intermediate JOIN

▶

Problem: Retrieve all employees and their department names, including employees who do not belong to a department.

SELECT
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id;

Practice Goal

Understand how LEFT JOIN preserves all rows from the left table.

#11

RIGHT JOIN

Intermediate JOIN

▶

Problem: Retrieve all departments and their employees, including departments that have no employees.

SELECT
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id;

Practice Goal

Understand how RIGHT JOIN preserves all rows from the right table.

#12

FULL OUTER JOIN

Intermediate JOIN

▶

Problem: Retrieve all employees and all departments, including records that do not have a matching record.

SELECT
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

Practice Goal

Understand how FULL OUTER JOIN returns matched and unmatched records from both tables.

#13

SELF JOIN

Intermediate SELF JOIN

▶

Problem: Retrieve each employee along with their manager's name.

SELECT
    e.first_name AS employee_name,
    m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

Practice Goal

Learn how a table can be joined to itself.

#14

Subquery

Intermediate Subqueries

▶

Problem: Retrieve employees whose salary is greater than the overall average salary.

SELECT
    first_name,
    last_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Practice Goal

Understand how a subquery can be used inside a WHERE condition.

#15

EXISTS

Intermediate EXISTS

▶

Problem: Retrieve departments that have at least one employee.

SELECT
    d.department_id,
    d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

Practice Goal

Learn how EXISTS checks whether a related record exists.

#16

IN

Intermediate IN

▶

Problem: Retrieve employees who belong to departments 1, 2, or 3.

SELECT
    first_name,
    last_name,
    department_id
FROM employees
WHERE department_id IN (1, 2, 3);

Practice Goal

Learn how to match a column against multiple values.

#17

UNION

Intermediate Set Operations

▶

Problem: Retrieve all unique job titles from jobs and job_history.

SELECT job_title
FROM jobs

UNION

SELECT job_title
FROM job_history;

Practice Goal

Understand how UNION combines result sets and removes duplicates.

#18

UNION ALL

Intermediate Set Operations

▶

Problem: Retrieve all job titles from jobs and job_history, including duplicates.

SELECT job_title
FROM jobs

UNION ALL

SELECT job_title
FROM job_history;

Practice Goal

Understand the difference between UNION and UNION ALL.

#19

CASE Statement

Intermediate Conditional Logic

▶

Problem: Display whether each employee's salary is above or below the overall average salary.

SELECT
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary > (
            SELECT AVG(salary)
            FROM employees
        )
        THEN 'Above Average'
        ELSE 'Below Average'
    END AS salary_comparison
FROM employees;

Practice Goal

Learn how to create conditional output using CASE.

#20

COALESCE

Intermediate NULL Handling

▶

Problem: Display each employee's manager name. If the employee does not have a manager, display No Manager.

SELECT
    e.first_name,
    e.last_name,
    COALESCE(m.first_name, 'No Manager') AS manager_name
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

Practice Goal

Learn how to handle NULL values using COALESCE().

Advanced SQL Exercises (21-40)

Window functions, CTEs, PIVOT/MERGE, views, procedures, and Data Engineer interview patterns. Click to expand each solution.

#21

ROW_NUMBER

Advanced Window Functions

▶

Problem: Assign a unique row number to each employee within their department.

SELECT
    first_name,
    last_name,
    department_id,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY first_name
    ) AS row_num
FROM employees;

Practice Goal

Understand ROW_NUMBER() and PARTITION BY.

#22

RANK

Advanced Window Functions

▶

Problem: Rank employees within each department based on salary, from highest to lowest.

SELECT
    first_name,
    last_name,
    department_id,
    salary,
    RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Practice Goal

Understand ranking with ties.

#23

DENSE_RANK

Advanced Window Functions

▶

Problem: Rank employees within each department based on salary using dense ranking.

SELECT
    first_name,
    last_name,
    department_id,
    salary,
    DENSE_RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS dense_rank
FROM employees;

Practice Goal

Understand the difference between RANK() and DENSE_RANK().

#24

NTILE

Advanced Window Functions

▶

Problem: Divide employees into 4 salary groups within each department.

SELECT
    first_name,
    last_name,
    department_id,
    salary,
    NTILE(4) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS quartile
FROM employees;

Practice Goal

Learn how NTILE() divides rows into groups.

#25

LEAD

Advanced Window Functions

▶

Problem: Retrieve the next employee's salary within each department.

SELECT
    first_name,
    last_name,
    department_id,
    salary,
    LEAD(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS next_salary
FROM employees;

Practice Goal

Learn how to access the value from the following row.

#26

LAG

Advanced Window Functions

▶

Problem: Retrieve the previous employee's salary within each department.

SELECT
    first_name,
    last_name,
    department_id,
    salary,
    LAG(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS previous_salary
FROM employees;

Practice Goal

Learn how to access the value from the previous row.

#27

FIRST_VALUE

Advanced Window Functions

▶

Problem: Retrieve the highest salary within each department.

SELECT
    first_name,
    last_name,
    department_id,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS first_salary
FROM employees;

Practice Goal

Understand how FIRST_VALUE() works with window ordering.

#28

LAST_VALUE

Advanced Window Functions

▶

Problem: Retrieve the last salary according to the specified window ordering within each department.

SELECT
    first_name,
    last_name,
    department_id,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS last_salary
FROM employees;

Practice Goal

Understand LAST_VALUE() and window frames. Note: behavior can depend on window frame.

#29

Common Table Expression

Advanced CTE

▶

Problem: Calculate the average salary for each department and display it alongside each employee.

WITH AvgSalaries AS (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.first_name,
    e.last_name,
    e.salary,
    a.avg_salary
FROM employees e
JOIN AvgSalaries a
    ON e.department_id = a.department_id;

Practice Goal

Learn how CTEs make complex queries easier to read.

#30

Recursive CTE

Advanced Recursive CTE

▶

Problem: Generate an employee hierarchy starting from employees who do not have a manager.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        manager_id,
        0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh
        ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy;

Practice Goal

Learn how recursive CTEs can process hierarchical data.

#31

PIVOT

Advanced PIVOT

▶

Problem: Generate a report showing total sales by product category and month.

SELECT *
FROM (
    SELECT
        product_category,
        MONTH(order_date) AS month,
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY
        product_category,
        MONTH(order_date)
) AS PivotTable
PIVOT (
    SUM(total_sales)
    FOR month IN (
        1, 2, 3, 4, 5, 6,
        7, 8, 9, 10, 11, 12
    )
) AS MonthlySales;

Practice Goal

Understand how row-based data can be transformed into columns. Dialect-specific.

#32

UNPIVOT

Advanced UNPIVOT

▶

Problem: Convert monthly columns back into rows.

SELECT
    product_id,
    month,
    sales_amount
FROM MonthlySales
UNPIVOT (
    sales_amount
    FOR month IN (
        1, 2, 3, 4, 5, 6,
        7, 8, 9, 10, 11, 12
    )
) AS UnpivotedSales;

Practice Goal

Understand how column-based data can be transformed back into rows. Dialect-specific.

#33

MERGE

Advanced MERGE / Upsert

▶

Problem: Update existing records and insert new records from a staging table into a target table.

MERGE INTO target_table AS T
USING staging_table AS S
ON T.id = S.id

WHEN MATCHED THEN
    UPDATE SET
        T.col1 = S.col1,
        T.col2 = S.col2

WHEN NOT MATCHED THEN
    INSERT (id, col1, col2)
    VALUES (S.id, S.col1, S.col2);

Practice Goal

Understand the concept of combining INSERT and UPDATE logic. Dialect-specific.

#34

UPDATE with a Condition

Intermediate DML

▶

Problem: Increase salary by 10% for employees with more than 5 years of service.

UPDATE employees
SET salary = salary * 1.10
WHERE years_of_service > 5;

Practice Goal

Practice conditional data modification.

#35

INSERT INTO SELECT

Advanced DML

▶

Problem: Insert employee records from a temporary table into the main employees table.

INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    department_id
)
SELECT
    employee_id,
    first_name,
    last_name,
    department_id
FROM temp_employees;

Practice Goal

Learn how to insert query results into another table.

#36

Create a View

Advanced Views

▶

Problem: Create a view containing employee and department information.

CREATE VIEW employee_details AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

Practice Goal

Understand how views can simplify frequently used queries.

#37

Query a View

Advanced Views

▶

Problem: Retrieve data from the employee_details view.

SELECT *
FROM employee_details;

Practice Goal

Learn how to use a view like a table.

#38

Create a Stored Procedure

Advanced Stored Procedures

▶

Problem: Create a stored procedure that calculates a 10% bonus for an employee. (MySQL-style)

DELIMITER //

CREATE PROCEDURE calculate_bonus(IN emp_id INT)
BEGIN
    DECLARE bonus_amount DECIMAL(10,2);

    SELECT salary * 0.10
    INTO bonus_amount
    FROM employees
    WHERE employee_id = emp_id;

    UPDATE employees
    SET bonus = bonus_amount
    WHERE employee_id = emp_id;
END //

DELIMITER ;

Practice Goal

Learn the basic structure of a stored procedure.

#39

Call a Stored Procedure

Advanced Stored Procedures

▶

Problem: Execute the calculate_bonus procedure for employee 1001.

CALL calculate_bonus(1001);

Practice Goal

Learn how to execute a stored procedure.

#40

Exception Handling in Stored Procedures

Advanced Stored Procedures

▶

Problem: Create a stored procedure that handles an error while inserting an employee. (MySQL-style)

DELIMITER //

CREATE PROCEDURE insert_employee (
    IN emp_id INT,
    IN emp_name VARCHAR(255),
    IN dept_id INT
)
BEGIN

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error: Unable to insert employee.';
    END;

    START TRANSACTION;

    INSERT INTO employees (
        employee_id,
        employee_name,
        department_id
    )
    VALUES (
        emp_id,
        emp_name,
        dept_id
    );

    COMMIT;

END //

DELIMITER ;

Practice Goal

Understand error handling and transaction control inside stored procedures.

Bookmarks

SQL Practice Exercises — 40 Hands-On Problems

Basic SQL Exercises (1-8)

Select All Columns

Select Specific Columns

Filter Rows Using WHERE

Sort Data Using ORDER BY

Limit the Number of Rows

Calculate Average Salary

Average Salary by Department

Filter Groups Using HAVING

Intermediate SQL Exercises (9-20)

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

SELF JOIN

Subquery

EXISTS

IN

UNION

UNION ALL

CASE Statement

COALESCE

Advanced SQL Exercises (21-40)

ROW_NUMBER

RANK

DENSE_RANK

NTILE

LEAD

LAG

FIRST_VALUE

LAST_VALUE

Common Table Expression

Recursive CTE

PIVOT

UNPIVOT

MERGE

UPDATE with a Condition

INSERT INTO SELECT

Create a View

Query a View

Create a Stored Procedure

Call a Stored Procedure

Exception Handling in Stored Procedures