Quiz 3 CSE 3241 Download for quality grades |

Document Content and Description Below

1. Select supplier names for suppliers who supply at least one red part a. (7 Points) Express in SQL Select SNAME From SUPPLIER Where SNO in (Select SNO From SHIPMENT Where PNO in (Select PNO ... From PARTS Where color = ‘RED’)) b. (1 Point) Show the result if it is applied to the sample database Smith, Jones and Clerk 2. Delete suppliers (from supplier table) who has no shipments a. (7 Points) Express in SQL Delete SUPPLIER Where (Select count(*) From SHIPMENT Where SNO = SUPPLIER.SNO) = 0 b. (1 Point) List the deleted supplier names if it is applied to the sample database. Adams 3. (7 Points) Add all shipments by suppliers in London to the TEMP (SNO, PNO, QTY) table. Insert [into] TEMP Select SNO, PNO, QTY From SHIPMENT Where SNO in (Select SNO From SUPPLIER Where CITY=’London’) 4. (7 Points) Double the status of suppliers in London (use update query) if the total number of shipments by all of the London suppliers is greater than 5. Hint: use the TEMP table from the problem 3 to count the shipments. Update SUPPLIER Set status = 2*status Where (Select count(*) From TEMP where SNO = SUPPLIER.SNO) >5 [Show More]

Last updated: 1 year ago

Preview 1 out of 2 pages