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
   302   303   304   305   306   307   308   309   310   311   312