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!

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!