Who ordered what

Show every order with the name of the customer who placed it.

What to build

A join that shows each order with its customer's name.

Requirements

• Join orders to customers on the customer's id.
• Name the order's id order_id, because both tables have an id.
• Sort by the order's id.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL,
  amount INTEGER NOT NULL
);

Output

Columns order_id, name, item and amount, in order of order_id. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Every order belongs to an existing customer.

Example

When a test starts with:

INSERT INTO customers VALUES
  (1, 'Rahim', 'Dhaka'),
  (2, 'Nadia', 'Sylhet');
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (101, 1, 'Pen', 30),
  (102, 2, 'Bag', 900),
  (103, 1, 'Notebook', 120);

your SQL should print:

order_id  name   item      amount
--------  -----  --------  ------
101       Rahim  Pen       30
102       Nadia  Bag       900
103       Rahim  Notebook  120
(3 rows)

Why it works

The join pairs each order with the one customer whose id matches, so each order appears once with its customer's name.

Examples

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);
INSERT INTO customers VALUES
  (1, 'Rahim', 'Dhaka'),
  (2, 'Nadia', 'Sylhet');
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (101, 1, 'Pen', 30),
  (102, 2, 'Bag', 900),
  (103, 1, 'Notebook', 120);

order_id  name   item      amount
--------  -----  --------  ------
101       Rahim  Pen       30
102       Nadia  Bag       900
103       Rahim  Notebook  120
(3 rows)

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);
INSERT INTO customers VALUES
  (1, 'Karim', 'Khulna'),
  (2, 'Tania', 'Dhaka'),
  (3, 'Sumon', NULL);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (101, 3, 'Glue', 40),
  (102, 2, 'Marker', 60);

order_id  name   item    amount
--------  -----  ------  ------
101       Sumon  Glue    40
102       Tania  Marker  60
(2 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT o.id AS order_id, c.name, o.item, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

Lesson: Joining tables →