ShopDB: A Free Sample Database for Practising SQL (MySQL and SQL Server)

The fastest way to learn SQL is to type real queries against real data. But most beginners get stuck before they start: what data? A table of ten made-up rows teaches you SELECT, and then runs out of surprises.

That’s why I built ShopDB: a small but realistic database for an online electronics shop. It has customers, products, orders and staff, linked the way a real business system is. It’s the practice database behind every example in my free book, Simplify SQL for Absolute Beginners, and you can download it here for free, for MySQL or SQL Server.

Download ShopDB (free)

One zip file with the MySQL script, the SQL Server script and a short README. 10 KB, no sign-up needed.

What’s in the download

The zip file contains three files:

File Use it with
shopdb_mysql.sql MySQL 8 or MariaDB (including XAMPP)
shopdb_sqlserver.sql SQL Server 2016 or later, including the free Express and Developer editions
README.md A short guide to the tables and the “interesting” rows

Each script creates a database called ShopDB, builds six tables with primary keys, foreign keys and constraints, and fills them with data. If ShopDB already exists, the script drops it and starts again, so you can run it any time to get the original data back after you’ve broken something.

The six tables

ShopDB: six tables, linked by keysCustomers15 rowsCustomerIDPKFirstNameLastNameEmailPhoneCityCountrySignupDateOrders48 rowsOrderIDPKCustomerIDFKEmployeeIDFKOrderDateShippedDateStatusEmployees8 rowsEmployeeIDPKFirstNameLastNameJobTitleManagerIDFKHireDateSalaryEmailCategories7 rowsCategoryIDPKCategoryNameDescriptionProducts21 rowsProductIDPKProductCodeProductNameCategoryIDFKUnitPriceUnitsInStockDiscontinuedAddedDateOrderItems95 rowsOrderIDFKPKProductIDFKPKQuantityUnitPriceDiscountPKprimary keyFKforeign keymany rows (fork) point to one row (bar)
ShopDB’s six tables. Each line joins a foreign key (FK) to the primary key (PK) it points to.
Table Rows One row is…
Categories 7 A product group, such as Laptops or Printers
Products 21 Something the shop sells, with its price and stock
Customers 15 A person who signed up, from Bahrain, Saudi Arabia, the UK and more
Employees 8 A member of staff, with a ManagerID pointing to their manager
Orders 48 One order from January 2025 to June 2026, with its status
OrderItems 95 One product inside an order, with quantity, price and discount

That’s small enough to see every row, and big enough for real questions: monthly sales, best customers, products nobody has bought, who reports to whom.

How to load ShopDB

MySQL (Workbench): choose File > Open SQL Script, pick shopdb_mysql.sql, and click the lightning-bolt Execute button.

SQL Server (SSMS): choose File > Open > File, pick shopdb_sqlserver.sql, and press F5.

The last query in the script counts the rows it loaded. You should see 7, 21, 15, 8, 48 and 95. Then run your first query:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

No database installed yet? MySQL Explained for Beginners and Microsoft SQL Server Explained for Beginners show you how to install a free one.

Built-in surprises (on purpose)

Clean sample data teaches you less than messy data. ShopDB has a few traps built in, the same ones you’ll meet at work:

  • A customer with no orders and a product nobody has bought, for practising LEFT JOIN.
  • Missing values (NULL): customers with no phone or email, a customer with no country, online orders with no salesperson, and orders that haven’t shipped.
  • Ties: two products cost 899.00, two cost 99.00, and one customer placed two orders on the same day. Perfect for learning why ORDER BY needs a tie-breaker.
  • A manager hierarchy: Employees.ManagerID points back into the same table, ready for SELF JOIN. One employee even earns more than her manager.
  • Text to clean: an email in mixed case, another with leading spaces, and a product name containing a % sign for LIKE escaping.

10 questions to try

Try each one yourself. The linked tutorial shows the technique if you get stuck.

  1. List all products in the Accessories category, cheapest first. (WHERE, ORDER BY and LIMIT)
  2. Which customers have no phone number? (NULL in SQL)
  3. Find every customer whose email ends in .co.uk. (LIKE, IN and BETWEEN)
  4. Label each product as Budget, Mid-range or Premium by price. (CASE expression)
  5. How many orders does each status have? (GROUP BY)
  6. Show every order with the customer’s name. (SQL JOINs)
  7. List each employee next to their manager’s name. (SELF JOIN)
  8. Total sales per month, using Quantity * UnitPrice * (1 - Discount). (Date functions)
  9. Each customer’s latest order. (Top N per group)
  10. A running total of sales over time. (Running totals)

Learn SQL with ShopDB: get the free book

Simplify SQL for Absolute Beginners uses ShopDB in every chapter, with real results you can check row by row and practice questions with answers. The preview edition (Chapters 1–3) is free as a PDF and EPUB.

Related articles

Scroll to Top