SQL BY EXAMPLES

All of the following examples are based on the Tennis Club Sample Database used in the book: Rick F. van der Lans, Introduction to SQL, Addison-Wesley, New York, 1993.

The database is used by a tennis club to record its players' progress in a competition. The database consists of four tables: Players, Teams, Matches, Penalties.

The Players table contains data about members of the club, such as names, addresses and year of birth. Two players cannot have the same combination of name and initials. The description of the columns follows.

The Teams table contains data about teams taking part in competiotions. The captain of each team and the division in which is currently competing is recordered.

The Matches table contains data about matches. In the table we show for each match separately which player was in the match and for which team he played. In addition, we record how many sets the player won and lost.

The Penalties table contains data about penalties. If a player is badly behaved the league imposes a penalty in the form of a fine. The club pays these fines and records them in the table.

 

The SELECT statement for the suggested queries are not necessarily the only ones possible.

 

CREATE DATABASE

CREATE DATABASE SportDB IN SPORT;
Top

 

CREATE TABLE

CREATE   TABLE Players
        (PlayerNo        INTEGER      NOT NULL,
         Name            VARCHAR(15)  NOT NULL,
         Initials        VARCHAR(3)   NOT NULL,
         BirthYear       INTEGER              ,
         Sex             VARCHAR(1)   NOT NULL,
         Joined          INTEGER      NOT NULL,
         Street          VARCHAR(15)  NOT NULL,
         HouseNo         VARCHAR(4)           ,
         PostCode        VARCHAR(6)           ,
         Town            VARCHAR(10)  NOT NULL,
         PhoneNo         VARCHAR(10)          ,
         LeagueNo        VARCHAR(4)           ,

         PRIMARY KEY    (PlayerNo)            );
Top

CREATE   TABLE Teams
        (TeamNo          INTEGER     NOT NULL,
         PlayerNo        INTEGER     NOT NULL,
         Division        VARCHAR(6)  NOT NULL,

         PRIMARY KEY     (TeamNo)            ,       
		  FOREIGN KEY     (PlayerNo)
			REFERENCES     Players      
           ON DELETE     CASCADE             );
Top

CREATE   TABLE Matches
        (MatchNo         INTEGER     NOT NULL,
         TeamNo          INTEGER     NOT NULL,
         PlayerNo        INTEGER     NOT NULL,
         Won             INTEGER     NOT NULL,
         Lost            INTEGER     NOT NULL,

         PRIMARY KEY     (MatchNo)           , 
		  FOREIGN KEY     (TeamNo)
			REFERENCES     Teams     
			 ON DELETE     CASCADE             ,
		  FOREIGN KEY     (PlayerNo)
			REFERENCES     Players   
			 ON DELETE     CASCADE             );
Top

CREATE   TABLE Penalties
        (PaymentNo       INTEGER      NOT NULL,
         PlayerNo        INTEGER      NOT NULL,
         PenaltyDate     VARCHAR(10)  NOT NULL,
         Amount          INTEGER      NOT NULL,

         PRIMARY KEY     (PaymentNo)          , 
		  FOREIGN KEY     (PlayerNo)
			REFERENCES     Players  
			 ON DELETE     NO ACTION            );
Top

CREATE INDEX

CREATE   UNIQUE INDEX PLAYER_Idx ON
         Players (Name ASC, Initials ASC);

Top

INSERT

INSERT INTO Players VALUES (
  6, 'Parmenter', 'R', 1964, 'M', 1977, 'Haseltine Lane',
    '80', '1234KK', 'Stratford', '070-476537', '8467');

INSERT INTO Players VALUES (
 44, 'Baker', 'E', 1963, 'M', 1980, 'Lewis Street',
    '23', '4444LJ', 'Inglewood', '070-368753', '1124');

INSERT INTO Players VALUES (
 83, 'Hope', 'PK', 1956, 'M', 1982, 'Magdalene Road',
    '16A', '1812UP', 'Stratford', '070-353548', '1608');

INSERT INTO Players VALUES (
  2, 'Everett', 'R', 1948, 'M', 1975, 'Stoney Road',
    '43', '3575NH', 'Stratford', '070-237893', '2411');

