Question

PART II: Answer 4 of the 5 problems from this part (13 pts each; 52 pts total) important Fil out the box accompanying each problem to indicate if it should be graded If you leave the boxes blank, we will not count your best 4 problems. l-1. Relational Model and SQL (13 pts total) Recall the relations in our movie database DONT GRADE GRADE Movie(id, name, year, rating, runtime, genre, earnings rank) Person(id, name, dob, pob) Directori director id, movie id) Actor(actor_id, movie id) Oscar(movie_ id, person id, type, year a. (5 points) Write a SQL command to find the names of all people who won an Oscar in the 198Os (from 1990 to 1989). A given person's name should appear at most once. b. (5 points) Write a SQL command to find the name and runtime of the R-rated movie with the largest runtime. Hint: Use a subquery (3 points) Give an example of a problem involving this database that would be solved using an outer join, and explain why the outer join would be needed. You do not need to actually write the query C.

Answer 1

ANS1:

Select distinct(name) from person where person_id IN (select person_id from oscar where year between 1980 and 1990);

ANS2:

Select name,runtime from movie where runtime = (select max(runtime) from movie) and rating='R%';

ANS3:

In the above database, if we want to join table Director and Actor,there arises a conflict of movie_id as this column is present in both tables.This may arise duplicacy or other problem.But with the help of outer join the column movie_id is select only once as this lies in intersection area of both tables.