CS 348 - Homework 3 - Due Thursday Sep 25

Answer the questions listed below based on our parts and supplier database. These are SQL problems. Queries should execute properly on the server. Save a file named hw3.sql to your T: drive folder. Each query should be commented with the problem number and with the problem statement below repeated. Single line comments in an SQL file start with a # character. Also, make sure you turn in a printout of the file.

  1. List all of the supplier numbers and the part numbers supplied by the supplier.
  2. Get supplier numbers for suppliers who supply part 1. Answer should have one attribute, (sno).
  3. Get supplier names and status for all suppliers who have a status between 15 and 25 inclusive. Answer should have two attributes (sname,status)
  4. Get all part numbers and the supplier names for parts supplied by a supplier in London. Answer should have two attributes (pno,sname).
  5. Find the average status for all the suppliers. Answer is scalar (a table with one rwo and one column).
  6. Get part numbers for parts not supplied by any supplier in London. Answer should have one attribute (pno).
  7. Get city names for cities in which at least two suppliers are located. ANswer has one attribute (city).
  8. Get all pairs of part numbers and a supplier name such that the supplier supplies both of the parts. Don't include trivial pairs of part numbers. For example don't list the tuple Smith 1 1. That's obvious. For ease, you can list symmetric tuples, for example, Smith 1 2 and Smith 2 1. Your answer schema should have three attributes (sname,pno,pno).
  9. Get the total number of different parts supplied by supplier one. Answer is scalar.
  10. Get supplier numbers for suppliers with a status lower than that of supplier 1. Answer has one attribute (sno).
  11. Get supplier numbers for suppliers whose city is first in the lexicographic order of cities. Answer has one attribute (sno).
  12. Get part numbers for parts supplied by all suppliers in London. Answer has one attribute (pno).
  13. Get supplier number, supplier name, and part number such that the supplier does not supply the part. Answer has three attributes (sno,sname,pno).