Instructions: Create (compose) ten queries according to their requirements (see
ID: 3598541 • Letter: I
Question
Instructions:
Create (compose) ten queries according to their requirements (see below).
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.
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 at least two different orders in 2011, we would like to see the customer ID and the total number of orders placed by that customer in 2011. Sort the results by the total number of orders in ascending order.
4. Compose a query to show every product that has a value of $350 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 value. Sort the results by the product 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 to generate a list of two least expensive vendors (suppliers) for each raw material. In the result table, 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 (supplier), that supplier and its unit price for the raw material should also be in the result (output) table [hint: use a correlated subquery].
Explanation / Answer
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.
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 at least two different orders in 2011, we would like to see the customer ID and the total number of orders placed by that customer in 2011. Sort the results by the total number of orders in ascending order.
SELECT CustomerID,count(OrderID) FROM [Orders] where to_char(OrderDate,YYYY)=2011 group by CustomerID having count(distinct(OrderID))>=2 order by CustomerID asc;
4.Compose a query to show every product that has a value of $350 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 value. Sort the results by the product value in descending order.