Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Dialects and Concepts

The SQL sample databases

Tables, columns, row counts and relationships of the four sample databases used by the live SQL examples: bookstore, shop, company and school.

Every runnable SQL example on this site starts from one of four small sample databases. They are loaded into an in-memory SQLite database (sql.js) each time you press Run, so you can change, delete or drop anything without hurting the next run. The db attribute of a <TryIt> block chooses which one is loaded: db="bookstore", db="shop", db="company" or db="school". Without it, you start with an empty database.

This page describes what is in each one, how the tables relate, and a few things to know before you experiment. All data is made up for this site.

Overview

Database Tables Good for
bookstore authors, books, customers, orders First queries, joins, NULL handling (one author has no birth year)
shop categories, products, customers, orders, order_items Aggregates, grouping, many-to-many style joins, dates, window functions
company departments, employees, projects, assignments Self joins, recursive CTEs, salaries, composite keys
school terms, teachers, courses, students, enrollments Grades, ranking, percentiles, composite keys, missing values

Try it

SQL List the tables and row counts of the shop database ยท database: shop

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

SQL Peek at the bookstore, one table at a time ยท database: bookstore

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

SQL The company hierarchy: who reports to whom ยท database: company

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

The bookstore database

A small independent bookstore. It is the smallest database, so its whole content fits on one screen.

Table Rows Columns
authors 5 id, name, country, born (may be NULL)
books 10 id, title, author_id, genre, published, price, stock
customers 5 id, name, city, joined
orders 10 id, customer_id, book_id, quantity, ordered_on

Relationships: an author has many books (books.author_id), and orders links a customer to a book. Each order row holds one book, so a customer who buys two titles in one visit has two order rows. Two books have stock = 0, and one author has born = NULL, which makes this database handy for NULL and outer join examples.

The shop database

An online shop with realistic volumes for grouping and reporting.

Table Rows Columns
categories 6 id, name (unique)
products 26 id, name, category_id, price, stock, added_on
customers 40 id, first_name, last_name, email (unique), city, country, signed_up
orders 120 id, customer_id, ordered_at, status, shipping
order_items 292 id, order_id, product_id, quantity, unit_price

An order has many items (order_items.order_id) and each item points to a product (order_items.product_id), so products and orders have a many-to-many relationship through order_items. The status column is limited by a CHECK constraint to pending, shipped, delivered or cancelled, and price must not be negative. unit_price is stored on the item, because the price a customer paid can differ from today's product price.

SQL Revenue per order status ยท database: shop

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

The company database

A company with departments, a management chain and projects.

Table Rows Columns
departments 6 id, name (unique), office
employees 36 id, first_name, last_name, title, department_id, manager_id, hire_date, salary, email
projects 7 id, name, department_id, budget, starts_on, ends_on
assignments 39 employee_id, project_id, role, hours_per_week

employees.manager_id points back at employees.id, and it is NULL for the top of the hierarchy. That makes the table the standard playground for self joins and recursive CTEs. assignments is a link table with a composite primary key (employee_id, project_id): one employee can work on several projects and a project has several people. projects.budget and ends_on can be NULL.

The school database

A school with courses, students and grades.

Table Rows Columns
terms 3 id, name, starts_on, ends_on
teachers 7 id, name, subject, email
courses 10 id, code (unique), title, teacher_id, credits, capacity
students 36 id, first_name, last_name, birth_date, enrolled_on, homeroom
enrollments 299 student_id, course_id, term_id, grade

enrollments is a link table with the composite key (student_id, course_id, term_id), so a student can take the same course again in another term. grade must be between 0 and 100 and can be NULL when no grade has been given yet. courses.teacher_id and students.homeroom can also be NULL.

SQL Average grade per course ยท database: school

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

Things to know

  • Each run starts fresh. Your INSERT, UPDATE, DELETE and DROP TABLE statements last only until the next run. Nothing is saved.
  • Dates are text. Dates are stored as ISO 8601 strings such as 2024-02-14, as SQLite does it. They sort correctly and work with date() and strftime(). See date formats.
  • Foreign keys are declared but not enforced. SQLite checks them only after PRAGMA foreign_keys = ON;, which these examples do not run unless they say so. Other databases enforce them always.
  • Identical table names exist in several databases. customers and orders appear in both bookstore and shop, with different columns. Always check which db an example uses.
  • Your own tables work too. Use CREATE TABLE inside any example, or a db-less <TryIt> for a blank database.
  • Everything you need to explore is built in. SELECT name, sql FROM sqlite_master lists the tables with their definitions.

Dialect differences

The databases are plain SQL, but some of what you see depends on SQLite:

  • Types: TEXT, INTEGER and REAL are SQLite storage classes. In PostgreSQL you would use varchar/text, integer/bigint and numeric or double precision, and real date or timestamp columns for the dates.
  • Prices: a production schema stores money in numeric(10,2) or integer cents. REAL is floating point, so sums can show tiny rounding noise.
  • Auto-numbering: INTEGER PRIMARY KEY makes SQLite assign ids. Other systems use GENERATED ... AS IDENTITY, AUTO_INCREMENT or IDENTITY(1,1); see auto-increment across dialects.
  • Catalog: sqlite_master is SQLite's own list of objects. Other systems use information_schema or their own catalog views.
SQL
-- PostgreSQL, MySQL and SQL Server
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';  -- PostgreSQL; use your database name in MySQL