CO226 - Database Systems  - Lab Sheet Number - 06 - Writing SQL Queries - Part II - Solved

Write the following SQL queries using MySQL, to retrieve the data from the database, you created in the previous lab.

 

1.    Write a nested query to list the details of the movies directed by a director,

A.   who is also a reviewer. (1 mark)

B.   who is not a reviewer. (1 mark)

 

2.    Write a nested query to list the details of the movie ratings,

A.   reviewed by the reviewer ‘Sarah Martinez’. (1 mark)

B.   not reviewed by the reviewer ‘Sarah Martinez’. (1 mark)

 

3.    Write a nested query to list the movie ids where each movie has some rating,

A.   less than to any of the ratings received by the movie which has a movie id equal to 103. (1 mark)

B.   less than or equal to any of the ratings received by the movie which has a movie id equal to 103. (1 mark)

C.   equal to any of the ratings received by the movie which has a movie id equal to 103. (1 mark)

D.   greater than to any of the ratings received by the movie which has a

movie id equal to 103. (1 mark)

E.   greater than or equal to any of the ratings received by the movie which has a movie id equal to 103. (1 mark)

F.    not equal to any of the ratings received by the movie which has a movie id equal to 103. (1 mark)

 

4.    Write a nested query to list the reviewer ids who has the same (movie id, stars) combination on some movie which has a rating date equal to 2011-01-12. (5 marks)

 

5.    Find all the years that have a movie that received a rating of 4 or 5 and sort them in increasing order of the year. Write,

A.   a non-nested query. (5 marks)

B.   a non-correlated nested query. (5 marks)

 

6.    Find the titles of all movies that have no ratings. Write,

A.   non-correlated nested query. (5 marks)

B.   a correlated nested query. (5 marks)

7.    Some reviewers did not provide a date with their rating. Find the names of all reviewers who have a NULL value for the date. Write,

A.   a non-nested query. (5 marks)

B.   a non-correlated nested query. (5 marks)

C.   a correlated nested query. (5 marks)

 

8.    For each movie that has some rating, find A. the highest stars value received. (2 marks)

B.   the least stars value received. (2 marks)

C.   the average value of stars received. (2 marks)

D.   the sum of all the stars received. (2 marks)

E.   the number of times each movie was rated. (2marks)

 

In each of the above cases, return the movie title and asked stars value. Sort the results by movie title.

 

9.    Find the names of all the reviewers who have contributed three or more ratings.

Write,

A.   a non-nested query. (5 marks)

B.   a non-correlated nested query. (5 marks)

C.   a correlated nested query. (5 marks)

 

10. List the movie titles and average ratings, from the highest-rated to lowest-rated. If two or more movies have the same average rating, list them in alphabetical order. (5 marks)

 

11. Remove all ratings where the movie's year is before 1970 or after 2000. (5 marks)

 

12. Remove all ratings where the rating date is NULL. (5 marks)

 

13. Insert 5-star ratings by James Cameron for all movies in the database. Leave the review date as NULL. (5 marks)

 

14. For all movies that have an average rating of 4 stars or higher, add 25 to the release year. (Update the existing tuples. Do not insert new tuples). (5 marks)