Table of Contents

• SET-A
• SET-B

SET-A

 

1. Create table `mobileModel` and `prepaid` as per following Table Instance Chart. Restrict deletion of data in mobileModel table if modelid exists in the prepaid    (½+½=1)

 

`mobileModel` table

 

Column Name	modelId	modelName
Key Type	Primary
Auto Increment	TRUE
Nulls/Unique		NOT NULL
Fk Table
Fk Column
Datatype	NUMBER	VARCHAR
Length	6	30

 

`prepaid` table

 

Column Name	custoID	custName	modelID	connection	plan
Key Type	Primary
Auto Increment	TRUE
Nulls/Unique		NOT NULL		NOT NULL
Fk Table			mobileModel
Fk Column			modelID
Datatype		VARCHAR	NUMBER	VARCHAR	NUMBER
Length		30	6	20	5,2

 

­­2. Show the structures of both the tables. (1)

 

3. Insert the following data:   (½+½=1)

 

For `mobileModel` table:

 

+---------+-----------+
| modelId | modelName |
+---------+-----------+
|    1    |   Xiomi   |
+---------+-----------+
|    2    |  Samsung  |
+---------+-----------+
|    3    |    Oppo   |
+---------+-----------+
|    4    |    Vivo   |
+---------+-----------+
|    5    |   Redmi   |
+---------+-----------+

 

For `prepaid` table

 

+---------+----------+---------+------------+--------+
| custoID | custName | modelID | connection | plan   |
+---------+----------+---------+------------+--------+
| 1       | Dominic  | 1       | Vodafone   | 399.00 |
+---------+----------+---------+------------+--------+
| 2       | Engi     | 1       | Jio        | 349.00 |
+---------+----------+---------+------------+--------+
| 3       | Hriata   | 3       | Jio        | 149.00 |
+---------+----------+---------+------------+--------+
| 4       | Peka     | 5       | Airtel     | 249.00 |
+---------+----------+---------+------------+--------+
| 5       | Hruaia   | 2       | Vodafone   | 699.00 |
+---------+----------+---------+------------+--------+
| 6       | Rindika  | 2       | Vodafone   | 459.00 |
+---------+----------+---------+------------+--------+
| 7       | Zami     | 4       | Jio        | 349.00 |
+---------+----------+---------+------------+--------+
| 8       | Malsawmi | 3       | Airtel     | 256.00 |
+---------+----------+---------+------------+--------+
| 9       | Hmingi   | 5       | BSNL       | 255.00 |
+---------+----------+---------+------------+--------+
| 10      | Zovi     | 4       | Airtel     | 799.00 |
+---------+----------+---------+------------+--------+

 

Sol:

 

CREATE TABLE IF NOT EXISTS `mobileModel`(
    modelId int(3) primary key auto_increment,
    modelName varchar(30) not null
)engine=InnoDB;
CREATE TABLE IF NOT EXISTS prepaid(
    custoID	int(3) primary key auto_increment,
    custName varchar(40) not null,
    modelID int(3),
    connection varchar(30) not null,
    plan decimal(5,2),
    foreign key (modelID) references mobileModel(modelId)
    on delete restrict
)engine=InnoDB;
insert into `mobileModel` (`modelName`) VALUES
('Xiomi'),
('Samsung'),
('Oppo'),
('Vivo'),
('Redmi');
insert into `prepaid` (`custName`, `modelID`, `connection`, `plan`) VALUES
('Dominic',1,'Vodafone',399.00),
('Engi',1,'Jio',349.00),
('Hriata',3,'Jio',149.00),
('Peka',5,'Airtel',249.00),
('Hruaia',2,'Vodafone',699.00),
('Rindika',2,'Vodafone',699.00),
('Zami',4,'Jio',349.00),
('Malsawmi',3,'Airtel',256.00),
('Hmingi',5,'BSNL',255.00),
('Zovi',4,'Airtel',799.00);
SELECT * FROM prepaid;

 

SET-B

 

1. Suppose your school management has decided to conduct football matches between students of Class XI and Class XII. Students of each class are asked to join any one of the three teams – Team Sangliana, Team JM Lloyd and Team Lalsangpuii. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:

 

• Create a database “Sports”. (½)
• Create a table “team” in the ‘Sports’ database 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 25 characters.
Using table level constraint, make teamId as the primary key.

 

Show the structure of the table `team` using a SQL statement.

 

As per the preferences of the two student teams were formed as given below. Insert these two rows in team table:

 

1. Row-1: Team Sangliana
2. Row-2: Team JM Lloyd
3. Row-3: Team Lalsangpuii

 

2. Now create another table `matchDetails` as per following Table Instance Chart below. (1)

 

Column Name	matchId	matchDate	teamId	OppTeam
Key Type	Primary
Auto Increment	TRUE
Nulls/Unique
Fk Table			team	team
Fk Column			teamId	teamId
Datatype		DATE	NUMBER	NUMBER
Length			1-9	1-9

 

3. Insert the following data in the `matchDetails` table: (1)

 

+------------+--------+---------+
|  matchDate | teamId | oppTeam |
+------------+--------+---------+
| 2022-10-04 |    1   |    2    |
+------------+--------+---------+
| 2022-10-04 |    2   |    1    |
+------------+--------+---------+
| 2022-10-05 |    2   |    3    |
+------------+--------+---------+
| 2022-10-06 |    3   |    1    |
+------------+--------+---------+

 

Sol:

 

CREATE TABLE IF NOT EXISTS `team`(
    teamId int(3) primary key auto_increment,
    teamName varchar(30) not null
)engine=InnoDB;

CREATE TABLE IF NOT EXISTS matchDetails(
    matchId	int(3) primary key auto_increment,
    matchDate date,
    teamId int(3),
    OppTeam int(3),
    foreign key (teamId) references team(teamId) on delete restrict,
    foreign key (OppTeam) references team(teamId) on delete restrict
)engine=InnoDB;

insert into `team` (`teamName`) VALUES
('Team Sangliana'),
('Team JM Lloyd'),
('Team Lalsangpuii');

insert into `matchDetails` (`matchDate`, `teamId`, `OppTeam`) VALUES
('2022-10-04',1,2),
('2022-10-04',2,1),
('2022-10-05',2,3),
('2022-10-06',3,1);

SELECT * FROM matchDetails;