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

select lastName, firstName, city, state, playerRatePerMatch from players;

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.

select name, rent from venues order by rent desc;

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

select username, playerRatePerMatch from players where playerRatePerMatch >= 250;

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

select matchid, ticketPrice from matches where ticketPrice >= 7.00;

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

select matchid, sum(tickets) as total from enrollment group by matchid order by total desc;

6. List the most expensive player(s)?

select lastName, firstName, playerRatePerMatch from players where playerRatePerMatch = (select max(playerRatePerMatch) from players); 

(Assume Bird and Barkley cost as much as Jordan).

mysql> update players set playerRatePerMatch = 500 where username in ("lbird", "cbarkley"); 
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select lastName, firstName, playerRatePerMatch from players where playerRatePerMatch = (select max(playerRatePerMatch) from players); 
+----------+-----------+--------------------+
| lastName | firstName | playerRatePerMatch |
+----------+-----------+--------------------+
| Barkley  | Charles   |             500.00 | 
| Bird     | Larry     |             500.00 | 
| Jordan   | Michael   |             500.00 | 
+----------+-----------+--------------------+
3 rows in set (0.00 sec)

mysql> update players set playerRatePerMatch = 450 where username = "cbarkley"; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update players set playerRatePerMatch = 350 where username = "lbird"; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

select name, capacity from venues where capacity = (select min(capacity) from venues);

(Same as above, assume Wrigley, Woodburn and RCA Dome are the same size).

--

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

Preliminary query: 

select matches.matchid, venue, tickets, capacity 
from matches, venues, enrollment 
where venue = name and enrollment.matchid = matches.matchid order by matchid;

Final query: 

select matches.matchid, sum(tickets), capacity 
from matches, venues, enrollment 
where venue = name and enrollment.matchid = matches.matchid 
group by matchid;

9. List all the matches and the percentage of seats they sold. What's the match that had the least percent of seats sold?

select matches.matchid, venue, tickets, capacity, tickets / capacity as percent 
from matches, venues, enrollment 
where venue = name and 
      enrollment.matchid = matches.matchid and 
      tickets / capacity = (select min(tickets/capacity) 
                            from enrollment, matches, venues 
                            where venue = name and enrollment.matchid = matches.matchid);

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

Here's a version of this query that is a bit more general than we need it: 

select matches.round, sum(playerRatePerMatch) 
from players, participation, matches 
where players.username = participation.username and 
      participation.matchid = matches.matchid 
group by matches.round;

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

select participation.username, avg(playerRatePerMatch) 
from players, participation 
where players.username = participation.username 
group by participation.username;

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

Yes, we need to pick this info up from the matches table, not the players table. 

Here's the average cost per game overall (not an average per player): 

select avg(playerRatePerMatch) 
from participation, players 
where participation.username = players.username;

This is useful in the next query. 

12. Which players cost above average? (Take the average from the prices listed in players).

select username, playerRatePerMatch 
from players 
where playerRatePerMatch >= (select avg(playerRatePerMatch) 
                             from participation, players 
                             where participation.username = players.username);

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

select count(username) 
from players 
where playerRatePerMatch >= (select avg(playerRatePerMatch) 
                             from participation, players 
                             where participation.username = players.username);

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

select spectators.customer, count(matchid) as howMany 
from spectators, enrollment 
where spectators.customer = enrollment.customer 
group by spectators.customer 
order by howMany desc;

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

That's definitely an interesting question (with two possible answers).

select spectators.customer, count(tickets) 
from spectators left join enrollment on spectators.customer = enrollment.customer 
group by spectators.customer;

15. List the games attended by Indiana University students.

select * from matches where matchid in (select matchid from enrollment where customer = 'Indiana');

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

select avg(tickets) from enrollment where customer = 'Indiana';

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

select customer, sum(tickets * ticketPrice) 
from enrollment inner join matches on matches.matchid = enrollment.matchid 
group by customer;

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

select sum(playerRatePerMatch) 
from participation inner join players on participation.username = players.username 
                   inner join matches on matches.matchid = participation.matchid 
where round in ('round one', 'round two', 'round three', 'round four');

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

select sum(tickets * ticketPrice) 
from enrollment inner join matches on matches.matchid = enrollment.matchid and round in ('round one', 
                                                                                         'round two', 
                                                                                         'round three', 
                                                                                         'round four');

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

That's right, it doesn't matter (see the use of the IN operator). 

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

Left as an exercise (too easy). 

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).

It's better to calculate (or, take a look at) the next one first.

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?

31. List the standings after the fourth round.

select username, sum(points) 
from participation inner join matches on matches.matchid = participation.matchid and round in ('round one', 
                                                                                               'round two', 
                                                                                               'round three', 
                                                                                               'round four') 
group by username 
order by sum(points) desc;

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.