Writing the several SQL queries on a relational Movie Database

Question 1. Write the SQL code to create the table structures for the entities shown in Figure1. The structure should contain the attributes specified in Figure1. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the diagram.

Question 2. The following tables provide some example data that will be inserted into the database. Write the INSERT commands necessary to place the following data in the tables that were created in Question 1. Alternatively provide the text files (copy and pasted into your final report) and the open/insert from file commands.

For the questions 3-12, use the tables created in question1 and the data inserted in question2.

Question 3. Write a command in SQL to find the year when the movie “American Beauty” released.

Question 4. Write the SQL command to find the name and year of the movies that contain the word “the”.

Question 5. Write a query in SQL to return the name of all reviewers and name of movies together in a single list.

Question 6. Write a query in SQL to find the movies which was released before 1990.

Question 7. Write a query in SQL to find the name of all reviewers who have rated 4 or more stars to their rating.

Question 8. Write the SQL command to list the names of the actors who did not play a role in the movie Avatar. Sort the output by movie title.

Question 9. Write the SQL command to find the titles of all movies that have more than two reviewers.

Question 10. Write the SQL command to find the movie title, and the total number of actors that appeared in that movie and arrange the result according to the number of actors in descending order.

Question 11. Update the director table to set the values in DETAIL_RETURNDATE to include a date of birth (dob) component and correct some of the name mistakes. For those not listed, assume the original name is correct, and give a null value for dob. Make each entry match the values shown in the following Table.