Introduction
Structured Query Language (SQL) is a powerful tool for managing and manipulating relational databases. Whether you are a budding data scientist, a web developer, or someone looking to enhance your database skills, practicing SQL is essential. So, are you a beginner in SQL looking to enhance your skills? Well, you’re in luck! In this article, we will explore 10 beginner SQL practice exercises along with their solutions. These exercises will help you better understand SQL and improve your query writing abilities. So, let’s dive in and start practicing!
Table of contents
- Understanding the Importance of SQL Practice Exercises
- Benefits of Solving SQL Practice Exercises
- Overview of SQL Practice Exercises
- SQL Practice Exercises 1: Retrieving Data
- SQL Practice Exercises 2: Filtering Data
- SQL Practice Exercises 3: Sorting Data
- SQL Practice Exercises 4: Aggregating Data
- SQL Practice Exercises 5: Joining Tables
- SQL Practice Exercises 6: Subqueries
- SQL Practice Exercises 7: Modifying Data
- SQL Practice Exercises 8: Creating and Modifying Tables
- SQL Practice Exercises 9: Working with Views
- SQL Practice Exercises 10: Advanced SQL Queries
Understanding the Importance of SQL Practice Exercises
Solving and going through the SQL queries helps in understanding the code better. They provide hands-on experience and allow you to apply the concepts you’ve learned in a real-world scenario. With the exercises below, you can reinforce your knowledge, identify areas for improvement, and build confidence in writing SQL queries.
Benefits of Solving SQL Practice Exercises
Solving the questions offers several benefits for beginners. Firstly, it helps you become familiar with the syntax and structure of SQL queries. Secondly, it improves your problem-solving skills by challenging you to think critically and logically. Additionally, they enhance your understanding of database concepts such as data retrieval, filtering, sorting, aggregating, joining tables, and more.
Overview of SQL Practice Exercises
In this section, we will provide a brief overview of exercises and their corresponding solution. Let’s get started!
SQL Practice Exercises 1: Retrieving Data
Exercise Description
In this exercise, you will practice retrieving data from a database table using the SELECT statement. You will learn to specify columns, use aliases, and apply filtering conditions.
Solution
#Retrieving data from a table
SELECT column1, column2, column3
FROM your_table_name;SQL Practice Exercises 2: Filtering Data
Exercise Description
This exercise focuses on filtering data based on specific criteria using the WHERE clause. You will learn how to use comparison operators, logical operators, and wildcard characters to filter data effectively.
Solution
#Filtering data based on a specific condition
SELECT column1, column2, column3
FROM your_table_name
WHERE column1 = 'some_value';SQL Practice Exercises 3: Sorting Data
Exercise Description
In this exercise, you will practice sorting data in ascending or descending order using the ORDER BY clause. You will also learn how to sort data based on multiple columns.
Solution
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;SQL Practice Exercises 4: Aggregating Data
Exercise Description
This exercise focuses on aggregating data using SQL functions such as COUNT, SUM, AVG, MIN, and MAX. You will learn how to calculate summary statistics and group data using the GROUP BY clause.
Solution
#Assuming you have a table named 'sales' with columns 'product', 'quantity', and 'price'
#Count the number of sales for each product
SELECT
product,
COUNT(*) AS sales_count
FROM
sales
GROUP BY
product;
#Calculate the total quantity sold for each product
SELECT
product,
SUM(Quantity) AS total_quantity
FROM
sales
GROUP BY
product;
#Calculate the average price for each product
SELECT
product,
AVG(price) AS average_price
FROM
sales
GROUP BY
product;
#Find the minimum and maximum quantity sold for each product
SELECT
product,
MIN(quantity) AS min_quantity,
MAX(quantity) AS max_quantity
FROM
sales
GROUP BY
product;
SQL Practice Exercises 5: Joining Tables
Exercise Description
In this exercise, you will practice joining tables based on common columns using INNER JOIN, LEFT JOIN, and RIGHT JOIN. You will learn how to combine data from multiple tables to retrieve meaningful information.
In this exercise, there are two tables, “employees” and “departments,” with a common column “department_id.”
Solution
#Creating sample tables
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(255),
department_id INT
);
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(255)
);
#Inserting sample data
INSERT INTO employees VALUES (1, 'John Doe', 101);
INSERT INTO employees VALUES (2, 'Jane Smith', 102);
INSERT INTO employees VALUES (3, 'Bob Johnson', 101);
INSERT INTO departments VALUES (101, 'HR');
INSERT INTO departments VALUES (102, 'IT');
#INNER JOIN example
SELECT employees.employee_id, employee_name, department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
#LEFT JOIN example
SELECT employees.employee_id, employee_name, department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
#RIGHT JOIN example
SELECT employees.employee_id, employee_name, department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;SQL Practice Exercises 6: Subqueries
Exercise Description
This exercise introduces subqueries, which are queries nested within another query. You will learn how to use subqueries to retrieve data based on the results of another query.
Solution
In this exercise, let’s consider a scenario where we have two tables: employees and departments. The employees table contains information about employees, and the departments table contains information about different departments in a company.
#Create tables (for illustration purposes, no actual data is inserted)
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(255)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(255),
department_id INT,
salary DECIMAL(10, 2),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
#Insert some sample data (not necessary for the exercise)
INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(101, 'John Doe', 1, 50000),
(102, 'Jane Smith', 2, 60000),
(103, 'Bob Johnson', 1, 55000),
(104, 'Alice Williams', 3, 70000);
#Subquery to retrieve employees in the Finance department
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Finance');SQL Practice Exercises 7: Modifying Data
Exercise Description
In this exercise, you will practice modifying data in a database table using the UPDATE statement. You will learn how to update specific columns and rows based on certain conditions.
Solution
#Assuming you have a table named 'employees' with columns 'employee_id', 'employee_name', 'salary', and 'department_id'
#Update the salary of a specific employee by employee_id
UPDATE employees
SET salary = 60000
WHERE employee_id = 123;
#Update the department_id for employees in a specific department
UPDATE employees
SET department_id = 2
WHERE department_id = 1;
#Increase the salary of all employees in a certain department by 10%
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 3;
#Update the employee_name for a specific employee
UPDATE employees
SET employee_name = 'John Doe'
WHERE employee_id = 456;You can also checkout the SQL Full Course – in 3 hours | SQL Tutorial for Beginners | Free Certification 2023
SQL Practice Exercises 8: Creating and Modifying Tables
Exercise Description
This exercise focuses on creating and modifying tables using the CREATE TABLE and ALTER TABLE statements. You will learn how to define columns, specify data types, and add constraints to ensure data integrity.
Solution
#Create a new table called 'employees'
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
birth_date DATE,
hire_date DATE,
salary DECIMAL(10, 2)
);
#Modify the 'employees' table to add a new column 'department'
ALTER TABLE employees
ADD COLUMN department VARCHAR(50);
#Modify the 'employees' table to change the data type of 'salary' column
ALTER TABLE employees
ALTER COLUMN salary DECIMAL(12, 2);
#Modify the 'employees' table to add a foreign key constraint
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department)
REFERENCES departments (department_id);
SQL Practice Exercises 9: Working with Views
Exercise Description
In this exercise, you will practice creating and working with views. Views are virtual tables that are derived from the result of a query. You will learn how to create, update, and delete views.
Solution
#Create a sample table
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
#Insert some sample data
INSERT INTO Employee VALUES (1, 'John', 'Doe', 'IT', 60000.00);
INSERT INTO Employee VALUES (2, 'Jane', 'Smith', 'HR', 55000.00);
INSERT INTO Employee VALUES (3, 'Bob', 'Johnson', 'Finance', 70000.00);
#Create a view to display employees in the IT department
CREATE VIEW IT_Employees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employee
WHERE Department = 'IT';
#Query the view
SELECT * FROM IT_Employees;
#Update the view to include only employees with a salary above 60000.00
CREATE OR REPLACE VIEW IT_Employees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employee
WHERE Department = 'IT' AND Salary > 60000.00;
#Query the updated view
SELECT * FROM IT_Employees;
#Drop the view
DROP VIEW IF EXISTS IT_Employees;
#Drop the sample table
DROP TABLE IF EXISTS Employee;SQL Practice Exercises 10: Advanced SQL Queries
Exercise Description
This exercise covers advanced SQL queries, including nested queries, self-joins, and complex filtering conditions. You will be challenged to apply your SQL knowledge to solve more complex problems.
Solution
#Problem 1: Find the total sales for each product category
SELECT
category_name,
SUM(unit_price * quantity) AS total_sales
FROM
products
JOIN
order_details ON products.product_id = order_details.product_id
JOIN
categories ON products.category_id = categories.category_id
GROUP BY
category_name;
#Problem 2: Identify customers who have made multiple orders on the same day
SELECT
customer_id,
order_date,
COUNT(*) AS order_count
FROM
orders
GROUP BY
customer_id, order_date
HAVING
COUNT(*) > 1;
#Problem 3: List employees who have supervised other employees
SELECT
e1.employee_id,
e1.employee_name,
e2.employee_id AS supervised_employee_id,
e2.employee_name AS supervised_employee_name
FROM
employees e1
JOIN
employees e2 ON e1.employee_id = e2.supervisor_id;
#Problem 4: Find the top 5 customers with the highest total spending
SELECT
customer_id,
SUM(unit_price * quantity) AS total_spending
FROM
orders
JOIN
order_details ON orders.order_id = order_details.order_id
GROUP BY
customer_id
ORDER BY
total_spending DESC
LIMIT 5;These queries cover various advanced SQL concepts such as joins, aggregations, subqueries, and filtering conditions. Feel free to adapt them based on your specific exercise requirements.
Conclusion
Practicing SQL is essential for beginners to strengthen their SQL skills. By working through these 10 beginner SQL practice exercises, you will gain hands-on experience and improve your ability to write SQL queries. Remember to practice regularly and challenge yourself with more complex exercises to become a proficient SQL developer.
If you want to enhance your SQL skills and upskill for better growth, consider opting for courses from Vidhya Analytics. Our comprehensive courses can provide in-depth knowledge, practical insights, and real-world applications to sharpen your SQL proficiency. Invest in your learning journey with Vidhya Analytics and unlock new opportunities for career advancement.
Happy coding!
