CO226 -  Database Systems - Lab Sheet Number - 05 - Writing SQL Queries – Part I - Solved

Lab Task01: (25 marks)
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

 

Lab Task02: (75 marks = 3marks x 25)
Write the following SQL queries using MySQL, to retrieve the data from the database, you created in task01 above.

1.    Find all the details about the movies presented in the populated MOVIE table.

2.    Find all the details about the movies directed by ‘James Cameron’.

3.    Find all the details about the movies directed by ‘James Cameron’, on or after the year 2000.

4.    Find all the stars presented in the rating table.

5.    Find the distinct stars presented in the table.

6.    Find movie ids and each movie’s director.

7.    Find movie ids, titles, years of the movies directed by ‘Steven Spielberg’.

8.    Obtain the Cartesian product of the details presented in two tables MOVIE and RATING.

9.    Obtain the Cartesian product of the movie id and title from MOVIE table with movie id, reviewer id and stars from RATING table.

10.  Select movie ids of each movie with its title, reviewer id and stars received.

11.  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.

12.  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).

13.  Select reviewer ids with the corresponding movie id reviewed by each reviewer.

14.  Select distinct tuples from the results produced by the execution of the above query (query number 14).

15.  Select each movie id with its corresponding title, reviewer id, reviewer name and stars received.

16.  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.

17.  Select movie title with its corresponding reviewer name and stars, where the movie's rating date is missing.

18.  Select all the movie director names and reviewer names into one column. Do not include null values.

19.  Select the details about the reviewers who have a last name called ‘Martinez’.

20.  Select the details about the ratings which have been rated before the 10th day of the month. Use substring comparison.

21.  Write the above query (query number 20) without using substring comparison.

22.  Show the effect of giving one more star to the movies reviewed by ‘Brittany Harris’. Here select relevant details from the RATING table.

23.  Select movie titles with its reviewer name and stars received. Order the result by movie title in the alphabetical order.

24.  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.

25.  Write a nested query to retrieve the details of the movies directed by a director who is also a reviewer.