CSci 241.AL: Database Management Systems

Department of Computer Science
The George Washington University, Fall 2010
CRN 73415

Parts Assignment


Warmup

  1. How many different kinds of parts are there. Disregard color differences.
  2. How many colors is each kind of part available in? List part name and count.
  3. How many suppliers have names starting with 'A'?
  4. What are the names and costs of the most expensive and cheapest items in the catalog? List all items that match the criteria, disregarding color differences. Do this with one query. Bonus: format the costs with a '$' and commas every three digits (starting from the decimal).

The Main Event

  1. Find the names of parts for which there is some 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 sids 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 sids of suppliers who do not sell any non-red parts.
  8. Find the sids of suppliers who sell a red part and a green part.
  9. Find the sids 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 the name and price of the most expensive part that she supplies.

mmburke@gwu.edu
Modified: Tue Oct 5 22:06:46 EDT 2010