Minute paper for today: In your Homework Three database develop the query Which player was the most watched player? You should have your database set up from Friday's lab. E-mail me the answer before you leave the classroom. dgerman@indiana.edu Solution: 1. "Which player"? Report the last, first name and username. 2. What table is this data coming from? From players. 3. Review structure of players: 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.01 sec) mysql> Simple query that produces a projection of table players: mysql> select username, lastName, firstname from players; +----------+----------+-----------+ | username | lastName | firstname | +----------+----------+-----------+ | cbarkley | Barkley | Charles | | lbird | Bird | Larry | | mjordan | Jordan | Michael | | rmiller | Miller | Reggie | | tduncan | Duncan | Tim | | tkukoc | Kukoc | Toni | +----------+----------+-----------+ 6 rows in set (0.02 sec) mysql> The number of tickets sold in games these players played is missing. 4. Number of tickets: where do I find that? The number of tickets purchased by customers are listed in enrollment. Unfortunately there doesn't seem to be any link between players and enrollment. That means that the intersection of the data they store is empty. What do we do? We need to find a connection. We look into the E-R diagram and we find this long link: players - participation - matches - enrollment So we start in players and include participation. 5. List all the players and the game id's they played in: 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 username, matchid -> from players, enrollment; One idea: select players.username, lastName, firstName, participation.username, matchid, points from players, participation; But this creates a cartesian product! How do we rule out those combinations that don't match? Answer: define what it means to match and ask only for those. select players.username, lastName, firstName, participation.username, matchid, points from players, participation where players.username = participation.username This rules out the mismatches records, we now have a natural join. mysql> select players.username, lastName, firstName, matchid -> from players, participation -> where players.username = participation.username; +----------+----------+-----------+---------+ | username | lastName | firstName | matchid | +----------+----------+-----------+---------+ | cbarkley | Barkley | Charles | duba | | cbarkley | Barkley | Charles | joba | | cbarkley | Barkley | Charles | kuba | | cbarkley | Barkley | Charles | miba | | lbird | Bird | Larry | bijo | | lbird | Bird | Larry | dubi | | lbird | Bird | Larry | kubi | | lbird | Bird | Larry | mibi | | mjordan | Jordan | Michael | bijo | | mjordan | Jordan | Michael | joba | | mjordan | Jordan | Michael | joku | | mjordan | Jordan | Michael | mijo | | rmiller | Miller | Reggie | dumi | | rmiller | Miller | Reggie | miba | | rmiller | Miller | Reggie | mibi | | rmiller | Miller | Reggie | mijo | | tduncan | Duncan | Tim | duba | | tduncan | Duncan | Tim | dubi | | tduncan | Duncan | Tim | dumi | | tduncan | Duncan | Tim | kudu | | tkukoc | Kukoc | Toni | joku | | tkukoc | Kukoc | Toni | kuba | | tkukoc | Kukoc | Toni | kubi | | tkukoc | Kukoc | Toni | kudu | +----------+----------+-----------+---------+ 24 rows in set (0.00 sec) mysql> 6. So how do we get the tickets in the picture? Answer: natural join between three tables, see below. select players.username, lastName, firstName, participation.username, participation.matchid, enrollment.matchid, tickets from players, participation , enrollment where players.username = participation.username and participation.matchid = enrollment.matchid Here's how this query runs: mysql> edit UW PICO(tm) 4.10 File: /tmp/sqlPnnY8M Modified select players.username, lastName, firstName, participation.username, participation.matchid, enrollment.matchid, tickets from players, participation , enrollment where players.username = participation.username and participation.matchid = enrollment.matchid [ Wrote 12 lines ] -> ; +----------+----------+-----------+----------+---------+---------+---------+ | username | lastName | firstName | username | matchid | matchid | tickets | +----------+----------+-----------+----------+---------+---------+---------+ | rmiller | Miller | Reggie | rmiller | dumi | dumi | 40 | | tduncan | Duncan | Tim | tduncan | dumi | dumi | 40 | | cbarkley | Barkley | Charles | cbarkley | joba | joba | 120 | | mjordan | Jordan | Michael | mjordan | joba | joba | 120 | | cbarkley | Barkley | Charles | cbarkley | kuba | kuba | 180 | | tkukoc | Kukoc | Toni | tkukoc | kuba | kuba | 180 | | lbird | Bird | Larry | lbird | mibi | mibi | 140 | | rmiller | Miller | Reggie | rmiller | mibi | mibi | 140 | | lbird | Bird | Larry | lbird | bijo | bijo | 200 | | mjordan | Jordan | Michael | mjordan | bijo | bijo | 200 | | lbird | Bird | Larry | lbird | dubi | dubi | 120 | | tduncan | Duncan | Tim | tduncan | dubi | dubi | 120 | | lbird | Bird | Larry | lbird | kubi | kubi | 180 | | tkukoc | Kukoc | Toni | tkukoc | kubi | kubi | 180 | | lbird | Bird | Larry | lbird | bijo | bijo | 150 | | mjordan | Jordan | Michael | mjordan | bijo | bijo | 150 | | cbarkley | Barkley | Charles | cbarkley | joba | joba | 100 | | mjordan | Jordan | Michael | mjordan | joba | joba | 100 | | mjordan | Jordan | Michael | mjordan | joku | joku | 60 | | tkukoc | Kukoc | Toni | tkukoc | joku | joku | 60 | | mjordan | Jordan | Michael | mjordan | mijo | mijo | 210 | | rmiller | Miller | Reggie | rmiller | mijo | mijo | 210 | | cbarkley | Barkley | Charles | cbarkley | duba | duba | 300 | | tduncan | Duncan | Tim | tduncan | duba | duba | 300 | | mjordan | Jordan | Michael | mjordan | joku | joku | 240 | | tkukoc | Kukoc | Toni | tkukoc | joku | joku | 240 | | tduncan | Duncan | Tim | tduncan | kudu | kudu | 220 | | tkukoc | Kukoc | Toni | tkukoc | kudu | kudu | 220 | | lbird | Bird | Larry | lbird | dubi | dubi | 80 | | tduncan | Duncan | Tim | tduncan | dubi | dubi | 80 | | cbarkley | Barkley | Charles | cbarkley | miba | miba | 200 | | rmiller | Miller | Reggie | rmiller | miba | miba | 200 | | lbird | Bird | Larry | lbird | mibi | mibi | 60 | | rmiller | Miller | Reggie | rmiller | mibi | mibi | 60 | | mjordan | Jordan | Michael | mjordan | mijo | mijo | 120 | | rmiller | Miller | Reggie | rmiller | mijo | mijo | 120 | | cbarkley | Barkley | Charles | cbarkley | duba | duba | 80 | | tduncan | Duncan | Tim | tduncan | duba | duba | 80 | | rmiller | Miller | Reggie | rmiller | dumi | dumi | 120 | | tduncan | Duncan | Tim | tduncan | dumi | dumi | 120 | | mjordan | Jordan | Michael | mjordan | joku | joku | 60 | | tkukoc | Kukoc | Toni | tkukoc | joku | joku | 60 | | tduncan | Duncan | Tim | tduncan | kudu | kudu | 20 | | tkukoc | Kukoc | Toni | tkukoc | kudu | kudu | 20 | +----------+----------+-----------+----------+---------+---------+---------+ 44 rows in set (0.00 sec) mysql> 7. Let's organize this data to make easy to calculate it by hand. mysql> select lastName, firstName, tickets from players, participation, enrollment where players.username = part icipation.username and participation.matchid = enrollment.matchid order by lastName asc; +----------+-----------+---------+ | lastName | firstName | tickets | +----------+-----------+---------+ | Barkley | Charles | 100 | | Barkley | Charles | 300 | | Barkley | Charles | 120 | | Barkley | Charles | 200 | | Barkley | Charles | 180 | | Barkley | Charles | 80 | | Bird | Larry | 60 | | Bird | Larry | 140 | | Bird | Larry | 180 | | Bird | Larry | 80 | | Bird | Larry | 200 | | Bird | Larry | 150 | | Bird | Larry | 120 | | Duncan | Tim | 120 | | Duncan | Tim | 40 | | Duncan | Tim | 300 | | Duncan | Tim | 120 | | Duncan | Tim | 80 | | Duncan | Tim | 220 | | Duncan | Tim | 20 | | Duncan | Tim | 80 | | Jordan | Michael | 100 | | Jordan | Michael | 60 | | Jordan | Michael | 120 | | Jordan | Michael | 240 | | Jordan | Michael | 60 | | Jordan | Michael | 120 | | Jordan | Michael | 200 | | Jordan | Michael | 210 | | Jordan | Michael | 150 | | Kukoc | Toni | 220 | | Kukoc | Toni | 20 | | Kukoc | Toni | 180 | | Kukoc | Toni | 60 | | Kukoc | Toni | 240 | | Kukoc | Toni | 60 | | Kukoc | Toni | 180 | | Miller | Reggie | 40 | | Miller | Reggie | 120 | | Miller | Reggie | 60 | | Miller | Reggie | 140 | | Miller | Reggie | 120 | | Miller | Reggie | 200 | | Miller | Reggie | 210 | +----------+-----------+---------+ 44 rows in set (0.00 sec) mysql> What did we do? We eliminated four columns and sorted by a fifth one. 8. Can we list the total number of tickets sold by each player. Yes, if we use GROUP BY and aggregate function SUM: mysql> edit UW PICO(tm) 4.10 File: /tmp/sqlPaP5B4 Modified select lastName, firstName, sum(tickets) from players, participation, enrollment where players.username = participation.username and participation.matchid = enrollment.matchid group by lastName [ Wrote 5 lines ] -> ; +----------+-----------+--------------+ | lastName | firstName | sum(tickets) | +----------+-----------+--------------+ | Barkley | Charles | 980 | | Bird | Larry | 930 | | Duncan | Tim | 980 | | Jordan | Michael | 1260 | | Kukoc | Toni | 960 | | Miller | Reggie | 890 | +----------+-----------+--------------+ 6 rows in set (0.00 sec) mysql> 9. So who is the most watched player? mysql> select lastName, firstName, sum(tickets) from players, participation, enrollment where players.username = participation.username and participation.matchid = enrollment.matchid group by lastName order by sum(tickets) desc; +----------+-----------+--------------+ | lastName | firstName | sum(tickets) | +----------+-----------+--------------+ | Jordan | Michael | 1260 | | Duncan | Tim | 980 | | Barkley | Charles | 980 | | Kukoc | Toni | 960 | | Bird | Larry | 930 | | Miller | Reggie | 890 | +----------+-----------+--------------+ 6 rows in set (0.00 sec) mysql> edit UW PICO(tm) 4.10 File: /tmp/sqloKgi80 Modified select lastName, firstName, sum(tickets) as totalTickets from players, participation, enrollment where players.username = participation.username and participation.matchid = enrollment.matchid group by lastName order by totalTickets desc [ Wrote 6 lines ] -> ; +----------+-----------+--------------+ | lastName | firstName | totalTickets | +----------+-----------+--------------+ | Jordan | Michael | 1260 | | Duncan | Tim | 980 | | Barkley | Charles | 980 | | Kukoc | Toni | 960 | | Bird | Larry | 930 | | Miller | Reggie | 890 | +----------+-----------+--------------+ 6 rows in set (0.01 sec) mysql>