Due: Thursday, October 25

Please upload your solutions to CSNS. Note that file uploading will be disabled automatically after 11:59PM of the due date, so please turn in your work on time.

Consider the following database schema:

[Part A] (15pt) Use SQL statements to create these tables in a PostgreSQL database, and populate the tables with some test data so each table has at least two records. Make sure that you use appropriate data types and data integrity constraints for the tables. For this exercise, please submit an SQL script hw2-create.sql.

[Part B] (60pt) Use the tables you created in Part A to complete the following queries. For this exercise, please submit an SQL script hw2-queries.sql.

1. Assuming the phone numbers are in the format (###) ###-####, find the names of the suppliers who are located in the area code 323.

2. Find the number of parts ordered in June this year (you may not assume "this year" is 2012).

3. Find the id's of the orders that were placed two weeks ago but were not yet received.

4. List the total payment made to each supplier last year. The results should include supplier name and the total payment to the supplier ordered by payment in descending order.

5. List the total payment made in each month this year. The results should include month (in full name like January, February ... instead of 1, 2, ...) and the total payment in the month ordered by month in ascending order.

6. Find the names of the parts for which there is at least one supplier.

7. Find the names of the parts for which there are at least two suppliers.

8. Find the names of the parts for which there is no supplier.

9. Find the names of the suppliers who supply every red part.

10. Find the names of the suppliers who supply only red parts.

11. Find the names of the parts that are supplied by Acme Widget Suppliers and no one else.

12. For each part, find the name of the supplier who charges the least for the part.