Design the products table – Databases Practice Problem

Design a table so that rows copied into it get sensible values.

What to build

A products table that is filled from a supplier's list and then shown.

Requirements

Create products with id INTEGER PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL, and stock INTEGER with a default of 0.
Copy the supplier's list in with the INSERT given in the starter.
Show every column of products, in order of id.

Tables

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

CREATE TABLE supplier_list (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER NOT NULL
);

Output

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

Constraints

The supplier's list may be empty. It has a name and a price for every product, and no stock.

Example

When a test starts with:

INSERT INTO supplier_list VALUES (1, 'Pen', 10), (2, 'Notebook', 60);

your SQL should print:

id  name      price  stock
--  --------  -----  -----
1   Pen       10     0
2   Notebook  60     0
(2 rows)

Why it works

The INSERT names only name and price, so stock takes its DEFAULT and id is numbered automatically in the order the rows are copied.

Examples

Example 1 · Input

CREATE TABLE supplier_list (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER NOT NULL
);
INSERT INTO supplier_list VALUES (1, 'Pen', 10), (2, 'Notebook', 60);

Expected output

id  name      price  stock
--  --------  -----  -----
1   Pen       10     0
2   Notebook  60     0
(2 rows)

Example 2 · Input

CREATE TABLE supplier_list (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER NOT NULL
);
INSERT INTO supplier_list VALUES (1, 'Bag', 900);

Expected output

id  name  price  stock
--  ----  -----  -----
1   Bag   900    0
(1 row)

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

Hint 1

`stock INTEGER DEFAULT 0` gives new products a stock of 0 when none is given.

Hint 2

Keep the INSERT and the SELECT from the starter; only the CREATE TABLE needs work.

Solution

Show one correct solution (try it yourself first)

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER NOT NULL,
  stock INTEGER DEFAULT 0
);

INSERT INTO products (name, price)
SELECT name, price FROM supplier_list ORDER BY id;

SELECT * FROM products ORDER BY id;

Lesson: Creating tables and adding rows →