MySQL Practice: Best Exercises for Beginners

These 15 MySQL exercises are especially designed for beginners. They cover essential topics like selecting data from one table, ordering and grouping data, and joining data from multiple tables

This article showcases beginner-level MySQL practice exercises, including solutions and comprehensive explanations. If you need to practice …

• Selecting data from one table
• Ordering and grouping data
• Joining data from multiple tables

… these 15 tasks are just for you!

Most of the exercises come from our SQL Practice Set in MySQL, which provides a complete training environment where you can run queries and see the output data. Upon enrolling in this course, you’ll have access to 88 interactive beginner-friendly exercises to get you started towards SQL mastery.

While doing the exercises in this article, you may find this MySQL Cheat Sheet helpful. It contains a quick reference guide for MySQL syntax.

Continuous practice makes perfect, so the only way to learn and master your SQL skills is through practice. Following and solving practice exercises is the key to perfecting your SQL skills.

Let’s get started.

A Note on Our MySQL Practice Exercises

The exercises in the following sections use different datasets and cover distinct topics, including selecting data from one table, ordering and grouping data, and joining data from multiple tables.

Each section contains five exercises that include the following:

• The text of the exercise, which describes what data to fetch from the database.
• The solution query.
• A discussion on how the solution works and how the query was constructed.

We encourage you to solve the exercise on your own before looking at the solution. If you like these exercises, sign up for our SQL Practice Set in MySQL course and solve all 88 exercises!

Section 1: Selecting Data from One Table

We’ll start with the most basic SQL syntax, which is the SELECT statement. It selects data from a single table. You can use the WHERE clause to filter data based on defined conditions.

Let’s introduce the dataset and get to the MySQL practice exercises.

Dataset: Cats

In this section, we’ll use the cat table. It consists of the following columns:

• id – The ID number of a given cat.
• name – The cat’s name.
• breed – The cat’s breed (e.g. Siamese, Ragdoll, etc.).
• coloration – The cat’s coloration.
• age– The cat’s age.
• sex – The cat’s sex.
• fav_toy – The cat’s favorite toy.

Exercise 1: List All Cats

Exercise:

Select all data from the cat table.

Solution:

SELECT *
FROM cat;

Explanation:

We use the SELECT statement to define the columns to be outputted by the query. Here, we want to select all columns, so we use the asterisk character (*), which stands for all columns.

The FROM clause specifies the name of the table from which data is extracted.

It works just like we’d say it – we select all the columns from the cat table.

Exercise 2: Select Younger Cats

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:

Again we use the SELECT statement to define the columns to be output by the query – here, the name, breed, and coloration columns.

It is followed by the FROM clause, which tells the database to get the data from the cat table.

Finally, we define the condition in the WHERE clause. As we want to select only cats that are younger than five years old, we impose a condition on the age column to be less than 5: WHERE age < 5.

Exercise 3: List All Ragdoll Cats

Exercise:

Select the ID and name for every cat that is of the Ragdoll breed.

Solution:

SELECT
  id,
  name
FROM cat
WHERE breed = 'Ragdoll';

Explanation:

We select the id and name columns from the cat table.

Next, we define a condition in the WHERE clause. As we want to list all cats of the Ragdoll breed, we impose a condition on the breed column to be equal to Ragdoll: WHERE breed = 'Ragdoll'. Note that in SQL we must enclose text strings in single quotation marks.

Exercise 4: Select Cats Whose Favorite Toy Is a Ball

Exercise:

Select all data for cats whose:

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

Solution:

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

Explanation:

We use the SELECT statement to select all data. The asterisk character (*) stands for all columns. Therefore, SELECT * selects all columns available in the cat table, which is defined in the FROM clause.

Here, we define multiple conditions on different columns. We use the percentage character (%), which is a wildcard that stands for any sequence of characters.

• The breed column value must start with an 'R' followed by any sequence of characters: breed LIKE 'R%'.
• The fav_toy column value must start with the word 'ball' followed by any sequence of characters: fav_toy LIKE 'ball%'.
• The coloration column value must end with an 'm' preceded by any sequence of characters: coloration LIKE '%m'.

Since all these conditions must be applied at the same time, we combine them in the WHERE clause using the AND operator. This ensures that all conditions hold true for the selected data rows.

Exercise 5: Find Older Cats with a Favorite Toy

Exercise:

Select all data for cats that are:

• More than 10 years old.
• Either of the Ragdoll or Abyssinian breeds.
• Have a known favorite toy.

Solution:

SELECT *
FROM cat
WHERE age > 10
  AND (breed = 'Ragdoll' OR breed = 'Abyssinian')
  AND fav_toy IS NOT NULL;

Explanation:

Again we select all columns from the cat table using an asterisk character (*) listed along the SELECT statement. Then we impose the conditions on the columns:

• We want to select cats that are older than 10, so we impose a condition on the age column: age > 10.
• We want to select cats that are of Ragdoll or Abyssinian breeds, so we impose a condition on the breed column: (breed = 'Ragdoll' OR breed = 'Abyssinian'). Please note that this is a composite condition and we must enclose it in parentheses. The two parts are combined with the OR operator because we want to select either Ragdoll or Abyssinian cats.
• We want to select cats that have a favorite toy. Therefore, we impose a condition on the fav_toy column: fav_toy IS NOT NULL. We use the IS NOT NULL expression to ensure that selected cats have a favorite toy assigned.

