Using the class_music_festival, answer these 7 questions. For each query, show the intermediate queries, the final query and the result that you get. If you are having trouble getting the syntax exactly, just write out what you think the recipe for getting to the answer is, that's really at least half the battle. Remember to do all your joins before ORDER BY and GROUP BY. If you see a "disconnected from server" in phpmyadmin that means you missed a join condition (e.g. SELECT * FROM tickets, purchases, without the needed WHERE clause).
- What is the name of the person who spent the most (and how much did they spend)? (Vebil Miesys 765)
- Which performance had the highest revenue? (ticket prices are incoming income, thus revenue) (ID: 41)
- Which performance was the most profitable? (profit is revenue - costs. bands.fee is the cost) (ID: 41)
- Which band was the least profitable for the festival? (note: bands are paid their fee for each performance, hint: dealing with that requires a COUNT(DISTINCT ...) within a group) (If you have trouble with this one, just do as though they are only paid the fee once). (WAX)
- Which venues were oversold (and what were their capacities)? (hint: this should use a HAVING clause) (oversold requires math around capacity) (AMD)
- What was the total revenue from ticket sales each month? (hint: needs MONTH(date) in the SELECT clause) (Month 7: 260668. Note: Please indicate the total revenue for each month, we have just provided one month for checking your work.)
- What was the average purchase total each month? (note: purchase total is the sum of the price of tickets bought together in the same purchase, Q1 in in-class exercises.) (two ways to get this done, one that uses AVG and that uses /). (Month 7: 196.4341)
INSERT/UPDATE/DELETE questions (no results provided for these):
Import this file: bookstore_queries_3.sql.zip to a database named 'yourusername_bookstore_queries_3' (ie mine would be named 'jhowison_bookstore_queries_3'. Include in the file you submit the queries that you ran on your copy of the database and the number of rows affected.
8. We've got three new books as follows. Insert records to the table (look up and include the authors)
Harry Potter and the Goblet of Fire
We received 3 copies of Goblet of Fire and The Hobbit, but 5 copies of Mansfield Park.
9. The customer at "1333 Customer street" wants their address changed to "1616 Guadalupe street". Update the table.
10. "Another Bronte Book" is not sold anymore in our book store. Delete the record.