CIS 450/550: Database and Information Systems Homework 1 ‒ SQL

CIS 450/550: Database and Information Systems Homework 1 ‒ SQL Due by by 23:59:59 EDT on Sept. 16, 2019 For all questions, please submit the query only. Submission instructions are on Piazza Part 1: Querying an AWS Oracle database (60 points) IMDB Database. The database you will use consists of a portion of the Internet Movie Database (IMDB.com). It has been uploaded to Amazon's AWS/RDS and conforms to the following schema (where keys are underlined): ● movie (   1. (2 points) Connecting to the Oracle instance. For all questions, please submit the query only. Write SQL queries for each of the following questions. A good reference for Oracle syntax can be found at 2. (3 points) 3. (5 points) Print the first 20 distinct keywords that begin with a letter (rather than a number) in alphabetical order. Schema: (kwd_name) 4. (5 points) For the movie 'Kill Game' released in 2018, print the name of all actors and their role in the movie. 5. (5 points) Print the title and number of genres for each movie released in 2001 whose title begins with ‘A’. If the movie has no genres, the number of genres should be 0. Schema: (title 6. (7 points) Print the movie title and director name of all movies with genre ‘Comedy’ that appeared in 2017 and were directed by a director whose (first) name starts with ‘Dav’. 7. (7 points) Print the name of the actor who has, in a given movie, played the largest number of different characters, along with the title of the movie and number of different characters played. Result schema: ( 8. (8 points) Print the crew_id of directors who have directed at least one movie in every genre that starts with ‘A’. Note that directors are crew rather than actors. Your query should instance independent, e.g. if another genre starting with ‘A’ was added along with new movies of that genre your query should still be correct. Result schema: (crew_id) ● movie (movie_id: number(7), title: varchar(255), runtime: number(7), release_year: number(7), rating: float(2), num_ratings: number(7)) ● crew (id: number(7), gender: number(7), name: varchar(255)) ● movie_cast(id: number(7), gender: number(7), name: varchar(255)) -- Think of this as a relation of actors ● crew_in (movie_id: number(7), crew_id: number(7), job: varchar(255)) -- movie_id REFERENCES movie(movie_id) -- crew_id REFERENCES crew(id) ● movie_genre (movie_id: number(7), genre_name: varchar(255)) -- movie_id REFERENCES movie(movie_id) -- genre_name REFERENCES genre(name) 9. (8 points) Print name of directors who have only directed near-top rated movies after 2015. By “near-top” we mean in the interval [m-1, m], where m is the max rating of movies released after 2015. Recall that directors are crew rather than actors. Schema: (name) 10. (10 points) Print the name of director and year, where the director has directed more than one top-rated movie in that year, where “top-rated” means the max rating within the year. Schema: (name, year)   Part 2: Setting up and Querying a MySQL Instance (40 points) The goal of the second part of the homework is to get experience with MySQL (MariaDB v. 10.2.25), one of the most popular open source databases, and to understand how to create your …………………………………CONTINUED………………………………….