Assignment 3 Queries

CSE 180 Database Systems I 
Lab Assignment 3
1 Introduction
In this assignment, you are asked to write a number of SQL expressions that perform queries on the movie
database created in the previous assignment. The queries should correctly run on the PostgreSQL server.
2 Queries
(Q1) List the name and age of every actor.
(Q2) List the first name and last name of every actor, split into two columns. (HINT: You may find the
SPLIT PART command useful here.)
(Q3) List the minimum, maximum, and average values over all of the imdb ratings.
(Q4) List the combined, average, minimum, and max age of the actors in each gender.
(Q5) List all of the actor names, semi-colon separated, that acted in the same movie. Your final list should
include both the movie title and semi-colon separated actor names.
(Q6) List the full record for the top 3 movies according to the average ratings of both the Reviews rating
and the Imdb rating. You do not need to list their average rating. (HINT: You may use UNION
function to integrate desirable values into one table and may use ROW NUMBER function to add
an extra column so that you can choose the tuples you want.)
(Q7) List the number of characters, and the number of sentences, in each customer review. You should also
list the associated movieid. (HINT: You can consider each period the end of a sentence.)
(Q8) Count the number of movies for each different genre. List the genre and the count. (HINT: You may
find the REGEXP SPLIT TO TABLE command useful here.)
(Q9) List the name and age of each actor when they first appeared in a movie, along with the movie title.
(Q10) List the average customer rating of each movie genre.
(Q11) List the movie titles, and the absolute difference in rating between the customer reviews and the imdb
rating. Sort your results in descending order based on this difference. Additionally, return a column
that indicates ”imdb” if the imdb rating is higher, or ”customer” if the rating from the reviews table
is higher. (HINT: Your final output table shouldn’t have any negative numbers. Also, you can use a
CASE command to condition the column value returned.)
3 Submission
Save the commands issued and the corresponding query results in a file named Queries.txt. Upload this file
to the Lab Assignment 3 submission page on Canvas by 11:59pm on Friday May 15th.
1