IDS 410 - Database Technology Individual Homework Assignment 4 Due Date: submit your solution file to the Blackboard by 11:59 PM on Feb 21, 2022 Instructions: - 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. - Upload this renamed file to the Blackboard. A. Single Table Queries 1. Compose a query to list the number of customers in each state. In the result table, your query should show state and the number of customers in each state. Sort the results by state in ascending order. 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 $500 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: 1234 Lakeshore Drive; City: Chicago; State: IL; Postal Code: 60603. 6. Compose a query to update the postal code of Lakeshore Furniture from 60603 to 60605. Assume that you know the customer's name is Lakeshore Furniture, but you do not know its Customer ID. 7. Compose a query to delete all customers who are in Illinois (IL). B. Multiple Table Queries 8. Compose an SQL statement that will show all customers who purchased an Entertainment Center in 2011. In the result table, show the customer's name, city, state, order ID, order date, and product name. 9. The management would like to know which order in year 2011 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's name who submitted that order. 10. For each raw material, the purchasing manager would like to see a list of two least expensive vendors (suppliers). Compose an SQL statement to provide that list, which show the following columns: material ID, material description, vendor ID, vendor name, and the supplier's unit price. Sort the result table by material ID and supplier's unit price in ascending order. Note: If a raw material has only one vendor, that vendor and its unit price for the raw material should also be in the result (output) table [hint: use a correlated subquery]