Page 306 - Information_Practice_Fliipbook_Class11
P. 306
ii. Data of primary key only
iii. NULL data
iv. None of the mentioned
Ans. a. ii. Foreign Key
b. i. All data from the table
17. 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 Punjabi_Movie Comedy - 30500 -
a. Display all the information from the Movie table.
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: Net Profit = BusinessCost – ProductionCost)
Make sure that the new column name is labelled as NetProfit. Is this column now a part of the MOVIE relation. If no, then what name
is 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,25,000 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.
Ans. a. SELECT * FROM MOVIE;
b. SELECT MovieID, MovieName, BusinessCost FROM MOVIE;
c. SELECT DISTINCT(Category) FROM MOVIE;
d. SELECT MovieId, Name, (BusinessCost – ProductionCost) as 'Net Profit' FROM MOVIE;
e. SELECT MovieId, Name, ProductionCost FROM MOVIE WHERE ProductionCost > 80000 AND
ProductionCost < 125000;
f. SELECT * FROM MOVIE WHERE category = 'Comedy' OR category = 'Action';
g. SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;
18. 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.
292 Touchpad Informatics Practices-XI

