Before we get started let's agree on something: the names used are silly, made up and irrelevant.

So don't pay attention to them. But read the text of the problem and take data in exactly as specified.

The simplest, most naive approach to this problem would be the use an Excel spreadsheet.

Let's describe the type of data that we would store and give some examples.

Round No.	First player	Second player	First player honorarium	Second player honorarium	Venue	Ticket price	Capacity	Rent	Spectator	No. of tickets	First player points	Second player points
1	Jordan	Barkley	500	450	Soldier Field	5.50	2800	800	IU	100	1	0

So what you see above adequately summarizes the following information:

• Jordan played Barkley in the first round
• Jordan is paid $500 per game, Barkley: $450
• the game was in Soldier Field
• Soldier Field has a capacity of 2,800 and the rent is $800 per game
• there were 100 IU students in attendance
• the ticket price for the game was $5.50
• Jordan won the game

There's nothing wrong with how we store the data so far.

But let's assume that we need to add this piece of information:

• the game was also attended by 120 Univ. of Illinois students

The table now becomes (or should become):

Round No.	First player	Second player	First player honorarium	Second player honorarium	Venue	Ticket price	Capacity	Rent	Spectator	No. of tickets	First player points	Second player points
1	Jordan	Barkley	500	450	Soldier Field	5.50	2800	800	IU	100	1	0
									Illinois	120

Now, you see the problem. We are actually thinking about something like this:

Round No.	First player	Second player	First player honorarium	Second player honorarium	Venue	Ticket price	Capacity	Rent	Spectators	First player points	Second player points
1	Jordan	Barkley	500	450	Soldier Field	5.50	2800	800	University No. of tickets IU 100 Illinois 120	1	0

So with this we can easily add the other games in the first round, in the same way.

But is there any part of data missing?

Yes:

• Address information for each player is missing
• Last and first name information for each player is missing too

OK, let's address this:

Round No.	First player	Second player	Venue	Ticket price	Capacity	Rent	Spectators	First player points	Second player points
1	Username Last name First name Rate Address mjordan Jordan Michael 500 ...	Username Last name First name Rate Address cbarkley Barkley Charles 450 ...	Soldier Field	5.50	2800	800	University No. of tickets IU 100 Illinois 120	1	0
1	Username Last name First name Rate Address rmiller Miller Reggie 150 ...	Username Last name First name Rate Address lbird Bird Larry 350 ...	Madison Square Garden	8.00	4000	1200	University No. of tickets Illinois 140 Purdue 60	0.5	0.5

OK, so now all the info is acknowledged.

Can we (or should we, even?) present the whole data this way?

Believe me, it's not worth it.

There's so much redundance it's going to be so tedious, so impossible to make a mistake.

Let's try though a few more lines.

We add a new column, one that identifies the match.

Match ID	Round No.	First player	Second player	Venue	Ticket price	Capacity	Rent	Spectators	First player points	Second player points
joba	1	Username Last name First name Rate Address mjordan Jordan Michael 500 ...	Username Last name First name Rate Address cbarkley Barkley Charles 450 ...	Soldier Field	5.50	2800	800	University No. of tickets IU 100 Illinois 120	1	0
mibi	1	Username Last name First name Rate Address rmiller Miller Reggie 150 ...	Username Last name First name Rate Address lbird Bird Larry 350 ...	Madison Square Garden	8.00	4000	1200	University No. of tickets Illinois 140 Purdue 60	0	1
kudu	1	Username Last name First name Rate Address tkukoc Kukoc Toni 50 ...	Username Last name First name Rate Address tduncan Duncan Tim 210 ...	Wrigley Field	4.25	1200	800	University No. of tickets Minnesota 20 Michigan 220	0.5	0.5
duba	2	Username Last name First name Rate Address tduncan Duncan Tim 210 ...	Username Last name First name Rate Address cbarkley Barkley Charles 450 ...	Woodburn Hall	6.50	2000	600	University No. of tickets Minnesota 80 Michigan 300	0.5	0.5

OK, so before we even think of going on let's discuss if this is the right structure.

If we were to bring this to first normal form we'd just flatten the records, there's no epiphany in that.

Let me emphasize that: an unnormalized table contains repeating groups.

These are those tables inside tables. We can't allow that.

So let's pretend we eliminated those.

That results in a table with the following columns:

• matchID
• roundNumber
• usernameFirstPlayer
• lastNameFirstPlayer
• firstNameFirstPlayer
• rateFirstPlayer
• addressFirstPlayer
• usernameSecondPlayer
• lastNameSecondPlayer
• firstNameSecondPlayer
• rateSecondPlayer
• addressSecondPlayer
• venue
• ticketPrice
• capacity
• rent
• university
• tickets
• pointsFirstPlayer
• pointsSecondPlayer

To bring this to second normal form we need that

• no non-key column be dependent on only a portion of the primary key.

So we need to ask: what is the primary key here?

Fortunately, since we introduced it, the column matchID is the primary key.

Because of that the table is already in 2NF since the primary key is atomic.

To make it in 3NF we need to look at determinants. They need to be candidate keys.

Here's what non-key determinants we have:

• usernameFirstPlayer determines (lastNameFirstPlayer, firstNameFirstPlayer, rateFirstPlayer)
• usernameSecondPlayer determines (lastNameSecondPlayer, firstNameSecondPlayer, rateSecondPlayer)
• venue determines (capacity, rent)
• (matchID, university) determines tickets -- notice the composite determinant, which is still not a candidate key
• (usernameFirstPlayer, matchID) determines pointsFirstPlayer
• (usernameSecondPlayer, matchID) determines pointsSecondPlayer

Obviously this means that a player always charges the same for a game, regardless of the game.

