NCERT Exercise Solution Ch 8 – SQL
Introduction to Structured Query Language NCERT Exercise Solution
5. Suppose your school management has decided to conduct cricket matches between students of class XI and Class XII. Students of each class are asked to join any one of the four teams — Team Titan, Team Rockers, Team Magnet and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:
a) Create a database “Sports”.
Answer: CREATE DATABASE Sports;
b) Create a table “TEAM” with following considerations:
i) It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
ii) Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.
Answer:
CREATE TABLE TEAM ( TeamId integer CHECK (TeamId BETWEEN 1 AND 9), TeamName VARCHAR(40) CHECK (Length(TeamName ) >= 10), PRIMARY KEY (TeamId) );
c) Using table level constraint, make TeamID as primary key.
Answer: PRIMARY KEY (TeamId)
See Answer 5 – (b)
d) Show the structure of the table TEAM using SQL command.
Answer: DESC Team
e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:
Row 1: (1, Team Titan)
Row 2: (2, Team Rockers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Hurricane)
Answer:
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) Show the contents of the table TEAM.
Answer: SELECT * FROM Team;
g) Now create another table below. MATCH_DETAILS and insert data as shown in table. Choose appropriate domains and constraints for each attribute.
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.
Answer: (g) & (h)
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) );
OR
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) );
