Activity 3.5-2

Just like in the first activity, we will use the following site to enter SQL queries and see their effect on the database: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all. This page will allow you to put in SQL statements into an interpreter, and work with a database without having to install programs locally. If anything goes wrong, you can hit the "Restore Database" button and the default tables will be created for the database. You should hit this button once before you start the activity. After you type in a statement in the interpreter window, hit the "Run SQL" button to run your code. Note that you can only run one statement at a time! Also note that in this activity, we will be working with the built in database and tables, rather than creating anything of our own.

First, we are going to practice using the aggregation functions mentioned in class. These include AVG, SUM, MIN, MAX, and COUNT. Be sure to hit "Restore Database" once before trying the problems. You can round to two decimal places if needed.

1. Find the sum of the prices of all the products in the Products table. Don't worry about units; just use the values listed in the price column.
2. Find the average price of all the products in the Products table.
3. How many orders are there in total in the Orders table? (You can use any column to find this.)
4. Find the maximum quantity of items from all orders using the OrderDetails table.
5. Find the product that has the lowest price per unit using the Products table.
6. What is the name of the product that has the least price per unit? (Hint: Use the value for the MIN(price) from above.)
7. What is the product that has the max price per unit, and what is its productID as well? (Hint: Use two queries!)
8. What is the average quantity of this product - the one with the max price per unit - that was ordered? (Hint: Use the id found from the previous question and the OrderDetails table.)

Now we're going to practice using grouping. Note that to find the answers below, be sure to use a GROUP BY somewhere in your SQL query. After using group to create a table, we will look at the resulting tables to answer a couple of questions.

1. Table 1: Find the average price for each supplier using the Products table. Be sure to also display the SupplierID in your table.
   • Find the average prices of suppliers 5, 10, and 15.
2. Table 2: Using the OrderDetails table, create a table that displays the average quantity of a certain product. Be sure to display the ProductIDs in your table.
   • What is the average quantity of products 8 and 66?
   • What is the average quantity of product 38? Does this match up to your answer from the previous section? Why or why not?
3. Table 3: Using the customers table, create a table that displays the number of customers (or the count of customers) from each city. Be sure to display the city name in your table.
   • How many customers are in London, Madrid, Buenos Aires, and Paris?

Now, we're going to practice getting information by joining different tables together. Similarly to the last section, first you will join the tables, and then you will look at the resulting table to answer some questions.

1. Join 1: Look at both the Products table and the Categories table. The products table has each product labeled with a CategoryID, but we'd like to be able to view what type of product something is right away, rather than having to switch back and forth between the Categories and Products table. Join the Products table with the Categories table on the CategoryID.
   • Think back to the products with the least and most price per quantity. Their productIDs should have been 33 and 38, respectively. Based on your joined table, what type of products are they?
   • What type of product is product 42?
2. Join 2: Now, we want to look at some information on which products are being ordered by which customers. Join the Orders table with the Customers table on CustomerIDs.
   • We're curious to see who was the last person who ordered anything in the database. What's that customer's name and where is the customer from? (Note the last order has the highest OrderID, so it'll be at the bottom of the table)
   • You're curious to see if there were any orders on December 31, 1996. Were there any? And if so, what country did the order come from and from whom?

Now we want to combine all our skills to answer some questions we have about the most expensive product.

1. Go back to the Products table. Find the item with the max price and display its ProductID, ProductName, SupplierID, along with the price. What are these? Write them down. (Hint: you'll need to use two queries.)
2. Now, use the OrderDetails table, and find the max Quantity of each item grouped by ProductID.
3. Since you know the ProductID of the pricey item, find the max Quantity of our pricey item in the table made by the previous query by scrolling through. Remember this value. Now, what is the OrderID of the order that contains this quantity of our pricey item (remember to match both Quantity and ProductID)? If you run into more than one order that meets the specifications, choose the lower OrderID. Write it down.
4. Now, we want to see who is behind this order. If you look at the Orders table, you'll notice there isn't any specific information on Customers, just CustomerID's. We'll have to join the Orders table with another table to find out who our mystery customer is. Do this, and find out the name and country of the customer who ordered our pricey item.