(This is a conscious assumption, if we were to change it we'd lose one determining relation above.)

We also assume that a certain venue always has the same rent, regardless of the game, etc.

By "etc." I mean that these assumptions are immediate. (For example venue does not determine ticketPrice).

So we would then create the following tables:

Players:

Username	LastName	FirstName	Rate	Address
lbird	Bird	Larry	350	...

Venues:

Name	Capacity	Rent
Madison Square Garden	4000	1200

Enrollment:

MatchID	University	Tickets
mibi	Illinois	140
mibi	Purdue	60
kudu	Minnesota	20
kudu	Michigan	220

Participation:

Username	MatchID	Points
cbarkley	joba	0
mjordan	joba	1

This leaves us with:

Matches:

MatchID	RoundNumber	Venue	TicketPrice
joba	1	Soldier Field	5.50

Now let's assume that for each university we need additional information, like:

• logo
• address
• city
• state

Then it's obvious that we need to create a new table (Spectators).

This brings us to: the E-R diagram in the book.

Now let's put all the data in the database and start writing queries.

Note:

http://www.cs.indiana.edu/classes/a114-dger/fall2001/lectures/Review.html

One more thing:

-bash-3.2$ cat step008-safe
/nobackup/dgerman/mysql/bin/mysqld_safe --skip-grant-tables \
  --user=dgerman \
  --pid-file=/nobackup/dgerman/mysql/mysqld.pid \
  --log=/nobackup/dgerman/mysql/mysqld.log \
  --socket=/nobackup/dgerman/mysql/mysql.sock \
  --basedir=/nobackup/dgerman/mysql \
  --log-error=/nobackup/dgerman/mysqld-error.log \
  --datadir=/nobackup/dgerman/mysql \
  --port=13038 &

-bash-3.2$ 

Finally, here's how the database would get created:

create table enrollment (
   customer varchar(25),
   matchid  varchar(30) ,
   tickets  decimal(4,0) ,
   primary key (customer, matchid)
)

insert into enrollment values 
('Illinois' , 'dumi' ,  40), 
('Illinois' , 'joba' , 120), 
('Illinois' , 'kuba' , 180), 
('Illinois' , 'mibi' , 140), 
('Iowa'     , 'bijo' , 200), 
('Iowa'     , 'dubi' , 120), 
('Iowa'     , 'kubi' , 180), 
('Indiana'  , 'bijo' , 150), 
('Indiana'  , 'joba' , 100), 
('Indiana'  , 'joku' ,  60), 
('Indiana'  , 'mijo' , 210), 
('Michigan' , 'duba' , 300), 
('Michigan' , 'joku' , 240), 
('Michigan' , 'kudu' , 220), 
('Purdue'   , 'dubi' ,  80), 
('Purdue'   , 'miba' , 200), 
('Purdue'   , 'mibi' ,  60), 
('Purdue'   , 'mijo' , 120), 
('Minnesota', 'duba' ,  80), 
('Minnesota', 'dumi' , 120), 
('Minnesota', 'joku' ,  60), 
('Minnesota', 'kudu' ,  20)

    -> ;
Query OK, 22 rows affected (0.00 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> select * from enrollment;
+-----------+---------+---------+
| customer  | matchid | tickets |
+-----------+---------+---------+
| Illinois  | dumi    |      40 | 
| Illinois  | joba    |     120 | 
| Illinois  | kuba    |     180 | 
| Illinois  | mibi    |     140 | 
| Iowa      | bijo    |     200 | 
| Iowa      | dubi    |     120 | 
| Iowa      | kubi    |     180 | 
| Indiana   | bijo    |     150 | 
| Indiana   | joba    |     100 | 
| Indiana   | joku    |      60 | 
| Indiana   | mijo    |     210 | 
| Michigan  | duba    |     300 | 
| Michigan  | joku    |     240 | 
| Michigan  | kudu    |     220 | 
| Purdue    | dubi    |      80 | 
| Purdue    | miba    |     200 | 
| Purdue    | mibi    |      60 | 
| Purdue    | mijo    |     120 | 
| Minnesota | duba    |      80 | 
| Minnesota | dumi    |     120 | 
| Minnesota | joku    |      60 | 
| Minnesota | kudu    |      20 | 
+-----------+---------+---------+
22 rows in set (0.00 sec)

mysql> 

create table matches (
   matchnum    int primary key, 
   matchid     varchar(30), 
   round       varchar(30), 
   venue       varchar(30),  
   ticketPrice double     
)

insert into matches values
(  1      , 'joba'    , 'round one'   , 'Soldier Field        ' ,  5.50), 
(  2      , 'mibi'    , 'round one'   , 'Madison Square Garden' ,  8.00), 
(  3      , 'kudu'    , 'round one'   , 'Wrigley Field        ' ,  4.25), 
(  4      , 'duba'    , 'round two'   , 'Woodburn Hall        ' ,  6.50), 
(  5      , 'kubi'    , 'round two'   , 'Staples Center       ' ,  5.50), 
(  6      , 'mijo'    , 'round two'   , 'RCA Dome             ' ,  5.75), 
(  7      , 'bijo'    , 'round three' , 'Rawles Arena         ' ,  7.35), 
(  8      , 'kuba'    , 'round three' , 'Soldier Field        ' ,  8.00), 
(  9      , 'dumi'    , 'round three' , 'Madison Square Garden' ,  9.99), 
( 10      , 'miba'    , 'round four'  , 'Wrigley Field        ' ,  9.99), 
( 11      , 'dubi'    , 'round four'  , 'Woodburn Hall        ' ,  8.50), 
( 12      , 'joku'    , 'round four'  , 'Staples Center       ' , 12.00)

    -> ;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from matches;
+----------+---------+-------------+-----------------------+-------------+
| matchnum | matchid | round       | venue                 | ticketPrice |
+----------+---------+-------------+-----------------------+-------------+
|        1 | joba    | round one   | Soldier Field         |         5.5 | 
|        2 | mibi    | round one   | Madison Square Garden |           8 | 
|        3 | kudu    | round one   | Wrigley Field         |        4.25 | 
|        4 | duba    | round two   | Woodburn Hall         |         6.5 | 
|        5 | kubi    | round two   | Staples Center        |         5.5 | 
|        6 | mijo    | round two   | RCA Dome              |        5.75 | 
|        7 | bijo    | round three | Rawles Arena          |        7.35 | 
|        8 | kuba    | round three | Soldier Field         |           8 | 
|        9 | dumi    | round three | Madison Square Garden |        9.99 | 
|       10 | miba    | round four  | Wrigley Field         |        9.99 | 
|       11 | dubi    | round four  | Woodburn Hall         |         8.5 | 
|       12 | joku    | round four  | Staples Center        |          12 | 
+----------+---------+-------------+-----------------------+-------------+
12 rows in set (0.00 sec)

mysql> 

create table participation (
  username varchar(8) , 
  matchid  varchar(30), 
  points   double     ,
  primary key (username, matchid) 
) 

insert into participation values 

('cbarkley', 'duba' , 0.5 ), 
('cbarkley', 'joba' , 0   ), 
('cbarkley', 'kuba' , 0   ), 
('cbarkley', 'miba' , 0.5 ), 
('lbird'   , 'bijo' , 1   ), 
('lbird'   , 'dubi' , 1   ), 
('lbird'   , 'kubi' , 1   ), 
('lbird'   , 'mibi' , 1   ), 
('mjordan' , 'bijo' , 0   ), 
('mjordan' , 'joba' , 1   ), 
('mjordan' , 'joku' , 1   ), 
('mjordan' , 'mijo' , 0.5 ), 
('rmiller' , 'dumi' , 0.5 ), 
('rmiller' , 'miba' , 0.5 ), 
('rmiller' , 'mibi' , 0   ), 
('rmiller' , 'mijo' , 0.5 ), 
('tduncan' , 'duba' , 0.5 ), 
('tduncan' , 'dubi' , 0   ), 
('tduncan' , 'dumi' , 0.5 ), 
('tduncan' , 'kudu' , 0.5 ), 
('tkukoc'  , 'joku' , 0   ), 
('tkukoc'  , 'kuba' , 1   ), 
('tkukoc'  , 'kubi' , 0   ), 
('tkukoc'  , 'kudu' , 0.5 ) 


    -> ;
Query OK, 24 rows affected (0.01 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql> select * from participation;
+----------+---------+--------+
| username | matchid | points |
+----------+---------+--------+
| cbarkley | duba    |    0.5 | 
| cbarkley | joba    |      0 | 
| cbarkley | kuba    |      0 | 
| cbarkley | miba    |    0.5 | 
| lbird    | bijo    |      1 | 
| lbird    | dubi    |      1 | 
| lbird    | kubi    |      1 | 
| lbird    | mibi    |      1 | 
| mjordan  | bijo    |      0 | 
| mjordan  | joba    |      1 | 
| mjordan  | joku    |      1 | 
| mjordan  | mijo    |    0.5 | 
| rmiller  | dumi    |    0.5 | 
| rmiller  | miba    |    0.5 | 
| rmiller  | mibi    |      0 | 
| rmiller  | mijo    |    0.5 | 
| tduncan  | duba    |    0.5 | 
| tduncan  | dubi    |      0 | 
| tduncan  | dumi    |    0.5 | 
| tduncan  | kudu    |    0.5 | 
| tkukoc   | joku    |      0 | 
| tkukoc   | kuba    |      1 | 
| tkukoc   | kubi    |      0 | 
| tkukoc   | kudu    |    0.5 | 
+----------+---------+--------+
24 rows in set (0.00 sec)

mysql>

create table players (              
    username   varchar(8) primary key,
    firstName  varchar(20),
    lastName   varchar(20),
    picture    varchar(120),
    address    varchar(120),
    city       varchar(30),
    state      varchar(2),
    playerRatePerMatch
               decimal(12, 2)
)  

insert into players values

('cbarkley', 'Charles'  , 'Barkley' , "", ""                    , 'Phoenix'     , 'AZ'   ,         450.00 ), 
('lbird'   , 'Larry'    , 'Bird'    , "", ""                    , 'Naples'      , 'FL'   ,         350.00 ),
('mjordan' , 'Michael'  , 'Jordan'  , "", ""                    , 'Chicago'     , 'IL'   ,         500.00 ),
('rmiller' , 'Reggie'   , 'Miller'  , "", ""                    , 'Indianapolis', 'IN'   ,         150.00 ),
('tduncan' , 'Tim'      , 'Duncan'  , "", ""                    , 'Houston'     , 'TX'   ,         210.00 ),
('tkukoc'  , 'Toni'     , 'Kukoc'   , "", "Petar Zrinski Street", 'New Zagreb'  , 'PA'   ,          50.00 )



    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

mysql>


create table spectators (
  customer varchar(30) primary key , 
  logourl  varchar(120), 
  address  varchar(120),
  city     varchar(30) ,
  state    varchar(2)  
) 

insert into spectators values
('Illinois'             , ''              , ''       , ''    , 'IL'),
('Iowa'                 , ''              , ''       , ''    , 'IO'),
('Indiana'              , ''              , ''       , ''    , 'IN'),
('Michigan'             , ''              , ''       , ''    , 'MI'),
('Purdue'               , ''              , ''       , ''    , 'IN'),
('UCLA'                 , ''              , ''       , ''    , 'CA'),
('Minnesota'            , ''              , ''       , ''    , 'MN')

    -> ;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from spectators;
+-----------+---------+---------+------+-------+
| customer  | logourl | address | city | state |
+-----------+---------+---------+------+-------+
| Illinois  |         |         |      | IL    | 
| Iowa      |         |         |      | IO    | 
| Indiana   |         |         |      | IN    | 
| Michigan  |         |         |      | MI    | 
| Purdue    |         |         |      | IN    | 
| UCLA      |         |         |      | CA    | 
| Minnesota |         |         |      | MN    | 
+-----------+---------+---------+------+-------+
7 rows in set (0.00 sec)

mysql>

create table venues 
(
  name     varchar(30) primary key, 
  address  varchar(120),
  city     varchar(30) ,
  state    varchar(2)  ,
  rent     double      ,
  capacity int
)

insert into venues values 

( 'Madison Square Garden', ''                             , 'New York'     , 'NY'   ,  1200.00    , 4000), 
( 'Rawles Arena'         , 'Math Magic Alley'             , 'Las Vegas'    , 'NV'   ,   740.00    , 3400), 
( 'RCA Dome'             , 'Hoosier Center Avenue'        , 'Indianapolis' , 'IN'   ,   230.00    , 1800), 
( 'Soldier Field'        , 'One Soldier Field Avenue'     , 'Chicago'      , 'IL'   ,   800.00    , 2800), 
( 'Staples Center'       , 'One Big Office Supplies Ave.' , 'Los Angeles'  , 'LA'   ,   500.00    , 3200), 
( 'Woodburn Hall'        , 'Woodburn Avenue'              , 'Atlanta'      , 'GA'   ,   600.00    , 2000), 
( 'Wrigley Field'        , 'Wrigley Shore Drive'          , 'Chicago'      , 'IL'   ,   800.00    , 1200)

mysql> select * from venues;
+-----------------------+------------------------------+--------------+-------+------+----------+
| name                  | address                      | city         | state | rent | capacity |
+-----------------------+------------------------------+--------------+-------+------+----------+
| Madison Square Garden |                              | New York     | NY    | 1200 |     4000 | 
| Rawles Arena          | Math Magic Alley             | Las Vegas    | NV    |  740 |     3400 | 
| RCA Dome              | Hoosier Center Avenue        | Indianapolis | IN    |  230 |     1800 | 
| Soldier Field         | One Soldier Field Avenue     | Chicago      | IL    |  800 |     2800 | 
| Staples Center        | One Big Office Supplies Ave. | Los Angeles  | LA    |  500 |     3200 | 
| Woodburn Hall         | Woodburn Avenue              | Atlanta      | GA    |  600 |     2000 | 
| Wrigley Field         | Wrigley Shore Drive          | Chicago      | IL    |  800 |     1200 | 
+-----------------------+------------------------------+--------------+-------+------+----------+
7 rows in set (0.00 sec)

mysql> 

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

mysql> describe players;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| username           | varchar(8)    | NO   | PRI |         |       | 
| firstName          | varchar(20)   | YES  |     | NULL    |       | 
| lastName           | varchar(20)   | YES  |     | NULL    |       | 
| picture            | varchar(120)  | YES  |     | NULL    |       | 
| address            | varchar(120)  | YES  |     | NULL    |       | 
| city               | varchar(30)   | YES  |     | NULL    |       | 
| state              | varchar(2)    | YES  |     | NULL    |       | 
| playerRatePerMatch | decimal(12,2) | YES  |     | NULL    |       | 
+--------------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select lastName, firstName, city, state, playerRatePerMatch from players;
+----------+-----------+--------------+-------+--------------------+
| lastName | firstName | city         | state | playerRatePerMatch |
+----------+-----------+--------------+-------+--------------------+
| Barkley  | Charles   | Phoenix      | AZ    |             450.00 | 
| Bird     | Larry     | Naples       | FL    |             350.00 | 
| Jordan   | Michael   | Chicago      | IL    |             500.00 | 
| Miller   | Reggie    | Indianapolis | IN    |             150.00 | 
| Duncan   | Tim       | Houston      | TX    |             210.00 | 
| Kukoc    | Toni      | New Zagreb   | PA    |              50.00 | 
+----------+-----------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

mysql> 

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.

mysql> show tables;
+-----------------+
| Tables_in_teddy |
+-----------------+
| enrollment      | 
| matches         | 
| participation   | 
| players         | 
| spectators      | 
| venues          | 
+-----------------+
6 rows in set (0.00 sec)

mysql> describe venues;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(30)  | NO   | PRI |         |       | 
| address  | varchar(120) | YES  |     | NULL    |       | 
| city     | varchar(30)  | YES  |     | NULL    |       | 
| state    | varchar(2)   | YES  |     | NULL    |       | 
| rent     | double       | YES  |     | NULL    |       | 
| capacity | int(11)      | YES  |     | NULL    |       | 
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select name, rent from venues order by rent desc;
+-----------------------+------+
| name                  | rent |
+-----------------------+------+
| Madison Square Garden | 1200 | 
| Soldier Field         |  800 | 
| Wrigley Field         |  800 | 
| Rawles Arena          |  740 | 
| Woodburn Hall         |  600 | 
| Staples Center        |  500 | 
| RCA Dome              |  230 | 
+-----------------------+------+
7 rows in set (0.00 sec)

mysql>

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

mysql> describe players;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| username           | varchar(8)    | NO   | PRI |         |       | 
| firstName          | varchar(20)   | YES  |     | NULL    |       | 
| lastName           | varchar(20)   | YES  |     | NULL    |       | 
| picture            | varchar(120)  | YES  |     | NULL    |       | 
| address            | varchar(120)  | YES  |     | NULL    |       | 
| city               | varchar(30)   | YES  |     | NULL    |       | 
| state              | varchar(2)    | YES  |     | NULL    |       | 
| playerRatePerMatch | decimal(12,2) | YES  |     | NULL    |       | 
+--------------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

mysql> select username, playerRatePerMatch from players where playerRatePerMatch >= 250;
+----------+--------------------+
| username | playerRatePerMatch |
+----------+--------------------+
| cbarkley |             450.00 | 
| lbird    |             350.00 | 
| mjordan  |             500.00 | 
+----------+--------------------+
3 rows in set (0.00 sec)

mysql> 

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

mysql> describe matches;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| matchnum    | int(11)     | NO   | PRI |         |       | 
| matchid     | varchar(30) | YES  |     | NULL    |       | 
| round       | varchar(30) | YES  |     | NULL    |       | 
| venue       | varchar(30) | YES  |     | NULL    |       | 
| ticketPrice | double      | YES  |     | NULL    |       | 
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> select * from matches;
+----------+---------+-------------+-----------------------+-------------+
| matchnum | matchid | round       | venue                 | ticketPrice |
+----------+---------+-------------+-----------------------+-------------+
|        1 | joba    | round one   | Soldier Field         |         5.5 | 
|        2 | mibi    | round one   | Madison Square Garden |           8 | 
|        3 | kudu    | round one   | Wrigley Field         |        4.25 | 
|        4 | duba    | round two   | Woodburn Hall         |         6.5 | 
|        5 | kubi    | round two   | Staples Center        |         5.5 | 
|        6 | mijo    | round two   | RCA Dome              |        5.75 | 
|        7 | bijo    | round three | Rawles Arena          |        7.35 | 
|        8 | kuba    | round three | Soldier Field         |           8 | 
|        9 | dumi    | round three | Madison Square Garden |        9.99 | 
|       10 | miba    | round four  | Wrigley Field         |        9.99 | 
|       11 | dubi    | round four  | Woodburn Hall         |         8.5 | 
|       12 | joku    | round four  | Staples Center        |          12 | 
+----------+---------+-------------+-----------------------+-------------+
12 rows in set (0.00 sec)

mysql> select matchid, ticketPrice from matches where ticketPrice >= 7.00;
+---------+-------------+
| matchid | ticketPrice |
+---------+-------------+
| mibi    |           8 | 
| bijo    |        7.35 | 
| kuba    |           8 | 
| dumi    |        9.99 | 
| miba    |        9.99 | 
| dubi    |         8.5 | 
| joku    |          12 | 
+---------+-------------+
7 rows in set (0.00 sec)

mysql>

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

mysql> describe enrollment;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| customer | varchar(25)  | NO   | PRI |         |       | 
| matchid  | varchar(30)  | NO   | PRI |         |       | 
| tickets  | decimal(4,0) | YES  |     | NULL    |       | 
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from enrollment;
+-----------+---------+---------+
| customer  | matchid | tickets |
+-----------+---------+---------+
| Illinois  | dumi    |      40 | 
| Illinois  | joba    |     120 | 
| Illinois  | kuba    |     180 | 
| Illinois  | mibi    |     140 | 
| Iowa      | bijo    |     200 | 
| Iowa      | dubi    |     120 | 
| Iowa      | kubi    |     180 | 
| Indiana   | bijo    |     150 | 
| Indiana   | joba    |     100 | 
| Indiana   | joku    |      60 | 
| Indiana   | mijo    |     210 | 
| Michigan  | duba    |     300 | 
| Michigan  | joku    |     240 | 
| Michigan  | kudu    |     220 | 
| Purdue    | dubi    |      80 | 
| Purdue    | miba    |     200 | 
| Purdue    | mibi    |      60 | 
| Purdue    | mijo    |     120 | 
| Minnesota | duba    |      80 | 
| Minnesota | dumi    |     120 | 
| Minnesota | joku    |      60 | 
| Minnesota | kudu    |      20 | 
+-----------+---------+---------+
22 rows in set (0.00 sec)

mysql> select matchid, sum(tickets) from enrollment group by matchid;
+---------+--------------+
| matchid | sum(tickets) |
+---------+--------------+
| bijo    |          350 | 
| duba    |          380 | 
| dubi    |          200 | 
| dumi    |          160 | 
| joba    |          220 | 
| joku    |          360 | 
| kuba    |          180 | 
| kubi    |          180 | 
| kudu    |          240 | 
| miba    |          200 | 
| mibi    |          200 | 
| mijo    |          330 | 
+---------+--------------+
12 rows in set (0.00 sec)

mysql> select matchid, sum(tickets) as total from enrollment group by matchid order by total desc;
+---------+-------+
| matchid | total |
+---------+-------+
| duba    |   380 | 
| joku    |   360 | 
| bijo    |   350 | 
| mijo    |   330 | 
| kudu    |   240 | 
| joba    |   220 | 
| mibi    |   200 | 
| dubi    |   200 | 
| miba    |   200 | 
| kubi    |   180 | 
| kuba    |   180 | 
| dumi    |   160 | 
+---------+-------+
12 rows in set (0.00 sec)

mysql> 

6. List the most expensive player(s)?

mysql> describe players;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| username           | varchar(8)    | NO   | PRI |         |       | 
| firstName          | varchar(20)   | YES  |     | NULL    |       | 
| lastName           | varchar(20)   | YES  |     | NULL    |       | 
| picture            | varchar(120)  | YES  |     | NULL    |       | 
| address            | varchar(120)  | YES  |     | NULL    |       | 
| city               | varchar(30)   | YES  |     | NULL    |       | 
| state              | varchar(2)    | YES  |     | NULL    |       | 
| playerRatePerMatch | decimal(12,2) | YES  |     | NULL    |       | 
+--------------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select max(playerRatePerMatch) from players; 
+-------------------------+
| max(playerRatePerMatch) |
+-------------------------+
|                  500.00 | 
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

mysql> select lastName, firstName from players where playerRatePerMatch = (select max(playerRatePerMatch) from players); 
+----------+-----------+
| lastName | firstName |
+----------+-----------+
| Jordan   | Michael   | 
+----------+-----------+
1 row in set (0.00 sec)

mysql> select lastName, firstName, playerRatePerMatch from players where playerRatePerMatch = (select max(playerRatePerMatch) from players); 
+----------+-----------+--------------------+
| lastName | firstName | playerRatePerMatch |
+----------+-----------+--------------------+
| Jordan   | Michael   |             500.00 | 
+----------+-----------+--------------------+
1 row in set (0.01 sec)

mysql>

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

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

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 * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             500.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             500.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

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

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

mysql> 

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

mysql> describe venues;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(30)  | NO   | PRI |         |       | 
| address  | varchar(120) | YES  |     | NULL    |       | 
| city     | varchar(30)  | YES  |     | NULL    |       | 
| state    | varchar(2)   | YES  |     | NULL    |       | 
| rent     | double       | YES  |     | NULL    |       | 
| capacity | int(11)      | YES  |     | NULL    |       | 
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select * from venues;
+-----------------------+------------------------------+--------------+-------+------+----------+
| name                  | address                      | city         | state | rent | capacity |
+-----------------------+------------------------------+--------------+-------+------+----------+
| Madison Square Garden |                              | New York     | NY    | 1200 |     4000 | 
| Rawles Arena          | Math Magic Alley             | Las Vegas    | NV    |  740 |     3400 | 
| RCA Dome              | Hoosier Center Avenue        | Indianapolis | IN    |  230 |     1800 | 
| Soldier Field         | One Soldier Field Avenue     | Chicago      | IL    |  800 |     2800 | 
| Staples Center        | One Big Office Supplies Ave. | Los Angeles  | LA    |  500 |     3200 | 
| Woodburn Hall         | Woodburn Avenue              | Atlanta      | GA    |  600 |     2000 | 
| Wrigley Field         | Wrigley Shore Drive          | Chicago      | IL    |  800 |     1200 | 
+-----------------------+------------------------------+--------------+-------+------+----------+
7 rows in set (0.00 sec)

mysql> select name, capacity from venues where capacity = (select max(capacity) from venues);
+-----------------------+----------+
| name                  | capacity |
+-----------------------+----------+
| Madison Square Garden |     4000 | 
+-----------------------+----------+
1 row in set (0.06 sec)

mysql> select name, capacity from venues where capacity = (select min(capacity) from venues);
+---------------+----------+
| name          | capacity |
+---------------+----------+
| Wrigley Field |     1200 | 
+---------------+----------+
1 row in set (0.00 sec)

mysql> 

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

Answer: we know what that means, by now.

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

mysql> describe matches;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| matchnum    | int(11)     | NO   | PRI |         |       | 
| matchid     | varchar(30) | YES  |     | NULL    |       | 
| round       | varchar(30) | YES  |     | NULL    |       | 
| venue       | varchar(30) | YES  |     | NULL    |       | 
| ticketPrice | double      | YES  |     | NULL    |       | 
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> describe venues;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(30)  | NO   | PRI |         |       | 
| address  | varchar(120) | YES  |     | NULL    |       | 
| city     | varchar(30)  | YES  |     | NULL    |       | 
| state    | varchar(2)   | YES  |     | NULL    |       | 
| rent     | double       | YES  |     | NULL    |       | 
| capacity | int(11)      | YES  |     | NULL    |       | 
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select matchid from matches;
+---------+
| matchid |
+---------+
| joba    | 
| mibi    | 
| kudu    | 
| duba    | 
| kubi    | 
| mijo    | 
| bijo    | 
| kuba    | 
| dumi    | 
| miba    | 
| dubi    | 
| joku    | 
+---------+
12 rows in set (0.00 sec)

mysql> select matchid, capacity from matches, venues where venue = name;
+---------+----------+
| matchid | capacity |
+---------+----------+
| joba    |     2800 | 
| mibi    |     4000 | 
| kudu    |     1200 | 
| duba    |     2000 | 
| kubi    |     3200 | 
| mijo    |     1800 | 
| bijo    |     3400 | 
| kuba    |     2800 | 
| dumi    |     4000 | 
| miba    |     1200 | 
| dubi    |     2000 | 
| joku    |     3200 | 
+---------+----------+
12 rows in set (0.00 sec)

mysql> select matchid, venue, capacity from matches, venues where venue = name;
+---------+-----------------------+----------+
| matchid | venue                 | capacity |
+---------+-----------------------+----------+
| joba    | Soldier Field         |     2800 | 
| mibi    | Madison Square Garden |     4000 | 
| kudu    | Wrigley Field         |     1200 | 
| duba    | Woodburn Hall         |     2000 | 
| kubi    | Staples Center        |     3200 | 
| mijo    | RCA Dome              |     1800 | 
| bijo    | Rawles Arena          |     3400 | 
| kuba    | Soldier Field         |     2800 | 
| dumi    | Madison Square Garden |     4000 | 
| miba    | Wrigley Field         |     1200 | 
| dubi    | Woodburn Hall         |     2000 | 
| joku    | Staples Center        |     3200 | 
+---------+-----------------------+----------+
12 rows in set (0.00 sec)

mysql> select matchid, venue, capacity from matches, venues where venue = name order by capacity;
+---------+-----------------------+----------+
| matchid | venue                 | capacity |
+---------+-----------------------+----------+
| miba    | Wrigley Field         |     1200 | 
| kudu    | Wrigley Field         |     1200 | 
| mijo    | RCA Dome              |     1800 | 
| dubi    | Woodburn Hall         |     2000 | 
| duba    | Woodburn Hall         |     2000 | 
| joba    | Soldier Field         |     2800 | 
| kuba    | Soldier Field         |     2800 | 
| joku    | Staples Center        |     3200 | 
| kubi    | Staples Center        |     3200 | 
| bijo    | Rawles Arena          |     3400 | 
| dumi    | Madison Square Garden |     4000 | 
| mibi    | Madison Square Garden |     4000 | 
+---------+-----------------------+----------+
12 rows in set (0.00 sec)

mysql> select matchid, venue, capacity from matches, venues where venue = name order by capacity desc;
+---------+-----------------------+----------+
| matchid | venue                 | capacity |
+---------+-----------------------+----------+
| dumi    | Madison Square Garden |     4000 | 
| mibi    | Madison Square Garden |     4000 | 
| bijo    | Rawles Arena          |     3400 | 
| joku    | Staples Center        |     3200 | 
| kubi    | Staples Center        |     3200 | 
| joba    | Soldier Field         |     2800 | 
| kuba    | Soldier Field         |     2800 | 
| dubi    | Woodburn Hall         |     2000 | 
| duba    | Woodburn Hall         |     2000 | 
| mijo    | RCA Dome              |     1800 | 
| miba    | Wrigley Field         |     1200 | 
| kudu    | Wrigley Field         |     1200 | 
+---------+-----------------------+----------+
12 rows in set (0.00 sec)

mysqlgt; select matches.matchid, venue, tickets, capacity from matches, venues, enrollment where venue = name and enrollment.matchid = matches.matchid order by capacity desc;
+---------+-----------------------+---------+----------+
| matchid | venue                 | tickets | capacity |
+---------+-----------------------+---------+----------+
| mibi    | Madison Square Garden |     140 |     4000 | 
| dumi    | Madison Square Garden |      40 |     4000 | 
| dumi    | Madison Square Garden |     120 |     4000 | 
| mibi    | Madison Square Garden |      60 |     4000 | 
| bijo    | Rawles Arena          |     150 |     3400 | 
| bijo    | Rawles Arena          |     200 |     3400 | 
| joku    | Staples Center        |     240 |     3200 | 
| joku    | Staples Center        |      60 |     3200 | 
| joku    | Staples Center        |      60 |     3200 | 
| kubi    | Staples Center        |     180 |     3200 | 
| joba    | Soldier Field         |     100 |     2800 | 
| joba    | Soldier Field         |     120 |     2800 | 
| kuba    | Soldier Field         |     180 |     2800 | 
| dubi    | Woodburn Hall         |      80 |     2000 | 
| duba    | Woodburn Hall         |      80 |     2000 | 
| duba    | Woodburn Hall         |     300 |     2000 | 
| dubi    | Woodburn Hall         |     120 |     2000 | 
| mijo    | RCA Dome              |     210 |     1800 | 
| mijo    | RCA Dome              |     120 |     1800 | 
| kudu    | Wrigley Field         |      20 |     1200 | 
| miba    | Wrigley Field         |     200 |     1200 | 
| kudu    | Wrigley Field         |     220 |     1200 | 
+---------+-----------------------+---------+----------+
22 rows in set (0.00 sec)

mysql> select matches.matchid, venue, tickets, capacity from matches, venues, enrollment where venue = name and enrollment.matchid = matches.matchid order by tickets/capacity desc;
+---------+-----------------------+---------+----------+
| matchid | venue                 | tickets | capacity |
+---------+-----------------------+---------+----------+
| kudu    | Wrigley Field         |     220 |     1200 | 
| miba    | Wrigley Field         |     200 |     1200 | 
| duba    | Woodburn Hall         |     300 |     2000 | 
| mijo    | RCA Dome              |     210 |     1800 | 
| joku    | Staples Center        |     240 |     3200 | 
| mijo    | RCA Dome              |     120 |     1800 | 
| kuba    | Soldier Field         |     180 |     2800 | 
| dubi    | Woodburn Hall         |     120 |     2000 | 
| bijo    | Rawles Arena          |     200 |     3400 | 
| kubi    | Staples Center        |     180 |     3200 | 
| bijo    | Rawles Arena          |     150 |     3400 | 
| joba    | Soldier Field         |     120 |     2800 | 
| dubi    | Woodburn Hall         |      80 |     2000 | 
| duba    | Woodburn Hall         |      80 |     2000 | 
| joba    | Soldier Field         |     100 |     2800 | 
| mibi    | Madison Square Garden |     140 |     4000 | 
| dumi    | Madison Square Garden |     120 |     4000 | 
| joku    | Staples Center        |      60 |     3200 | 
| joku    | Staples Center        |      60 |     3200 | 
| kudu    | Wrigley Field         |      20 |     1200 | 
| mibi    | Madison Square Garden |      60 |     4000 | 
| dumi    | Madison Square Garden |      40 |     4000 | 
+---------+-----------------------+---------+----------+
22 rows in set (0.00 sec)

mysql> select matches.matchid, venue, tickets, capacity, tickets / capacity as percent from matches, venues, enrollment where venue = name and enrollment.matchid = matches.matchid order by percent desc;
+---------+-----------------------+---------+----------+---------+
| matchid | venue                 | tickets | capacity | percent |
+---------+-----------------------+---------+----------+---------+
| kudu    | Wrigley Field         |     220 |     1200 |  0.1833 | 
| miba    | Wrigley Field         |     200 |     1200 |  0.1667 | 
| duba    | Woodburn Hall         |     300 |     2000 |  0.1500 | 
| mijo    | RCA Dome              |     210 |     1800 |  0.1167 | 
| joku    | Staples Center        |     240 |     3200 |  0.0750 | 
| mijo    | RCA Dome              |     120 |     1800 |  0.0667 | 
| kuba    | Soldier Field         |     180 |     2800 |  0.0643 | 
| dubi    | Woodburn Hall         |     120 |     2000 |  0.0600 | 
| bijo    | Rawles Arena          |     200 |     3400 |  0.0588 | 
| kubi    | Staples Center        |     180 |     3200 |  0.0563 | 
| bijo    | Rawles Arena          |     150 |     3400 |  0.0441 | 
| joba    | Soldier Field         |     120 |     2800 |  0.0429 | 
| dubi    | Woodburn Hall         |      80 |     2000 |  0.0400 | 
| duba    | Woodburn Hall         |      80 |     2000 |  0.0400 | 
| joba    | Soldier Field         |     100 |     2800 |  0.0357 | 
| mibi    | Madison Square Garden |     140 |     4000 |  0.0350 | 
| dumi    | Madison Square Garden |     120 |     4000 |  0.0300 | 
| joku    | Staples Center        |      60 |     3200 |  0.0188 | 
| joku    | Staples Center        |      60 |     3200 |  0.0188 | 
| kudu    | Wrigley Field         |      20 |     1200 |  0.0167 | 
| mibi    | Madison Square Garden |      60 |     4000 |  0.0150 | 
| dumi    | Madison Square Garden |      40 |     4000 |  0.0100 | 
+---------+-----------------------+---------+----------+---------+
22 rows in set (0.00 sec)

mysql>

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

We almost did this when we discussed the previous query.

mysql> 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);
+---------+-----------------------+---------+----------+---------+
| matchid | venue                 | tickets | capacity | percent |
+---------+-----------------------+---------+----------+---------+
| dumi    | Madison Square Garden |      40 |     4000 |  0.0100 | 
+---------+-----------------------+---------+----------+---------+
1 row in set (0.00 sec)

mysql> 

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

mysql> describe players;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| username           | varchar(8)    | NO   | PRI |         |       | 
| firstName          | varchar(20)   | YES  |     | NULL    |       | 
| lastName           | varchar(20)   | YES  |     | NULL    |       | 
| picture            | varchar(120)  | YES  |     | NULL    |       | 
| address            | varchar(120)  | YES  |     | NULL    |       | 
| city               | varchar(30)   | YES  |     | NULL    |       | 
| state              | varchar(2)    | YES  |     | NULL    |       | 
| playerRatePerMatch | decimal(12,2) | YES  |     | NULL    |       | 
+--------------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

mysql> select * from matches;
+----------+---------+-------------+-----------------------+-------------+
| matchnum | matchid | round       | venue                 | ticketPrice |
+----------+---------+-------------+-----------------------+-------------+
|        1 | joba    | round one   | Soldier Field         |         5.5 | 
|        2 | mibi    | round one   | Madison Square Garden |           8 | 
|        3 | kudu    | round one   | Wrigley Field         |        4.25 | 
|        4 | duba    | round two   | Woodburn Hall         |         6.5 | 
|        5 | kubi    | round two   | Staples Center        |         5.5 | 
|        6 | mijo    | round two   | RCA Dome              |        5.75 | 
|        7 | bijo    | round three | Rawles Arena          |        7.35 | 
|        8 | kuba    | round three | Soldier Field         |           8 | 
|        9 | dumi    | round three | Madison Square Garden |        9.99 | 
|       10 | miba    | round four  | Wrigley Field         |        9.99 | 
|       11 | dubi    | round four  | Woodburn Hall         |         8.5 | 
|       12 | joku    | round four  | Staples Center        |          12 | 
+----------+---------+-------------+-----------------------+-------------+
12 rows in set (0.00 sec)

mysql> select * from participation;
+----------+---------+--------+
| username | matchid | points |
+----------+---------+--------+
| cbarkley | duba    |    0.5 | 
| cbarkley | joba    |      0 | 
| cbarkley | kuba    |      0 | 
| cbarkley | miba    |    0.5 | 
| lbird    | bijo    |      1 | 
| lbird    | dubi    |      1 | 
| lbird    | kubi    |      1 | 
| lbird    | mibi    |      1 | 
| mjordan  | bijo    |      0 | 
| mjordan  | joba    |      1 | 
| mjordan  | joku    |      1 | 
| mjordan  | mijo    |    0.5 | 
| rmiller  | dumi    |    0.5 | 
| rmiller  | miba    |    0.5 | 
| rmiller  | mibi    |      0 | 
| rmiller  | mijo    |    0.5 | 
| tduncan  | duba    |    0.5 | 
| tduncan  | dubi    |      0 | 
| tduncan  | dumi    |    0.5 | 
| tduncan  | kudu    |    0.5 | 
| tkukoc   | joku    |      0 | 
| tkukoc   | kuba    |      1 | 
| tkukoc   | kubi    |      0 | 
| tkukoc   | kudu    |    0.5 | 
+----------+---------+--------+
24 rows in set (0.00 sec)

mysql> select matches.matchid, lastName, firstName, playerRatePerMatch 
    -> from players, participation, matches 
    -> where players.username = participation.username and 
    ->       participation.matchid = matches.matchid;
+---------+----------+-----------+--------------------+
| matchid | lastName | firstName | playerRatePerMatch |
+---------+----------+-----------+--------------------+
| joba    | Barkley  | Charles   |             450.00 | 
| joba    | Jordan   | Michael   |             500.00 | 
| mibi    | Bird     | Larry     |             350.00 | 
| mibi    | Miller   | Reggie    |             150.00 | 
| kudu    | Duncan   | Tim       |             210.00 | 
| kudu    | Kukoc    | Toni      |              50.00 | 
| duba    | Barkley  | Charles   |             450.00 | 
| duba    | Duncan   | Tim       |             210.00 | 
| kubi    | Bird     | Larry     |             350.00 | 
| kubi    | Kukoc    | Toni      |              50.00 | 
| mijo    | Jordan   | Michael   |             500.00 | 
| mijo    | Miller   | Reggie    |             150.00 | 
| bijo    | Bird     | Larry     |             350.00 | 
| bijo    | Jordan   | Michael   |             500.00 | 
| kuba    | Barkley  | Charles   |             450.00 | 
| kuba    | Kukoc    | Toni      |              50.00 | 
| dumi    | Miller   | Reggie    |             150.00 | 
| dumi    | Duncan   | Tim       |             210.00 | 
| miba    | Barkley  | Charles   |             450.00 | 
| miba    | Miller   | Reggie    |             150.00 | 
| dubi    | Bird     | Larry     |             350.00 | 
| dubi    | Duncan   | Tim       |             210.00 | 
| joku    | Jordan   | Michael   |             500.00 | 
| joku    | Kukoc    | Toni      |              50.00 | 
+---------+----------+-----------+--------------------+
24 rows in set (0.00 sec)

mysql> select matches.round, lastName, firstName, playerRatePerMatch 
    -> from players, participation, matches 
    -> where players.username = participation.username and 
    ->       participation.matchid = matches.matchid;
+-------------+----------+-----------+--------------------+
| round       | lastName | firstName | playerRatePerMatch |
+-------------+----------+-----------+--------------------+
| round one   | Barkley  | Charles   |             450.00 | 
| round one   | Jordan   | Michael   |             500.00 | 
| round one   | Bird     | Larry     |             350.00 | 
| round one   | Miller   | Reggie    |             150.00 | 
| round one   | Duncan   | Tim       |             210.00 | 
| round one   | Kukoc    | Toni      |              50.00 | 
| round two   | Barkley  | Charles   |             450.00 | 
| round two   | Duncan   | Tim       |             210.00 | 
| round two   | Bird     | Larry     |             350.00 | 
| round two   | Kukoc    | Toni      |              50.00 | 
| round two   | Jordan   | Michael   |             500.00 | 
| round two   | Miller   | Reggie    |             150.00 | 
| round three | Bird     | Larry     |             350.00 | 
| round three | Jordan   | Michael   |             500.00 | 
| round three | Barkley  | Charles   |             450.00 | 
| round three | Kukoc    | Toni      |              50.00 | 
| round three | Miller   | Reggie    |             150.00 | 
| round three | Duncan   | Tim       |             210.00 | 
| round four  | Barkley  | Charles   |             450.00 | 
| round four  | Miller   | Reggie    |             150.00 | 
| round four  | Bird     | Larry     |             350.00 | 
| round four  | Duncan   | Tim       |             210.00 | 
| round four  | Jordan   | Michael   |             500.00 | 
| round four  | Kukoc    | Toni      |              50.00 | 
+-------------+----------+-----------+--------------------+
24 rows in set (0.00 sec)

mysql> select matches.round, matches.matchid, lastName, firstName, playerRatePerMatch 
    -> from players, participation, matches 
    -> where players.username = participation.username and 
    ->       participation.matchid = matches.matchid;
+-------------+---------+----------+-----------+--------------------+
| round       | matchid | lastName | firstName | playerRatePerMatch |
+-------------+---------+----------+-----------+--------------------+
| round one   | joba    | Barkley  | Charles   |             450.00 | 
| round one   | joba    | Jordan   | Michael   |             500.00 | 
| round one   | mibi    | Bird     | Larry     |             350.00 | 
| round one   | mibi    | Miller   | Reggie    |             150.00 | 
| round one   | kudu    | Duncan   | Tim       |             210.00 | 
| round one   | kudu    | Kukoc    | Toni      |              50.00 | 
| round two   | duba    | Barkley  | Charles   |             450.00 | 
| round two   | duba    | Duncan   | Tim       |             210.00 | 
| round two   | kubi    | Bird     | Larry     |             350.00 | 
| round two   | kubi    | Kukoc    | Toni      |              50.00 | 
| round two   | mijo    | Jordan   | Michael   |             500.00 | 
| round two   | mijo    | Miller   | Reggie    |             150.00 | 
| round three | bijo    | Bird     | Larry     |             350.00 | 
| round three | bijo    | Jordan   | Michael   |             500.00 | 
| round three | kuba    | Barkley  | Charles   |             450.00 | 
| round three | kuba    | Kukoc    | Toni      |              50.00 | 
| round three | dumi    | Miller   | Reggie    |             150.00 | 
| round three | dumi    | Duncan   | Tim       |             210.00 | 
| round four  | miba    | Barkley  | Charles   |             450.00 | 
| round four  | miba    | Miller   | Reggie    |             150.00 | 
| round four  | dubi    | Bird     | Larry     |             350.00 | 
| round four  | dubi    | Duncan   | Tim       |             210.00 | 
| round four  | joku    | Jordan   | Michael   |             500.00 | 
| round four  | joku    | Kukoc    | Toni      |              50.00 | 
+-------------+---------+----------+-----------+--------------------+
24 rows in set (0.00 sec)

mysql> select matches.round, sum(playerRatePerMatch) 
    -> from players, participation, matches 
    -> where players.username = participation.username and 
    ->       participation.matchid = matches.matchid 
    -> group by matches.round;
+-------------+-------------------------+
| round       | sum(playerRatePerMatch) |
+-------------+-------------------------+
| round four  |                 1710.00 | 
| round one   |                 1710.00 | 
| round three |                 1710.00 | 
| round two   |                 1710.00 | 
+-------------+-------------------------+
4 rows in set (0.00 sec)

mysql>

You see why this is so: players charge the same regardless of the matchup.

But the query we wrote would be able to handle variations in players' rate per match, should there be any.

Here's another interesting query:

mysql> select matches.round, matches.matchnum, matches.matchid, a.username, b.username, a.points, b.points
    -> from matches, participation as a, participation as b
    -> where a.username <> b.username and
    ->       a.matchid = b.matchid and
    ->       matches.matchid = a.matchid
    -> order by matches.matchnum asc; 

+-------------+----------+---------+----------+----------+--------+--------+
| round       | matchnum | matchid | username | username | points | points |
+-------------+----------+---------+----------+----------+--------+--------+
| round one   |        1 | joba    | cbarkley | mjordan  |      0 |      1 | 
| round one   |        1 | joba    | mjordan  | cbarkley |      1 |      0 | 
| round one   |        2 | mibi    | lbird    | rmiller  |      1 |      0 | 
| round one   |        2 | mibi    | rmiller  | lbird    |      0 |      1 | 
| round one   |        3 | kudu    | tkukoc   | tduncan  |    0.5 |    0.5 | 
| round one   |        3 | kudu    | tduncan  | tkukoc   |    0.5 |    0.5 | 
| round two   |        4 | duba    | cbarkley | tduncan  |    0.5 |    0.5 | 
| round two   |        4 | duba    | tduncan  | cbarkley |    0.5 |    0.5 | 
| round two   |        5 | kubi    | lbird    | tkukoc   |      1 |      0 | 
| round two   |        5 | kubi    | tkukoc   | lbird    |      0 |      1 | 
| round two   |        6 | mijo    | mjordan  | rmiller  |    0.5 |    0.5 | 
| round two   |        6 | mijo    | rmiller  | mjordan  |    0.5 |    0.5 | 
| round three |        7 | bijo    | mjordan  | lbird    |      0 |      1 | 
| round three |        7 | bijo    | lbird    | mjordan  |      1 |      0 | 
| round three |        8 | kuba    | cbarkley | tkukoc   |      0 |      1 | 
| round three |        8 | kuba    | tkukoc   | cbarkley |      1 |      0 | 
| round three |        9 | dumi    | tduncan  | rmiller  |    0.5 |    0.5 | 
| round three |        9 | dumi    | rmiller  | tduncan  |    0.5 |    0.5 | 
| round four  |       10 | miba    | rmiller  | cbarkley |    0.5 |    0.5 | 
| round four  |       10 | miba    | cbarkley | rmiller  |    0.5 |    0.5 | 
| round four  |       11 | dubi    | lbird    | tduncan  |      1 |      0 | 
| round four  |       11 | dubi    | tduncan  | lbird    |      0 |      1 | 
| round four  |       12 | joku    | tkukoc   | mjordan  |      0 |      1 | 
| round four  |       12 | joku    | mjordan  | tkukoc   |      1 |      0 | 
+-------------+----------+---------+----------+----------+--------+--------+
24 rows in set (0.00 sec)

mysql> 

The question is: how do we eliminate the duplicates?

The answer is: we can't, we don't have information on which player should be listed first or second.

So we do this:

drop table participation;

create table participation (
   username varchar(8) ,
   matchid  varchar(30),
   points   double     ,
   host     enum ('first', 'last') ,
   primary key (username, matchid)
);

insert into participation values
  ('cbarkley', 'duba' , 0.5 , 'last'),
  ('cbarkley', 'joba' , 0   , 'last'),
  ('cbarkley', 'kuba' , 0   , 'last'), 
  ('cbarkley', 'miba' , 0.5 , 'last'), 
  ('lbird'   , 'bijo' , 1   , 'first'),
  ('lbird'   , 'dubi' , 1   , 'last'),
  ('lbird'   , 'kubi' , 1   , 'last'),
  ('lbird'   , 'mibi' , 1   , 'last'), 
  ('mjordan' , 'bijo' , 0   , 'last'), 
  ('mjordan' , 'joba' , 1   , 'first'),
  ('mjordan' , 'joku' , 1   , 'first'),
  ('mjordan' , 'mijo' , 0.5 , 'last'), 
  ('rmiller' , 'dumi' , 0.5 , 'last'), 
  ('rmiller' , 'miba' , 0.5 , 'first'),
  ('rmiller' , 'mibi' , 0   , 'first'),
  ('rmiller' , 'mijo' , 0.5 , 'first'),
  ('tduncan' , 'duba' , 0.5 , 'first'),
  ('tduncan' , 'dubi' , 0   , 'first'),
  ('tduncan' , 'dumi' , 0.5 , 'first'),
  ('tduncan' , 'kudu' , 0.5 , 'last'), 
  ('tkukoc'  , 'joku' , 0   , 'last'), 
  ('tkukoc'  , 'kuba' , 1   , 'first'),
  ('tkukoc'  , 'kubi' , 0   , 'first'),
  ('tkukoc'  , 'kudu' , 0.5 , 'first');

And now we have:

mysql> select matches.round, matches.matchnum, matches.matchid, a.username, b.username, a.points, b.points       
    -> from matches, participation as a, participation as b       
    -> where a.username <> b.username and       
    ->       a.host = 'first' and
    ->       a.matchid = b.matchid and         
    ->       matches.matchid = a.matchid    
    -> order by matches.matchnum asc;

                                                                                [ Wrote 8 lines ]

    -> ;
+-------------+----------+---------+----------+----------+--------+--------+
| round       | matchnum | matchid | username | username | points | points |
+-------------+----------+---------+----------+----------+--------+--------+
| round one   |        1 | joba    | mjordan  | cbarkley |      1 |      0 | 
| round one   |        2 | mibi    | rmiller  | lbird    |      0 |      1 | 
| round one   |        3 | kudu    | tkukoc   | tduncan  |    0.5 |    0.5 | 
| round two   |        4 | duba    | tduncan  | cbarkley |    0.5 |    0.5 | 
| round two   |        5 | kubi    | tkukoc   | lbird    |      0 |      1 | 
| round two   |        6 | mijo    | rmiller  | mjordan  |    0.5 |    0.5 | 
| round three |        7 | bijo    | lbird    | mjordan  |      1 |      0 | 
| round three |        8 | kuba    | tkukoc   | cbarkley |      1 |      0 | 
| round three |        9 | dumi    | tduncan  | rmiller  |    0.5 |    0.5 | 
| round four  |       10 | miba    | rmiller  | cbarkley |    0.5 |    0.5 | 
| round four  |       11 | dubi    | tduncan  | lbird    |      0 |      1 | 
| round four  |       12 | joku    | mjordan  | tkukoc   |      1 |      0 | 
+-------------+----------+---------+----------+----------+--------+--------+
12 rows in set (0.00 sec)

Now: how do we replace the usernames with their last names?

mysql> select x.lastName, y.lastName, c.pointsFirst, c.pointsLast, c.round, c.matchnum, c.matchid
    -> from (select matches.round, matches.matchnum, matches.matchid, a.username as first, b.username as last, a.points as pointsFirst, b.points as pointsLast
    ->       from matches, participation as a, participation as b
    ->       where a.username <> b.username and
    ->             a.host = 'first' and
    ->             a.matchid = b.matchid and
    ->             matches.matchid = a.matchid) as c, players as x, players as y
    -> where x.username = c.first and y.username = c.last
    -> order by c.matchnum asc;
+----------+----------+-------------+------------+-------------+----------+---------+
| lastName | lastName | pointsFirst | pointsLast | round       | matchnum | matchid |
+----------+----------+-------------+------------+-------------+----------+---------+
| Jordan   | Barkley  |           1 |          0 | round one   |        1 | joba    | 
| Miller   | Bird     |           0 |          1 | round one   |        2 | mibi    | 
| Kukoc    | Duncan   |         0.5 |        0.5 | round one   |        3 | kudu    | 
| Duncan   | Barkley  |         0.5 |        0.5 | round two   |        4 | duba    | 
| Kukoc    | Bird     |           0 |          1 | round two   |        5 | kubi    | 
| Miller   | Jordan   |         0.5 |        0.5 | round two   |        6 | mijo    | 
| Bird     | Jordan   |           1 |          0 | round three |        7 | bijo    | 
| Kukoc    | Barkley  |           1 |          0 | round three |        8 | kuba    | 
| Duncan   | Miller   |         0.5 |        0.5 | round three |        9 | dumi    | 
| Miller   | Barkley  |         0.5 |        0.5 | round four  |       10 | miba    | 
| Duncan   | Bird     |           0 |          1 | round four  |       11 | dubi    | 
| Jordan   | Kukoc    |           1 |          0 | round four  |       12 | joku    | 
+----------+----------+-------------+------------+-------------+----------+---------+
12 rows in set (0.00 sec)

mysql> 

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

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

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

mysql> select participation.username, playerRatePerMatch from players, participation where players.username = participation.username;
+----------+--------------------+
| username | playerRatePerMatch |
+----------+--------------------+
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
+----------+--------------------+
24 rows in set (0.00 sec)

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
6 rows in set (0.00 sec)

mysql> insert into players (username, playerRatePerMatch) values ('djwhite', 180);
Query OK, 1 row affected (0.02 sec)

mysql> select * from players;
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| username | firstName | lastName | picture | address              | city         | state | playerRatePerMatch |
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
| cbarkley | Charles   | Barkley  |         |                      | Phoenix      | AZ    |             450.00 | 
| lbird    | Larry     | Bird     |         |                      | Naples       | FL    |             350.00 | 
| mjordan  | Michael   | Jordan   |         |                      | Chicago      | IL    |             500.00 | 
| rmiller  | Reggie    | Miller   |         |                      | Indianapolis | IN    |             150.00 | 
| tduncan  | Tim       | Duncan   |         |                      | Houston      | TX    |             210.00 | 
| tkukoc   | Toni      | Kukoc    |         | Petar Zrinski Street | New Zagreb   | PA    |              50.00 | 
| djwhite  | NULL      | NULL     | NULL    | NULL                 | NULL         | NULL  |             180.00 | 
+----------+-----------+----------+---------+----------------------+--------------+-------+--------------------+
7 rows in set (0.00 sec)

mysql> select participation.username, playerRatePerMatch from players, participation where players.username = participation.username;
+----------+--------------------+
| username | playerRatePerMatch |
+----------+--------------------+
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
+----------+--------------------+
24 rows in set (0.00 sec)

mysql> select participation.username, avg(playerRatePerMatch) 
    -> from players, participation 
    -> where players.username = participation.username 
    -> group by participation.username;
+----------+-------------------------+
| username | avg(playerRatePerMatch) |
+----------+-------------------------+
| cbarkley |              450.000000 | 
| lbird    |              350.000000 | 
| mjordan  |              500.000000 | 
| rmiller  |              150.000000 | 
| tduncan  |              210.000000 | 
| tkukoc   |               50.000000 | 
+----------+-------------------------+
6 rows in set (0.00 sec)

mysql> 

So, it looks like the answer to the second question is: no.

The query above calculate the average cost of each player (one average per player).

For the overall average of all players (one average only) we have the following simpler query:

mysql> select participation.username, playerRatePerMatch from participation, players where participation.username = players.username;
+----------+--------------------+
| username | playerRatePerMatch |
+----------+--------------------+
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
+----------+--------------------+
24 rows in set (0.00 sec)

mysql> select avg(playerRatePerMatch) from participation, players where participation.username = players.username;
+-------------------------+
| avg(playerRatePerMatch) |
+-------------------------+
|              285.000000 | 
+-------------------------+
1 row in set (0.00 sec)

mysql> 

So it looks like we have to pay $570.00 per game, on average (both players).

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

mysql> select avg(playerRatePerMatch) 
    -> from participation, players 
    -> where participation.username = players.username;
+-------------------------+
| avg(playerRatePerMatch) |
+-------------------------+
|              285.000000 | 
+-------------------------+
1 row in set (0.00 sec)

mysql> select username, playerRatePerMatch 
    -> from players 
    -> where playerRatePerMatch <= (select avg(playerRatePerMatch) 
    ->                              from participation, players 
    ->                              where participation.username = players.username);
+----------+--------------------+
| username | playerRatePerMatch |
+----------+--------------------+
| rmiller  |             150.00 | 
| tduncan  |             210.00 | 
| tkukoc   |              50.00 | 
| djwhite  |             180.00 | 
+----------+--------------------+
4 rows in set (0.00 sec)

mysql> select username, playerRatePerMatch 
    -> from players 
    -> where playerRatePerMatch >= (select avg(playerRatePerMatch) from participation, players where participation.username = players.username);
+----------+--------------------+
| username | playerRatePerMatch |
+----------+--------------------+
| cbarkley |             450.00 | 
| lbird    |             350.00 | 
| mjordan  |             500.00 | 
+----------+--------------------+
3 rows in set (0.00 sec)

mysql> 

I didn't follow the suggestion in parens, although I could've (and things would have turned out the same).

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

mysql> select count(username) 
    -> from players 
    -> where playerRatePerMatch >= (select avg(playerRatePerMatch) 
    ->                              from participation, players 
    ->                              where participation.username = players.username);
+-----------------+
| count(username) |
+-----------------+
|               3 | 
+-----------------+
1 row in set (0.00 sec)

mysql> 

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

mysql> show tables;
+-----------------+
| Tables_in_teddy |
+-----------------+
| enrollment      | 
| matches         | 
| participation   | 
| players         | 
| spectators      | 
| venues          | 
+-----------------+
6 rows in set (0.00 sec)

mysql> select * from spectators;
+-----------+---------+---------+------+-------+
| customer  | logourl | address | city | state |
+-----------+---------+---------+------+-------+
| Illinois  |         |         |      | IL    | 
| Iowa      |         |         |      | IO    | 
| Indiana   |         |         |      | IN    | 
| Michigan  |         |         |      | MI    | 
| Purdue    |         |         |      | IN    | 
| UCLA      |         |         |      | CA    | 
| Minnesota |         |         |      | MN    | 
+-----------+---------+---------+------+-------+
7 rows in set (0.00 sec)

mysql> select * from enrollment;
+-----------+---------+---------+
| customer  | matchid | tickets |
+-----------+---------+---------+
| Illinois  | dumi    |      40 | 
| Illinois  | joba    |     120 | 
| Illinois  | kuba    |     180 | 
| Illinois  | mibi    |     140 | 
| Iowa      | bijo    |     200 | 
| Iowa      | dubi    |     120 | 
| Iowa      | kubi    |     180 | 
| Indiana   | bijo    |     150 | 
| Indiana   | joba    |     100 | 
| Indiana   | joku    |      60 | 
| Indiana   | mijo    |     210 | 
| Michigan  | duba    |     300 | 
| Michigan  | joku    |     240 | 
| Michigan  | kudu    |     220 | 
| Purdue    | dubi    |      80 | 
| Purdue    | miba    |     200 | 
| Purdue    | mibi    |      60 | 
| Purdue    | mijo    |     120 | 
| Minnesota | duba    |      80 | 
| Minnesota | dumi    |     120 | 
| Minnesota | joku    |      60 | 
| Minnesota | kudu    |      20 | 
+-----------+---------+---------+
22 rows in set (0.00 sec)

mysql> select spectators.customer, tickets 
    -> from spectators, enrollment 
    -> where spectators.customer = enrollment.customer;
+-----------+---------+
| customer  | tickets |
+-----------+---------+
| Illinois  |      40 | 
| Illinois  |     120 | 
| Illinois  |     180 | 
| Illinois  |     140 | 
| Indiana   |     150 | 
| Indiana   |     100 | 
| Indiana   |      60 | 
| Indiana   |     210 | 
| Iowa      |     200 | 
| Iowa      |     120 | 
| Iowa      |     180 | 
| Michigan  |     300 | 
| Michigan  |     240 | 
| Michigan  |     220 | 
| Minnesota |      80 | 
| Minnesota |     120 | 
| Minnesota |      60 | 
| Minnesota |      20 | 
| Purdue    |      80 | 
| Purdue    |     200 | 
| Purdue    |      60 | 
| Purdue    |     120 | 
+-----------+---------+
22 rows in set (0.00 sec)

mysql> select spectators.customer, matchid, tickets 
    -> from spectators, enrollment 
    -> where spectators.customer = enrollment.customer;
+-----------+---------+---------+
| customer  | matchid | tickets |
+-----------+---------+---------+
| Illinois  | dumi    |      40 | 
| Illinois  | joba    |     120 | 
| Illinois  | kuba    |     180 | 
| Illinois  | mibi    |     140 | 
| Indiana   | bijo    |     150 | 
| Indiana   | joba    |     100 | 
| Indiana   | joku    |      60 | 
| Indiana   | mijo    |     210 | 
| Iowa      | bijo    |     200 | 
| Iowa      | dubi    |     120 | 
| Iowa      | kubi    |     180 | 
| Michigan  | duba    |     300 | 
| Michigan  | joku    |     240 | 
| Michigan  | kudu    |     220 | 
| Minnesota | duba    |      80 | 
| Minnesota | dumi    |     120 | 
| Minnesota | joku    |      60 | 
| Minnesota | kudu    |      20 | 
| Purdue    | dubi    |      80 | 
| Purdue    | miba    |     200 | 
| Purdue    | mibi    |      60 | 
| Purdue    | mijo    |     120 | 
+-----------+---------+---------+
22 rows in set (0.00 sec)

mysql> select spectators.customer, count(matchid) 
    -> from spectators, enrollment 
    -> where spectators.customer = enrollment.customer 
    -> group by spectators.customer;
+-----------+----------------+
| customer  | count(matchid) |
+-----------+----------------+
| Illinois  |              4 | 
| Indiana   |              4 | 
| Iowa      |              3 | 
| Michigan  |              3 | 
| Minnesota |              4 | 
| Purdue    |              4 | 
+-----------+----------------+
6 rows in set (0.00 sec)

mysql> select spectators.customer, count(matchid) as howMany 
    -> from spectators, enrollment 
    -> where spectators.customer = enrollment.customer 
    -> group by spectators.customer 
    -> order by howMany desc;
+-----------+---------+
| customer  | howMany |
+-----------+---------+
| Purdue    |       4 | 
| Illinois  |       4 | 
| Indiana   |       4 | 
| Minnesota |       4 | 
| Iowa      |       3 | 
| Michigan  |       3 | 
+-----------+---------+
6 rows in set (0.00 sec)

mysql> 

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

mysql> select * from spectators;
+-----------+---------+---------+------+-------+
| customer  | logourl | address | city | state |
+-----------+---------+---------+------+-------+
| Illinois  |         |         |      | IL    | 
| Iowa      |         |         |      | IO    | 
| Indiana   |         |         |      | IN    | 
| Michigan  |         |         |      | MI    | 
| Purdue    |         |         |      | IN    | 
| UCLA      |         |         |      | CA    | 
| Minnesota |         |         |      | MN    | 
+-----------+---------+---------+------+-------+
7 rows in set (0.00 sec)

mysql> select * from enrollment;
+-----------+---------+---------+
| customer  | matchid | tickets |
+-----------+---------+---------+
| Illinois  | dumi    |      40 | 
| Illinois  | joba    |     120 | 
| Illinois  | kuba    |     180 | 
| Illinois  | mibi    |     140 | 
| Iowa      | bijo    |     200 | 
| Iowa      | dubi    |     120 | 
| Iowa      | kubi    |     180 | 
| Indiana   | bijo    |     150 | 
| Indiana   | joba    |     100 | 
| Indiana   | joku    |      60 | 
| Indiana   | mijo    |     210 | 
| Michigan  | duba    |     300 | 
| Michigan  | joku    |     240 | 
| Michigan  | kudu    |     220 | 
| Purdue    | dubi    |      80 | 
| Purdue    | miba    |     200 | 
| Purdue    | mibi    |      60 | 
| Purdue    | mijo    |     120 | 
| Minnesota | duba    |      80 | 
| Minnesota | dumi    |     120 | 
| Minnesota | joku    |      60 | 
| Minnesota | kudu    |      20 | 
+-----------+---------+---------+
22 rows in set (0.00 sec)

mysql> select spectators.customer, tickets 
    -> from spectators inner join enrollment on spectators.customer = enrollment.customer;
+-----------+---------+
| customer  | tickets |
+-----------+---------+
| Illinois  |      40 | 
| Illinois  |     120 | 
| Illinois  |     180 | 
| Illinois  |     140 | 
| Indiana   |     150 | 
| Indiana   |     100 | 
| Indiana   |      60 | 
| Indiana   |     210 | 
| Iowa      |     200 | 
| Iowa      |     120 | 
| Iowa      |     180 | 
| Michigan  |     300 | 
| Michigan  |     240 | 
| Michigan  |     220 | 
| Minnesota |      80 | 
| Minnesota |     120 | 
| Minnesota |      60 | 
| Minnesota |      20 | 
| Purdue    |      80 | 
| Purdue    |     200 | 
| Purdue    |      60 | 
| Purdue    |     120 | 
+-----------+---------+
22 rows in set (0.00 sec)

mysql> select state, spectators.customer, tickets 
    -> from spectators inner join enrollment on spectators.customer = enrollment.customer;
+-------+-----------+---------+
| state | customer  | tickets |
+-------+-----------+---------+
| IL    | Illinois  |      40 | 
| IL    | Illinois  |     120 | 
| IL    | Illinois  |     180 | 
| IL    | Illinois  |     140 | 
| IO    | Iowa      |     200 | 
| IO    | Iowa      |     120 | 
| IO    | Iowa      |     180 | 
| IN    | Indiana   |     150 | 
| IN    | Indiana   |     100 | 
| IN    | Indiana   |      60 | 
| IN    | Indiana   |     210 | 
| MI    | Michigan  |     300 | 
| MI    | Michigan  |     240 | 
| MI    | Michigan  |     220 | 
| IN    | Purdue    |      80 | 
| IN    | Purdue    |     200 | 
| IN    | Purdue    |      60 | 
| IN    | Purdue    |     120 | 
| MN    | Minnesota |      80 | 
| MN    | Minnesota |     120 | 
| MN    | Minnesota |      60 | 
| MN    | Minnesota |      20 | 
+-------+-----------+---------+
22 rows in set (0.00 sec)

mysql> select spectators.customer, count(tickets) 
    -> from spectators inner join enrollment on spectators.customer = enrollment.customer group by spectators.customer;
+-----------+----------------+
| customer  | count(tickets) |
+-----------+----------------+
| Illinois  |              4 | 
| Indiana   |              4 | 
| Iowa      |              3 | 
| Michigan  |              3 | 
| Minnesota |              4 | 
| Purdue    |              4 | 
+-----------+----------------+
6 rows in set (0.00 sec)

mysql> select spectators.customer, count(tickets) 
    -> from spectators left join enrollment on spectators.customer = enrollment.customer group by spectators.customer;
+-----------+----------------+
| customer  | count(tickets) |
+-----------+----------------+
| Illinois  |              4 | 
| Indiana   |              4 | 
| Iowa      |              3 | 
| Michigan  |              3 | 
| Minnesota |              4 | 
| Purdue    |              4 | 
| UCLA      |              0 | 
+-----------+----------------+
7 rows in set (0.00 sec)

mysql>

The main point was to get UCLA to show up as a very inactive spectator.

15. List the games attended by Indiana University students.

mysql> show tables;
+-----------------+
| Tables_in_teddy |
+-----------------+
| enrollment      | 
| matches         | 
| participation   | 
| players         | 
| spectators      | 
| venues          | 
+-----------------+
6 rows in set (0.01 sec)

mysql> select * from spectators;
+-----------+---------+---------+------+-------+
| customer  | logourl | address | city | state |
+-----------+---------+---------+------+-------+
| Illinois  |         |         |      | IL    | 
| Iowa      |         |         |      | IO    | 
| Indiana   |         |         |      | IN    | 
| Michigan  |         |         |      | MI    | 
| Purdue    |         |         |      | IN    | 
| UCLA      |         |         |      | CA    | 
| Minnesota |         |         |      | MN    | 
+-----------+---------+---------+------+-------+
7 rows in set (0.00 sec)

mysql> select * from enrollment;
+-----------+---------+---------+
| customer  | matchid | tickets |
+-----------+---------+---------+
| Illinois  | dumi    |      40 | 
| Illinois  | joba    |     120 | 
| Illinois  | kuba    |     180 | 
| Illinois  | mibi    |     140 | 
| Iowa      | bijo    |     200 | 
| Iowa      | dubi    |     120 | 
| Iowa      | kubi    |     180 | 
| Indiana   | bijo    |     150 | 
| Indiana   | joba    |     100 | 
| Indiana   | joku    |      60 | 
| Indiana   | mijo    |     210 | 
| Michigan  | duba    |     300 | 
| Michigan  | joku    |     240 | 
| Michigan  | kudu    |     220 | 
| Purdue    | dubi    |      80 | 
| Purdue    | miba    |     200 | 
| Purdue    | mibi    |      60 | 
| Purdue    | mijo    |     120 | 
| Minnesota | duba    |      80 | 
| Minnesota | dumi    |     120 | 
| Minnesota | joku    |      60 | 
| Minnesota | kudu    |      20 | 
+-----------+---------+---------+
22 rows in set (0.00 sec)

mysql> select * from matches where matchid in (select matchid from enrollment where customer = 'Indiana');
+----------+---------+-------------+-----------------------+-------------+
| matchnum | matchid | round       | venue                 | ticketPrice |
+----------+---------+-------------+-----------------------+-------------+
|        1 | joba    | round one   | Soldier Field         |         5.5 | 
|        6 | mijo    | round two   | RCA Dome              |        5.75 | 
|        7 | bijo    | round three | Rawles Arena          |        7.35 | 
|       12 | joku    | round four  | Staples Center        |          12 | 
+----------+---------+-------------+-----------------------+-------------+
4 rows in set (0.00 sec)

mysql> 

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

mysql> select tickets from enrollment where customer = 'Indiana';
+---------+
| tickets |
+---------+
|     150 | 
|     100 | 
|      60 | 
|     210 | 
+---------+
4 rows in set (0.00 sec)

mysql> select avg(tickets) from enrollment where customer = 'Indiana';
+--------------+
| avg(tickets) |
+--------------+
|     130.0000 | 
+--------------+
1 row in set (0.00 sec)

mysql> 

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

mysql> select * from enrollment;
+-----------+---------+---------+
| customer  | matchid | tickets |
+-----------+---------+---------+
| Illinois  | dumi    |      40 | 
| Illinois  | joba    |     120 | 
| Illinois  | kuba    |     180 | 
| Illinois  | mibi    |     140 | 
| Iowa      | bijo    |     200 | 
| Iowa      | dubi    |     120 | 
| Iowa      | kubi    |     180 | 
| Indiana   | bijo    |     150 | 
| Indiana   | joba    |     100 | 
| Indiana   | joku    |      60 | 
| Indiana   | mijo    |     210 | 
| Michigan  | duba    |     300 | 
| Michigan  | joku    |     240 | 
| Michigan  | kudu    |     220 | 
| Purdue    | dubi    |      80 | 
| Purdue    | miba    |     200 | 
| Purdue    | mibi    |      60 | 
| Purdue    | mijo    |     120 | 
| Minnesota | duba    |      80 | 
| Minnesota | dumi    |     120 | 
| Minnesota | joku    |      60 | 
| Minnesota | kudu    |      20 | 
+-----------+---------+---------+
22 rows in set (0.00 sec)

mysql> select * from matches where matchid in (select matchid from enrollment where customer = 'Indiana');
+----------+---------+-------------+-----------------------+-------------+
| matchnum | matchid | round       | venue                 | ticketPrice |
+----------+---------+-------------+-----------------------+-------------+
|        1 | joba    | round one   | Soldier Field         |         5.5 | 
|        6 | mijo    | round two   | RCA Dome              |        5.75 | 
|        7 | bijo    | round three | Rawles Arena          |        7.35 | 
|       12 | joku    | round four  | Staples Center        |          12 | 
+----------+---------+-------------+-----------------------+-------------+
4 rows in set (0.00 sec)

mysql> select tickets, ticketPrice from enrollment inner join matches on matches.matchid = enrollment.matchid where customer = 'Indiana';
+---------+-------------+
| tickets | ticketPrice |
+---------+-------------+
|     100 |         5.5 | 
|     210 |        5.75 | 
|     150 |        7.35 | 
|      60 |          12 | 
+---------+-------------+
4 rows in set (0.00 sec)

mysql> select tickets * ticketPrice from enrollment inner join matches on matches.matchid = enrollment.matchid where customer = 'Indiana';
+-----------------------+
| tickets * ticketPrice |
+-----------------------+
|                   550 | 
|                1207.5 | 
|                1102.5 | 
|                   720 | 
+-----------------------+
4 rows in set (0.00 sec)

mysql> select sum(tickets * ticketPrice) from enrollment inner join matches on matches.matchid = enrollment.matchid where customer = 'Indiana';
+----------------------------+
| sum(tickets * ticketPrice) |
+----------------------------+
|                       3580 | 
+----------------------------+
1 row in set (0.01 sec)

mysql> select customer, sum(tickets * ticketPrice) from enrollment inner join matches on matches.matchid = enrollment.matchid group by customer;
+-----------+----------------------------+
| customer  | sum(tickets * ticketPrice) |
+-----------+----------------------------+
| Illinois  |                     3619.6 | 
| Indiana   |                       3580 | 
| Iowa      |                       3480 | 
| Michigan  |                       5765 | 
| Minnesota |                     2523.8 | 
| Purdue    |                       3848 | 
+-----------+----------------------------+
6 rows in set (0.00 sec)

mysql> 

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

mysql> select * from participation;
+----------+---------+--------+-------+
| username | matchid | points | host  |
+----------+---------+--------+-------+
| cbarkley | duba    |    0.5 | last  | 
| cbarkley | joba    |      0 | last  | 
| cbarkley | kuba    |      0 | last  | 
| cbarkley | miba    |    0.5 | last  | 
| lbird    | bijo    |      1 | first | 
| lbird    | dubi    |      1 | last  | 
| lbird    | kubi    |      1 | last  | 
| lbird    | mibi    |      1 | last  | 
| mjordan  | bijo    |      0 | last  | 
| mjordan  | joba    |      1 | first | 
| mjordan  | joku    |      1 | first | 
| mjordan  | mijo    |    0.5 | last  | 
| rmiller  | dumi    |    0.5 | last  | 
| rmiller  | miba    |    0.5 | first | 
| rmiller  | mibi    |      0 | first | 
| rmiller  | mijo    |    0.5 | first | 
| tduncan  | duba    |    0.5 | first | 
| tduncan  | dubi    |      0 | first | 
| tduncan  | dumi    |    0.5 | first | 
| tduncan  | kudu    |    0.5 | last  | 
| tkukoc   | joku    |      0 | last  | 
| tkukoc   | kuba    |      1 | first | 
| tkukoc   | kubi    |      0 | first | 
| tkukoc   | kudu    |    0.5 | first | 
+----------+---------+--------+-------+
24 rows in set (0.00 sec)

mysql> select players.username, playerRatePerMatch 
    -> from players inner join participation on participation.username = players.username;
+----------+--------------------+
| username | playerRatePerMatch |
+----------+--------------------+
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| cbarkley |             450.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| lbird    |             350.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| mjordan  |             500.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| rmiller  |             150.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tduncan  |             210.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
| tkukoc   |              50.00 | 
+----------+--------------------+
24 rows in set (0.00 sec)

mysql> select players.username, playerRatePerMatch, round 
    -> from participation inner join players on participation.username = players.username 
    ->                    inner join matches on matches.matchid = participation.matchid;
+----------+--------------------+-------------+
| username | playerRatePerMatch | round       |
+----------+--------------------+-------------+
| cbarkley |             450.00 | round one   | 
| mjordan  |             500.00 | round one   | 
| lbird    |             350.00 | round one   | 
| rmiller  |             150.00 | round one   | 
| tduncan  |             210.00 | round one   | 
| tkukoc   |              50.00 | round one   | 
| cbarkley |             450.00 | round two   | 
| tduncan  |             210.00 | round two   | 
| lbird    |             350.00 | round two   | 
| tkukoc   |              50.00 | round two   | 
| mjordan  |             500.00 | round two   | 
| rmiller  |             150.00 | round two   | 
| lbird    |             350.00 | round three | 
| mjordan  |             500.00 | round three | 
| cbarkley |             450.00 | round three | 
| tkukoc   |              50.00 | round three | 
| rmiller  |             150.00 | round three | 
| tduncan  |             210.00 | round three | 
| cbarkley |             450.00 | round four  | 
| rmiller  |             150.00 | round four  | 
| lbird    |             350.00 | round four  | 
| tduncan  |             210.00 | round four  | 
| mjordan  |             500.00 | round four  | 
| tkukoc   |              50.00 | round four  | 
+----------+--------------------+-------------+
24 rows in set (0.00 sec)

mysql> select players.username, playerRatePerMatch, round 
    -> 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');
+----------+--------------------+-----------+
| username | playerRatePerMatch | round     |
+----------+--------------------+-----------+
| cbarkley |             450.00 | round one | 
| mjordan  |             500.00 | round one | 
| lbird    |             350.00 | round one | 
| rmiller  |             150.00 | round one | 
| tduncan  |             210.00 | round one | 
| tkukoc   |              50.00 | round one | 
| cbarkley |             450.00 | round two | 
| tduncan  |             210.00 | round two | 
| lbird    |             350.00 | round two | 
| tkukoc   |              50.00 | round two | 
| mjordan  |             500.00 | round two | 
| rmiller  |             150.00 | round two | 
+----------+--------------------+-----------+
12 rows in set (0.00 sec)

mysql> 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');
+-------------------------+
| sum(playerRatePerMatch) |
+-------------------------+
|                 3420.00 | 
+-------------------------+
1 row in set (0.00 sec)

mysql> 

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

mysql > SELECT sum( ticketPrice * tickets ), round                   
    -> FROM matches, enrollment
    -> WHERE enrollment.matchid = matches.matchid and round in ('round one', 'round three')
    -> GROUP BY round ;
+------------------------------+-------------+
| sum( ticketPrice * tickets ) | round       |
+------------------------------+-------------+
|                         3830 | round one   | 
|                       5610.9 | round three | 
+------------------------------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT sum( ticketPrice * tickets ), round 
    -> FROM matches, enrollment 
    -> WHERE enrollment.matchid = matches.matchid and round in ('round one', 'round three') 
    -> GROUP BY round;
+------------------------------+-------------+
| sum( ticketPrice * tickets ) | round       |
+------------------------------+-------------+
|                         3830 | round one   | 
|                       5610.9 | round three | 
+------------------------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from enrollment inner join matches on matches.matchid = enrollment.matchid and round = 'round one';
+-----------+---------+---------+----------+---------+-----------+-----------------------+-------------+
| customer  | matchid | tickets | matchnum | matchid | round     | venue                 | ticketPrice |
+-----------+---------+---------+----------+---------+-----------+-----------------------+-------------+
| Illinois  | joba    |     120 |        1 | joba    | round one | Soldier Field         |         5.5 | 
| Illinois  | mibi    |     140 |        2 | mibi    | round one | Madison Square Garden |           8 | 
| Indiana   | joba    |     100 |        1 | joba    | round one | Soldier Field         |         5.5 | 
| Michigan  | kudu    |     220 |        3 | kudu    | round one | Wrigley Field         |        4.25 | 
| Purdue    | mibi    |      60 |        2 | mibi    | round one | Madison Square Garden |           8 | 
| Minnesota | kudu    |      20 |        3 | kudu    | round one | Wrigley Field         |        4.25 | 
+-----------+---------+---------+----------+---------+-----------+-----------------------+-------------+
6 rows in set (0.00 sec)

mysql> select tickets * ticketPrice from enrollment inner join matches on matches.matchid = enrollment.matchid and round = 'round one';
+-----------------------+
| tickets * ticketPrice |
+-----------------------+
|                   660 | 
|                  1120 | 
|                   550 | 
|                   935 | 
|                   480 | 
|                    85 | 
+-----------------------+
6 rows in set (0.00 sec)

mysql> select sum(tickets * ticketPrice) from enrollment inner join matches on matches.matchid = enrollment.matchid and round = 'round one';
+----------------------------+
| sum(tickets * ticketPrice) |
+----------------------------+
|                       3830 | 
+----------------------------+
1 row in set (0.00 sec)

mysql> 

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

That's right: it doesn't matter, since what we showed above is more than enough for that as well.

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?

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

31. List the standings after the fourth round.

mysql> show tables;
+-----------------+
| Tables_in_teddy |
+-----------------+
| enrollment      | 
| matches         | 
| participation   | 
| players         | 
| spectators      | 
| venues          | 
+-----------------+
6 rows in set (0.00 sec)

mysql> select * from participation;
+----------+---------+--------+-------+
| username | matchid | points | host  |
+----------+---------+--------+-------+
| cbarkley | duba    |    0.5 | last  | 
| cbarkley | joba    |      0 | last  | 
| cbarkley | kuba    |      0 | last  | 
| cbarkley | miba    |    0.5 | last  | 
| lbird    | bijo    |      1 | first | 
| lbird    | dubi    |      1 | last  | 
| lbird    | kubi    |      1 | last  | 
| lbird    | mibi    |      1 | last  | 
| mjordan  | bijo    |      0 | last  | 
| mjordan  | joba    |      1 | first | 
| mjordan  | joku    |      1 | first | 
| mjordan  | mijo    |    0.5 | last  | 
| rmiller  | dumi    |    0.5 | last  | 
| rmiller  | miba    |    0.5 | first | 
| rmiller  | mibi    |      0 | first | 
| rmiller  | mijo    |    0.5 | first | 
| tduncan  | duba    |    0.5 | first | 
| tduncan  | dubi    |      0 | first | 
| tduncan  | dumi    |    0.5 | first | 
| tduncan  | kudu    |    0.5 | last  | 
| tkukoc   | joku    |      0 | last  | 
| tkukoc   | kuba    |      1 | first | 
| tkukoc   | kubi    |      0 | first | 
| tkukoc   | kudu    |    0.5 | first | 
+----------+---------+--------+-------+
24 rows in set (0.00 sec)

mysql> select * from participation inner join matches on matches.matchid = participation.matchid and round in ('round one', 'round two', 'round three');
+----------+---------+--------+-------+----------+---------+-------------+-----------------------+-------------+
| username | matchid | points | host  | matchnum | matchid | round       | venue                 | ticketPrice |
+----------+---------+--------+-------+----------+---------+-------------+-----------------------+-------------+
| cbarkley | duba    |    0.5 | last  |        4 | duba    | round two   | Woodburn Hall         |         6.5 | 
| cbarkley | joba    |      0 | last  |        1 | joba    | round one   | Soldier Field         |         5.5 | 
| cbarkley | kuba    |      0 | last  |        8 | kuba    | round three | Soldier Field         |           8 | 
| lbird    | bijo    |      1 | first |        7 | bijo    | round three | Rawles Arena          |        7.35 | 
| lbird    | kubi    |      1 | last  |        5 | kubi    | round two   | Staples Center        |         5.5 | 
| lbird    | mibi    |      1 | last  |        2 | mibi    | round one   | Madison Square Garden |           8 | 
| mjordan  | bijo    |      0 | last  |        7 | bijo    | round three | Rawles Arena          |        7.35 | 
| mjordan  | joba    |      1 | first |        1 | joba    | round one   | Soldier Field         |         5.5 | 
| mjordan  | mijo    |    0.5 | last  |        6 | mijo    | round two   | RCA Dome              |        5.75 | 
| rmiller  | dumi    |    0.5 | last  |        9 | dumi    | round three | Madison Square Garden |        9.99 | 
| rmiller  | mibi    |      0 | first |        2 | mibi    | round one   | Madison Square Garden |           8 | 
| rmiller  | mijo    |    0.5 | first |        6 | mijo    | round two   | RCA Dome              |        5.75 | 
| tduncan  | duba    |    0.5 | first |        4 | duba    | round two   | Woodburn Hall         |         6.5 | 
| tduncan  | dumi    |    0.5 | first |        9 | dumi    | round three | Madison Square Garden |        9.99 | 
| tduncan  | kudu    |    0.5 | last  |        3 | kudu    | round one   | Wrigley Field         |        4.25 | 
| tkukoc   | kuba    |      1 | first |        8 | kuba    | round three | Soldier Field         |           8 | 
| tkukoc   | kubi    |      0 | first |        5 | kubi    | round two   | Staples Center        |         5.5 | 
| tkukoc   | kudu    |    0.5 | first |        3 | kudu    | round one   | Wrigley Field         |        4.25 | 
+----------+---------+--------+-------+----------+---------+-------------+-----------------------+-------------+
18 rows in set (0.01 sec)

mysql> select username, matches.matchid, round from participation inner join matches on matches.matchid = participation.matchid and round in ('round one', 'round two', 'round three');
+----------+---------+-------------+
| username | matchid | round       |
+----------+---------+-------------+
| cbarkley | duba    | round two   | 
| cbarkley | joba    | round one   | 
| cbarkley | kuba    | round three | 
| lbird    | bijo    | round three | 
| lbird    | kubi    | round two   | 
| lbird    | mibi    | round one   | 
| mjordan  | bijo    | round three | 
| mjordan  | joba    | round one   | 
| mjordan  | mijo    | round two   | 
| rmiller  | dumi    | round three | 
| rmiller  | mibi    | round one   | 
| rmiller  | mijo    | round two   | 
| tduncan  | duba    | round two   | 
| tduncan  | dumi    | round three | 
| tduncan  | kudu    | round one   | 
| tkukoc   | kuba    | round three | 
| tkukoc   | kubi    | round two   | 
| tkukoc   | kudu    | round one   | 
+----------+---------+-------------+
18 rows in set (0.00 sec)

mysql> select username, points, matches.matchid, round from participation inner join matches on matches.matchid = participation.matchid and round in ('round one', 'round two', 'round three');
+----------+--------+---------+-------------+
| username | points | matchid | round       |
+----------+--------+---------+-------------+
| cbarkley |    0.5 | duba    | round two   | 
| cbarkley |      0 | joba    | round one   | 
| cbarkley |      0 | kuba    | round three | 
| lbird    |      1 | bijo    | round three | 
| lbird    |      1 | kubi    | round two   | 
| lbird    |      1 | mibi    | round one   | 
| mjordan  |      0 | bijo    | round three | 
| mjordan  |      1 | joba    | round one   | 
| mjordan  |    0.5 | mijo    | round two   | 
| rmiller  |    0.5 | dumi    | round three | 
| rmiller  |      0 | mibi    | round one   | 
| rmiller  |    0.5 | mijo    | round two   | 
| tduncan  |    0.5 | duba    | round two   | 
| tduncan  |    0.5 | dumi    | round three | 
| tduncan  |    0.5 | kudu    | round one   | 
| tkukoc   |      1 | kuba    | round three | 
| tkukoc   |      0 | kubi    | round two   | 
| tkukoc   |    0.5 | kudu    | round one   | 
+----------+--------+---------+-------------+
18 rows in set (0.00 sec)

mysql> select username, sum(points) from participation inner join matches on matches.matchid = participation.matchid and round in ('round one', 'round two', 'round three') group by username;
+----------+-------------+
| username | sum(points) |
+----------+-------------+
| cbarkley |         0.5 | 
| lbird    |           3 | 
| mjordan  |         1.5 | 
| rmiller  |           1 | 
| tduncan  |         1.5 | 
| tkukoc   |         1.5 | 
+----------+-------------+
6 rows in set (0.00 sec)

mysql> select username, sum(points) from participation inner join matches on matches.matchid = participation.matchid and round in ('round one', 'round two', 'round three') group by username order by sum(points) desc;
+----------+-------------+
| username | sum(points) |
+----------+-------------+
| lbird    |           3 | 
| mjordan  |         1.5 | 
| tduncan  |         1.5 | 
| tkukoc   |         1.5 | 
| rmiller  |           1 | 
| cbarkley |         0.5 | 
+----------+-------------+
6 rows in set (0.00 sec)

mysql> 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;
+----------+-------------+
| username | sum(points) |
+----------+-------------+
| lbird    |           4 | 
| mjordan  |         2.5 | 
| rmiller  |         1.5 | 
| tduncan  |         1.5 | 
| tkukoc   |         1.5 | 
| cbarkley |           1 | 
+----------+-------------+
6 rows in set (0.00 sec)

mysql> select * from participation where username = 'lbird';
+----------+---------+--------+-------+
| username | matchid | points | host  |
+----------+---------+--------+-------+
| lbird    | bijo    |      1 | first | 
| lbird    | dubi    |      1 | last  | 
| lbird    | kubi    |      1 | last  | 
| lbird    | mibi    |      1 | last  | 
+----------+---------+--------+-------+
4 rows in set (0.00 sec)

mysql> 

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.