Table of Contents
Introduction
Welcome to our collection of SQL practice exercises. Our exercises are designed to help you master the SQL language and build your skills as a data analyst or developer. Whether you’re a beginner just starting out or an experienced professional looking to brush up on your skills, our exercises will provide you with the knowledge and hands-on experience you need to succeed. Each exercise includes detailed explanations and sample code to help you understand the concepts and techniques involved. With our exercises, you’ll be able to easily write complex SQL queries, understand database design and management, and gain the confidence you need to work with large data sets. Start practicing today and take your SQL skills to the next level!
SQL Basic Exercises
1. Create a database with a table called “employees” that includes columns for employee ID, name, address, and salary. Insert at least 5 records into the table.
2. Write a query to retrieve all records from the “employees” table where the salary is greater than a specified amount.
3. Create a query to update the address of all employees who live in a specific city.
4. Write a query to retrieve the highest salary from the “employees” table.
5. Create a query to retrieve all records from the “employees” table and display the results sorted by employee ID in descending order.
6. Write a query to delete all records from the “employees” table where the employee ID is greater than a specified number.
7. Create a query to retrieve the number of records in the “employees” table.
8. Write a query to retrieve all records from the “employees” table and group the results by the city where the employees live.
9. Create a query to retrieve the average salary of all employees.
10. Write a query to retrieve all records from the “employees” table and join it with a “departments” table to show the department name for each employee.
Note: These exercises are meant to be basic examples of SQL queries, and you can add more complexity and variation to them.
SQL Advance Exercises
1. Create a query that uses a subquery to retrieve all records from the “employees” table where the salary is greater than the average salary of all employees.
2. Write a query that uses a window function to retrieve the rank of each employee based on their salary in the “employees” table.
3. Create a query that uses a common table expression (CTE) to retrieve all records from the “employees” table where the employee’s name starts with a specific letter and joins with other tables to retrieve additional information.
4. Write a query that uses a case statement to classify employees in the “employees” table as low, medium, or high earners based on their salary.
5. Create a query that uses a full outer join to combine records from two tables, “employees” and “departments”, and retrieve all records along with the department name even if the employee doesn’t have a department assigned.
6. Write a query that uses a pivot table to retrieve the total salary of all employees grouped by department and job title.
7. Create a query that uses a recursive CTE to retrieve all records in a hierarchical structure, such as all employees and their managers.
8. Write a query that uses a UNION operator to combine the results of multiple SELECT statements into a single result set.
9. Create a query that uses a stored procedure to retrieve all employees that have a specific job title and salary range.
10. Write a query that uses a trigger to automatically update the total salary of all employees in a specific department whenever a new employee is added, or an existing employee’s salary is updated.
Note: These exercises are more complex and meant to be tackled by developers who have a good understanding of SQL and database management systems.
0 Comments