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".

(b) Create a table "TEAM" with following considerations:

1. It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
2. Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.

(c) Using table level constraint, make TeamID as the primary key.

(d) Show the structure of the table TEAM using a SQL statement.

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

(f) Show the contents of the table TEAM using a DML statement.

(g) Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types 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-18	1	4	32	87
M6	2018-07-17	2	3	67	51

(a)

CREATE DATABASE Sports;

(b)

CREATE TABLE TEAM (
    TeamID INT,
    TeamName VARCHAR(20));

(c)

ALTER TABLE TEAM
ADD PRIMARY KEY(TeamID);

(d)

DESCRIBE TEAM;

Output

+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| TeamID   | int      | NO   | PRI | NULL    |       |
| TeamName | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+

(e)

INSERT INTO TEAM (TeamID, TeamName) VALUES 
(1, 'Team Titan'),
(2, 'Team Rockers'),
(3, 'Team Magnet'),
(4, 'Team Hurricane');

(f)

SELECT * FROM TEAM;

Output

+--------+----------------+
| TeamID | TeamName       |
+--------+----------------+
|      1 | Team Titan     |
|      2 | Team Rockers   |
|      3 | Team Magnet    |
|      4 | Team Hurricane |
+--------+----------------+

(g)

CREATE TABLE MATCH_DETAILS (
    MatchID VARCHAR(10),
    MatchDate DATE,
    FirstTeamID INT,
    SecondTeamID INT,
    FirstTeamScore INT,
    SecondTeamScore INT,
    CONSTRAINT PK_MatchID PRIMARY KEY (MatchID),
);
INSERT INTO MATCH_DETAILS (MatchID, MatchDate, FirstTeamID, SecondTeamID, FirstTeamScore, SecondTeamScore) VALUES
('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-18', 1, 4, 32, 87),
('M6', '2018-07-17', 2, 3, 67, 51);

Output

SELECT * FROM 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-18 |           1 |            4 |             32 |              87 |
| M6      | 2018-07-17 |           2 |            3 |             67 |              51 |
+---------+------------+-------------+--------------+----------------+-----------------+