(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.
| 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 |
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);