Restock day

The shop makes two changes. Products that are sold out (stock is 0) are removed. Every other product with fewer than 10 in stock costs 5 taka more. Then show the whole table.

What to build

A set of changes to the products table, followed by the table itself.

Requirements

• Delete the products whose stock is 0.
• Add 5 to the price of products with fewer than 10 in stock.
• Show the whole table, sorted by id.

Tables

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

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

Output

Every column of the changed table (id, name, price, 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

Prices and stock are whole numbers.

Example

When a test starts with:

INSERT INTO products VALUES
  (1, 'Pen', 10, 120),
  (2, 'Notebook', 60, 4),
  (3, 'Bag', 900, 0),
  (4, 'Ruler', 25, 9);

your SQL should print:

id  name      price  stock
--  --------  -----  -----
1   Pen       10     120
2   Notebook  65     4
4   Ruler     30     9
(3 rows)

Why it works

Each statement uses its own WHERE to touch only the right rows. The order does not matter, because a sold-out product is removed either way.

Examples

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER NOT NULL,
  stock INTEGER NOT NULL
);
INSERT INTO products VALUES
  (1, 'Pen', 10, 120),
  (2, 'Notebook', 60, 4),
  (3, 'Bag', 900, 0),
  (4, 'Ruler', 25, 9);

id  name      price  stock
--  --------  -----  -----
1   Pen       10     120
2   Notebook  65     4
4   Ruler     30     9
(3 rows)

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER NOT NULL,
  stock INTEGER NOT NULL
);
INSERT INTO products VALUES
  (1, 'Eraser', 5, 0),
  (2, 'Marker', 40, 10),
  (3, 'Glue', 30, 1);

id  name    price  stock
--  ------  -----  -----
2   Marker  40     10
3   Glue    35     1
(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

DELETE FROM products WHERE stock = 0;
UPDATE products SET price = price + 5 WHERE stock < 10;
SELECT * FROM products ORDER BY id;

Lesson: Changing and deleting rows →