IDS 410 homework assignment 4

A. Single Table Queries

1. Compose a query to show each raw material that has a dollar value of $800 or more. The dollar value of each raw material is defined as (footage on hand * unit price). In the result table, show material ID, material description, and the dollar value of each raw material. Sort the result by the dollar value in descending order.
2. Compose a query to find vendor IDs who supply material ID = 5. In the result table, show vendor ID, material ID, and the unit price. Sort the result by unit price in ascending order.
3. Compose a query as follows. For each customer who placed orders in 2011, we would like to see the customer ID and the total number of orders placed by that customer. Sort the results by the number of orders in descending order.
4. Compose a query as follows. For each product that had been ordered, we would like to know the total quantity that had been requested. List the most popular product first and the least popular product last. In the result table, show the product ID and the total quantity that had been requested.
5. Compose a query to insert a new vendor with the following values: Vendor Name: Illinois Lumber; Vendor Address: 12345 Lakeshore Drive; City: Chicago; State: IL; Postal Code: 60625.
6. Compose a query to update the postal code of Lakeshore Lumber from 60625 to 60614. Assume that you know the vendor name is Illinois Lumber but you do not know its Vendor ID.
7. Compose a query to delete all vendors who are located in Illinois (IL).

B. Multiple Table Queries

8. Compose a query to find vendors who supply Red Oak with grade C-4. In the result table, show vendor name, material description, grade, and the supplier’s unit price for Read Oak with grade C-4. Sort the result by unit price in ascending order.
9. The management would like to know which order that produced the highest total sales amount (in dollars). In the result table, show the order ID and its corresponding total sales amount as well as the customer name who submitted that order.
10. Suppose that you are a purchasing manager in a manufacturing company. Naturally, you want to know which vendors quoted the best price for each raw material. Compose an SQL statement to show a list of two least expensive vendors (suppliers) for each raw material. In the result table, show Material ID, Material Description, Vendor ID, Vendor Name, and Supplier’s unit price. Sort the result table by Material_ID and unit price in ascending order. Note: if a raw material has only one supplier (vendor), that vendor should also be in the result (output) table [hint: use a correlated subquery].