Skip to content
Tutorials Center
My learning

Topics

Lesson 72 of 75 ยท Practice and Review

MySQL Exercises with Solutions

Practice MySQL with 25 graded exercises on data types, joins, indexes, JSON, window functions and transactions, each with a worked solution you can run.

Reading about SQL is easy to mistake for knowing it. The fastest way to find what you really understand is to write queries without looking at an answer. This lesson gives you 25 exercises, ordered by topic and graded from one star (a single concept) to three stars (several ideas combined), and every exercise has a worked solution that you can run and edit.

How to use it: read the task, then write your own answer before looking at the solution. Click into the editor of the solution, select everything, delete it and type your version. When you are done, paste the solution back from the lesson text, or simply compare the output with the result described under each block. Some tasks are about MySQL-only features, and for those the solution is a read-only code block, because the browser runs SQLite.

What runs live. Exercises 2, 4 and 5 to 25 (except 12) run in the SQLite stand-in against the built-in shop and company sample databases or small tables you create. Where MySQL spells something differently (->>, JSON_TABLE, START TRANSACTION, ROW_COUNT()), the solution says so. Exercises 1, 3 and 12 are design questions with read-only answers.

The six topics

Types and design Exercises 1 to 4.
Queries and joins Exercises 5 to 11.
Indexes Exercises 12 to 15.
JSON Exercises 16 to 18.
1 / 2

Part 1: Data types and design

Exercise 1 (โ˜…): choose the column types

Design the columns of a users table with: an id, an e-mail address (unique, up to 254 characters), a display name (up to 80 characters), a birth date, an "is active" flag, and a created-at timestamp that is filled in automatically. Use the smallest sensible MySQL types and the right character set.

The solution is read-only MySQL DDL:

