The Sample Database Used in This Path

Concept

Every note in this path queries the same two small schemas, so you never have to re read a table definition mid example. Create them once in a scratch database and run the examples as you read.

The company schema

• departments - one row per department
• employees - one row per employee, with a department and a manager
• projects - one row per project, owned by a department
• assignments - which employee works on which project, and for how many hours

The shop schema

• customers, orders, products, order_items

Syntax

Written for MySQL 8, the dialect this site runs on. Dialect differences are called out in each note where they matter.

CREATE DATABASE IF NOT EXISTS sql_path;
USE sql_path;

CREATE TABLE departments (
    id       INT PRIMARY KEY,
    name     VARCHAR(60) NOT NULL,
    location VARCHAR(60)
);

CREATE TABLE employees (
    id         INT PRIMARY KEY,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    email      VARCHAR(120) UNIQUE,
    dept_id    INT          NULL,
    manager_id INT          NULL,
    hire_date  DATE         NOT NULL,
    salary     DECIMAL(10,2) NOT NULL,
    status     VARCHAR(10)  NOT NULL DEFAULT 'active',
    CONSTRAINT fk_emp_dept    FOREIGN KEY (dept_id)    REFERENCES departments(id),
    CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES employees(id)
);

CREATE TABLE projects (
    id         INT PRIMARY KEY,
    name       VARCHAR(80) NOT NULL,
    dept_id    INT NULL,
    budget     DECIMAL(12,2),
    start_date DATE,
    end_date   DATE NULL,
    CONSTRAINT fk_proj_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
);

CREATE TABLE assignments (
    employee_id INT,
    project_id  INT,
    hours       DECIMAL(6,1) NOT NULL DEFAULT 0,
    PRIMARY KEY (employee_id, project_id),
    CONSTRAINT fk_asg_emp  FOREIGN KEY (employee_id) REFERENCES employees(id),
    CONSTRAINT fk_asg_proj FOREIGN KEY (project_id)  REFERENCES projects(id)
);

The shop tables

CREATE TABLE customers (
    id          INT PRIMARY KEY,
    name        VARCHAR(80) NOT NULL,
    city        VARCHAR(60),
    country     VARCHAR(60),
    signup_date DATE NOT NULL
);

CREATE TABLE products (
    id       INT PRIMARY KEY,
    name     VARCHAR(80) NOT NULL,
    category VARCHAR(40) NOT NULL,
    price    DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    status      VARCHAR(12) NOT NULL,
    total       DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_ord_cust FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_oi_order   FOREIGN KEY (order_id)   REFERENCES orders(id),
    CONSTRAINT fk_oi_product FOREIGN KEY (product_id) REFERENCES products(id)
);

Example

Enough sample rows to make every later example return something interesting:

INSERT INTO departments (id, name, location) VALUES
(10, 'Engineering', 'Bengaluru'),
(20, 'Sales',       'Mumbai'),
(30, 'Finance',     'Pune'),
(40, 'Research',    'Hyderabad');   -- deliberately has no employees

INSERT INTO employees
(id, first_name, last_name, email, dept_id, manager_id, hire_date, salary, status) VALUES
(1, 'Asha',  'Nair',    'asha@example.com',   10, NULL, '2018-03-01',  145000.00, 'active'),
(2, 'Ravi',  'Kumar',   'ravi@example.com',   10, 1,    '2019-07-15',   92000.00, 'active'),
(3, 'Meera', 'Iyer',    'meera@example.com',  10, 1,    '2021-01-04',   78000.00, 'active'),
(4, 'Karan', 'Shah',    'karan@example.com',  20, 1,    '2017-11-20',  110000.00, 'active'),
(5, 'Priya', 'Menon',   'priya@example.com',  20, 4,    '2022-06-01',   64000.00, 'active'),
(6, 'Arjun', 'Rao',     'arjun@example.com',  30, 1,    '2020-09-10',   88000.00, 'active'),
(7, 'Divya', 'Bose',    'divya@example.com',  30, 6,    '2023-02-27',   59000.00, 'inactive'),
(8, 'Nikhil','Verma',   NULL,                 NULL, 1,    '2024-04-18',   71000.00, 'active');

INSERT INTO projects (id, name, dept_id, budget, start_date, end_date) VALUES
(100, 'Billing Rewrite', 10, 900000.00, '2023-01-09', NULL),
(101, 'Mobile App',      10, 450000.00, '2023-06-01', '2024-05-31'),
(102, 'Lead Tracker',    20, 220000.00, '2024-02-01', NULL),
(103, 'Audit Tooling',   30, 130000.00, '2024-08-12', NULL);

INSERT INTO assignments (employee_id, project_id, hours) VALUES
(1, 100, 120.0), (2, 100, 340.5), (3, 100, 210.0),
(2, 101, 80.0),  (3, 101, 190.5),
(4, 102, 150.0), (5, 102, 275.0),
(6, 103, 95.5);

INSERT INTO customers (id, name, city, country, signup_date) VALUES
(1, 'Vector Labs',   'Pune',      'India',  '2022-01-15'),
(2, 'Northwind Ltd', 'London',    'UK',     '2022-08-03'),
(3, 'Sunrise Foods', 'Mumbai',    'India',  '2023-03-22'),
(4, 'Delta Systems', 'Singapore', 'Singapore', '2024-01-09');

INSERT INTO products (id, name, category, price) VALUES
(1, 'Standard Licence', 'software', 4999.00),
(2, 'Premium Licence',  'software', 12999.00),
(3, 'Support Pack',     'service',  2500.00),
(4, 'Training Day',     'service',  8000.00);

INSERT INTO orders (id, customer_id, order_date, status, total) VALUES
(1000, 1, '2024-01-12', 'shipped',   17998.00),
(1001, 1, '2024-03-04', 'shipped',    4999.00),
(1002, 2, '2024-03-19', 'cancelled', 12999.00),
(1003, 3, '2024-05-27', 'shipped',   10500.00),
(1004, 1, '2024-09-02', 'pending',    8000.00);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1000, 1, 2, 4999.00), (1000, 3, 1, 2500.00),
(1001, 1, 1, 4999.00),
(1002, 2, 1, 12999.00),
(1003, 2, 1, 12999.00), (1003, 3, 1, 2500.00),
(1004, 4, 1, 8000.00);

Explanation

The data is deliberately awkward in useful ways, so the examples later in the path have something to show:

• Department 40 has no employees - that is what makes LEFT JOIN and NOT EXISTS examples meaningful.
• Nikhil has no department and no email - the NULL notes need him.
• Asha has no manager - the self join and recursive CTE notes need that root row.
• Customer 4 has no orders, and one order is cancelled - filtering and anti join examples need both.

Important rules

• Create this in a scratch database, never in a production one.
• Run the statements in the order shown; the foreign keys require parents before children.
• If you are on PostgreSQL, drop the CREATE DATABASE ... USE pair and connect to a database instead; USE is not PostgreSQL syntax.

Common mistakes

• Loading the child tables first and hitting a foreign key error.
• Copying the MySQL USE statement into a dialect that does not have it.

Best practices

• Keep this scratch schema around while you work through the path; every note assumes it exists.
• Re create it from scratch whenever an experiment leaves the data in a strange state.

Practice

1. Create both schemas and confirm SELECT COUNT(*) FROM employees; returns 8.
2. Which employee row would fail if dept_id were declared NOT NULL?
3. Which department would disappear from a report built with an inner join?