22 Exercises to Practice SQL Commands

Want to improve your SQL knowledge? Practice SQL commands in these 22 realistic exercises!

There's an old saying that "there's no substitute for experience". But how can you achieve that experience when you’re just starting to learn something new? The best way is by practice, practice, and more practice! In this article, we’ll provide you with some real-world SQL command practice.

All of our practice exercises are taken from courses on LearnSQL.com. We offer interactive SQL courses on different tracks. Some courses are aimed at beginners; our SQL Basics course includes more than 120 exercises covering basic SQL commands. Intermediate users can benefit from tracks like SQL Practice, which includes ten interactive courses and more than 100 hours of practice! And for experienced professionals, the three courses in the Advanced SQL track (with almost 400 practical exercises) will help master SQL’s most  in-depth features.

Let’s get  started on improving our knowledge of SQL commands!

Working with One Table: SELECT, WHERE, ORDER BY, GROUP BY

In the next few exercises, we will query data from a single table that contains information about cats. The table is named Cat and has the following columns:

• id – The ID of a given cat. This is the primary key of the table.
• name – The cat’s
• breed – The cat’s
• coloration – The cat’s
• age – The cat’s
• sex – The cat’s
• fav_toy – The cat’s favorite toy.

Exercise #1: Selecting Columns and Filtering Numeric Data

Exercise: Select the name, breed, and coloration for every cat that is younger than five years old.

Solution:

SELECT 
  name, 
  breed, 
  coloration
FROM cat
WHERE age < 5;

Explanation: List the required columns (name, breed, and coloration) in the SELECT clause; separate them with commas. Reference the Cat table in the FROM clause and use the required condition age < 5 in the WHERE clause.

Exercise #2: Selecting All Columns and Filtering Text Data

Exercise: Select all data for cats whose:

• Breed starts with an 'R'.
• Favorite toy starts with the word 'ball'.
• Coloration ends with an 'm'.

Solution:

SELECT *
FROM cat
WHERE breed LIKE 'R%'
  AND fav_toy LIKE 'ball%'
  AND coloration LIKE '%m';

Explanation: Since we want to get all the table columns in the result, we use the * symbol to represent all available columns in the table. We reference the Cat table in the FROM clause and include the three required filter conditions using the LIKE operator and the % wildcard. Using the AND operator means each condition is related to the previous one; all three must be true for the row to be included in the result.

Remember that text values need to be enclosed in single quotes.

Exercise #3: Selecting Columns and Filtering NULL Data

Exercise: Select the names of all male cats that don't have a favorite toy – that is, the value of the field fav_toy is NULL for these cats.

Solution:

SELECT name
FROM cat
WHERE sex = 'M'
  AND fav_toy IS NULL;

Explanation: Include only the name column in the SELECT clause. Use two filter conditions with the AND operator in the WHERE clause. The first condition is to match the sex column with the 'M' literal. The second condition filters for rows where the fav_toy column has a NULL value.

Important: You cannot compare NULLs with other values using comparison operators like equal; you need to ask if a value IS NULL or IS NOT NULL.

Exercise #4: Ordering Data Returned by a SELECT

Exercise: Select the name, age, breed, and sex for all cats, showing the older ones first.

Solution:

SELECT 
  name, 
  age, 
  breed, 
  sex
FROM cat
ORDER BY age DESC;

Explanation: List the required columns (name, age, breed, and sex) in the SELECT clause. Reference the Cat table in the FROM clause and order the rows using the ORDER BY< clause followed by the age column.

Since the default order is ascending, we need to include the optional DESC keyword to sort the data in descending order –i.e. retrieving older cats first.

Exercise #5: Grouping Data and Ordering by Aggregation

Exercise: Show the count of cats per each coloration type,  using number_of_cats as an alias for the  aggregate function that calculates that information. Show the colorations with fewer cats first.

Solution:

SELECT 
  coloration, 
  COUNT(*) AS number_of_cats
FROM cat
GROUP BY coloration
ORDER BY number_of_cats DESC;

