(a) Retrieve movies information without mentioning their column names.
Ans: SELECT * FROM MOVIE;
(b) List business done by the movies showing only MovieID, MovieName and BusinessCost.
Ans: SELECT MovieID, MovieName, BusinessCost
WHERE BusinessCost <> 0; [OR WHERE BusinessCost IS NOT NULL;
(c) List the different categories of movies
Ans: SELECT DISTINCT Category FROM MOVIE;
(d) Find the net profit of each movie showing its ID, Name and Net Profit.
(Hint: NetProfit = BusinessCost –ProductionCost)
Make sure that the new column name is labeled as NetProfit. Is this column now a part of the MOVIE relation. If no, then what name 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?
Ans: SELECT MovieID, MovieName, (BusinessCost – ProductionCost) AS NetProfit
WHERE ReleaseDate IS NOT NULL;
(In this query we are finding the NetProfit by subtracting ProductionCost from BusinessCost and naming the subtraction as NetProft. Such columns which are not part of table are called Generated Columns. Because we cannot find the profit of movie which has not yet released, therefore we have set the criteria in WHERE clause to ReleaseDate IS NOT Null.)
(e) List all movies with ProductionCost greater than 80,000 and less than 1,25000 showing ID, Name and ProductionCost.
Ans: SELECT MovieID, MovieName, ProductionCost from
WHERE ProductionCost BETWEEN 80000 AND 125000;
(f) List all movies which fall in the category of Comedy or Action.
Ans: SELECT *
WHERE Category IN (‘Action’, ‘Comedy’);
(g) List the movies which have not been released yet.
Ans: SELECT *
FROM MOVIE WHERE ReleaseDate IS NULL;