Archive, then delete

Never delete what you may need later. Copy it somewhere safe first.

What to build

Statements that move delivered orders into an archive table.

Requirements

• Copy every delivered order into archive, keeping its id.
• Delete the delivered orders from orders.
• Show every column of archive, then every column of orders, each in order of id.

Tables

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

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  status TEXT NOT NULL
);
CREATE TABLE archive (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  status TEXT NOT NULL
);

Output

Two results, each with columns id, customer and status: first archive, then orders. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

The archive may already hold older orders, with ids different from the orders being moved.

Example

When a test starts with:

INSERT INTO orders VALUES (1, 'Rahim', 'delivered'), (2, 'Nadia', 'pending'), (3, 'Karim', 'delivered');

your SQL should print:

id  customer  status
--  --------  ---------
1   Rahim     delivered
3   Karim     delivered
(2 rows)

id  customer  status
--  --------  -------
2   Nadia     pending
(1 row)

Why it works

Copying before deleting means no order is ever lost, even if the DELETE goes wrong. Using the same WHERE in both statements moves exactly the same rows.

Examples

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  status TEXT NOT NULL
);
CREATE TABLE archive (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  status TEXT NOT NULL
);
INSERT INTO orders VALUES (1, 'Rahim', 'delivered'), (2, 'Nadia', 'pending'), (3, 'Karim', 'delivered');

id  customer  status
--  --------  ---------
1   Rahim     delivered
3   Karim     delivered
(2 rows)

id  customer  status
--  --------  -------
2   Nadia     pending
(1 row)

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  status TEXT NOT NULL
);
CREATE TABLE archive (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  status TEXT NOT NULL
);
INSERT INTO orders VALUES (1, 'A', 'pending');

id  customer  status
--  --------  ------
(0 rows)

id  customer  status
--  --------  -------
1   A         pending
(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

Solution

INSERT INTO archive (id, customer, status)
SELECT id, customer, status FROM orders WHERE status = 'delivered';
DELETE FROM orders WHERE status = 'delivered';
SELECT * FROM archive ORDER BY id;
SELECT * FROM orders ORDER BY id;

Lesson: Changing and deleting rows →