Assignment 3 Product Solution

Reading: The posted Lecture 5 and Lecture 6 Slides, and Ullman/Widom Sections 6.1-6.2 and 6.4. [Next week: Ullman/Widom Sections 6.2-6.3.]

Your task in this assignment is to write a set of SQL queries (I will supply the  tables).

First, download the script file Furniture.sql from the dropbox and run it in SQLDeveloper to build the tables you will be querying.

This script will build and display the contents of the following four tables that store data for a fictitious furniture supplier:

CUSTOMER(CustomerID, Name, Address, City, State, Zip), which contains information on customers of the company;

PRODUCT(ProductID, Description, Finish, Price), which contains information on products sold by the company;

ORDERTABLE(OrderID, OrderDate, CustomerID), which contains information on orders placed with the company;

ORDERLINE(OrderID, ProductID, Quantity), which contains information on the individual products requested in customers’ orders.

In additional to the primary keys indicated above, OrderID and ProductID in ORDERLINE are foreign keys referencing OrderID in ORDERTABLE and ProductID in PRODUCT, respectively, and CustomerID in ORDERTABLE is a foreign key referencing CustomerID in CUSTOMER.

In SQLDeveloper, look at the Columns, Data, and Constraints for each of the four tables before continuing, to be sure that they have been constructed correctly. You might also want to draw the foreign keys and reference arrows into the set of relation schemas given above to be sure that you understand the links among the tables.

For each of the following query problems, follow the steps we discussed in class: interpret the problem, predict the output by solving it by hand on the table in question, write a query to solve the problem, and test the query. (Most of the query problems can be solved with information from just one of the given tables, but some will require joins.)

For each query, when you have it working correctly, include in your submission document a single screen shot that shows your open SQLDeveloper connection, the query displayed in the center window, and the entire result of the query in the Query Result window. (You may have to resize the Query Result window to see the entire result.) Be sure that the query and result are big enough to be readable. Also include the query number in your document before each screenshot.

1. Give an alphabetical list of all states that have at least one customer.

2. Give all of the information for customers in California, alphabetized by customer name.

3. For each customer who has placed at least one order, give the customer’s ID and the date of their earliest order. Sort the output by the customers’ IDs.

4. Give the state and zip for all customers in New York and California.

5. Give the finish and description of each product, sorted alphabetically by finish, and with the products with each finish given in order from the one with the highest price to the one with the lowest price.

6. Give the names of all customers with the word ‘Furnishings’ somewhere in their name.

7. For each date on which at least one order has been placed, give the date and the number of orders placed on that date. Sort the output by the date.

8. For each order, give the order ID and the total number of items requested in that order (e.g., for an order that requested 4 of one product, 3 of a second product, and 1 of a third product, the total number of items reported for that order should be 8). Sort the orders from the one with the smallest number of items to the one with the largest.

9. For each order, give the order ID and the city and state of the customer who placed that order. Sort the output by the order ID.

10. For each product, give the product name and a count of how many orders have requested it. (Don’t worry about the quantity of the product requested by each order, just count the number of order that requested any amount of the product.) Order the output by the product ID.