Appendix G. Bonus Exercises
The exercises in this appendix are bonus exercises that are specific to MySQL. We provide an explanation or question and then provide sample MySQL-based SQL code to execute. Remember that the SQL code can vary from implementation to implementation, so some of these statements need to be adjusted depending on what system you are working on. Study the question, code, and results carefully to improve your knowledge of SQL.
1. Create a new database for bonus exercises and name it BONUS.
CREATE DATABASE BONUS;
2. Point MySQL to your new database.
USE BONUS;
3. Create a table to keep track of basketball teams.
CREATE TABLE TEAMS
( TEAM_ID INTEGER(2) NOT NULL,
NAME VARCHAR(20) NOT NULL );
4. Create a table to keep track of basketball players.
CREATE TABLE PLAYERS
( PLAYER_ID INTEGER(2) NOT NULL,
LAST VARCHAR(20) NOT NULL,
FIRST VARCHAR(20) NOT NULL,
TEAM_ID INTEGER(2) NULL,
NUMBER INTEGER(2) NOT NULL );
5. Create a table to keep track of players’ personal information.
CREATE TABLE PLAYER_DATA
( PLAYER_ID INTEGER(2) NOT NULL,
HEIGHT DECIMAL(4,2) NOT NULL,
WEIGHT DECIMAL(5,2) NOT NULL );
6. Create a table to keep track of games played.
CREATE TABLE GAMES
( GAME_ID INTEGER(2) NOT NULL,
GAME_DT DATETIME NOT NULL,
HOME_TEAM_ID INTEGER(2) NOT NULL,
GUEST_TEAM_ID INTEGER(3) NOT NULL );
7. Create a table to keep track of each team’s score for each game.
CREATE TABLE SCORES
( GAME_ID INTEGER(2) NOT NULL,
TEAM_ID INTEGER(2) NOT NULL,
SCORE INTEGER(3) NOT NULL,
WIN_LOSE VARCHAR(4) NOT NULL );
8. View all the tables that you created.
SHOW TABLES;
9. Create records for the basketball teams.
INSERT INTO TEAMS VALUES ('1','STRING MUSIC'),
INSERT INTO TEAMS VALUES ('2','HACKERS'),
INSERT INTO TEAMS VALUES ('3','SHARP SHOOTERS'),
INSERT INTO TEAMS VALUES ('4','HAMMER TIME'),
10. Create records for the players.
INSERT INTO PLAYERS VALUES ('1','SMITH','JOHN','1','12'),
INSERT INTO PLAYERS VALUES ('2','BOBBIT','BILLY','1','2'),
INSERT INTO PLAYERS VALUES ('3','HURTA','WIL','2','32'),
INSERT INTO PLAYERS VALUES ('4','OUCHY','TIM','2','22'),
INSERT INTO PLAYERS VALUES ('5','BYRD','ERIC','3','6'),
INSERT INTO PLAYERS VALUES ('6','JORDAN','RYAN','3','23'),
INSERT INTO PLAYERS VALUES ('7','HAMMER','WALLY','4','21'),
INSERT INTO PLAYERS VALUES ('8','HAMMER','RON','4','44'),
INSERT INTO PLAYERS VALUES ('11','KNOTGOOD','AL',NULL,'0'),
11. Create records for the players’ personal data.
INSERT INTO PLAYER_DATA VALUES ('1','71','180'),
INSERT INTO PLAYER_DATA VALUES ('2','58','195'),
INSERT INTO PLAYER_DATA VALUES ('3','72','200'),
INSERT INTO PLAYER_DATA VALUES ('4','74','170'),
INSERT INTO PLAYER_DATA VALUES ('5','71','182'),
INSERT INTO PLAYER_DATA VALUES ('6','72','289'),
INSERT INTO PLAYER_DATA VALUES ('7','79','250'),
INSERT INTO PLAYER_DATA VALUES ('8','73','193'),
INSERT INTO PLAYER_DATA VALUES ('11','85','310'),
12. Create records in the GAMES table based on games that have been scheduled.
INSERT INTO GAMES VALUES ('1','2002-05-01','1','2'),
INSERT INTO GAMES VALUES ('2','2002-05-02','3','4'),
INSERT INTO GAMES VALUES ('3','2002-05-03','1','3'),
INSERT INTO GAMES VALUES ('4','2002-05-05','2','4'),
INSERT INTO GAMES VALUES ('5','2002-05-05','1','2'),
INSERT INTO GAMES VALUES ('6','2002-05-09','3','4'),
INSERT INTO GAMES VALUES ('7','2002-05-10','2','3'),
INSERT INTO GAMES VALUES ('8','2002-05-11','1','4'),
INSERT INTO GAMES VALUES ('9','2002-05-12','2','3'),
INSERT INTO GAMES VALUES ('10','2002-05-15','1','4'),
13. Create records in the SCORES table based on games that have been played.
INSERT INTO SCORES VALUES ('1','1','66','LOSE'),
INSERT INTO SCORES VALUES ('2','3','78','WIN'),
INSERT INTO SCORES VALUES ('3','1','45','LOSE'),
INSERT INTO SCORES VALUES ('4','2','56','LOSE'),
INSERT INTO SCORES VALUES ('5','1','100','WIN'),
INSERT INTO SCORES VALUES ('6','3','67','LOSE'),
INSERT INTO SCORES VALUES ('7','2','57','LOSE'),
INSERT INTO SCORES VALUES ('8','1','98','WIN'),
INSERT INTO SCORES VALUES ('9','2','56','LOSE'),
INSERT INTO SCORES VALUES ('10','1','46','LOSE'),
INSERT INTO SCORES VALUES ('1','2','75','WIN'),
INSERT INTO SCORES VALUES ('2','4','46','LOSE'),
INSERT INTO SCORES VALUES ('3','3','87','WIN'),
INSERT INTO SCORES VALUES ('4','4','99','WIN'),
INSERT INTO SCORES VALUES ('5','2','88','LOSE'),
INSERT INTO SCORES VALUES ('6','4','77','WIN'),
INSERT INTO SCORES VALUES ('7','3','87','WIN'),
INSERT INTO SCORES VALUES ('8','4','56','LOSE'),
INSERT INTO SCORES VALUES ('9','3','87','WIN'),
INSERT INTO SCORES VALUES ('10','4','78','WIN')
14. Determine the average height of all players.
SELECT AVG(HEIGHT) FROM PLAYER_DATA;
15. Determine the average weight of all players.
SELECT AVG(WEIGHT) FROM PLAYER_DATA;
16. Create a list of player information as follows:
NAME=LAST NUMBER=N HEIGHT=N WEIGHT=N
SELECT CONCAT('NAME=',P1.LAST,' NUMBER=',P1.NUMBER,'
HEIGHT=',P2.HEIGHT,' WEIGHT=',P2.WEIGHT)
FROM PLAYERS P1,
PLAYER_DATA P2
WHERE P1.PLAYER_ID = P2.PLAYER_ID;
17. Create a team roster that looks like the following:
TEAM NAME LAST, FIRST NUMBER
SELECT T.NAME, CONCAT(P.LAST,', ',P.FIRST), P.NUMBER
FROM TEAMS T,
PLAYERS P
WHERE T.TEAM_ID = P.TEAM_ID;
18. Determine which team has scored the most points of all games.
SELECT T.NAME, SUM(S.SCORE)
FROM TEAMS T,
SCORES S
WHERE T.TEAM_ID = S.TEAM_ID
GROUP BY T.NAME
ORDER BY 2 DESC;
19. Determine the most points scored in a single game by one team.
SELECT MAX(SCORE)
FROM SCORES;
20. Determine the most points scored collectively by both teams in a single game.
SELECT GAME_ID, SUM(SCORE)
FROM SCORES
GROUP BY GAME_ID
ORDER BY 2 DESC;
21. Determine if there are any players who are not assigned to a team.
SELECT LAST, FIRST, TEAM_ID
FROM PLAYERS
WHERE TEAM_ID IS NULL;
22. Determine the number of teams.
SELECT COUNT(*) FROM TEAMS;
23. Determine the number of players.
SELECT COUNT(*) FROM PLAYERS;
24. Determine how many games were played on May 5, 2002.
SELECT COUNT(*) FROM GAMES
WHERE GAME_DT = '2002-05-05';
25. Determine the tallest player.
SELECT P.LAST, P.FIRST, PD.HEIGHT
FROM PLAYERS P,
PLAYER_DATA PD
WHERE P.PLAYER_ID = PD.PLAYER_ID
ORDER BY 3 DESC;
OR
SELECT MAX(HEIGHT) FROM PLAYER_DATA;
SELECT P.LAST, P.FIRST, PD.HEIGHT
FROM PLAYERS P,
PLAYER_DATA PD
WHERE HEIGHT = 85;
26. Remove Ron Hammer’s record from the database, and replace him with Al Knotgood.
SELECT PLAYER_ID
FROM PLAYERS
WHERE LAST = 'HAMMER'
AND FIRST = 'RON';
DELETE FROM PLAYERS WHERE PLAYER_ID = '8';
DELETE FROM PLAYER_DATA WHERE PLAYER_ID = '8';
SELECT PLAYER_ID
FROM PLAYERS
WHERE LAST = 'KNOTGOOD'
AND FIRST = 'AL';
UPDATE PLAYERS
SET TEAM_ID = '4'
WHERE PLAYER_ID = '11';
27. Determine Al Knotgood’s new teammate.
SELECT TEAMMATE.LAST, TEAMMATE.FIRST
FROM PLAYERS TEAMMATE,
PLAYERS P
WHERE P.TEAM_ID = TEAMMATE.TEAM_ID
AND P.LAST = 'KNOTGOOD'
AND P.FIRST = 'AL';
28. Generate a list of all games and game dates. Also, list home and guest teams for each game.
SELECT G.GAME_ID, HT.NAME, GT.NAME
FROM GAMES G,
TEAMS HT,
TEAMS GT
WHERE HT.TEAM_ID = G.HOME_TEAM_ID
AND GT.TEAM_ID = G.GUEST_TEAM_ID;
29. Create indexes for all names in the database. Names are often indexed because you frequently search by name.
CREATE INDEX TEAM_IDX
ON TEAMS (NAME);
CREATE INDEX PLAYERS_IDX
ON PLAYERS (LAST, FIRST);
30. Determine which team has the most wins.
SELECT T.NAME, COUNT(S.WIN_LOSE)
FROM TEAMS T,
SCORES S
WHERE T.TEAM_ID = S.TEAM_ID
AND S.WIN_LOSE = 'WIN'
GROUP BY T.NAME
ORDER BY 2 DESC;
31. Determine which team has the most losses.
SELECT T.NAME, COUNT(S.WIN_LOSE)
FROM TEAMS T,
SCORES S
WHERE T.TEAM_ID = S.TEAM_ID
AND S.WIN_LOSE = 'LOSE'
GROUP BY T.NAME
ORDER BY 2 DESC;
32. Determine which team has the highest average score per game.
SELECT T.NAME, AVG(S.SCORE)
FROM TEAMS T,
SCORES S
WHERE T.TEAM_ID = S.TEAM_ID
GROUP BY T.NAME
ORDER BY 2 DESC;
33. Generate a report that shows each team’s record. Sort the report by teams with the most wins and then by teams with the fewest losses.
SELECT T.NAME, SUM(REPLACE(S.WIN_LOSE,'WIN',1)) WINS,
SUM(REPLACE(S.WIN_LOSE,'LOSE',1)) LOSSES
FROM TEAMS T,
SCORES S
WHERE T.TEAM_ID = S.TEAM_ID
GROUP BY T.NAME
ORDER BY 2 DESC, 3;
34. Determine the final score of each game.
SELECT G.GAME_ID,
HOME_TEAMS.NAME "HOME TEAM", HOME_SCORES.SCORE,
GUEST_TEAMS.NAME "GUEST TEAM", GUEST_SCORES.SCORE
FROM GAMES G,
TEAMS HOME_TEAMS,
TEAMS GUEST_TEAMS,
SCORES HOME_SCORES,
SCORES GUEST_SCORES
WHERE G.HOME_TEAM_ID = HOME_TEAMS.TEAM_ID
AND G.GUEST_TEAM_ID = GUEST_TEAMS.TEAM_ID
AND HOME_SCORES.GAME_ID = G.GAME_ID
AND GUEST_SCORES.GAME_ID = G.GAME_ID
AND HOME_SCORES.TEAM_ID = G.HOME_TEAM_ID
AND GUEST_SCORES.TEAM_ID = G.GUEST_TEAM_ID
ORDER BY G.GAME_ID;