Instructions: For this assignment you must submit a text document (with txt extension) with the sql answers to the following questions. Consider the MOVIES relational database schema description: A movie can have 0 or more actors A movie can have 1 or more categories A movie has 1 and only one language Rating is one of these values: 'G','PG','PG-13','R','NC-17' People can comment about the movies in a webpage, and the comments are stored in the comments table. The reviewer_name is introduced by the person on each comment, so we don't have a table with reviewers. A reviewer can create any number of comments about a movie The comments will have a score about the movie with values from 0 to 100. Note, the last_update fields are going to be stored as a "timestamp". IMPORTANT: 2 entries in the same relation can have the same last_update, so, for example, 2 movies can have the same last_update value. The relations are: ACTOR (actor_id, first_name, last_name, last_update) LANGUAGE (language_id, name, last_update) CATEGORY (category_id, name, last_update) FILM (film_id, title, description, release_year, language_id, length, rating, last_update) FILM_ACTOR(actor_id, film_id, last_update) FILM_CATEGORY (film_id, category_id, last_update) COMMENTS (review_id, film_id, reviewer_name, comment, score, last_update) 2. (40 points) Write the following queries in MYSQL, using the schema provided above. The queries will need to work on MySQL and return the expected results with the provided data set. This will make it easier for you because you can test the queries and see if they work or fail. The query (set of queries) tested on Mysql can be copied and pasted as a solution (in fact that is what is expected) in a text file. We will test your queries (copy-paste) in a real environment to check if they work or not. Note: In the assignment page in Canvas, it is attached a document (movie_mysql.sql) with the test data to load in mysql/sqlfiddle, and another document (expected_results.zip) with contains a file with the expected result for each of the queries. In that way, you can test your own query to check if it is correct or not, and if not, review it until you find the right solution. NOTE: The right solution must return what is asked, not just match with the expected result in the file. It needs to work as expected for any possible data and not only for the test data. A wrong query could, maybe, return the same correct result with the test data, but still be incorrect for other data so be careful about that. Your queries must not be "state-dependent", that is, they should work without modification even if another instance of the database is given or if the ID's change. That means don't use directly specific ID's in the query, the query will need to find the ID's based in the data provided. For example: If the data in the question is the Movie Title, we don't know a priori the ID of that movie, we will need to write the query in a way that is able to use the movie title, and not directly the ID NOTE: THIS IS SQL, NOT RELATIONAL ALGEBRA. Any answer provided in RELATIONAL ALGEBRA will be graded with a 0. 6. (5 points) Find the actors name and last name having movies in all the categories 7. (5 points) Calculate the average number of actors per movie for each category of movies, order the list from higher average to lower average. 8. (5 points) Show the 10 movies with the bigger number of actors (show the name of the movie and the number of actors for each movie) ordered by number of actors from bigger to smaller and in case of tie ordered by movie title in alphabetical order