INSERT INTO Players VALUES (
 27, 'Collins', 'DD', 1964, 'F', 1983, 'Long Drive',
    '804', '8457DK', 'Eltham', '079-234857', '2513');

INSERT INTO Players VALUES (
104, 'Moorman', 'D', 1970, 'F', 1984, 'Stout Street',
    '65', '9437AO', 'Eltham', '079-987571', '7060');

INSERT INTO Players VALUES (
  7, 'Wise', 'GWS', 1963, 'M', 1981, 'Edgecombe Way',
    '39', '9758VB', 'Stratford', '070-347689', NULL);

INSERT INTO Players VALUES (
 57, 'Brown', 'M', 1971, 'M', 1985, 'Edgecombe Way',
    '16', '4377CB', 'Stratford', '070-473458', '6409');

INSERT INTO Players VALUES (
 39, 'Bishop', 'D', 1956, 'M', 1980, 'Eaton Square',
    '78', '9629CD', 'Stratford', '070-393435', NULL);

INSERT INTO Players VALUES (
112, 'Bailey', 'IP', 1963, 'F', 1984, 'Vixen Road',
    '8', '6392LK', 'Plymouth', '010-548745', '1319');

INSERT INTO Players VALUES (
  8, 'Newcastle', 'B', 1962, 'F', 1980, 'Station Road',
    '4', '6584WO', 'Inglewood', '070-458458', '2983');

INSERT INTO Players VALUES (
100, 'Parmenter', 'P', 1963, 'M', 1979, 'Haseltine Lane',
    '80', '6494SG', 'Stratford', '070-494593', '6524');

INSERT INTO Players VALUES (
 28, 'Collins', 'C', 1963, 'F', 1983, 'Old Main Road',
    '10', '1294QK', 'Midhurst', '010-659599', NULL);

INSERT INTO Players VALUES (
 95, 'Miller', 'P', 1934, 'M', 1972, 'High Street',
    '33A', '5746OP', 'Douglas', '070-867564', NULL);

INSERT INTO Teams VALUES (1,  6, 'first');
INSERT INTO Teams VALUES (2, 27, 'second');

INSERT INTO Matches VALUES ( 1, 1,   6, 3, 1);
INSERT INTO Matches VALUES ( 2, 1,   6, 2, 3);
INSERT INTO Matches VALUES ( 3, 1,   6, 3, 0);
INSERT INTO Matches VALUES ( 4, 1,  44, 3, 2);
INSERT INTO Matches VALUES ( 5, 1,  83, 0, 3);
INSERT INTO Matches VALUES ( 6, 1,   2, 1, 3);
INSERT INTO Matches VALUES ( 7, 1,  57, 3, 0);
INSERT INTO Matches VALUES ( 8, 1,   8, 0, 3);
INSERT INTO Matches VALUES ( 9, 2,  27, 3, 2);
INSERT INTO Matches VALUES (10, 2, 104, 3, 2);
INSERT INTO Matches VALUES (11, 2, 112, 2, 3);
INSERT INTO Matches VALUES (12, 2, 112, 1, 3);
INSERT INTO Matches VALUES (13, 2,   8, 0, 3);

INSERT INTO Penalties VALUES (1,  6, '12/08/1980',100);
INSERT INTO Penalties VALUES (2, 44, '05/05/1981', 75);
INSERT INTO Penalties VALUES (3, 27, '09/10/1983',100);
INSERT INTO Penalties VALUES (4,104, '12/08/1984', 50);
INSERT INTO Penalties VALUES (5, 44, '12/08/1980', 25);
INSERT INTO Penalties VALUES (6,  8, '12/08/1980', 25);
INSERT INTO Penalties VALUES (7, 44, '12/30/1982', 30);
INSERT INTO Penalties VALUES (8, 27, '11/12/1984', 75);
Top


UPDATE STATISTICS;
Top

SELECT STATEMENT

7.4 Give the player number and the address of each player resident in Stratford

SELECT   PlayerNo, Street + ' ' + HouseNo AS Address
FROM     Players
WHERE    Town = 'Stratford';

