PART B. Given the following relational schema Person(pid: integer PRIMARY KEY, n

Question

 PART B. Given the following relational schema   Person(pid: integer PRIMARY KEY, name: string, age: integer, liftimeIncome: float);    Movie(mid: integer PRIMARY KEY, title: string, budget: real, yearReleased: integer,    director: integer REFERENCES Person);    appearsIn(pid: integer REFERENCES Person, mid: integer REFERENCES Movie,              role: varchar(10), PRIMARY KEY(pid,mid));            -- role is either 'starring' or 'supporting'  In the queries below, an "actor" is a person who has appeared in a movie;  and a "director" is a person who was the director of a movie.  Write the following queries in SQL (this time you may use aggregates,  groupBy, etc.):   a1. Find the pid of all known actors.  a2. Find the pid of all known actors and directors. (This is NOT meant to     to be people who were _both_ actors and directors.)  b. Find the name and age of each actor who appeared in both a movie released in   1995 and one released in 2005.    c. Find the name of each person whose lifetime income exceeds the budget    of all of the movies that he or she appeared in.  d. Find the name of each person(s) who directed the movie(s) with the     largest budget.   e. Find the id of each person who directed 3 or more movies and    controlled the largest total amounts.  (The director of a movie is said    to control its budget.) 

Explanation / Answer

a1.

Select Person.pid from Person inner join appears on Person.pid = appears.pid ;

a2.

Select pid from appears union Select director from Movie;

b.

Select name,age from Person inner join appears on Person.pid = appears.pid inner join Movie on Movie.mid = appears.mid where yearReleased = 1995 or yearReleased = 2005; ;

c.

Select name,sum(budget) from Person inner join appears on Person.pid = appears.pid inner join Movie on movie.mid = appears.mid group by name having lifetimeIncome > sum(budget) ;

d.

Select name from Person inner join Movie on Movie.director = Person.pid where budget = (Select max(budget) from Movie);

e.

Select pid ,count(mid) from Person inner join Movie on Movie.director = Person.pid group by pid having count(mid) > 3;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.