| The exercises in this appendix are bonus walkthrough exercises and are specific to MySQL. We provide an explanation or question, then provide the SQL code that you need to type into the mysql> prompt. Please study the question, code, and results carefully to improve your knowledge of SQL. | 1: | Invoke MySQL and create a new database for bonus exercises. 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, HEIGTH 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: | What is the average height of all players? SELECT AVG(HEIGHT) FROM PLAYER_DATA; | | 15: | What is the average weight of all players? SELECT AVG(WEIGHT) FROM PLAYER_DATA; | | 16: | View 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: | What 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: | What is the most points scored in a single game by one team? SELECT MAX(SCORE) FROM SCORES; | | 20: | What is 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: | Are there any players who are not assigned to a team? SELECT LAST, FIRST, TEAM_ID FROM PLAYERS WHERE TEAM_ID IS NULL; | | 22: | How many teams are there? SELECT COUNT(*) FROM TEAMS; | | 23: | How many players are there? SELECT COUNT(*) FROM PLAYERS; | | 24: | How many games were played on the 5th of May, 2002? SELECT COUNT(*) FROM GAMES WHERE GAME_DT = '2002-05-05'; | | 25: | Who is 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: | Ron Hammer received too many flagrant fouls and has been ejected. Remove his 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: | Who is 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 often search by name. CREATE INDEX TEAM_IDX ON TEAMS (NAME); CREATE INDEX PLAYERS_IDX ON PLAYERS (LAST, FIRST); | | 30: | 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: | 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: | 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 least 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: | What was 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; |  |
