Directions: This lab requires importing multiple csv files and doing analysis on the data. If you load csv’s using #1, make sure to delete the header row. If you load csv’s using #2, make sure to create a new table with correct datatypes. For each question, write a SQL query to obtain a result for each question. If the answer requires explanation or analysis, write that in a comment above the query.
Turning In: Please submit a single .sql file with the answer to each question. You can do this in DBVis or in a text editor of your choice. Make sure your queries run correctly and produce the desired result. The solution to each question should be structured like so:
EXAMPLE QUESTION #1: How many records does this table have in it?
EXAMPLE SOLUTION:
--solution for question #1
--this query selects the count of all records in <table_name_here
SELECT COUNT(*)
FROM <table_name_here;
If a solution requires multiple queries, make sure to put a comment on top of each query to explain what it does.
Other notes: I encourage you to work together with your peers to get problems solved, but your work must be your own (aka, don’t copy and paste someone else’s code). Please include any references you used to complete this assignment (including other students) in a comment section at the top of your .sql file.
How many receipts did KIP ARNN have? (answer should be 9)
Return the receipt_number, item_id, and price of the receipt that had the highest priced item. (answer should be 51991,26-8x10,15.95) (receipt_number could be different)
Find the top 5 busiest dates for the bakery. (HINT: which days had the highest number of items ordered? Use the SUM function with GROUP BY)
Answer:
12-Oct-2007 92
16-Oct-2007 81
10-Oct-2007 77
30-Oct-2007 76
4-Oct-2007 54
What was the total price of the order with receipt number 51991? Return only the summed price. (HINT: use a subquery) (answer should be $86.30)
How many unique foods were ordered on ‘29-Oct-2007’? (HINT: use a subquery and join to the subquery) (answer should be 7)
What is the name of the customer who had the most orders(receipts)?(RUPERT HELING)
