Meet the practice database and its tables

To learn queries you need data small enough to check every answer by eye. This track uses small invented datasets made for its lessons. This lesson introduces mart, a tiny online grocery that this lesson and the next one query, shows how to read its diagram, and teaches catalog queries: questions you ask a database about itself, which work on any database you are given.

How the practice data works

• Invented. Every customer, product and order is made up for this track. The names are drawn from many languages and regions of India, and they do not refer to any real person or business.
• Small. Each table has a handful of rows, so you can see why a query returns what it does.
• Built by the example itself. An example starts with the CREATE TABLE and INSERT statements for the tables it needs, then runs its queries. Every run in the browser, and every run of sqlite3 :memory:, starts from an empty database, so those first lines are what makes the data exist. Read them once; after that, the query at the end is what matters.
• Money in paise. Prices are whole numbers of paise in INTEGER columns, so totals stay exact.

Lessons that need other kinds of data later in the track build it the same way, in the example itself.

The mart tables

-- mart: a small, invented online grocery. Four tables, then every row of each.
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  city        TEXT    NOT NULL
);

CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL      -- 100 paise = 1 rupee
);

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status      TEXT    NOT NULL      -- 'placed', 'delivered' or 'cancelled'
);

CREATE TABLE order_items (
  order_id    INTEGER NOT NULL REFERENCES orders (order_id),
  product_id  INTEGER NOT NULL REFERENCES products (product_id),
  quantity    INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)  -- a product appears once per order
);

INSERT INTO customers (customer_id, name, city) VALUES
  (1, 'Asha Kulkarni', 'Pune'),
  (2, 'Vikram Nair',   'Kochi'),
  (3, 'Meera Rathore', 'Jaipur'),
  (4, 'Tenzin Dorjee', 'Gangtok');

INSERT INTO products (product_id, name, category, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 'Grains',    14999),
  (2, 'Toor dal 500 g',    'Pulses',     8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000),
  (4, 'Coconut oil 1 L',   'Oils',      21500),
  (5, 'Ragi flour 1 kg',   'Grains',     6800);

INSERT INTO orders (order_id, customer_id, status) VALUES
  (101, 1, 'delivered'),
  (102, 2, 'delivered'),
  (103, 1, 'placed'),
  (104, 3, 'cancelled');

INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (101, 1, 2), (101, 2, 1),
  (102, 4, 1),
  (103, 3, 2), (103, 5, 1),
  (104, 2, 3);

SELECT * FROM customers;
SELECT * FROM products;
SELECT * FROM orders;
SELECT * FROM order_items;

┌─────────────┬───────────────┬─────────┐
│ customer_id │     name      │  city   │
├─────────────┼───────────────┼─────────┤
│ 1           │ Asha Kulkarni │ Pune    │
│ 2           │ Vikram Nair   │ Kochi   │
│ 3           │ Meera Rathore │ Jaipur  │
│ 4           │ Tenzin Dorjee │ Gangtok │
└─────────────┴───────────────┴─────────┘
┌────────────┬───────────────────┬───────────┬─────────────┐
│ product_id │       name        │ category  │ price_paise │
├────────────┼───────────────────┼───────────┼─────────────┤
│ 1          │ Basmati rice 1 kg │ Grains    │ 14999       │
│ 2          │ Toor dal 500 g    │ Pulses    │ 8950        │
│ 3          │ Masala chai 250 g │ Beverages │ 12000       │
│ 4          │ Coconut oil 1 L   │ Oils      │ 21500       │
│ 5          │ Ragi flour 1 kg   │ Grains    │ 6800        │
└────────────┴───────────────────┴───────────┴─────────────┘
┌──────────┬─────────────┬───────────┐
│ order_id │ customer_id │  status   │
├──────────┼─────────────┼───────────┤
│ 101      │ 1           │ delivered │
│ 102      │ 2           │ delivered │
│ 103      │ 1           │ placed    │
│ 104      │ 3           │ cancelled │
└──────────┴─────────────┴───────────┘
┌──────────┬────────────┬──────────┐
│ order_id │ product_id │ quantity │
├──────────┼────────────┼──────────┤
│ 101      │ 1          │ 2        │
│ 101      │ 2          │ 1        │
│ 102      │ 4          │ 1        │
│ 103      │ 3          │ 2        │
│ 103      │ 5          │ 1        │
│ 104      │ 2          │ 3        │
└──────────┴────────────┴──────────┘

