Page 307 - Information_Practice_Fliipbook_Class11
P. 307
Table: MATCH_DETAILS
MatchID MatchDate FirstTeamID SecondTeamID FirstTeamScore SecondTeamScore
M1 2018-07-17 1 2 90 86
M2 2018-07-18 3 4 45 48
M3 2018-07-19 1 3 78 56
M4 2018-07-19 2 4 56 67
M5 2018-07-20 1 4 32 87
M6 2018-07-21 2 3 67 51
h. Use the foreign key constraint in the MATCH_ DETAILS table with reference to TEAM table so that MATCH_DETAILS table records score
of teams existing in the TEAM table only.
Ans. a. CREATE DATABASE Sports;
b. CREATE TABLE TEAM (
TeamId integer CHECK (TeamId BETWEEN 1 AND 9),
TeamName VARCHAR(40) CHECK (Length(TeamName ) >= 10),
PRIMARY KEY (TeamId)
);
c. PRIMARY KEY (TeamId)
d. DESC Team
e. INSERT INTO Team VALUES (1, 'Team Titan');
INSERT INTO Team VALUES (2, 'Team Rockers');
INSERT INTO Team VALUES (3, 'Team Magnet');
INSERT INTO Team VALUES (4, 'Team Hurricane');
f. SELECT * FROM Team;
g. CREATE TABLE MATCH_DETAILS (
MatchID CHAR(4) PRIMARY KEY,
MathcDate DATE NOT NULL,
FirstTeamID INTEGER(3),
SecondTeamID INTEGER(3),
FirstTeamScore INTEGER(4),
SecondTeamScore INTEGER(3),
FOREIGN KEY(FirstTeamID) REFERENCES Team(TeamID),
FOREIGN KEY(SecondTeamID) REFERENCES Team(TeamID)
);
h. CREATE TABLE MATCH_DETAILS (
MatchID CHAR(4) PRIMARY KEY,
MathcDate DATE NOT NULL,
FirstTeamID INTEGER(3) REFERENCES Team(TeamID),
SecondTeamID INTEGER(3)REFERENCES Team(TeamID),
FirstTeamScore INTEGER(4),
SecondTeamScore INTEGER(3)
);
19. Using the sports database containing two relations (TEAM, MATCH_DETAILS), answer the following relational algebra queries.
a. Retrieve the MatchID of all those matches where both the teams have scored > 70.
b. Retrieve the MatchID of all those matches where FirstTeam has scored < 70 but SecondTeam has scored > 70.
c. Find out the MatchID and date of matches played by Team 1 and won by it.
d. Find out the MatchID of matches played by Team 2 and not won by it.
e. In the TEAM relation, change the name of the relation to T_DATA. Also change the attributes TeamID and TeamName to T_ID and
T_NAME respectively.
Ans. a. SELECT MatchID
FROM MATCH_DETAILS
Database Concepts and the Structured Query Language 293

