SINGLE TABLE QUERIES Solution

For this deliverable, you will be writing queries to use the data in the TheHipp database.
STEP 1: Download the SQL script (TheHipp.sql) file attached to assigment IP#4. Run the scripts into MySQL as follows:
• Open the SQL script file using CTRL+SHIFT+O.
• Run the entire script using CTRL+SHIFT+Enter.
STEP 2: You must write queries for the problem statements on Page 2 as follows:
• Section 1: Write queries for all 4 problem satetemnts. Each query is worth 1 point.
• Section 2: Write queries for 2 problem statements. Each query is worth 2 points.
• Section 3: Write queries for 2 problem statements. Each query is worth 2 points.
STEP 3: Do the following for each of the problem statements you attempt:
a) Write the SQL query that will achieve the desired result
b) Run the query in the MySQL database.
c) Copy and paste the results of the query below each SQL statement using the format on page 3 of this assignment.
Each query must satisfy the following criteria:
a) They must not include any extra/unnecessary tables.
b) The results must not include any extraneous columns.
c) All column headings should be meaningful names. Please do not have columns with headings that contain expressions or aggregate functions.
Department of Information Systems & Operations Management
Warrington College of Business Administration, University of Florida ISM 4210
Database Management
Individual Project #4
PROBLEM STATEMENTS
Section A: You must respond to ALL 4 of the following:
1. List the LastName, FirstName, Email Address and DonorID for all the customers who are also donors. Sort the results by LastName.
2. How many customers have been verified as students? Your query should return just one value.
3. List the EventCode and the Year for all the shows that are scheduled in the month of December. Your query should display each event code only once.
4. How many events have been scheduled for each venue? Your query should return two columns: the VenueID and the number of events.
Section B: You must respond to 2 of the following:
5. List the number of tickets sold during the month of November 2015. Group your results by show and only list those shows that have sold more than or equal to 10 tickets. Your query should have 4 columns: EventCode, ShowDate, ShowTime and the number of tickets sold.
6. What is the maximum number of tickets that a single customer has bought in one day? You query should have 3 columns (HippCode, Date of Purchase and the number of tickets) and just 1 row in the results.
7. For each of the event show, list the number of tickets that were made available, the number of tickets that were sold and the percentage of tickets that were sold. Your results should not include any rows where the shows were sold out (i.e. shows where all tickets were sold). Your results should look like the table below; but the numbers may differ.
Event Code
Show Date
Show Time
Total Tickets
Tickets Sold
Percentage Sold
802
2014-09-23
07:00:00
50
34
68%
803
2014-10-21
07:00:00
50
40
80%
804
2014-11-18
07:00:00
50
32
64%
Section C: You must respond to 2 of the following:
8. For each sponsor, list the sponsor name, the number of events and the total amount they have donated. Sort your results by donation amount in the ascending order.
9. For the top 5 grossing events, list the event code, number of tickets sold, the date of the first show and the date of the last shows (these may be the same).
10. Write a query that lists the number of tickets sold and the revenue earned for each day of the week. Group your results by year as well and sort them by year and day of the week.
Department of Information Systems & Operations Management
Warrington College of Business Administration, University of Florida ISM 4210
Database Management
Individual Project #4
For each query, you must present your results using the following template.
Problem Statement Number
Query:
SELECT * FROM Customer;
Results