Explanation: Include the coloration column and the COUNT(*) aggregate function in the SELECT clause. Use the alias AS number_of_cats to return a user-friendly name for the  COUNT(*) column.

Use the GROUP BY clause followed by the coloration column to group data based on the coloration. Order the results with the ORDER BY clause followed by a column name or alias – in this case, number_of_cats.

Exercise #6: Grouping Data and Filtering by Aggregation

Exercise: Show the average age of cats in each breed; use Average_Age as an alias for the aggregate function. Show only breeds where the average age is greater than five years.

Solution:

SELECT 
  breed, 
  AVG(age) AS Average_Age
FROM cat
GROUP BY breed
HAVING AVG(age) > 5;

Explanation: Include the breed column and the AVG(age) aggregate function in the SELECT clause. Use an alias to return a user-friendly column name for the aggregate function. Use the GROUP BY clause followed by the breed column to group data based on each cat breed.

Since we need to filter based on the results of the aggregation, we cannot use the WHERE clause; it filters rows BEFORE they are grouped. Instead, we need to use the HAVING clause; it is applied after rows are grouped.

Learn more: Find more practice GROUP BY examples in the 10 GROUP BY SQL Practice Exercises with Solutions article.

Working with Multiple Tables: JOIN

In the next few exercises, we will query data from more than one table. We will use a very simple model that includes employees, salaries and benefits.

Let’s see the employee table structure:

• id – the id of a given employee.
• first_name – The employee’s first name.
• last_name – The employee’s last name.
• salary – The employee’s
• manager_id – The ID of the employee's manager.

Next, the salgrade table keeps salary grade information:

• grade – The grade of a salary.
• lower_limit – The salary grade’s lower limit.
• upper_limit – The salary grade’s upper limit.

And the last table, benefits, has only two columns:

• benefit_name – The name of a given benefit.
• salary_req – The minimum salary required to obtain this benefit.

Exercise #7: Implicitly Joining Data from Multiple Tables

Exercise: Select the first name, last name, salary, and salary grade of employees whose salary fits between the lower_limit and upper_limit in the salgrade table.

Solution:

SELECT 
  first_name, 
  last_name, 
  salary, 
  grade
FROM employee, salgrade
WHERE salary BETWEEN lower_limit AND upper_limit;

Explanation: List the required columns (first_name, last_name, salary, grade) in the SELECT clause. Reference both the employee and salgrade tables in the FROM clause. We are using the WHERE clause to define the join criteria between the two tables, specifying that the employee salary must be between the lower and upper limits of the salgrade table.

Exercise #8: Explicitly Joining Data from Multiple Tables

Exercise: Show all the benefits that the employee with id = 5 would receive. Select the first and last name of that employee together with the benefits' names.

Solution:

SELECT 
  first_name, 
  last_name, 
  benefits.benefit_name
FROM employee
JOIN benefits 
ON employee.salary > benefits.salary_req
WHERE employee.id = 5;

Explanation: List the required columns (first_name, last_name, benefits.benefit_name) in the SELECT clause. Reference the employee table in the FROM clause and then explicitly join the rows to the benefit table using the JOIN keyword.

The condition that defines the relationship between the two tables is specified after the ON keyword. This keeps table relation conditions separate from filtering conditions, which improves the query’s clarity. Filter conditions are still defined in the WHERE clause; in this case, we use the employee.id = 5 condition.

Exercise #9: Joining and Grouping Data from Multiple Tables

Exercise: For each benefit, find the number of employees that receive it. Show two columns: the benefit_name and the number of employees that receive it (name that column employee_count). Don't forget about benefits that aren't received by anyone.

Solution:

SELECT 
  benefits.benefit_name, 
  COUNT(employee.id) AS employee_count
FROM benefits
LEFT JOIN employee 
ON salary_req <= employee.salary
GROUP BY benefits.benefit_name;