To apply all three conditions at the same time, we combine them using the AND operator.

Section 2: Ordering and Grouping Data

Now that we’ve recalled how to query data from a table and filter it by imposing different conditions, let’s move on to ordering, aggregating, and grouping data.

Let’s introduce the dataset and get to the MySQL practice exercises.

Dataset: Games

In this section, we’ll use the games table, which has the following columns:

• id – The ID of a given game.
• title – The game’s title.
• company – The name of the company that produced the game.
• type – The game’s genre.
• production_year – The year when the game was created.
• system – The system on which the game is played.
• production_cost – The cost of producing this game.
• revenue – The revenue generated by the game.
• rating – The game’s

Exercise 6: Order Data by Cost and Rating

Exercise:

Select all data from the games table and order the results by the production cost from cheapest to most expensive. If multiple games have the same production cost, order the results by ratings, from best to worst.

Solution:

SELECT *
FROM games
ORDER BY
  production_cost ASC,
  rating DESC;

Explanation:

We select all columns from the games table: SELECT * FROM games.

Next, we order the output data using the ORDER BY clause and the production_cost column. As we want it to be in ascending order, we follow the column name with the ASC keyword: ORDER BY production_cost ASC.

The secondary column by which we want to order the output data is the rating column. To order the data from best to worst rating (that is, in descending order), we use the DESC keyword: rating DESC.

Exercise 7: Order High-Rated Games by Production Cost

Exercise:

Show the production cost values of games that were produced between 2010 and 2015 and were rated higher than 7. Order data by the cheapest to the most expensive production cost.

Solution:

SELECT 
  production_cost
FROM games
WHERE production_year BETWEEN 2010 AND 2015
  AND rating > 7
ORDER BY production_cost ASC;

Explanation:

We select the production_cost column from the games table.

As we want to select only games produced between 2010 and 2015 and rated higher than 7, we impose conditions on the production_year and rating columns and combine them with the AND operator: production_year BETWEEN 2010 AND 2015 AND rating > 7.

Finally, we order the output data by the production_cost column. We add the ASC keyword (which stands for ascending)  to order the data from cheapest to most expensive production costs.

Exercise 8: Count Games Produced per Year

Exercise:

For each year, display:

• The year (production_year).
• How many games were released in this year (as the count column).
• The average production costs per game for that year (as the avg_cost column).
• The average revenue per game for that year (as the avg_revenue column).

Solution:

SELECT
  production_year,
  COUNT(*) AS count,
  AVG(production_cost) AS avg_cost,
  AVG(revenue) AS avg_revenue
FROM games
GROUP BY production_year;

Explanation:

We select the following from the games table:

• The production_year
• The count of all games produced in that We use the COUNT() aggregate function, passing an asterisk (*) as its argument, to count all rows.
• The average production cost in that We use the AVG() aggregate function, passing the production_cost column as its argument, to calculate the average production cost.
• The average revenue within a given year. We use the AVG() aggregate function, passing the revenue column as its argument, to calculate the average revenue.

As we want to calculate these statistics (count and average) for each year, we need to group all data by the production_year column. We use the GROUP BY clause, passing production_year as its argument, so the data is put into as many groups as there are distinct values in the production_year column.

Exercise 9: Count Profitable Games by Type and Company

Exercise:

Count how many games of a given type and produced by a given company turned out to be profitable (their revenue was greater than their production cost). Show the number of games (as number_of_games) and the company and type columns.

Solution:

SELECT 
  company,
  type,
  COUNT(*) AS number_of_games
FROM games
WHERE revenue > production_cost
GROUP BY company, type;

Explanation:

We select the company and type columns and the count of all games of each type produced by a given company. Therefore, we must list both the company and type columns in the GROUP BY clause so the groups are based on combinations of unique values from the company and type columns.

As we only need the profitable games, we impose a condition in the WHERE clause that ensures the revenue column value is greater than the production_cost column value. If that is not the case, this game will not be counted.

Exercise 10: List the Number of Games and Average Cost

Exercise:

For each company, select its name, the number of games it produced (as the number_of_games column) and the average cost of production (as the avg_cost column). Show only companies that produced more than one game.

Solution:

SELECT
  company,
  COUNT(company) AS number_of_games,
  AVG(production_cost) AS avg_cost
FROM games
GROUP BY company
HAVING COUNT(company) > 1;

Explanation:

We select the following from the games table:

• The company name.
• The number of games produced by the company using the COUNT() aggregate function.
  Please note that you can use either the company column or an asterisk character (*) as an argument to COUNT(). The difference is that COUNT(*) counts all rows and COUNT(company) counts all rows where the company column is not null. In this case, the company column stores values for each row, so we can use the two options interchangeably. In most other queries, COUNT(column) and COUNT(*) are not interchangeable.
