Please note #1: use the database tables you created when you were getting familiar to MySQL. Use existing tables and data. No need to enter your own data before doing this assignment.

Please note #2: if the answer is a SELECT statement, print the statement and the output; if the answer is an INSERT, UPDATE, or DETELE statement, print the statement and, the data before and after the statement is run.

Copy and paste your answers into a file, a TXT file is preferred, with fixed-width font such as Courier New.

1. Print a report that has

• acctnum from orders,
• ordernum from orders,
• salesfname from salesrep,
• saleslname from salesrep;

You need print ALL orders on the report, whether there was a salesrep or NOT! So use a left outer join or a right outer join.

2. Find the orders that have both Bike28B and ridinggloves. (Hint: a. do not include those orders that have only either one of the two items; b. use a subquery or self join. We talked about both in class. The result: order #8 is the only order that meets the requirement. Dont use ORDERNUM=8 in your WHERE clause as it is already told here.)

3. Find order_detail entries sold by sales rep Mary Halvorson. (Do not just plug in 3 as the sales number in your query. Do a join instead, or a subquery.)

4. Find all line items that are higher in price than all those sold by Mary Halvorson.

5. Do a right outer join on orders to order_detail.

6. Do a left outer join on orders to order_detail.

7. Explain the differences between the two reports in 5) and 6)

8. Create a view on orders that shows only three (any) columns from orders table; then create a select * from query based on that view and print results.

9. Insert a new order #25 into the orders table, make up some data for the columns; print all data of the orders table before and after the insertion.

10. Delete orders #18 and higher from the orders table; print all data of the orders table before and after the deletion.

11. Modify order #3 of the orders table. (Modify any of the attributes of order #3, such as order date, customer number, etc.); print all data of the orders table before and after the modification.

12. Select all columns from orders table, and then using a join, get the customer last name from customer table, and have it print in alphabetical order by customer's last name.

13. Select all columns of order_detail and product tables, joined by product number. Have it sorted by order number then by line number.

14. Your supervisor wants to know the total sales (qty*unitprice) grouped by salesrep. The report should show salesrep name, and the total sales. (Hint: You will need to join salesrep table to orders table and to order_detail table. You will need Group By clause.)