-Download the attached Microsoft Access Database file (hwk4_ids410.accdb).

-Rename hwk4_ids410.accdb to hwk4_yourNetid.accdb, where yourNetid is your Netid.

-Create (compose) ten queries according to their requirements (see below). Be sure to save all

queries inside the hwk4_yourNetid.accdb.

 

A. Single Table Queries
1. Compose a query to list the number of different products for each order ID. In the result table, your
query should show the order ID and the number of different products for each order ID. Sort the results
by order ID in ascending order. For example, your result table should look similar to the following:
Order_ID NumberOfDifferentProducts
1001               3
1002               1
…                     …
2. 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.
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 ascending order.
4. Compose a query to show every product that has a value of $400 or more. The value of each product
is defined as the quantity on hand multiplied by the unit price of that product. In the result table, show
product ID, unit price, quantity on hand, and the product’s value. Sort the results by the product’s value
in descending order.
5. Compose a query to insert a new customer with the following values: Customer Name: Lakeshore
Furniture; Customer Address: 300 East Lakeshore Drive; City: Chicago; State: IL; Postal Code: 60602.
6. Compose a query to update the postal code of Lakeshore Furniture from 60602 to 60607.
Assume that you know the customer name is Lakeshore Furniture but you do not know its Customer ID.
7. Compose a query to delete all customers who are located in Illinois (IL).

B. Multiple Table Queries
8. Compose an SQL statement that will show all customers who purchased an Entertainment Center or a
Dining Table in 2011. In the result table, show the customer name, city, state, order ID, order date, and
product name.
9. Compose a query to calculate the total cost of raw materials for each finish product. In the result
table, we would like to see Product ID, Product Name, and its total cost of raw materials. Sort the results
by the total cost of raw materials in descending order.
10. Compose an SQL statement that lists the order ID, order date, product ID, and quantity for all orders
for which the order quantity is greater than the average order quantity for that product [Hint: This
involves a correlated sub query].
To understand the requirement for query #10, let us use the following examples:
a. If you open the Order Line table, you will see that Product ID = 3 was requested in three
different orders: Order ID 1002 (Quantity = 5), Order ID 1003 (Quantity = 1), and Order ID 1008
(Quantity = 2). Thus, the average order quantity for Product ID = 3 was (5 + 1 + 2)/3 = 2.667.
Consequently, in the result table, there will be a row that shows Order ID = 1002, Order Date =
10/25/2011 (note that in the Order table, Order ID 1002 was submitted on 10/25/2011),
Product ID = 3, and Quantity = 5. The reason is because this row has Quantity = 5, which is
greater than the average order quantity of 2.667.

b. Similarly, Product ID = 1 was requested in two different orders: Order ID 1001 (Quantity = 2) and
Order ID 1007 (Quantity = 3). Thus, the average order quantity for Product ID = 1 was (2 + 3)/2 =
2.5. Consequently, in the result table, there will also be a row that shows Order ID = 1007, Order
Date = 11/28/2011, Product ID = 1, and Quantity = 3 because this row has Quantity = 3, which is
greater than the average order quantity of 2.5.
c. In the result table, you will also see other rows. Each of these rows shows a product ID that has
order quantity greater than the average order quantity for that product.

 

https://uic.blackboard.com/bbcswebdav/pid-4339507-dt-content-rid-50339658_2/courses/2015.fall.ids.410.14066/hwk4_ids410%281%29.accdb