8. Calculate each suppliers total sales quantity and get the sales persons name if the sales person supplies parts more than 1000 units in total.

9. Get job numbers for jobs whose city is first in the alphabetical list of the job cities.

10. Increase the status values of suppliers by 5 who are located in Paris. Please hand in the modified table and the query.

11. Change the name to Hammer of parts that are Red and located in London and whose name was Screw. Please hand in the modified table and the query.

12. Delete all jobs in Rome and all corresponding part shipments. (Please use 2 queries) Please hand in the modified table and the query.

13. Smith moved to Adams location. Please update Smiths city but do not use the city name directly.

14. Please create a view table supplier_shipment that shows each supplier and its total shipment quantity. (if the system do not allow you to create a view table then skip this question)

15. Please try to increase Smiths total shipment by 100 in the view table you created in question 14. Then discuss what happens when you try to update the view table. (if question 14 is successful)

16. Please create a base table supplier_shipment that shows each supplier and its total shipments from S and SPJ tables.

17. Please repeat question 15 on the base table supplier_shipment and discuss the result. Note: You might need following setting before run your update SQL SET SQL_SAFE_UPDATES = 0;

numjname C Sorter Paris 2 Punch Rome 3 Reader Athens Console Athens Collator London Terminal Oslo Tape London 6 p num pname color weight city Nut Red Bolt Gree Screw Blue p4Screw Red Cam Blue p6 Cog Red 12 London 17 Paris 17 Rome 14 London 12 Paris 19 London p5 num name status C 20 London 10 Paris 30 Paris 20 London 30 Athens Smith 82 83 s4 ones Blake Clark Adams