Chapter 8 Introduction To SQL Solution

1. Math the following clauses with their respective functions.

2. Choose appropriate answer with respect to the following code snippet.
CREATE TABLE student (
name CHAR(30),
student_id INT,
gender CHAR(1),
PRIMARY KEY (student_id));

(i) What will be the degree of student table?
a) 30
b) 1
c) 3
d) 4
(ii) What does ‘name’ represent in the above code snippet?
a) A table
b) A row
c) A column
d) A database
(iii) What is true about the following SQL statement?
SelecT * fROM student;
a) Display content of table ‘student’
b) Display column names and content of table ‘student’
c) Results in error as improper case has been used
d) Display only the column names of table ‘student’
(iv) What will be output of following query?
INSERT INTO student
VALUES (“Suhana”, 109, ‘F’),
VALUES (“Rivaan”, 102, ‘M’),
VALUES (“Atharv”, 103, ‘M’),
VALUES (“Rishika”, 105, ‘F’),
VALUES (“Garvit”, 104, ‘M’),
VALUES (“Shaurya”, 109, ‘M’);

(a) Error
(b) No Error
(c) Depends on Compiler
(d) Successful compilation of the query

(v) In the following query how many rows will be deleted?
DELETE student WHERE student_id =109;1
(a) row
(b) All the rows where student ID is equal to 109
(c) No rows will be deleted
(d) 2 rows

3. Fill in the blanks
(i) _______ declares that an index in one table is related to that in another table.
(a) Primary key
(b) Foreign Key
(c) Composite Key
(d) Secondary Key

Ans: (b) Foreign Key
(Because as it name implies ‘Foreign’ which means that field is related to field of another table. The key which is Primary key in another related table is called ‘Foreign Key’)

(ii) The symbol Asterisk (*) in a select query retrieves _________.
(a) All data from the table
(b) Data of primary key only
(c) NULL data
(d) None of the mentioned

Ans: (d) None of the mentioned.
(Because * retrieves data of all attributes (columns) from a table not all data from table.)

3. Consider the following MOVIE database and answer the SQL queries based on it.

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	–	100000	–
006	Punjabo_Movie	Comedy	–	30500	–

(a) Retrieve movies information without mentioning their column names.
(b) List business done by the movies showing only MovieID, MovieName and BusinessCost.
(c) List the different categories of movies
(d) Find the net profit of each movie showing its ID, Name and Net Profit.
(Hint: NetProfit = BusinessCost –ProductionCost)
Make sure that the new column name is labeled as NetProfit. Is this column now a part of the MOVIE relation. If no, then what name coined for such columns? What can you say about the profit of a movie which has not yet released? Does your query result show profit as zero?
(e) List all movies with ProductionCost greater than 80,000 and less than 1,25000 showing ID, Name and ProductionCost.
(f) List all movies which fall in the category of Comedy or Action.
(g) List the movies which have not been released yet.

4. 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:
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.
(c) Using table level constraint, make TeamID as primary key.
(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)
(g) Now 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