Explanation: Include the benefit_name column and the COUNT() aggregation function (applied to the employee.id column) in the SELECT clause. Use an alias for the aggregate function results: AS employee_count. Reference the benefits table in the FROM clause, and then join the employee table using a LEFT JOIN.

Remember that LEFT (and RIGHT) JOIN operations retrieve all data from the left (or right) side of the JOIN criteria – even if no matching rows exist in the other table. You can learn more in the article SQL JOIN Types Explained.

The join criteria is specified after the ON keyword. Finally, use the GROUP BY clause to aggregate the data at the benefit_name level.

Exercise #10: Joining a Table to Itself (Self-Joins)

Exercise: Show each employee's first name, last name, salary, and the first name and last name of their direct manager in the same row. For employees who don't have a manager (e.g. the CEO), show NULL as the manager's first and last names.

Use an alias to prefix the employee's columns with employee_ and the manager's with manager_ (e.g. employee_first_name).

Solution:

SELECT 
  e.first_name AS employee_first_name, 
  e.last_name AS employee_last_name, 
  e.salary AS employee_salary,
  m.first_name AS manager_first_name,
  m.last_name AS manager_last_name
FROM employee e
LEFT JOIN employee m 
ON e.manager_id = m.id;

Explanation: Include the employee’s first name, last name, and salary using e as the employee table alias. Include the manager’s first name and last name using m as table alias. In the FROM clause, use the employee table with the e alias to reference employee data; in the LEFT JOIN clause, use the employee table with an m alias to reference manager information. The join condition is that the manager_id value of the employee must match the id value of the manager.

Learn more: Joining a table to itself is a common operation. It might seem tricky, but is simpler to understand when you use the right aliases for the table involved! For more help with JOINs, check out our SQL JOINS course. It has more than 90 interactive exercises, including ten free samples for you to start practicing right now.

Using Sub-Queries

In this section, we are going to use subqueries to query data based on conditions that depend on other data stored in the database. We will use the following tables, starting with the orchestras table:

• id – The ID of the orchestra.
• name – The orchestra’s name.
• rating – The orchestra’s rating over the last ten years.
• city_origin – The orchestra’s home city (e.g. ‘Berlin’ for the Berlin Philharmonic).
• country_origin – The orchestra’s country of origin (e.g. ‘Germany’ for the Berlin Philharmonic).
• year – The year in which the orchestra was created.

The concert table keeps information about the concerts played by orchestras:

• id – The ID of the concert.
• city – The name of the city where the concert took place. This may be different from the orchestra’s home city.
• country – The name of the country where the concert took place.
• year – The year in which the concert was performed.
• rating – The rating critics gave to the concert.
• orchestra_id – The ID of the orchestra that performed the concert.

The members table keeps information about each orchestra member:

• id – The ID of a given member.
• name – The member’s name.
• position – The member’s position in the orchestra (e.g. second flute, principal viola).
• wage – A fixed monthly payment given to the orchestra member.
• experience – The number of years of experience this orchestra member has.
• orchestra_id – The ID of the orchestra.

Exercise #11: Using Subqueries to Filter Data

Exercise: Show the names of orchestras that were created after the 'Chamber Orchestra' and have a rating greater than 7.5.

Solution:

SELECT name
FROM orchestras 
WHERE year > 
(SELECT year 
FROM orchestras 
WHERE name = 'Chamber Orchestra') 
  AND rating > 7.5;

Explanation: The SELECT and FROM clauses of this exercise are very simple, but the tricky bit is the WHERE clause. We have two conditions to evaluate, and we know exactly one of them (rating > 7.5). The second one is unknown to us, since it is not a defined value like 7.5 but something that needs to be obtained from the data – i.e. the founding year of the Chamber Orchestra.

In those scenarios, we use a subquery – i.e., a query that runs “inside” a bigger query. This exercise requires a subquery that obtains the orchestra’s founding year where the name equals 'Chamber Orchestra'. We use the result of that subquery to filter the founding year for each orchestra.

Pro Tip: When working with subqueries, it is a good practice to start building the subquery first. Once it returns the desired result, embed it in the main query!

