Suppose that you have started a new movie-rating website and you have been collecting data on reviewer’s rating of various movies.
Figure01 shows a certain instance of the populated database. Log into MySQL server and create a database named E18XXXLab05. Create necessary tables in the database considering the following:
- Decide suitable names and data types for each field,
- Define primary keys and foreign keys for each table,
- Choose referential integrity options that should be used on each of the following operations
- ON UPDATE
- ON DELETE
Write the following SQL queries using MySQL, to retrieve the data from the database, you created in task01 above.
- Find all the details about the movies presented in the populated MOVIE
- Find all the details about the movies directed by ‘James Cameron’.
- Find all the details about the movies directed by ‘James Cameron’, on or after the year 2000.
- Find all the stars presented in the rating table.
- Find the distinct stars presented in the table.
- Find movie ids and each movie’s director.
- Find movie ids, titles, years of the movies directed by ‘Steven Spielberg’.
- Obtain the Cartesian product of the details presented in two tables MOVIE and RATING.
- Obtain the Cartesian product of the movie id and title from MOVIE table with movie id, reviewer id and stars from RATING
- Select movie ids of each movie with its title, reviewer id and stars
- Select movie ids of each movie with its title, reviewer id and stars received, where number of stars are less than or equal to three.
- Select movie ids of each movie with its title, reviewer id and stars received, where the number of stars is between two and four (two and four inclusive).
- Select reviewer ids with the corresponding movie id reviewed by each reviewer.
- Select distinct tuples from the results produced by the execution of the above query (query number 14).
- Select each movie id with its corresponding title, reviewer id, reviewer name and stars
- Select each movie id with its corresponding title, reviewer id, reviewer name and stars received, where the number of stars received is equal to five.
- Select movie title with its corresponding reviewer name and stars, where the movie’s rating date is missing.
- Select all the movie director names and reviewer names into one column. Do not include null values.
- Select the details about the reviewers who have a last name called ‘Martinez’.
- Select the details about the ratings which have been rated before the 10th day of the month. Use substring comparison.
- Write the above query (query number 20) without using substring comparison.
- Show the effect of giving one more star to the movies reviewed by ‘Brittany Harris’. Here select relevant details from the RATING
- Select movie titles with its reviewer name and stars Order the result by movie title in the alphabetical order.
- Select movie titles with its stars received and rating date. Order the result by movie title in the alphabetical order, then by stars and rating date both in descending order.
- Write a nested query to retrieve the details of the movies directed by a director who is also a reviewer.