8.1 Get the player number and the league number of each player resident in Stratford; order the result by league number.

SELECT   PlayerNo, LeagueNo
FROM     Players
WHERE    Town = 'Stratford'
ORDER BY LeagueNo;

10.2 Give the player number, the year of birth and the year of joining the club for each player who joined 17 years after he or she was born.

SELECT   PlayerNo, BirthYear, Joined
FROM     Players
WHERE    BirthYear + 17 = Joined;

10.6 Give the number, town and year of birth of each player who lives in Stratford, or was born in 1963, but do not include those who live in Stratford and were born in 1963.

SELECT   PlayerNo, Town, BirthYear
FROM     Players
WHERE    (Town = 'Stratford' OR  BirthYear = 1963)
AND      NOT (Town = 'Stratford' AND  BirthYear = 1963);

Top

SELECT STATEMENT: The Operator IN

10.12 Get the numbers and years of birth of the players born in 1962, 1963 or 1970.

SELECT   PlayerNo, BirthYear
FROM     Players
WHERE    BirthYear IN (1962, 1963, 1970);

10.18 Get the player number and the name of each player who has at least one match for the first team.

SELECT   PlayerNo, Name
FROM     Players
WHERE    PlayerNo IN 
        (SELECT   PlayerNo
         FROM     Matches
         WHERE    TeamNo = 1);

10.19 Give the number and name of each player who has played at least one match for the team which is not captained by player 6.

SELECT   PlayerNo, Name
FROM     Players
WHERE    PlayerNo IN 
        (SELECT   PlayerNo
         FROM     Matches
         WHERE    TeamNo NOT IN
                 (SELECT   TeamNo
                  FROM     Teams
                  WHERE    PlayerNo = 6));

Top

SELECT STATEMENT: The Operators ALL, ANY

10.22 Give the player numbers, names and yeras of birth of the oldest players. The oldest players are those whose year of birth is less than or equal to tha of each other player.

SELECT  PlayerNo, Name, BirthYear
FROM    Players 
WHERE   BirthYear  <= ALL 
       (SELECT   BirthYear 
        FROM     Players);

10.23 Get the player numbers, names and years of birth of players who are not amongst the oldest.

SELECT   PlayerNo, Name, BirthYear
FROM     Players 
WHERE    BirthYear  > ANY 
        (SELECT   BirthYear 
         FROM     Players);

10.24 Give the numbers of the players who have incurred at least one penalty which is higher than a penalty paid for player 27; this player may not appear in the result.

SELECT   DISTINCT PlayerNo 
FROM     Penalties 
WHERE    PlayerNo <> 27
AND      Amount  > ANY 
        (SELECT   Amount
         FROM     Penalties
         WHERE    PlayerNo = 27);

10.27 Find the player numbers of the players with the lowest league number of all players living in their respective towns.

SELECT   PlayerNo 
FROM     Players P1
WHERE    LeagueNo  <= ALL 
        (SELECT   LeagueNo 
         FROM     Players P2
         WHERE    P1.Town = P2.Town); 

Top


SELECT STATEMENT: The Operator EXISTS

15.1 Give the player number and the name of each player who has incurred at least one penalty.

SELECT   PlayerNo, Name
FROM     Players
WHERE    EXISTS
        (SELECT  *
         FROM    Penalties
         WHERE   Players.PlayerNo = Players.PlayerNo );

15.2 Get the team number and division of each team in which the player 44 has played.

SELECT   TeamNo, Division
FROM     Teams
WHERE    EXISTS
        (SELECT  *
         FROM    Matches 
         WHERE   PlayerNo = 44 
         AND     TeamNo = Teams.TeamNo );

15.3 Get the player number of each player who has incurred more than one penalty.

SELECT   DISTINCT PlayerNo
FROM     Penalties PN
WHERE    EXISTS
        (SELECT   *
         FROM     Penalties 
         WHERE    PN.PlayerNo = PlayerNo 
         AND      PaymentNo <> PN.PaymentNo );

15.5 Get the three highest league numbers that are registered .

