NCERT Exercise Solution Ch 8 – SQL
Introduction to Structured Query Language NCERT Exercise Solution
3. Fill in the blanks:
a) ____________ declares that an index in one table is related to that in another table.
i) Primary Key
ii) Foreign Key
iii) Composite Key
iv) Secondary Key
Answer: ii) Foreign Key
b) The symbol Asterisk (*) in a select query retrieves _________.
i) All data from the table
ii) Data of primary key only
iii) NULL data
iv) None of the mentioned
Answer: i) All data from the table
4. 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) Retrieve movies information without mentioning their column names.
Answer: SELECT * FROM MOVIE;
b) List business done by the movies showing only MovieID, MovieName and BusinessCost.
Answer: SELECT MovieID, MovieName, BusinessCost FROM MOVIE;
c) List the different categories of movies.
Answer: SELECT DISTINCT(Category) FROM MOVIE;
d) Find the net profit of each movie showing its ID, Name and Net Profit.
(Hint: Net Profit = BusinessCost – ProductionCost)
Answer: SELECT MovieId, Name, BusinessCost – ProductionCost as ‘Net Profit’ FROM MOVIE;
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?
Answer: SELECT MovieId, Name, BusinessCost – ProductionCost as ‘Net Profit’ FROM MOVIE;
For unreleased movie, it will show NULL as empty values are shown as NULL and not as zero.
e) List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.
Answer: SELECT MovieId, Name, ProductionCost FROM MOVIE WHERE ProductionCost > 80000 AND ProductionCost < 125000;
f) List all movies which fall in the category of Comedy or Action.
Answer: SELECT * FROM MOVIE WHERE category = ‘Comedy’ OR category =’Action’;
g) List the movies which have not been released yet.
Answer: SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;
