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”.
Ans: CREATE DATABASE Sports;
(b) Create a table “TEAM” with following considerations:
It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.
Ans: CREATE TABLE TEAM (
TeamID INT CHECK (TeamID BETWEEN 1 AND 9),
TeamName VARCHAR(20)
CHECK ( LENGTH (TeamName) > 10));

(c) Using table level constraint, make TeamID as primary key.
Ans:          ALTER TABLE TEAM
ADD PRIMARY KEY (TeamID);

(d) Show the structure of the table TEAM using SQL command.
Ans:          DESC TEAM; or DESCRIBE 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)
Ans:          INSERT INTO TEAM
VALUES
(1, “Team Titan”) ,
(2, “Team Rockers”) ,
(3, “Team Magnet”) ,
(4, “Team Hurricane”);
(f) Show the contents of the table TEAM.
Ans:          SELECT * FROM TEAM;

(g)cNow create another table below. MATCH_DETAILS and insert data as shown in table. Choose appropriate domains and constraints for each attribute.

MatchIDMatchDateFirstTeamIDSecondTeamIDFirstTeamScoreSecondTeamScore
M12018-07-17129086
M22018-07-18344548
M32018-07-19137856
M42018-07-19245667
M52018-07-20143287
M62018-07-21236751

Ans:          CREATE TABLE MATCH_DETAILS (
MatchID CHAR(2) PRIMARY KEY,
MatchDate DATE,
FirstTeamID INT,
SecondTeamID INT,
FirstTeamScore INT,
secondTeamScore INT);

(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:          ALTER TABLE MATCH_DETAILS
ADD FOREIGN KEY (FirstTeamID) REFERENCES TEAM(TeamID),
ADD FOREIGN KEY (SecondTeamID) REFERENCES TEAM(TeamID);