• customers: who buys. One row per customer.
• products: what the shop sells, with its category and price in paise.
• orders: one row per order: who placed it (customer_id) and where it stands (status).
• order_items: one row per product in an order: which order, which product and how many.

The tables point to each other by their keys. Follow order 103 by hand: its customer_id is 1, and customer 1 is Asha Kulkarni. In order_items, the rows with order_id 103 say it holds 2 of product 3 (masala chai) and 1 of product 5 (ragi flour). The module on joins teaches SQL to follow these links for you. Notice also that Tenzin Dorjee has no orders at all: “customers who never ordered” is a question later lessons answer.

Keys and the ER diagram

A primary key is a column, or a set of columns, whose value picks out exactly one row: no two rows of the table may share it. customer_id, product_id and order_id are the primary keys of their tables. The primary key of order_items is two columns together, (order_id, product_id): an order can hold many products and a product can be in many orders, but one product appears only once in each order.

A foreign key is a column whose values are the keys of rows in another table. orders.customer_id holds a customer_id from customers, and REFERENCES customers (customer_id) in the CREATE TABLE statement says so.

An entity-relationship (ER) diagram draws this: a box per table with its columns and types, PK and FK marks for the keys, and a line for each foreign key. The ends of a line say how many: two bars mean exactly one, and a circle with a crow’s foot means zero or many.

Read the top line from both ends: each order belongs to exactly one customer, and a customer has zero or many orders. This is a one-to-many relationship. Orders and products are different: an order has many products, and a product appears in many orders. A table in between, here order_items, turns that many-to-many relationship into two one-to-many links.

Ask the database about itself

Every database keeps a description of its own tables, called its catalog, and you can query it like any other table. That is how you find your way around a database somebody hands you.

SQLite: the sqlite_schema table

-- The mart tables again, without rows: catalog queries only need the definitions.
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  city        TEXT NOT NULL
);
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL,
                       category TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
                     customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
                     status TEXT NOT NULL);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders (order_id),
                          product_id INTEGER NOT NULL REFERENCES products (product_id),
                          quantity INTEGER NOT NULL,
                          PRIMARY KEY (order_id, product_id));

-- One row for every table, index, view and trigger in the database.
SELECT type, name, tbl_name FROM sqlite_schema;

-- The statement that created a table, as SQLite stored it.
SELECT sql FROM sqlite_schema WHERE name = 'customers';

┌───────┬────────────────────────────────┬─────────────┐
│ type  │              name              │  tbl_name   │
├───────┼────────────────────────────────┼─────────────┤
│ table │ customers                      │ customers   │
│ table │ products                       │ products    │
│ table │ orders                         │ orders      │
│ table │ order_items                    │ order_items │
│ index │ sqlite_autoindex_order_items_1 │ order_items │
└───────┴────────────────────────────────┴─────────────┘
┌────────────────────────────────────┐
│                sql                 │
├────────────────────────────────────┤
│ CREATE TABLE customers (           │
│   customer_id INTEGER PRIMARY KEY, │
│   name        TEXT NOT NULL,       │
│   city        TEXT NOT NULL        │
│ )                                  │
└────────────────────────────────────┘

sqlite_schema has one row for each table, index, view and trigger. type says which kind it is, name is its name, and tbl_name the table it belongs to. Two rows are worth a closer look:

• The index row. SQLite keeps the two-column primary key of order_items in an index it creates itself, called sqlite_autoindex_order_items_1. The other tables have none, because a single INTEGER PRIMARY KEY column is the row’s own number in SQLite (its rowid) and needs no separate index.
• The sql column holds the statement that created each object, a copy of what you wrote with only small details tidied, such as the capitals of CREATE TABLE. Line breaks are kept, as the result shows. It is what the .schema command of the sqlite3 program prints. For the automatic index it is empty (NULL).

Older programs and answers online use the name sqlite_master; it is the same table.

Columns and keys: pragma functions

