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

Q. 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.

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.

Answer :-

a = Select MovieID as “”, MovieName as “”, Category as “” , ReleaseDate as “” , ProductionCost as “” , BusinessCost as “” from movie ;

b = Select MovieID, MovieName , BusinessCost from movie ;

c = Select distinct ( Category ) from movie ;

d = Select MovieID, MovieName , (BusinessCost – ProductionCost) as “Netprofit” from movie ;
No, this column is not a part of the MOVIE relation.
Column alias is coined for such columns.
If movie is not released than our query return negative values .

e = Select * from movie where ProductionCost between 80000 and 125000 ;

f = select * from movie where category = “Comedy” or category = “Action” ;

g = select * from movie where releaseddate is null ;