CS660 Fall 2024 – Written Assignment 1
Title: SQL
Suppose you are given the following relational schema for a sample database:
MotionPicture (id, name, rating, production, budget)
User (email, name, age)
Likes (uemail, mpid)
Movie (mpid, boxoffice_collection)
Series (mpid, season_count)
People (id, name, nationality, dob, gender)
Role (mpid, pid, role_name)
Award (mpid, pid, award_name, award_year)
Genre (mpid, genre_name)
Location(mpid, zip, city, country)
Note: Primary keys are underlined and foreign keys are in blue.
Answer the following SQL Queries:
1. List movies part of the “Spiderman” franchise. The result should include the movie name, rating, production and budget.
2. List movies part of the “Spiderman” franchise produced by “Sony”. The result should include the movie name, rating and budget.
3. List movies part of the “Spiderman” franchise having a box office collection of more than 200 Million dollars. List the movie name, rating, production, budget and box office collection.
4. List all distinct motion pictures shot in the city of Boston in USA.
5. Find all people who have received more than 2 awards for a single motion picture in the same year. List the person name, motion picture name, award year and award count.
6. Find the youngest actor to win at least one award. List the actor names, their age (at the time they received the award). The age should be computed FROM the person’s date of birth to the award winning year only.
7. Find American producers who had a box office collection of more than $21 Million and less than or equal to $179 Million. List the producers name, movie name, box office collection and budget.
8. Find the top-two rated thriller movies that were shot exclusively in Boston, USA. List the movie names and their ratings.
9. Find all the movies with at least 124 likes by users of age of at least 34. List the movie names and the number of likes by those age-group users.
10.Find actors who have played a role in both “Marvel” and “Warner Bros” productions. List the actor names and their corresponding motion picture names.
11. Find the motion pictures that have a higher rating than the average rating of all comedy motion pictures. Show the names and ratings in descending order of ratings.
12. Find the top 5 movies with the highest number of people playing a role in that movie. Show the movie name, people count and role count for the movies.