SQL Assignments
SQL Assignment
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. 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. 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 total 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. Insert a new customer with the following values: Customer Name: Seashore Furniture; Customer
Address: 12345 Main St.; City: Los Angeles; State: CA; Postal Code: 90095.
6. Update the postal code of Seashore Furniture from 90095 to 90096.
Assume that you know the customer name is Seashore Furniture but you do not know its Customer ID.
7. Delete all customers who are located in California (CA).
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 name, city, state, order ID, order date, and product name.
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. Compose an SQL statement that lists the
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. 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. 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 total 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. Insert a new customer with the following values: Customer Name: Seashore Furniture; Customer
Address: 12345 Main St.; City: Los Angeles; State: CA; Postal Code: 90095.
6. Update the postal code of Seashore Furniture from 90095 to 90096.
Assume that you know the customer name is Seashore Furniture but you do not know its Customer ID.
7. Delete all customers who are located in California (CA).
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 name, city, state, order ID, order date, and product name.
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. Compose an SQL statement that lists the