Answer Happy

create following tables by using MySQL and answer the following questions. Submit a table creation script and data insert script. Then prepare the report as follows: question number, question, SQL statement and the result table for each question. 400 82]BBBBBB numnum numaty s2 p3 12 200 s2 j3 200 s2 p3 lj4 500 s2 p3 lis 600 s2 j6 s2 p3 j7 800 s2 ps li2 100 s3 p3 j1 200 Is 3 12 500 s4 j3 300 94 p6 li7 300 s5 pl lj4 100 ss p2 12 200 Iss p2 lj4 100 ss p3 li4 200 ss p4 li4 800 ss lj4 400 s5 ps lis 500 Iss j7 100 Iss p6 12 200 s5 po 14 500 J i num iname city ji Sorter Paris Punch Rome li3 Reader Athens li4 Console Athens Collator London 16 Terminal Oslo |j7 Tape London n2 |||E. 2 alam la al al 21% P B.num boame color weight city pl Nut Red 12 London p2 Bolt Green 17 Paris p3 Screw Blue 17 Rome Screw Red 14 London Cam Blue 12 Paris Cog Red 19 London Note: Qty, status, and weight are numbers. p4 ps po Exercise S sunums name status city s1 Smith 20 London s2 Jones 10 Paris s3 Blake 30 Paris S4 Clark 20 London Adams 30 Athens जानाजाधान 1. Get supplier names and numbers for all suppliers who supplied part P3 and whose name begins with letter A. 2. Get supplier names for suppliers who supplied for job J2. (Use a nested query) 3. Get supplier names for suppliers who supplied parts for jobs only in Athens. (Use a nested query) 4. Get part names for parts that are not supplied for job 13. (Use a nested query) SPJ s num num numaty s1 pl lji 1200 s1 pl li4 700 s2 p3 lji 400 5. Which city has the most suppliers? (Use a nested query) 2

6. Calculate each supplier's total sales quantity and get the sales person's name if the sales person supplies parts more than 1000 units in total 7. Increase the status values of suppliers by 5 who are located in Paris. Please hand in the modified table and the query. 8. 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. 9. Delete all jobs in Rome and all corresponding part shipments. (Please use 2 queries) Please hand in the modified table and the query. 10. Smith moved to Adam's location. Please update Smith's city but do not use the city name directly. Note: You might need following setting before run your update SQL SET SQL_SAFE_UPDATES =;