Assume that we can get the title and score from movie_T table View 19. Create a
ID: 3706438 • Letter: A
Question
Assume that we can get the title and score from movie_T table View 19. Create a view (called movie_rate) which shows movies with the following information: movie title, score and score rate. The score rate is derived data and is decided using the following rule: IF score> 7 THEN display 'Good'; ELSEIF score> 6 THEN display 'Average'; ELSE display Poor; So, the movie_rate view will show data like following TITLE SCORE Score Rate Young Guns You Only Live Twice Young Frankenstein 6.3 6.8 Average Average Good (Note: When you create the view, use one block SQL query. 20. Form movie_rate view, list all the movies whose title starts with 'X' and has "Good" score ratingExplanation / Answer
Answer 19: We will use CASE statement which is closer to if-else
CREATE VIEW movie_rate as SELECT *, CASE
WHEN score>7 THEN 'Good'
WHEN score>6 THEN 'Average'
ELSE 'Poor'
END as Score_rate FROM movie_T;
--------------------------------------------------------------------------------------------
Answer 20;
SELECT * FROM movie_rate WHERE title LIKE 'X%' AND Score_rate='Good';