SQL
CREATE TABLE users (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(254)    NOT NULL,
  display_name VARCHAR(80)     NOT NULL,
  birth_date   DATE            NULL,
  is_active    TINYINT(1)      NOT NULL DEFAULT 1,
  created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Why these choices: BIGINT UNSIGNED never runs out of ids, and INT UNSIGNED (4 billion) is also fine for a small system. VARCHAR(254) is the practical maximum of an e-mail address. DATE needs no time part. TINYINT(1) is what BOOLEAN becomes in MySQL. utf8mb4 stores all of Unicode, and the default _0900_ai_ci collation compares case-insensitively and ignores accents, which suits e-mail lookups (character sets, choosing data types).

Exercise 2 (โ˜…): why not FLOAT for money?

Show with a query that binary floating point cannot represent 0.1 + 0.2 exactly, then show how integer cents fix it.

SQL Exercise 2: floating point versus cents

Press Run (or Ctrl + Enter) to see the result here.

Result: float_equal is 0 (false), the stored sum prints as 0.30000000000000000000 only because it is rounded to 20 digits for display (the real double is slightly off from 0.3 in binary), and cents_equal is 1. Integers are exact, so whole cents add up without drift. MySQL's FLOAT and DOUBLE behave the same way. Use DECIMAL(10,2) or integer cents for money (numeric types). In MySQL, a decimal literal such as 0.1 + 0.2 = 0.3 is exact, because literals with a decimal point are DECIMAL, but a DOUBLE column is not.

Exercise 3 (โ˜…โ˜…): store a UUID, an IPv4/IPv6 address and a flag compactly

Pick MySQL types that use the least space for a UUID, an IP address that may be IPv4 or IPv6, and a yes/no flag, and name the functions that convert them.

SQL
CREATE TABLE sessions (
  id         BINARY(16)      NOT NULL,   -- UUID_TO_BIN(UUID(), 1) to store, BIN_TO_UUID(id, 1) to read
  ip         VARBINARY(16)   NOT NULL,   -- INET6_ATON('203.0.113.7') / INET6_NTOA(ip)
  is_revoked TINYINT(1)      NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

A UUID as CHAR(36) costs 36 bytes per row and per secondary-index entry, while BINARY(16) costs 16. The swap flag 1 in UUID_TO_BIN moves the time part first, so new ids are roughly increasing and inserts stay near the end of the primary-key B-tree. INET6_ATON returns 4 bytes for IPv4 and 16 for IPv6, which is why VARBINARY(16) fits both (auto-increment and ID strategies).

Exercise 4 (โ˜…โ˜…): a CHECK constraint and a generated column

Create a items table with a price and a discount percent. The discount must be between 0 and 100. Add a generated column final_price that applies the discount, then query it.

SQL Exercise 4: CHECK and a generated column

Press Run (or Ctrl + Enter) to see the result here.

Result: the Lamp ends at 30, the Desk stays 180, and the Chair is 89.91. The generated column is never written by you and always follows the other two columns. An insert with discount = 150 would be rejected by the CHECK. In MySQL the column is written final_price DECIMAL(10,2) AS (ROUND(price * (100 - discount) / 100, 2)) VIRTUAL, and CHECK is enforced from 8.0.16 (generated columns, keys and constraints).

Part 2: Queries and joins (shop and company databases)

The next exercises use the built-in shop sample database (categories, products, customers, orders, order_items) and company (departments, employees, projects, assignments).

Exercise 5 (โ˜…): filter, sort, limit

List the five most expensive products whose price is between 20 and 50 (inclusive), with name and price, most expensive first.

SQL Exercise 5: filter and sort ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: Camping Hammock (49.50), Pour-Over Coffee Kit (42), Ergonomic Mouse (39.90), Folding Camp Chair (38) and Cast Iron Skillet (34.90).

Exercise 6 (โ˜…): group and filter groups

Show the countries that have at least three customers, with the number of customers, largest first (ties by country name).

SQL Exercise 6: HAVING ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: eight countries qualify. Colombia and Poland lead with 6 customers each, then Australia, France and Turkey with 4, then Canada, Czechia and Germany with 3. WHERE filters rows before grouping and cannot use COUNT(*), while HAVING filters the finished groups (GROUP BY and aggregates).

Exercise 7 (โ˜…โ˜…): revenue per category

Calculate the revenue per product category from order_items (quantity times unit price), ignoring cancelled orders. Show the category name and the revenue rounded to two decimals, highest first.

SQL Exercise 7: joins and aggregates ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: Audio leads with 7,132, followed by Outdoor (6,557.2), Fitness (4,476.8), Kitchen (3,878.8), Home Office (2,757.8) and Stationery (1,713.3). Putting the status test in the ON clause or in WHERE is the same for an inner join. It matters for outer joins, as the next exercise shows (joins in MySQL).

Exercise 8 (โ˜…โ˜…): rows with no match

Find the customers who have never placed an order. Show first name, last name and country.

SQL Exercise 8: anti-join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: six customers never ordered: Kofi Adeyemi, Tara Fischer, Yusuf Kaya, Aiden Moreau, Yara Usman and Zoe Varga. The LEFT JOIN ... WHERE right.id IS NULL pattern keeps customers with no matching order. NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) gives the same rows and is often clearer. Avoid NOT IN with a subquery that can return NULL, because one NULL makes the whole condition unknown (null handling).

Exercise 9 (โ˜…โ˜…): top customers by spend

Who are the three customers with the highest total spend (non-cancelled orders, items only)? Show the full name and the total.

SQL Exercise 9: top N with a join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: Rania Andersson (1,710.30), Sami Kaya (1,610.70) and Hana Weber (1,576.00). In MySQL, || is a logical OR unless PIPES_AS_CONCAT is on, so write CONCAT(c.first_name, ' ', c.last_name) there. Group by the customer's primary key, not by the name: two people can share a name.

Exercise 10 (โ˜…โ˜…): a self join

List each employee with the name of their manager. The CEO has no manager and must still appear, with NULL. Show the first five rows in id order.

SQL Exercise 10: self join ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Result: employee 1, Kofi Okafor, has NULL as manager. Employees 2 to 5 all report to him. The same table appears twice under two aliases, once as the employee and once as the manager. The LEFT JOIN keeps the row of the employee without a manager.

Exercise 11 (โ˜…โ˜…โ˜…): groups above the overall average

Which departments pay an average salary above the average salary of the whole company? Show the department name, its average salary (rounded) and the company average.

SQL Exercise 11: HAVING with a subquery ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Result: only Finance (average 95,600) and Support (94,333) beat the company average of 90,361. The scalar subquery is evaluated once, because it does not depend on the outer row (subqueries).

Part 3: Indexes

Exercise 12 (โ˜…): design an index for a query

A table customers is queried constantly with WHERE country = ? AND city = ? ORDER BY signed_up DESC LIMIT 20. Which composite index serves this best, and why?

SQL
CREATE INDEX ix_customers_country_city_signed ON customers (country, city, signed_up);

Both filters are equalities, so they go first (in either order). The sort column goes last, so within one (country, city) pair the entries are already ordered by signed_up and MySQL can read them backwards for DESC without sorting, stopping after 20 rows. An index on (signed_up, country, city) would be worse, because the equality columns could not narrow the search (designing composite indexes).

Exercise 13 (โ˜…โ˜…): prove an index is used

Orders are listed by customer and status all day. Show the query plan for WHERE customer_id = 7 AND status = 'shipped' before and after you add a suitable index.

SQL Exercise 13: before and after an index ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: the first plan says SCAN orders, a full table scan. The second says SEARCH orders USING INDEX ix_orders_customer_status (customer_id=? AND status=?), so the engine jumps straight to the matching entries. On MySQL you would look at EXPLAIN, where type should change from ALL to ref and key should name the new index (EXPLAIN).

Exercise 14 (โ˜…โ˜…): a sargable rewrite

The query WHERE substr(ordered_at, 1, 10) = '2025-03-01' returns the orders of one day but cannot use an index on ordered_at. Rewrite it so that it can, and show both plans.

SQL Exercise 14: avoid a function on the column ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: the first plan walks the whole index entry by entry (SCAN ... USING COVERING INDEX), because the function hides the column and every value must be computed and compared. The second plan is a SEARCH with a range (ordered_at>? AND ordered_at<?), which reads only that day's entries. The rule is the same in MySQL: WHERE DATE(ordered_at) = '2025-03-01' hides the column inside a function, so the B-tree is useless, while a half-open range >= day AND < next day is sargable. The range also keeps working when the column has a time part (query optimization).

Exercise 15 (โ˜…โ˜…โ˜…): a covering index

A page shows status and ordered_at of all orders of one customer. Create an index so that the query is answered from the index alone, without reading the table, and prove it with the plan.

SQL Exercise 15: covering index ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: the plan says SEARCH orders USING COVERING INDEX ix_orders_cover (customer_id=?). The word COVERING means the table itself is never read. In InnoDB every secondary index also carries the primary key, so SELECT id, status, ordered_at ... would be covered by the same index too, and EXPLAIN shows Using index in the Extra column. Do not overdo it: each wide index costs space and slows writes (how MySQL indexes work).

Part 4: JSON

MySQL's JSON functions have SQLite counterparts for the basics. The live solutions use the SQLite names, and each solution notes the MySQL form.

Exercise 16 (โ˜…): read values from JSON

A table holds user profiles as JSON text. Select each user's name, plan and the first language, and list only users on the pro plan.

SQL Exercise 16: extract JSON values

Press Run (or Ctrl + Enter) to see the result here.

Result: Ana (first language en) and Cy (fr) are returned. Bo is on the free plan and is filtered out. In MySQL the column type is JSON (it validates automatically), and the same query reads doc->>'$.name', doc->>'$.plan' and doc->>'$.langs[0]'. The single arrow -> returns JSON (strings keep their quotes), the double arrow ->> returns unquoted text (JSON data type).

Exercise 17 (โ˜…โ˜…): modify JSON

Add a "verified": true key to user 2's profile and remove the langs key of user 3. Show the documents afterwards.

SQL Exercise 17: JSON_SET and JSON_REMOVE

Press Run (or Ctrl + Enter) to see the result here.

Result: Bo's document gains "verified":true at the end, and Cy's document no longer contains langs. MySQL has the same JSON_SET and JSON_REMOVE. There, JSON_SET(doc, '$.verified', TRUE) writes a real JSON boolean. JSON_SET creates or overwrites a key, JSON_INSERT only creates, JSON_REPLACE only overwrites.

Exercise 18 (โ˜…โ˜…โ˜…): turn a JSON array into rows

Count how many users speak each language, using the three profiles from exercise 16.

SQL Exercise 18: expand a JSON array

Press Run (or Ctrl + Enter) to see the result here.

Result: en has 2 speakers (Ana and Cy), and de, es, fr and it have 1 each. json_each returns one row per array element, with the element in the value column. The MySQL equivalent is JSON_TABLE, shown read-only:

SQL
SELECT jt.lang, COUNT(*) AS speakers
FROM profiles p,
     JSON_TABLE(p.doc, '$.langs[*]' COLUMNS (lang VARCHAR(10) PATH '$')) AS jt
GROUP BY jt.lang
ORDER BY speakers DESC, jt.lang;

This works, but it scans every row. For frequent "who speaks X" lookups, a multi-valued index with MEMBER OF is the better tool (JSON_TABLE and indexing).

Part 5: Window functions

Exercise 19 (โ˜…โ˜…): rank within a group

Rank the employees of each department by salary, highest first. Use RANK, show department id, name, salary and rank, and limit the output to department 1 and 2 in rank order.

SQL Exercise 19: RANK per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Result: department 1 (10 people) ranks from Yara Demir (157,000, rank 1) down to Kofi Dubois (60,000, rank 10), and department 2 (4 people) from Farah Tanaka (138,000) to Wen Kaya (56,000). Nobody ties here, so RANK and ROW_NUMBER agree, but with equal salaries RANK would give both the same number. Note that the WHERE filter runs before the window, so the ranks are computed among the rows that remain. Because this filter keeps whole departments, the ranks are the same as without it (window functions).

Exercise 20 (โ˜…โ˜…): the top two per group

Show the two best-paid employees per department with ROW_NUMBER. Break salary ties by employee id.

SQL Exercise 20: top 2 per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Result: two rows for each of the six departments, such as Yara Demir and Ivo Rossi for department 1, and Farah Tanaka and Umar Andersson for department 2. A window function cannot be used in WHERE, so the rank is computed in a CTE and filtered outside it.

Exercise 21 (โ˜…โ˜…): a running total

Count the orders per month in the shop database and add a running total of orders over the months.

SQL Exercise 21: running total ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Result: the first months have 10, 9, 16, 3, 16 and 14 orders, and the running totals are 10, 19, 35, 38, 54 and 68. The explicit ROWS frame gives a strict row-by-row total. Without it, ORDER BY uses RANGE, which would treat equal months as peers.

Exercise 22 (โ˜…โ˜…โ˜…): compare each row with its group

For each employee of department 1, show the salary, the department average, the difference, and the difference as a percentage of the average (one decimal).

SQL Exercise 22: window average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Result: the department average is 87,500. Yara Demir is 69,500 above it (79.4 percent), and Kofi Dubois is 27,500 below (minus 31.4 percent). The differences sum to 0 over the department. With a GROUP BY the individual salaries would disappear. The window keeps every row and adds the average next to it. Naming the window once (WINDOW w AS (PARTITION BY department_id)) removes the repetition in MySQL 8.

Part 6: Transactions

Exercise 23 (โ˜…): an all-or-nothing transfer

Move 50 from account 1 to account 2 so that either both updates happen or neither does. Show the balances afterwards.

SQL Exercise 23: transfer in a transaction

Press Run (or Ctrl + Enter) to see the result here.

Result: Ana has 150 and Bo has 80. If the process crashed between the two updates, the transaction would be rolled back and the money would not vanish. In MySQL write START TRANSACTION (or BEGIN), and make sure the table uses InnoDB, because MyISAM ignores transactions. If the second update failed, the application would run ROLLBACK and the first update would be undone (transactions and ACID).

Exercise 24 (โ˜…โ˜…): a partial rollback with a savepoint

Inside one transaction, insert two orders. Decide that the second one is wrong and undo only that one, then commit. Show which rows remain.

SQL Exercise 24: SAVEPOINT

Press Run (or Ctrl + Enter) to see the result here.

Result: two rows remain, the keyboard and the mouse. The typo-item insert was undone by ROLLBACK TO, although the transaction continued and committed the rest. MySQL supports SAVEPOINT name and ROLLBACK TO SAVEPOINT name in the same way. A savepoint does not end the transaction. Locks taken after it may stay held until the final commit or rollback.

Exercise 25 (โ˜…โ˜…โ˜…): reserve stock safely

A product has 5 units left. Write a statement that reserves 3 units only when at least 3 remain, run it twice, and show how the application can tell the second call failed.

SQL Exercise 25: conditional update

Press Run (or Ctrl + Enter) to see the result here.

Result: the first call reports 1 row and leaves 2 units. The second call reports 0 rows, which is the signal that the reservation failed, and the stock stays at 2. In MySQL, ROW_COUNT() (or the driver's affected-rows number) returns 1 and then 0. Checking and subtracting in one statement is safe under concurrency, while "SELECT, compare in code, UPDATE" is not, because two sessions can read the same number (e-commerce orders project, locking and deadlocks).

How did you do?

Count one point per exercise you solved without peeking, and weigh by stars if you like.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which filter belongs in HAVING rather than WHERE?

    HAVING filters groups after aggregation, so it can use aggregate functions. WHERE filters individual rows before grouping.
  2. 2. Which rewrite makes "orders on one day" able to use an index on ordered_at?

    A range on the bare column is sargable. A function around the column forces a scan of every row.
  3. 3. How do you get the top two rows per group?

    Window functions are not allowed in WHERE, so the number is calculated in an inner query and filtered in the outer one.
  4. 4. What does the affected-row count tell you after a conditional stock update?

    If the WHERE clause (including stock >= qty) matches no row, zero rows are changed, which means there was not enough stock.

Summary

  • Practice by writing the answer first. Compare with the solution and the described result, then vary the exercise.
  • Types: money as DECIMAL or cents, UUIDs as BINARY(16), utf8mb4 everywhere.
  • Queries: HAVING for groups, anti-joins for missing rows, self joins for hierarchies, grouping by key.
  • Indexes: equality columns first, then the sort column. Keep the indexed column free of functions, and cover hot queries when it pays off.
  • JSON: ->> to read text, JSON_SET/JSON_REMOVE to modify, JSON_TABLE to turn arrays into rows.
  • Windows: rank inside a CTE and filter outside. Use explicit ROWS frames for running totals.
  • Transactions: group related writes, use savepoints for partial undo, and make stock checks atomic.

Next: Common MySQL Pitfalls and Anti-Patterns.