Question

Consider the following MOVIE table and write the SQL query 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	-

1. Retrieve movies information without mentioning their column names.
2. List business done by the movies showing only MovieID, MovieName and BusinessCost.
3. List the different categories of movies
4. 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?
5. List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.
6. List all movies which fall in the category of Comedy or Action.
7. List the movies which have not been released yet.

Answer 1

a) SELECT * 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;

NetProfit is a derived/computed column. It is not a permanent part of the MOVIE relation. For movies not yet released, the profit will be NULL, not zero.

e) 

SELECT MovieID, MovieName, 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 = '-';