• The average production cost per company, using the AVG() aggregate function.
  Here, we pass the production_cost column as an argument to AVG() because we want to calculate an average for the values stored in this column.

As the SELECT statement lists one column (company) and two aggregate functions that perform calculations per company, we must group the output data by the company column.

To show only the companies that produced more than one game, we must impose a condition on the number_of_games column. However, we cannot use the WHERE clause because number_of_games is an alias name for the value calculated using the COUNT(company) aggregate function.

We use the HAVING clause to impose conditions on aggregate functions. This clause is processed after the GROUP BY clause. You can use either the alias name (HAVING number_of_games > 1) or the aggregate function itself (HAVING COUNT(company) > 1) to create such a condition.

Section 3: Joining Data from Multiple Tables

Now that we’ve reviewed how to query, filter, order, and group data from a single table, it’s time to move on to joining data from multiple tables and performing operations on joined data.

Let’s introduce the dataset and get to the MySQL practice exercises.

Dataset: Employees

In this section, we’ll use the employees dataset that consists of the following tables:

• The employee table, which has the following columns:
  • 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 salary.
  • manager_id – The ID of this employee's manager.
    
• The salgrade table, which has the following columns:
  • grade – The salary grade.
  • lower_limit – This grade’s lower limit.
  • upper_limit – This grade’s upper limit.
    
• The benefits table, which has the following columns:
  • salary_req – The minimum salary required to obtain a given benefit.
  • benefit_name – The benefit name.

Exercise 11: List Salary Grades for All Employees

Exercise:

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

Solution:

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

Explanation:

We want to select columns stored in the employee and salgrade tables. Therefore, we must join these two tables – or, as we do in this case, list them both in the FROM clause.

We select the first and last name and the salary from the employee table, and the grade column from the salgrade table. We list both these tables in the FROM clause.

We provide the join condition in the WHERE clause. The salary value from the employee table must fall between the lower_limit value and the upper_limit value from the salgrade table.

Exercise 13: List All Benefits for Employee 5

Exercise:

Show all benefits received by the employee with id = 5. Select the first and last name of that employee and 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:

We select the first and last name from the employee table and the name of the benefit from the benefits table. Therefore, we must join the two tables using the JOIN statement.

To qualify for a certain benefit, the employee’s salary (stored in the salary column of the employee table) must be greater than or equal to the salary value required to obtain this benefit (stored in the salary_req column of the benefits table). Therefore, the join condition is exactly that: employee.salary >= benefits.salary_req.

As we want to show all benefits for the employee with id = 5, we impose this condition in the WHERE clause.

Exercise 12: Show Employees and Direct Managers

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. List only employees who have a direct manager.

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
JOIN employee m
  ON e.manager_id = m.id;

Explanation:

Each record in the employee table contains a manager_id value that points to the id column of another employee who is this employee’s direct manager. Therefore, we must join the employee table with itself – i.e. a self-join.

The employee table aliased as e is used to select employees’ first name, last name, and salary. The employee table aliased as m is used to select the first and last name of the managers. This is defined in the ON clause, where we impose a join condition saying that the manager_id column from the e table must be equal to the id column from the m table. In effect, this makes the m table store managers of employees stored in the e table.

Exercise 14: Show Benefits and How Many Employees Receive Them

Exercise:

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

Solution:

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

Explanation:

We want to count how many employees receive a given benefit. Therefore, we must join the benefits and employee tables.

To qualify for a certain benefit, the employee’s salary (stored in the salary column of the employee table) must be greater than or equal to the salary value required to obtain this benefit (stored in the salary_req column of the benefits table). Therefore, the join condition is exactly that: salary_req <= employee.salary.

This exercise brings together JOINs and GROUP BY. We select the benefit name and the count of employees who receive a given benefit. Therefore, we must group the data by the benefit_name column.

Exercise 15: Show Benefits Not Received by Any Employee

Exercise:

For each benefit, find the number of employees that receive it. Show two columns: the benefit_name and the count (name that column employee_count). Impose a condition to select only benefits that are not received by any employee (employee_count = 0).

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
HAVING COUNT(employee.id) = 0;

Explanation:

This exercise is analogical to the previous exercise.

To list all benefits – including the ones that no employee receives – we use the LEFT JOIN statement: benefits LEFT JOIN employee. It ensures that all rows from the left-hand side table (here, benefits) are listed, no matter whether they match the join condition or not.

Next, to select only the employee_count values of zero, we must impose a condition on the COUNT(employee.id) aggregate function. To do so, we use the HAVING clause (introduced in exercise 10).

Embrace the Power of MySQL Practice Exercises!

This article covered the basics of MySQL queries, including selecting and filtering data, ordering and grouping data, and joining multiple tables.

If you enjoyed the MySQL practice exercises showcased in this article, I suggest taking our SQL Practice Set in MySQL for more.

For additional help on SQL practice, check out these blog posts:

• SQL for Data Analysis: 15 Practical Exercises with Solutions.
• 10 Beginner SQL Practice Exercises with Solutions.
• How to Create Your Own Database to Practice SQL.
• 12 Ways to Practice SQL Online.

And remember, practice makes perfect.

Good luck!