Sams Teach Yourself SQL in 24 Hours

Sep 9, 2005

📄 Contents

␡

⎙ Print

< Back Page 251 of 251

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.

Invoke MySQL and create a new database for bonus exercises.
```
CREATE DATABASE BONUS; 
```
Point MySQL to your new database.
```
USE BONUS; 
```

Create a table to keep track of basketball teams.

CREATE TABLE TEAMS 
( TEAM_ID        INTEGER(2)    NOT NULL,
  NAME        VARCHAR(20)    NOT NULL );

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

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

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

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

View all the tables that you created.
```
SHOW TABLES; 
```

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');

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');

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');

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');

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

What is the average height of all players?
```
SELECT AVG(HEIGHT) FROM PLAYER_DATA; 
```
What is the average weight of all players?
```
SELECT AVG(WEIGHT) FROM PLAYER_DATA; 
```

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;

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;

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;

What is the most points scored in a single game by one team?
```
SELECT MAX(SCORE) 
FROM SCORES;
```

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;

Are there any players who are not assigned to a team?

SELECT LAST, FIRST, TEAM_ID 
FROM PLAYERS
WHERE TEAM_ID IS NULL;

How many teams are there?
```
SELECT COUNT(*) FROM TEAMS; 
```
How many players are there?
```
SELECT COUNT(*) FROM PLAYERS; 
```

How many games were played on the 5th of May, 2002?

SELECT COUNT(*) FROM GAMES 
WHERE GAME_DT = '2002-05-05';

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;

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';

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';

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;

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);
```

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;

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;

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;

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;

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;

< Back Page 251 of 251

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Email Address

Sams Teach Yourself SQL in 24 Hours

Recommended Book

Recommended Book

Recommended Book 

Appendix G. Bonus Exercises

InformIT Promotional Mailings & Special Offers