Views: A Saved Query in Python

Order totals take a GROUP BY over order_items, and many queries need them: big orders, receipts, stats. Instead of copying that SELECT into each one, you can save it in the database under a name. That is a view: CREATE VIEW name AS, followed by a SELECT.

After that, a query reads the view like a table: its name goes after FROM, and its columns are used by name. A view stores the query, not the rows. Each time you read it, SQLite runs its SELECT on the tables as they are right now, so it is never out of date.

query = (
    "CREATE VIEW paid_orders AS "
    "SELECT id, customer_id, ordered "
    "FROM orders "
    "WHERE status = 'paid' "
)

first_customer = (
    "SELECT id, ordered "
    "FROM paid_orders "
    "WHERE customer_id = 1 "
)

Your view is order_totals: one row per order, with its order_id and its total. The check runs query and shows big_orders. Then it adds one more line to order 2, worth 15 (order 2 was worth 29), and shows big_orders again, without creating the view again:

order_id | total
3 | 41
5 | 73
7 | 62
(3 rows)
After INSERT INTO order_items VALUES (2, 5, 1, 15):
order_id | total
2 | 44
3 | 41
5 | 73
7 | 62
(4 rows)

Your task: in query, create the view order_totals: order_id and the SUM of quantity * unit_price named total, from order_items, grouped by order_id. In big_orders, select order_id, total from the view and keep the orders worth 40 or more.

Assignment
Follow the Coding Tutorial to save the order totals as a view and query it like a table.

Hint
Each clause is its own string on its own line, ending with a space. AS total gives the sum a column name, so the WHERE in big_orders compares total, not the SUM.