Fall Semester 2003

Remember Lab Three?

Homework Three extends that situation and asks you the following questions:

1. List all the players and their hometowns and rates.

2. List all the locations available for all events with the leasing rate.

List locations even if no games are scheduled at that location yet.

3. List all the players that charge more than $250 per match.

4. List all the matches whose ticket prices are above $7.00

5. List all the matches with number of tickets sold (per match).

6. List the most expensive player(s)?

7. What's the venue with the smallest capacity?

8. List all the matches, number of tickets sold, and the capacity of the venue for each game.

9. List all the matches and the percentage of seats they sold.

What's the match that had the least percent of seats sold?

10. What is the total cost of the players (per round)?

11. What's the average cost of player (per match)?

(Does it matter that the roster could be bigger than the invited players?)

12. Which players cost above average?

(Take the average from the prices listed in players).

13. List the number of players that cost above average.

14. List all universities and the number of games they bought tickets to.

(How would you identify the universities that bought tickets to ALL/NONE of the games?)

15. List the games attended by Indiana University students.

16. List the average attendance of Indiana Univ. students per game.

17. List the total amount of money IU students paid for tickets.

(Watch out, the query I gave you might just be slightly wrong.)

18. Calculate the entire amount spent on players up to (and including) round four (not five).

19. Calculate the entire amount collected on tickets sold to matches up to (and including) round four.

(What if the question were to ask up to an earlier round, or selected rounds?)

20. Calculate the total amount spent on leasing the rooms.

21. Calculate the net profit up to (and including) round four.

(Difference between income from tickets and the sum of expenses with players and rent).

22-23. Calculate the profit per match and round (two queries).

24-25. Calculate the average profit per match and round (two queries).

26-27. List the matches and rounds with an above average profit (two queries).

28. How many students saw Bird playing?

(How many tickets were sold for games he was in?)

29. Who was the most watched player?

30. How much money did each player bring?

Sort the result of this query descending by amount and ascending by player's name (alphabetically, that is).

31. List the standings after the fifth round.

32. Which matches only had IN students in the audience?

(Purdue is in Indiana as is IU).

33. Which University did not purchase tickets to any game?

34. Which University did not purchase tickets to the Bird-Jordan game?

35. List the Universities sorted by the number of games they purchased tickets to.