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. Display all the information from the Movie table.
2. List business is done by the movies showing only MovieID, MovieName, and Total_Earning. Total_ Earning to be calculated as the sum of ProductionCost and BusinessCost.
3. List the different categories of movies.
4. Find the net profit of each movie showing its MovieID, MovieName, and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.
5. List MovieID, MovieName, and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.
6. List details of all movies which fall in the category of comedy or action.
7. List details of all movies which have not been released yet.

Answer 1

1. SELECT * FROM MOVIE;
2. SELECT MovieID, MovieName, ProductionCost + BusinessCost as “Total_Earning” FROM MOVIE;
3. SELECT DISTINCT Category FROM MOVIE;
   OR
   SELECT DISTINCT(Category) FROM MOVIE;
4. SELECT MovieID, MovieName, BusinessCost – ProductionCost as “Net Profict” FROM MOVIE;
5. SELECT MovieID, MovieName, ProductionCost FROM MOVIE WHERE ProductionCost > 10000 and ProductionCost < 100000;
6. SELECT * FROM MOVIE WHERE Category IN (‘Comedy’, ‘Action’);
7. SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;