SQL Practice Exercises
Basic SQL Queries
Exercise 1: Select All Columns from a Table
● Description: Retrieve all columns from the employees table.
Code:
sql
*
SELECT * FROM employees;
Exercise 2: Select Specific Columns from a Table
● Description: Retrieve the first_name and last_name columns from the employees
table.
Code:
sql
*
SELECT first_name, last_name FROM employees;
Exercise 3: Filter Rows Using WHERE Clause
● Description: Retrieve employees whose age is greater than 30.
Code:
sql
*
SELECT * FROM employees WHERE age > 30;
Exercise 4: Sort Data Using ORDER BY
● Description: Retrieve all employees sorted by last_name in ascending order.
1
Code:
sql
*
SELECT * FROM employees ORDER BY last_name ASC;
Exercise 5: Using LIMIT
● Description: Retrieve the first 5 rows from the employees table.
Code:
sql
*
SELECT * FROM employees LIMIT 5;
Exercise 6: Aggregate Functions (SUM, AVG, COUNT, MIN, MAX)
● Description: Calculate the average salary from the salaries table.
Code:
sql
*
SELECT AVG(salary) AS average_salary FROM salaries;
Exercise 7: GROUP BY Clause
● Description: Calculate the average salary by department.
Code:
sql
*
SELECT department_id, AVG(salary) AS average_salary FROM salaries
GROUP BY department_id;
Exercise 8: HAVING Clause
● Description: Retrieve departments with an average salary greater than 50000.
2
Code:
sql
*
SELECT department_id, AVG(salary) AS average_salary
FROM salaries
GROUP BY department_id
HAVING AVG(salary) > 50000;
Intermediate SQL Queries
Exercise 9: Inner Join
● Description: Retrieve employees and their respective department names.
Code:
sql
*
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
Exercise 10: Left Join
● Description: Retrieve all employees and their respective department names (including
those without a department).
Code:
sql
*
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
Exercise 11: Right Join
3
● Description: Retrieve all departments and the employees in each department (including
departments with no employees).
Code:
sql
*
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
Exercise 12: Full Outer Join
● Description: Retrieve all employees and all departments, including those without
matches.
Code:
sql
*
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;
Exercise 13: Self Join
● Description: Retrieve employees and their managers.
Code:
sql
*
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;
Exercise 14: Subqueries
● Description: Retrieve employees whose salary is above the average salary.
4
Code:
sql
*
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Exercise 15: EXISTS Clause
● Description: Retrieve departments that have at least one employee.
Code:
sql
*
SELECT department_id, department_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id =
d.department_id);
Exercise 16: IN Clause
● Description: Retrieve employees who belong to the departments with IDs 1, 2, or 3.
Code:
sql
*
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (1, 2, 3);
Exercise 17: UNION
● Description: Retrieve all unique job titles from jobs and job_history.
Code:
sql
5
*
SELECT job_title FROM jobs
UNION
SELECT job_title FROM job_history;
Exercise 18: UNION ALL
● Description: Retrieve all job titles from jobs and job_history including duplicates.
Code:
sql
*
SELECT job_title FROM jobs
UNION ALL
SELECT job_title FROM job_history;
Exercise 19: CASE Statement
● Description: Retrieve employees with a new column indicating whether their salary is
above or below the average.
Code:
sql
*
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;
Exercise 20: COALESCE Function
● Description: Retrieve employees and display their manager's name. If no manager,
display 'No Manager'.
6
Code:
sql
*
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;
Advanced SQL Queries
Exercise 21: Window Functions (ROW_NUMBER)
● Description: Assign a unique row number to each employee within their department.
Code:
sql
*
SELECT first_name, last_name, department_id,
ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY
first_name) AS row_num
FROM employees;
Exercise 22: Window Functions (RANK)
● Description: Rank employees within each department based on their salary.
Code:
sql
*
SELECT first_name, last_name, department_id, salary,
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS
rank
FROM employees;
Exercise 23: Window Functions (DENSE_RANK)
7
● Description: Densely rank employees within each department based on their salary.
Code:
sql
*
SELECT first_name, last_name, department_id, salary,
DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary
DESC) AS dense_rank
FROM employees;
Exercise 24: Window Functions (NTILE)
● Description: Distribute employees into 4 quartiles within their department based on their
salary.
Code:
sql
*
SELECT first_name, last_name, department_id, salary,
NTILE(4) OVER(PARTITION BY department_id ORDER BY salary DESC)
AS quartile
FROM employees;
Exercise 25: Window Functions (LEAD)
● Description: Retrieve the next employee's salary within each department.
Code:
sql
*
SELECT first_name, last_name, department_id, salary,
LEAD(salary) OVER(PARTITION BY department_id ORDER BY salary
DESC) AS next_salary
FROM employees;
Exercise 26: Window Functions (LAG)
8
● Description: Retrieve the previous employee's salary within each department.
Code:
sql
*
SELECT first_name, last_name, department_id, salary,
LAG(salary) OVER(PARTITION BY department_id ORDER BY salary
DESC) AS previous_salary
FROM employees;
Exercise 27: Window Functions (FIRST_VALUE)
● Description: Retrieve the first salary within each department.
Code:
sql
*
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;
Exercise 28: Window Functions (LAST_VALUE)
● Description: Retrieve the last salary within each department.
Code:
sql
*
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;
Exercise 29: Common Table Expressions (CTEs)
● Description: Retrieve the average salary per department using a CTE.
9
Code:
sql
*
WITH AvgSalaries AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.first_name, e.last_name, [Link], a.avg_salary
FROM employees e
JOIN AvgSalaries a ON e.department_id = a.department_id;
Exercise 30: Recursive CTEs
● Description: Retrieve the hierarchy of employees.
● **Code:
4o
continue
ChatGPT
Sure, here are more SQL practice exercises covering advanced topics:
Advanced SQL Queries (continued)
Exercise 31: Pivot Tables
● Description: Generate a pivot table to display total sales by product category and
month.
Code:
sql
*
SELECT *
FROM (
SELECT product_category, MONTH(order_date) AS month,
SUM(sales_amount) AS total_sales
FROM sales
10
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;
Exercise 32: Unpivot Tables
● Description: Unpivot a table to normalize data.
Code:
sql
*
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;
Exercise 33: Merge Tables Using MERGE Statement
● Description: Update existing records and insert new records from a staging table into
the main table.
Code:
sql
*
MERGE INTO target_table AS T
USING staging_table AS S
ON [Link] = [Link]
WHEN MATCHED THEN
UPDATE SET T.col1 = S.col1, T.col2 = S.col2
WHEN NOT MATCHED THEN
INSERT (id, col1, col2) VALUES ([Link], S.col1, S.col2);
11
Exercise 34: Data Transformation with UPDATE Statement
● Description: Update salary by applying a 10% increase for employees with more than 5
years of service.
Code:
sql
*
UPDATE employees
SET salary = salary * 1.1
WHERE years_of_service > 5;
Exercise 35: Data Transformation with INSERT INTO SELECT
● Description: Insert new employees into the main table from a temporary table.
Code:
sql
*
INSERT INTO employees (employee_id, first_name, last_name,
department_id)
SELECT employee_id, first_name, last_name, department_id
FROM temp_employees;
Exercise 36: Creating Views
● Description: Create a view to simplify access to employee details.
Code:
sql
*
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;
Exercise 37: Using Views
12
● Description: Query the employee_details view to retrieve employee information.
Code:
sql
*
SELECT * FROM employee_details;
Exercise 38: Creating Stored Procedures
● Description: Create a stored procedure to calculate employee bonuses.
Code:
sql
*
DELIMITER //
CREATE PROCEDURE calculate_bonus (IN emp_id INT)
BEGIN
DECLARE bonus_amount DECIMAL(10, 2);
SELECT salary * 0.1 INTO bonus_amount FROM employees WHERE
employee_id = emp_id;
UPDATE employees SET bonus = bonus_amount WHERE employee_id =
emp_id;
END //
DELIMITER ;
Exercise 39: Calling Stored Procedures
● Description: Call the calculate_bonus stored procedure for a specific employee.
Code:
sql
*
CALL calculate_bonus(1001);
13
Exercise 40: Exception Handling in Stored Procedures
● Description: Create a stored procedure with exception handling to handle errors
gracefully.
Code:
sql
*
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 ;
Exercise 41: Common Table Expressions (Recursive)
● Description: Generate a hierarchical structure of employees using a recursive CTE.
Code:
sql
*
WITH RECURSIVE EmployeeHierarchy AS (
14
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,
[Link] + 1
FROM employees e
JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;
Exercise 42: Using Window Functions with PARTITION BY
● Description: Calculate the average salary per department and rank employees within
each department based on salary.
Code:
sql
*
SELECT department_id, employee_id, first_name, last_name, salary,
AVG(salary) OVER(PARTITION BY department_id) AS avg_salary,
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS
salary_rank
FROM employees;
Exercise 43: Using Window Functions with ROWS/RANGE
● Description: Calculate the moving average of sales using a window of 3 rows.
Code:
sql
*
SELECT order_date, sales_amount,
AVG(sales_amount) OVER(ORDER BY order_date ROWS BETWEEN 2
PRECEDING AND CURRENT ROW) AS moving_avg_sales
FROM sales;
15
Exercise 44: Managing Transactions
● Description: Perform a transaction to update salary and insert a bonus for employees.
Code:
sql
*
START TRANSACTION;
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 1;
INSERT INTO bonuses (employee_id, bonus_amount) VALUES (1001, 1000);
COMMIT;
Exercise 45: Cross Apply and Outer Apply
● Description: Retrieve all employees and their respective managers using CROSS
APPLY.
Code:
sql
*
SELECT e.first_name AS employee_name, m.first_name AS manager_name
FROM employees e
CROSS APPLY (
SELECT first_name
FROM employees
WHERE employee_id = e.manager_id
) m;
Exercise 46: Bulk Insert
● Description: Bulk insert data from a CSV file into the employees table.
Code:
sql
*
LOAD DATA INFILE '/path/to/[Link]'
INTO TABLE employees
FIELDS TERMINATED BY ','
16
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Exercise 47: Dynamic SQL
● Description: Use dynamic SQL to execute a query based on user input.
Code:
sql
*
SET @column_name = 'department_id';
SET @query = CONCAT('SELECT * FROM employees WHERE ', @column_name, '
= 1');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Exercise 48: Using Cursors
● Description: Use a cursor to iterate through employees and calculate their total salary.
Code:
sql
*
DECLARE emp_id INT;
DECLARE total_salary DECIMAL(10, 2) DEFAULT 0;
DECLARE emp_cursor CURSOR FOR
SELECT employee_id FROM employees;
OPEN emp_cursor;
emp_loop: LOOP
FETCH emp_cursor INTO emp_id;
IF done THEN
LEAVE emp_loop;
END IF;
17
SELECT salary INTO @salary FROM employees WHERE employee_id =
emp_id;
SET total_salary = total_salary + @salary;
END LOOP;
CLOSE emp_cursor;
Exercise 49: Analyzing Query Performance
● Description: Use EXPLAIN to analyze the execution plan of a query.
Code:
sql
*
EXPLAIN SELECT * FROM employees WHERE department_id = 1;
Exercise 50: Analyzing Index Usage
● Description: Check which indexes are being used in a specific query.
Code:
sql
*
EXPLAIN SELECT * FROM employees WHERE employee_id = 1001;
18