The following is an old in-class test for CA218, and a sample of the correct answers.
There are two separate sets of questions. The answers included here are just samples
of the correct answers as for each question there are a number of
correct SQL statements.
Here is the test:
You are required to study the database schema and its associated explanation below and to familiarise yourself with its semantics. You will then turn the English language requests for information which will be displayed on the OHP screen, into SQL and write them in the spaces provided overleaf. You may use roughwork on this sheet or elsewhere but what is handed up is this single sheet with all your answers. Try to be neat !
You have 40 minutes to complete the assignment. Nobody is allowed to leave until at least 15 minutes into the assignment and no latecomers after 15 minutes will be admitted. The actual data types used in these tables (char vs. int vs. long, etc.) do not matter.
PLAYERS(P#, PNAME, YOB, YEAR_JOINED)
CAPTAINS (T#, P#)
GAMES (T#, P#, WON, LOST)
PENALTIES (PEN#, P#, AMOUNT)
The interpretation of these tables is as follows. PLAYERS contains information on players in the club including their unique player number (P#, the primary key), their name (PNAME) year of birth (YOB) and the year they joined the club (YEAR-JOINED). For this club there are a number of teams, each with a unique team number and the single captain for each team is listed in the CAPTAINS table which shows the team number (T#, primary key) and the player number of the captain of that team (P#). Each player can have played for more than one team and for each team that each player has played for the table GAMES shows the number of games won and the number of games lost. There are no draws and since a player could have played for more than one team, the combination of T#, P# is the primary key. Players occasionally incur penalties for rowdy behaviour etc. (lets say it is a rugby club ;-) and these are recorded in the PENALTIES table which records a unique penalty number (PEN#), the player number of the player penalised (P#) and the amount of the penalty (AMOUNT). Players can have any number of penalties.
And now, here are some of the correct answers:
Question 1: Give the names of the team captains.
SELECT DISTINCT PNAME
FROM PLAYERS, CAPTAINS
WHERE CAPTAINS.P# = PLAYERS.P# ;
Question 2: Give the names of players who are team captains and who have not incurred any penalties.
SELECT DISTINCT PNAME
FROM PLAYERS, CAPTAINS
WHERE CAPTAINS.P# = PLAYERS.P#
AND P# NOT IN
( SELECT PENALTIES.P# FROM PENALTIES );
Question 3: Give the names of players who are team captains and who have incurred penalties.
SELECT DISTINCT PNAME
FROM PLAYERS, CAPTAINS
WHERE CAPTAINS.P# = PLAYERS.P#
AND P# IN (SELECT PENALTIES.P# FROM PENALTIES );
Question 4: What is the average number of games won by players who are not captains and who have no penalties and who joined in 1997.
SELECT AVG(WON)
FROM GAMES, PLAYERS
WHEREGAMES.P# = PLAYERS.P#
AND YEAR_JOINED = 1977
AND GAMES.P# NOT IN (SELECT P# FROM CAPTAINS)
AND GAMES.P# NOT IN (SELECT P# FROM PENALTIES);
Question 5: For each player who has played games for any team, what is their overall standing, i.e. difference between games won and lost.
SELECT P#, SUM(WON) - SUM(LOST)
FROM GAMES
GROUP BY P;
Question 6: How many games has the player won who has had to pay the largest total amount in fines.
CREATE VIEW VIEW1(P#, SUM_AMT) AS
SELECT P#, SUM(AMOUNT)
FROM PENALTIES
GROUP BY P#;SELECT SUM(GAMES.WON)
FROM GAMES, VIEW1
WHERE GAMES.P# = VIEW1.P#
AND VIEW1.SUM_AMT =
(SELECT MAX(SUM_AMT)
FROM VIEW1);
Question 7: How many players are in the club.
SELECT COUNT(*)
FROM PLAYERS;
Question 8: How many games more has team "X" won than it has lost (retrieve a negative number if team X has lost more than it has won.
SELECT SUM(WON) - SUM(LOST)
FROM GAMES
WHERE GAMES.T# = 'X';
Question 1: Give the total number of players who have played for each team.
SELECT T#, COUNT(*)
FROM GAMES
GROUP BY T#;
Question 2: Give the names of players who joined after 1990 and who have not incurred any penalties.
SELECT PNAME
FROM PLAYERS
WHERE YEAR_JOINED > 1990
AND PLAYERS.P# NOT IN
( SELECT P# FROM PENALTIES );
Question 3: Give the names of players who have joined after 1990 and who have incurred penalties.
SELECT PNAME
FROM PLAYERS, PENALTIES
WHERE YEAR_JOINED > 1990
AND PLAYERS.P# = PENALTIES.P#;
Question 4: What is the average number of games won by players who are not captains and who have no penalties and who joined in 1997.
SELECT AVG(WON)
FROM GAMES
WHERE GAMES.P# NOT IN(SELECT P# FROM CAPTAINS)
AND GAMES.P# NOT IN (SELECT P# FROM PENALTIES)
AND GAMES.P# = PLAYERS.P#
AND PLAYERS.YEAR_JOINED = 1997;
Question 5: What is the year of birth of the player who has incurred the most number of penalties.
CREATE VIEW BADDIES (P#,TOT) AS
SELECT P#, COUNT(*)
FROM P
GROUP BY P;CREATE VIEW BADDEST(P#,TOT) AS
SELECT P#, MAX(TOT)
FROM BADDIES;SELECT YOB
FROM PLAYERS,BADDEST
WHERE PLAYERS.P# = BADDEST.P#;
Question 6: How many games has the player won who has had to pay the largest total amount in fines.
SELECT GAMES.WON
FROM GAMES, PENALTIES
WHERE GAMES.P# = (SELECT P#
FROM PENALTIES
GROUP BY P#
HAVING MAX(SUM(AMOUNT));;
Question 7: What is the total amount of penalties that have been issued.
SELECT COUNT(*)
FROM PENALTIES;
Question 8: For each player who has penalties, what is the total number of times (s)he has been penalised.
SELECT COUNT(*), P#
FROM PENALTIES
GROUP BY P#;