Page 100 - IPP-11

CHAPTER: STRUCTURED QUERY LANGUAGE (SQL)

1. Kunal has entered the following SQL command in the table ‘STUDENT’ that has TotalMarks as one of
the columns:
Select * from Student;
The output displayed is 20.
Then Kunal enters the following command:
SELECT * FROM STUDENT WHERE TotalMarks < 100;
The output displayed is 15.
Kunal then enters the following command:
SELECT * FROM STUDENT WHERE TotalMarks >= 100;
He predicts the output of the above query as 5. Do you agree with Kunal? Give reasons for your answer.
Ans. The output for the two SELECT statements is different since statement–1 shall return all the records and
columns of the table student.
Whereas in statement–2, output is to be obtained on the basis of column ‘Marks’ which may include NULL
values and is, hence, ignored by MySQL compiler and, as a result, less number of records are displayed.

2. Your school management has decided to organize cricket matches between students of Classes XI and
Supplement – Informatics Practices with Python–XI
XII. All the students are divided into four teams—Team Rockstars, Team BigGamers, Team Magnet and
Team Current. During the summer vacations, various matches are to be held between these teams. Help
your sports teacher do the following:
(a) Create a database “Sports”.
(b) Create a table “TEAM” with the following considerations:
(i) It should have a column TeamID for storing an integer value between 1 and 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.
(iii) Give the statement to make TeamID as the primary key.
(c) Show the structure of the table TEAM using SQL command.
(d) 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 Rockstars)
Row 2: (2, Team BigGamers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Current)
Ans. (a) create database sports;
(b) create table TEAM(TeamID int(9) primary key, TeamName varchar(10));
(c) desc TEAM;
(d) insert into TEAM values(1,“Team Rockstars”); #Row 1
insert into TEAM values(2,“Team BigGamers”); #Row 2
insert into TEAM values(3,“Team Magnet”); #Row 3
C.6 insert into TEAM values(4,“Team Current”); #Row 4