Exercise #12: Using Subqueries in FROM

Exercise: Find the average number of members per orchestra.

Solution:

SELECT AVG(d.count) 
FROM (SELECT 
        orchestra_id, 
        COUNT(id) 
FROM members 
GROUP BY orchestra_id) d;

Explanation: Start with the subquery obtaining the count of members for each orchestra and the orchestra identifier. Put the subquery in the FROM clause of the main query and give it an alias. The SELECT clause just needs to calculate the average number of members using the AVG() aggregate function on the count results from the subquery.

Exercise #13: Correlated Subqueries

Exercise: Select the name, wage, and experience of each orchestra’s highest-paid members.

Solution:

SELECT 
  name, 
  wage, 
  experience
FROM members m1
WHERE wage = (SELECT MAX(wage) 
             FROM members m2 
		WHERE m1.orchestra_id = m2.orchestra_id);

Explanation: Include the name, wage, and experience columns in the main SELECT clause. Put the members table in the FROM clause and alias it m1. In the WHERE clause, compare the wage value (from the main query) with the results of the correlated subquery.

The correlated subquery obtains the maximum wage using the MAX() aggregate function for all the rows in the members table (aliased m2) where the orchestra_id matches the one from the main query.

Unlike regular subqueries that run once before the main query, correlated subqueries are executed once for each row in the main query. This is because they reference values from the main query.

Exercise #14: Use Filters, Joins, GROUP BY and Subqueries

Exercise: For each orchestra, show its name, the name of the city where the orchestra received the highest rating for its performance, and that rating.

Solution:

SELECT 
  o.name, 
  c.city, 
  c.rating
FROM orchestras o
JOIN concerts c 
ON o.id = c.orchestra_id
WHERE c.rating IN ( SELECT MAX(con.rating)
			FROM concerts con
			WHERE con.orchestra_id = o.id) ;

Explanation: Include the orchestra name, the concert city, and the rating columns in the SELECT clause of the main query. In the FROM clause, include the orchestras table and JOIN the concerts table using the orchestra IDs.

Use the WHERE clause to equal the value of the concert rating with the maximum concert rating obtained by the orchestra. To obtain this, use a correlated subquery that applies the MAX() function to the rating column. In the subquery’s WHERE clause, match the concert orchestra ID to the orchestra ID from the main query.

Data Modification SQL Commands: INSERT, UPDATE, DELETE

In the previous sections, we used the SELECT command. In this section, we will practice using other SQL commands like INSERT, UPDATE, and DELETE. We will use the dish table, which contains information about meals in a restaurant. It has the following columns:

• id - The ID of each dish and the primary key of this table.
• type - The type of dish (e.g. starter, main course, dessert).
• name - The dish’s
• price - The dish’s

Exercise #15: Inserting a Single Row Without Specifying Columns

Exercise: Add a dish called Cevapcici with an ID of 9 and a price of 27. It's a main course.

Solution:

INSERT INTO dish 
VALUES (9, 'main course', 'Cevapcici', 27);

Explanation: The INSERT INTO clause, followed by the table name, indicates the table where we want to insert data. Since we did not provide a list of columns, values for all columns must be provided in the VALUES clause. List them in the order they are defined in the table and enclose the list in brackets. In this case, we are inserting the dish’s ID, type, name, and price into the table.

Exercise #16: Inserting a Single Row and Specifying Columns

Exercise: Balkan cuisine is getting popular, so we need another Balkan item on the menu. Add Kosovo Bread with an ID of 10; it's a starter. We have yet to decide on the price, so omit it for now.

Solution:

INSERT INTO dish (id, type, name) 
VALUES (10, 'starter', 'Kosovo Bread');

Explanation: After the INSERT INTO clause and the table name, place the list of columns to be inserted. (Remember the brackets!) Columns not included in the list will be assigned a NULL value.

 In the VALUES clause, values are provided for each of the columns specified on the list.

Exercise #17: Updating Certain Rows

