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
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
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.
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.
Press Run (or Ctrl + Enter) to see the result here.
Things to know
- Each run starts fresh. Your
INSERT,UPDATE,DELETEandDROP TABLEstatements 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 withdate()andstrftime(). 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.
customersandordersappear in bothbookstoreandshop, with different columns. Always check whichdban example uses. - Your own tables work too. Use
CREATE TABLEinside any example, or adb-less<TryIt>for a blank database. - Everything you need to explore is built in.
SELECT name, sql FROM sqlite_masterlists the tables with their definitions.
Dialect differences
The databases are plain SQL, but some of what you see depends on SQLite:
- Types:
TEXT,INTEGERandREALare SQLite storage classes. In PostgreSQL you would usevarchar/text,integer/bigintandnumericordouble precision, and realdateortimestampcolumns for the dates. - Prices: a production schema stores money in
numeric(10,2)or integer cents.REALis floating point, so sums can show tiny rounding noise. - Auto-numbering:
INTEGER PRIMARY KEYmakes SQLite assign ids. Other systems useGENERATED ... AS IDENTITY,AUTO_INCREMENTorIDENTITY(1,1); see auto-increment across dialects. - Catalog:
sqlite_masteris SQLite's own list of objects. Other systems useinformation_schemaor their own catalog views.
-- PostgreSQL, MySQL and SQL Server SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; -- PostgreSQL; use your database name in MySQL