SELECT    LeagueNo
FROM 	   Players P1
WHERE	   3 >
		  (SELECT   COUNT(*)
		   FROM	  Players P2
		   WHERE	  P1.LeagueNo > P2.LeagueNo)
ORDER BY 	LeagueNo DESC;

15.6 Give the team number of each team in which player 57 has not played.

SELECT   TeamNo 
FROM     Teams
WHERE    NOT EXISTS
        (SELECT   *
         FROM     Matches 
         WHERE    PlayerNo = 57 AND TeamNo = Teams.TeamNo);

15.7 Which players have competed for all teams named in the Teams table?

SELECT   PlayerNo
FROM     Players P
WHERE    NOT EXISTS
        (SELECT   *
         FROM     Teams T
         WHERE    NOT EXISTS
                 (SELECT   *
                  FROM     Matches M
                  WHERE    T.TeamNo = M.TeamNo
                  AND      P.PlayerNo = M.PlayerNo));

15.8 Give the player number of each player who has played for at least all the teams in which player 57 has ever played.

SELECT   PlayerNo
FROM     Players 
WHERE    NOT EXISTS
        (SELECT   *
         FROM     Matches M1
         WHERE    PlayerNo = 57
         AND      NOT EXISTS
                 (SELECT  *
                  FROM    Matches M2
                  WHERE   M1.TeamNo = M2.TeamNo
                  AND     Players.PlayerNo = M2.PlayerNo));

15.9 Give the player number of each player who has played for the same teams as player 57.

SELECT   PlayerNo
FROM     Players P
WHERE    NOT EXISTS
        (SELECT *
         FROM   Matches M1
         WHERE  PlayerNo = 57
         AND    NOT EXISTS
               (SELECT  *
                FROM    Matches M2
                WHERE   M1.TeamNo = M2.TeamNo
                AND     P.PlayerNo = M2.PlayerNo))
AND      PlayerNo NOT IN 
        (SELECT   PlayerNo
         FROM     Matches 
         WHERE    TeamNo IN
                 (SELECT   TeamNo
                  FROM     Teams 
                  WHERE    TeamNo NOT IN
                          (SELECT TeamNo
                           FROM   Matches 
                           WHERE  PlayerNo = 57)));


Top

SELECT STATEMENT: The JOIN

9.3 Find the team number and the name of the captain of each team.

SELECT   TeamNo, Name
FROM     Teams, Players
WHERE    Teams.PlayerNo = Players.PlayerNo;

9.4 For each penalty, find the payment number, the amount of the penalty, the player number, the name and the initials of the player who incurred in the penalty.

SELECT   PaymentNo, Amount, Penalties.PlayerNo, Name, Initials
FROM     Penalties, Players
WHERE    Penalties.PlayerNo = Players.PlayerNo;

9.7 For each match give the player number, the team number, the name of the player and the division in which the team plays.

SELECT   DISTINCT M.PlayerNo, M.TeamNo, P.Name, T.Division  
FROM     Matches M, Players P, Teams T  
WHERE    M.PlayerNo = P.PlayerNo
AND      M.TeamNo = T.TeamNo;

9.9 Get the numbers of the players who are older than R. Parmenter.

SELECT   P.PlayerNo 
FROM     Players P, Players PAR 
WHERE    PAR.Name = 'Parmenter'
AND      PAR.Initials = 'R'
AND      P.BirthYear < PAR.BirthYear;

Top

SELECT STATEMENT: GROUP BY and HAVING

 

11.7 How many different place names are there in the Town column of Players?

SELECT  COUNT(DISTINCT Town)
FROM    Players ;

11.20 What is the unweighted arithmetic mean of the penalty amounts? By unweighted we mean that each different value is considered only once in the calculation.

SELECT  AVG(DISTINCT Amount)
FROM    Penalties;

12.1 Give all the different place names from the players table.

SELECT   Town
FROM     Players    
GROUP BY Town;

12.2 For each town, find the number of players

SELECT   Town, COUNT(*)
FROM     Players    
GROUP BY Town;

12.3 For each team, give the team number, the number of matches that has been played for that team and the total number of sets won.

