Write SQL queries that will answer the following questions, using the data in the PetStore d
Write SQL queries that will answer the following questions, using the data in the PetStore database. As usual, please write all of your queries using your favorite text editor. When you are finished, save them as a file named Assignment6.sql and upload the file here to Canvas.
(6 points) What is the total (sale) value of the animals sold in December of 2004?
(8 points) To what state did the pet store sell the most merchandise (as measured by the total sales value) in July of 2004? Hint: Order your output appropriately and use LIMIT 1 at the end of your query (look up LIMIT using Google).
(6 points) Which animals sold for less than their cost (from the supplier)?
(8 points) List the cats that took longer than average cats to sell (measure time to sell as the sale date minus the order date from the supplier).
(7 points) Use an outer join to list the animal IDs of all dogs that are still in stock in the pet store (i.e., all dogs that have not been sold).
(7 points) Use an outer join to list all merchandise items (show the item ID and description) with more than 100 units on hand that have not been ordered on any order recorded in the database.
(6 points) Repeat the previous question, but use a subquery instead of an outer join. Hint: Use NOT IN.
(7 points) Using an outer join, produce a list of all of the customer IDs, and for each customer show how many sales have been made to that customer (each record in the tSale table is considered one sale). The number of sales must be zero for all customers who have never purchased anything from the pet store.
