MySQL exercises

What transfers directly

Filters, inner and left joins, aggregates, and RANK or ROW_NUMBER transfer to MySQL 8 with the same shape. LIMIT is shared. Quoting identifiers with double quotes is not MySQL's default; backticks are. These exercises do not require quoted identifiers.

What you will rewrite

SQLite uses substr and strftime for the dates in this dataset. In MySQL you would write DATE_FORMAT(paid_at, '%Y-%m') or LEFT(paid_at, 7) if the column is text. ONLY_FULL_GROUP_BY will also reject a selected column that is not grouped. Grouping by the primary key, which these solutions do, satisfies that mode.

Example

-- MySQL equivalent of the month bucket used in the cash report
SELECT DATE_FORMAT(paid_at, '%Y-%m') AS month,
       SUM(amount_cents) AS revenue_cents
FROM payments
WHERE status = 'succeeded'
GROUP BY DATE_FORMAT(paid_at, '%Y-%m');

The business rule is identical. Only the month extraction function changes.

Practice

• BeginnerE-commerce
  Customers in Germany

  Find the customers Northwind serves in Germany.

• EasyE-commerce
  Order line totals

  Attach each order line to its product and compute the line total.

• EasyE-commerce
  Revenue by category

  Sum recognized revenue for each product category.

• EasyE-commerce
  Customers with repeat orders

  Find customers with at least two orders that were not cancelled.

• HardHR
  Employees and their managers

  Show each employee and the manager they report to.

• HardE-commerce
  Monthly succeeded revenue

  Sum succeeded payments by calendar month.

• MediumMarketing
  Duplicate lead emails

  Find email addresses that were captured more than once.

• HardE-commerce
  Rank products by revenue

  Rank products by recognized revenue.

Common mistakes

• Relying on MySQL's old loose GROUP BY, which hides a wrong grain.
• Using double-quoted identifiers and having them interpreted as strings.
• Writing ILIKE, which MySQL does not support. Use LIKE or a collation.
• Assuming a TEXT date sorts correctly after you store it as '03/01/2024'.

FAQ

Can I paste these queries into MySQL?

The join and aggregate exercises paste cleanly. Rewrite substr and strftime before you run the date exercises. The expected results were produced by SQLite.

Why not run MySQL in the browser?

SQLite's WASM build is small and fast. It keeps practice free to operate and keeps your SQL off a shared server. Dialect pages exist so the differences stay visible.

Related practice

• SQL practice
• Beginner SQL practice
• SQL JOINs
• GROUP BY