SELECT   TeamNo, COUNT(*), SUM(Won)
FROM     Matches    
GROUP BY TeamNo;
 

12.4 Give for each team that is captained by a player resident in Eltham, the team number and the number of matches that has been played for that team.

SELECT   TeamNo, COUNT(*) 
FROM     Matches  
WHERE    TeamNo IN
        (SELECT  TeamNo
         FROM    Teams, Players
         WHERE   Teams.PlayerNo = Players.PlayerNo
         AND     Town = 'Eltham')
GROUP BY TeamNo;

12.5 Give for the matches table all the different combinations of team numbers and players numbers.

SELECT   TeamNo, PlayerNo
FROM     Matches    
GROUP BY TeamNo, PlayerNo;

12.6 For each player who has ever incurred a penalty, give the player number, the name and the total amount in penalties incurred.

SELECT   P.PlayerNo, Name, SUM(Amount)
FROM     Players P , Penalties PN  
WHERE    P.PlayerNo = PN.PlayerNo
GROUP BY P.PlayerNo, Name;

12.9 Find the different values of league numbers

SELECT   LeagueNo
FROM     Players
GROUP BY LeagueNo;

12.13 For each player who is a captain and who has incurred more than 80 worth of penalties in total, find the player number and the total amount of penalties.

SELECT   PlayerNo, SUM(Amount)
FROM     Penalties  
WHERE    PlayerNo IN 
        (SELECT  PlayerNo 
         FROM    Teams)     
GROUP BY PlayerNo
HAVING SUM(Amount) > 80 ;

12.14 Give the player number and the total amount of penalties for each player with the highest penalty total.

 
SELECT   PlayerNo, SUM(Amount)
FROM     Penalties PN
GROUP BY PlayerNo
HAVING   SUM(Amount) >= ALL 
	     (SELECT   SUM(Amount) 
		   FROM     Penalties 
		   GROUP BY PlayerNo) ;
Top

 

SELECT STATEMENT: Set Operators

14.1 Give the player number and place of residence of each player from Inglewood and Plymouth

SELECT  PlayerNo, Town
FROM    Players
WHERE   Town = 'Inglewood'
UNION
SELECT  PlayerNo, Town
FROM    Players
WHERE   Town = 'Plymouth';

14.4 Give the number of eaach player who has incurred at least one penalty, who is captain, who lives in Stratford or for whom two or three of these conditions apply.

SELECT  PlayerNo 
FROM    Penalties 
UNION
SELECT  PlayerNo 
FROM    Teams
UNION
SELECT  PlayerNo 
FROM    Players
WHERE   Town = 'Stratford';

14.5 Give the player number and the year of birth of each player living in Stratford and born after 1960.

SELECT  PlayerNo, BirthYear 
FROM    Players
WHERE   Town = 'Stratford'
INTERSECT
SELECT  PlayerNo, BirthYear 
FROM    Players
WHERE   BirthYear > 1960;

14.5 Give the player number and the year of birth of each player living in Stratford and born after 1960.

SELECT  PlayerNo , Name
FROM    Players  
WHERE   PlayerNo IN 
       (SELECT  PlayerNo 
        FROM    Teams
        INTERSECT
        SELECT  PlayerNo 
        FROM    Penalties);

 

Top

 

VIEWS

24.1 Create a view with all towns from the players table.

CREATE VIEW Towns AS
SELECT DISTINCT Town
FROM   Players ;

24.2 Create a view with the player numbers and the league numbers of all players who have a league number.

CREATE VIEW CPlayers AS
SELECT PlayerNo, LeagueNo
FROM   Players
WHERE  LeagueNo IS NOT NULL;

24.6 Create a view with the player number, name, initials and the year of birth of each player who lives in Stratford.

CREATE VIEW SFD_FOLK(PlyNo, Name, Initials, Born) AS
SELECT PlayerNo, Name, Initials, BirthYear
FROM   Players
WHERE  Town = 'Stratford';

24.6 Create a view with the number of residents of each town.

CREATE VIEW RESIDENT (Town, Number) AS
SELECT   Town, COUNT(*) 
FROM     Players
GROUP BY Town;

Top

Index Page