Computer Science 455

Second Exercise Set

Due: Monday, Feb. 21. Please note that these problems are also good review for Friday's exam.

Write SQL statements for the following queries (these come from the third edition of Date's book):

1. Get JNAME values for projects supplied by supplier S1
2. Get S# values for suppliers who supply both projects J1 and J2
3. Get S# values for suppliers who supply a London or Paris project with a red part.
4. Get J# values for projects supplied by at least one supplier not in the same city.
5. Get S#, SNAME values for suppliers supplying at least one part supplied by at least one supplier who supplies at least one red part.
6. Get S# values for suppliers who supply the same part to all projects.
7. Get J# values for projects supplied with at least all parts supplied by supplier S2.
8. Get the total number of projects supplied by supplier S3
9. Get the total quantity of part P1 supplied by supplier S1
10. For each part being supplied to a project, get the part number, the project number, and the corresponding total quantity.
11. This problem has been removed.

Place these in a file called <your first initial + your last name>.sql (it is important that you use this name) in the class handins folder on Plato (please don't do this until I send a note saying that the first sql exercise has been picked up. The command file should have the same format as your first SQL assignment.

Any questions? Please let me know. Many thanks!