CSci 6441.BA2: Database Management Systems

Department of Computer Science
The George Washington University, Spring 2011
CRN 15165

SQL Assignment


For this assignment you should use the relations defined in create-parts.sql and populate them with data from populate-parts.sql. Your queries should be included in a plain text file named partsqueries.sql which you should commit to your git repository. As with any code you write, this file should have proper comments at the top including, at least, your name, email address and the date.

  1. Find the names of parts for which there is no supplier.
  2. Find the names of suppliers who supply every part.
  3. Find the names of suppliers who supply every red part.
  4. Find the names of parts supplied by Acme Widget Suppliers and no one else.
  5. Find the IDs of suppliers who charge more for some part then the average cost of that part (average over all suppliers who supply that part).
  6. For each part, find the name of the supplier who charges the most for that part.
  7. Find the IDs of suppliers who do not sell any non-red parts.
  8. Find the IDs of suppliers who sell a red part and a green part.
  9. Find the IDs of suppliers who sell a red part or a green part.
  10. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.
  11. For every supplier that supplies a green part and a red part, print both the name and price of the most expensive part that she supplies and the name and price of the least expensive part that she supplies.

mmburke@gwu.edu
Modified: Sun Jan 30 22:06:46 EDT 2011