Description

INSTRUCTIONS

Please read the description and the requirements of Deliverable #4 carefully before responding. If you have any questions or clarifications, feel free to contact me via email (aditimukherjee@ ufl.edu), during my office hours, or by appointment  

All submissions should be individual work only. Do not discuss your answers with your class mates or group members. Plagiarism will not be tolerated.

No late submissions will be accepted. If you have trouble submitting assignments to the e-learning system, please contact me before the submission deadline.

Page Limit:  There is no fixed page limit for this deliverable.

Format:   You will submit 8 SQL statements and the results of running the queries in the attached database. Copy and paste all the statements and the diagram into a Word document that you can save and submit as a PDF. Please note the number of the problem statement clearly above each query. See page 3 of this document for an example of how to present the statements.  The name of your file should be <LastName>_<FirstName>_IP4.pdf. Submissions that do not follow the formatting guidelines will not be graded.  Grading:  This deliverables will be graded out of 12 points.

SINGLE TABLE QUERIES

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:

1. Write the SQL query that will achieve the desired result
2. Run the query in the MySQL database.
3. 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:

1. They must not include any extra/unnecessary tables.
2. The results must not include any extraneous columns.
3. All column headings should be meaningful names. Please do not have columns with headings that contain expressions or aggregate functions.

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.

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.

For each query, you must present your results using the following template.