Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Appendix G. Bonus Exercises

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;