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:
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.
c) Using table level constraint, make TeamID as primary key.
d) Show the structure of the table TEAM using SQL command.
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.
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.
You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.
Start your 14-day free trial to unlock the full solution →Build the Sports database end to end: CREATE DATABASE, a TEAM table whose TeamID (1-9, via CHECK) becomes the primary key through a table-level constraint, DESC to show the structure, a multi-row INSERT, then a MATCH_DETAILS table whose two TeamID columns are tied back to TEAM with foreign keys so only existing teams can appear in a match.
a) Create the database
CREATE DATABASE Sports;
USE Sports;
USE Sports; makes it the active database so the tables that follow land inside it.
b) + c) Create TEAM with the required constraints (primary key at table level)
CREATE TABLE TEAM (
TeamID INT CHECK (TeamID BETWEEN 1 AND 9),
TeamName VARCHAR(20) NOT NULL,
PRIMARY KEY (TeamID) -- table-level constraint (part c)
);
CHECK (TeamID BETWEEN 1 AND 9)enforces the 1-9 integer range.VARCHAR(20)comfortably holds names of at least 10 characters ("Team Hurricane" is 14).- Writing
PRIMARY KEY (TeamID)as its own clause after the column list — rather than beside the column — is exactly what "table-level constraint" means.
d) Show the structure
DESC TEAM;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| TeamID | int | NO | PRI | NULL | |
| TeamName | varchar(20) | NO | NULL |
e) Insert the four teams
INSERT INTO TEAM VALUES
(1, 'Team Titan'),
(2, 'Team Rockers'),
(3, 'Team Magnet'),
(4, 'Team Hurricane');
One VALUES keyword, comma-separated row tuples.
f) Show the contents
SELECT * FROM TEAM;
| TeamID | TeamName |
|---|---|
| 1 | Team Titan |
| 2 | Team Rockers |
| 3 | Team Magnet |
| 4 | Team Hurricane |
g) Create MATCH_DETAILS with appropriate domains and constraints, and load it
CREATE TABLE MATCH_DETAILS (
MatchID VARCHAR(3) PRIMARY KEY,
MatchDate DATE,
FirstTeamID INT,
SecondTeamID INT,
FirstTeamScore INT,
SecondTeamScore INT
);
INSERT INTO MATCH_DETAILS 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-20', 1, 4, 32, 87),
('M6', '2018-07-21', 2, 3, 67, 51);
Domain choices: VARCHAR(3) fits IDs like 'M1'; DATE stores the match date properly (enabling date queries later); the four numeric columns are INT.
h) Add the foreign keys referencing TEAM
ALTER TABLE MATCH_DETAILS
ADD CONSTRAINT fk_first_team
FOREIGN KEY (FirstTeamID) REFERENCES TEAM(TeamID),
ADD CONSTRAINT fk_second_team
FOREIGN KEY (SecondTeamID) REFERENCES TEAM(TeamID);
``` …
Unlock everything free for 14 days
- Full step-by-step solutions
- Concept-first explanations
- Methods, shortcuts & mistakes
- PYQ mapping + timed mock tests
Full access for 14 days. No credit card required.