The Sample Database

Every lesson queries the same two small data sets: a company (departments and employees) and a shop (customers, products, orders). The data is tiny on purpose, so you can check every result by eye.

Run PostgreSQL

The quickest way is Docker:

Terminal

docker run --name sqlcourse -e POSTGRES_PASSWORD=pw -p 5432:5432 -d postgres:16
docker exec -it sqlcourse psql -U postgres

Or install PostgreSQL locally and run psql. Any SQL client works.

Load the data

Paste this script into psql once.

SQL

CREATE TABLE departments (
    id   int PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE employees (
    id         int PRIMARY KEY,
    name       text NOT NULL,
    dept_id    int REFERENCES departments(id),
    manager_id int REFERENCES employees(id),
    salary     int NOT NULL,
    hired_on   date NOT NULL
);

CREATE TABLE customers (
    id        int PRIMARY KEY,
    name      text NOT NULL,
    city      text NOT NULL,
    joined_on date NOT NULL
);

CREATE TABLE products (
    id       int PRIMARY KEY,
    name     text NOT NULL,
    category text NOT NULL,
    price    numeric(8,2) NOT NULL
);

CREATE TABLE orders (
    id          int PRIMARY KEY,
    customer_id int NOT NULL REFERENCES customers(id),
    order_date  date NOT NULL,
    status      text NOT NULL
);

CREATE TABLE order_items (
    order_id   int REFERENCES orders(id),
    product_id int REFERENCES products(id),
    quantity   int NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO departments VALUES
    (1, 'Engineering'), (2, 'Sales'), (3, 'Marketing'), (4, 'HR'), (5, 'Legal');

INSERT INTO employees VALUES
    (1,  'Meera',  1,    NULL, 250000, '2015-03-01'),
    (2,  'Ravi',   1,    1,    180000, '2016-07-15'),
    (3,  'Kiran',  1,    2,    150000, '2018-01-10'),
    (4,  'Neha',   1,    2,    150000, '2019-09-01'),
    (5,  'Arjun',  1,    3,    110000, '2021-05-20'),
    (6,  'Divya',  2,    1,    160000, '2017-02-11'),
    (7,  'Sameer', 2,    6,     90000, '2020-06-30'),
    (8,  'Pooja',  2,    6,     85000, '2022-03-14'),
    (9,  'Vikram', 3,    1,    120000, '2018-11-05'),
    (10, 'Anita',  3,    9,     80000, '2023-01-09'),
    (11, 'Rohan',  4,    1,     95000, '2019-04-22'),
    (12, 'Tara',   NULL, 1,     70000, '2024-02-01');

INSERT INTO customers VALUES
    (1, 'Aarav',  'Bengaluru', '2024-01-05'),
    (2, 'Isha',   'Mumbai',    '2024-02-10'),
    (3, 'Karan',  'Delhi',     '2024-03-15'),
    (4, 'Lata',   'Bengaluru', '2024-04-01'),
    (5, 'Manish', 'Pune',      '2024-05-20'),
    (6, 'Nisha',  'Mumbai',    '2024-06-11');

INSERT INTO products VALUES
    (1, 'Keyboard', 'Accessories', 50.00),
    (2, 'Mouse',    'Accessories', 25.00),
    (3, 'Monitor',  'Displays',    200.00),
    (4, 'Laptop',   'Computers',   900.00),
    (5, 'Webcam',   'Accessories', 60.00),
    (6, 'Desk',     'Furniture',   350.00);

INSERT INTO orders VALUES
    (1, 1, '2024-06-01', 'delivered'),
    (2, 1, '2024-06-15', 'delivered'),
    (3, 2, '2024-06-03', 'delivered'),
    (4, 2, '2024-07-01', 'cancelled'),
    (5, 3, '2024-06-20', 'delivered'),
    (6, 1, '2024-07-05', 'shipped'),
    (7, 4, '2024-07-06', 'placed'),
    (8, 3, '2024-07-10', 'delivered'),
    (9, 5, '2024-07-12', 'placed');

INSERT INTO order_items VALUES
    (1, 4, 1), (1, 2, 2),
    (2, 1, 1),
    (3, 3, 2), (3, 5, 1),
    (4, 2, 3),
    (5, 4, 1),
    (6, 1, 1), (6, 2, 1),
    (7, 3, 1),
    (8, 5, 2), (8, 1, 1),
    (9, 2, 5);

What is in the data

The data has a few deliberate quirks that later lessons use:

Legal (department 5) has no employees.
Tara has no department (dept_id is NULL), and Meera has no manager (manager_id is NULL).
Kiran and Neha earn the same salary, which matters for ranking.
Nisha has never placed an order, and the Desk has never been ordered.
Orders have different statuses: delivered, shipped, placed, cancelled.

erDiagram
    departments ||--o{ employees : "has"
    employees ||--o{ employees : "manages"
    customers ||--o{ orders : "places"
    orders ||--|{ order_items : "contains"
    products ||--o{ order_items : "appears in"

First query

SQL

SELECT id, name, salary FROM employees ORDER BY id LIMIT 3;

Output

 id | name  | salary 
----+-------+--------
  1 | Meera | 250000
  2 | Ravi  | 180000
  3 | Kiran | 150000
(3 rows)

If you see three rows, the database is ready.