Please put all the queries, stored procedures, and triggers in an SQL script file lab1.sql and upload it CSNS at the end of the lab. Note that file uploading will be disabled automatically after 8PM, so please turn in your work on time.

1. (15pt) Complete the following queries using the sample database Store (store-create.sql, store-drop.sql). Feel free to insert more records into the tables to test your queries, but do not use any views.

• List the CPU products whose prices are below $100.
• List all the products whose prices are higher than the average price of their category.
• List the orders that were placed ten days ago but were not shipped yet (i.e. date_shipped is null).
• List the names of the customers who have not bought anything since 2004.
• List the top 3 customer last year in terms of total dollar amount spent.

2. (10pt)  Create a table called restock. This table lists the products that need to be restocked, i.e. the quantity of the product is below a certain threshold, e.g. 5. This table has three fields: the id of the product, the quantity of the product, and a timestamp.

• If the quantity of a product drops below 5, checks if the product is listed in the restock table.
• If the product is not in the restock table, insert the product id, quantity, and the current time into the restock table.
• If the product is already listed in the restock table, update the quantity and the timestamp field.
• If the quantity of a product goes above 5 and the product is listed in the restock table, remove it from the restock table.