Messy customers sample database for MySQL
A ready-to-run MySQL script with a customer list with duplicates, blanks, mixed dates and text in number columns. Tested on MySQL 8.4.13. Updated 19 November 2026.
Messy customers database for MySQL
The customer table with problems added on purpose: repeated customer IDs, names in the wrong case, trailing spaces, blank emails, phone numbers in three formats, state names mixed with codes, dates in three formats, and $ signs or N/A in the spend column.
This page has it as a MySQL script: one table and 1,000 rows, with column types chosen for MySQL, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the messy customers dataset page, where it is also available as CSV and Excel.
We loaded this exact file into MySQL 8.4.13 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
mysql client
mysql -u root -p -e "CREATE DATABASE practice"
mysql -u root -p practice < messy-customers-mysql.sqlMySQL Workbench
File > Open SQL Script, choose the database with USE practice; then Execute.
Tables and columns
messy_customers 1,000 rows
| Column | MySQL type |
|---|---|
| customer_id | VARCHAR(10) |
| first_name | VARCHAR(20) |
| last_name | VARCHAR(20) |
| VARCHAR(40) | |
| phone | VARCHAR(20) |
| city | VARCHAR(20) |
| state | VARCHAR(20) |
| signup_date | VARCHAR(20) |
| segment | VARCHAR(20) |
| total_orders | VARCHAR(10) |
| total_spent | VARCHAR(20) |
| notes | VARCHAR(20) |
Practice questions
answers from MySQL 8.4.13
How many rows and how many distinct customer_id values are there? (duplicates show up as the difference)
SQL
SELECT COUNT(*) AS row_count, COUNT(DISTINCT customer_id) AS distinct_ids
FROM messy_customers;Result
| row_count | distinct_ids |
|---|---|
| 1000 | 960 |
How many rows have a missing or blank email?
SQL
SELECT COUNT(*) AS blank_emails
FROM messy_customers
WHERE (email IS NULL OR TRIM(email) = '');Result
| blank_emails |
|---|
| 44 |
After lower-casing and trimming, how many distinct emails remain, compared with the raw values?
SQL
SELECT COUNT(DISTINCT email COLLATE utf8mb4_bin) AS raw_distinct, COUNT(DISTINCT LOWER(TRIM(email))) AS cleaned_distinct
FROM messy_customers
WHERE NOT (email IS NULL OR TRIM(email) = '');The default utf8mb4 collation ignores case, so COUNT(DISTINCT email) would already merge upper- and lower-case copies; COLLATE utf8mb4_bin makes the raw count case-sensitive (838 instead of 827 here).
Result
| raw_distinct | cleaned_distinct |
|---|---|
| 838 | 827 |
Which state values are not a clean two-letter code? List them with counts.
SQL
SELECT state, COUNT(*) AS n
FROM messy_customers
WHERE CHAR_LENGTH(state) <> 2
GROUP BY state
ORDER BY n DESC, state;CHAR_LENGTH counts characters (LENGTH counts bytes).
Result
| state | n |
|---|---|
| New York | 13 |
| Texas | 12 |
| Florida | 8 |
| California | 7 |
| Illinois | 7 |
| Pennsylvania | 5 |
| Georgia | 4 |
| Indiana | 4 |
| Massachusetts | 4 |
| Nevada | 4 |
| Ohio | 4 |
| Oregon | 4 |
| Utah | 3 |
| Wisconsin | 3 |
First 14 of 26 rows.
How many signup dates are not in ISO format (YYYY-MM-DD)?
SQL
SELECT COUNT(*) AS non_iso_dates
FROM messy_customers
WHERE signup_date NOT LIKE '____-__-__';Result
| non_iso_dates |
|---|
| 225 |
How many rows have a blank total_orders value?
SQL
SELECT COUNT(*) AS blank_total_orders
FROM messy_customers
WHERE (total_orders IS NULL OR TRIM(total_orders) = '');Result
| blank_total_orders |
|---|
| 69 |
MySQL differences used here
- The default utf8mb4 collation ignores case, so COUNT(DISTINCT email) would already merge upper- and lower-case copies; COLLATE utf8mb4_bin makes the raw count case-sensitive (838 instead of 827 here).
- CHAR_LENGTH counts characters (LENGTH counts bytes).