Many of SQLite’s PRAGMA statements, the ones that only report something, have twins that you can use in FROM like a table, named with a pragma_ prefix:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL,
                       category TEXT NOT NULL, price_paise INTEGER NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
                     customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
                     status TEXT NOT NULL);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders (order_id),
                          product_id INTEGER NOT NULL REFERENCES products (product_id),
                          quantity INTEGER NOT NULL,
                          PRIMARY KEY (order_id, product_id));

-- Every column of orders: its position, name, declared type, whether it is
-- NOT NULL, its default value and its place in the primary key (0 = not in it).
SELECT * FROM pragma_table_info('orders');

-- The foreign keys of order_items. "table", "from" and "to" are words SQL
-- uses itself, so these column names need double quotes.
SELECT "from" AS column_name, "table" AS refers_to, "to" AS key_column
FROM pragma_foreign_key_list('order_items');

┌─────┬─────────────┬─────────┬─────────┬────────────┬────┐
│ cid │    name     │  type   │ notnull │ dflt_value │ pk │
├─────┼─────────────┼─────────┼─────────┼────────────┼────┤
│ 0   │ order_id    │ INTEGER │ 0       │            │ 1  │
│ 1   │ customer_id │ INTEGER │ 1       │            │ 0  │
│ 2   │ status      │ TEXT    │ 1       │            │ 0  │
└─────┴─────────────┴─────────┴─────────┴────────────┴────┘
┌─────────────┬───────────┬────────────┐
│ column_name │ refers_to │ key_column │
├─────────────┼───────────┼────────────┤
│ product_id  │ products  │ product_id │
│ order_id    │ orders    │ order_id   │
└─────────────┴───────────┴────────────┘

pragma_table_info('orders') lists the columns: their position (cid), name, declared type, whether they are NOT NULL (1) or not (0), their default value and their place in the primary key (pk, 0 when they are not part of it). One value surprises people: order_id shows notnull 0, because it was not declared NOT NULL. It still never holds NULL: an INTEGER PRIMARY KEY is the row’s own number, and inserting NULL into it makes SQLite fill in a new number instead, usually one more than the largest already in the table.

pragma_foreign_key_list('order_items') lists the table’s foreign keys: the column, the table it refers to and the key column there. In the sqlite3 program, .tables and .schema orders show much the same.

Two common mistakes

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL);

-- The table's name is text here, so it needs single quotes.
SELECT * FROM pragma_table_info(orders);

-- A PostgreSQL and MySQL habit: SQLite has no information_schema.
SELECT table_name FROM information_schema.tables;

Parse error near line 4: no such column: orders
Parse error near line 7: no such table: information_schema.tables

• pragma_table_info(orders) without quotes reads orders as the name of a column, which does not exist here. The function wants the table’s name as text: 'orders'.
• information_schema is how PostgreSQL and MySQL describe themselves, and answers online often use it. SQLite does not have it, so the query stops with “no such table”.

PostgreSQL and MySQL: information_schema

The SQL standard defines information_schema, a set of views that describe the tables and columns of a database, and both PostgreSQL and MySQL have it. Here is how to list the tables, and then the columns of orders, in each of the three engines:

• SQLite: SELECT name FROM sqlite_schema WHERE type = 'table'; and SELECT * FROM pragma_table_info('orders');. In the sqlite3 program: .tables and .schema orders.
• PostgreSQL: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; and SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders';. Tables you create without naming a schema go into the schema called public. In psql: \dt and \d orders.
• MySQL: the same two queries, but table_schema is the name of the database, such as WHERE table_schema = 'practice'. MySQL also has statements of its own for this: SHOW TABLES; and DESCRIBE orders;.

PostgreSQL also has system catalogs of its own, with details that information_schema leaves out; the standard views are the part that the two server engines have in common.

Key takeaways

• The practice data is invented and small, and every example creates the tables it uses, because each run starts from an empty database.
• A primary key picks out one row; a foreign key holds the key of a row in another table.
• In an ER diagram, two bars mean exactly one and a crow’s foot means many. A table in between turns a many-to-many relationship into two one-to-many links.
• sqlite_schema and pragma_table_info('…') describe a SQLite database; PostgreSQL and MySQL use information_schema, plus \d in psql and SHOW TABLES or DESCRIBE in MySQL.