Exercise: It's happy hour at our restaurant! Change the price of all main courses to 20.

Solution:

UPDATE dish 
SET price = 20 
WHERE type = 'main course';

Explanation: Use the UPDATE sentence followed by the name of the table you want to update. In the SET clause, specify the column you want to update (in this case, just price) followed by the new value to assign to it.

The WHERE clause works in the same way as the SELECT sentence, but here it identifies the rows to be updated instead of the rows to be returned.

Exercise #18: Updating Multiple Columns

Exercise: Spring rolls are selling like crazy, but nobody's interested in prawn salad (ID 1) anymore. We need to change its name to something more exotic — let's try Green Sea Dragon. Set the price at 10 to encourage customers to try this dish.

Solution:

UPDATE dish 
SET name = 'Green Sea Dragon', price = 10 
WHERE id = 1;

Explanation: Use the UPDATE sentence followed by the name of the table you want to update. In the SET clause, specify the columns you want to update (name and price) followed by the new value(s) we’re assigning; separate each pair of name and values with a comma. The WHERE clause works in the same way as the SELECT sentence.

Exercise #19: Deleting a Single Row

Exercise: Oysters Bienville (ID 7) aren't very popular. Let's remove them from the menu.

Solution:

DELETE FROM dish 
WHERE id = 7;

Explanation: Use the DELETE FROM clause followed by the table name to indicate the table where data will be deleted.

The WHERE clause works in the same way with DELETE as with SELECT. In this case, it compares the id column with a single value. Thus, only one or zero rows will be deleted (depending if the row with id = 7 exists or not.

Exercise #20: Deleting Multiple Rows

Exercise: Oops, we've run out of sugar! Delete all desserts from our menu.

Solution:

DELETE FROM dish 
WHERE type = 'dessert';

Explanation: Use the DELETE FROM clause followed by the table name to indicate the table where data will be deleted. The WHERE clause works in the same way as the SELECT command; in this case, the SQL command will delete all rows with the value ‘dessert’ in the type column.

Learn more: Take a look at the course How to INSERT, UPDATE, and DELETE Data in SQL fpr 50+ exercises on inserting, updating, and deleting data.

CREATE TABLE and ALTER TABLE Commands

Now  we can practice using two additional SQL commands that let us create and alter tables.

Exercise #21: Creating a Simple Table

Exercise: Create a table named result with two INTEGER columns (id and score) and one DATE column (score_date).

Solution:

CREATE TABLE result (
  id integer,
  score integer,
  score_date date
);

Explanation: Use the CREATE TABLE sentence followed by the name of the table you want to create. Provide a list of column names with the appropriate data type, separated by commas. Make sure your entire column list is enclosed in brackets.

Exercise #22: Add a NOT NULL Column

Exercise: Modify the result table, making the id column NOT NULL and adding a new VARCHAR column called name with a limit of 32 characters.

Solution:

ALTER TABLE result ALTER COLUMN id SET NOT NULL;
ALTER TABLE result ADD COLUMN name varchar(32);

Explanation: Use the ALTER TABLE command followed by the table name. Use the ALTER COLUMN clause followed by the name of the column you want to modify. In this case, you set the column to NOT NULL.

Use the ALTER TABLE command again. This time, put the ADD COLUMN clause followed by the new column’s name and its data type and character limit (varchar(32)).

Learn more: The course The Basics of Creating Tables in SQL includes more than 80 exercises and 10 hours of practice on creating and altering database tables.

Ready for More SQL Command Practice?

We have covered SQL commands that allow you to query one or several tables, filter data, group and order data, insert and update data, and delete data. We’ve also shown you basic options to create and alter database tables.

However, there is still a wealth of knowledge to gain about SQL commands. Check out Your Guide to SQL Practice at LearnSQL.com to find dozens of articles providing additional SQL command practice.

For comprehensive learning, consider purchasing our All Forever SQL Package. This package grants you access to all current and future courses, ensuring you have the resources to continuously enhance your SQL skills. Don't miss out on this opportunity to stay ahead!