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; is a person who was the director of a movie. Write the following queries in SQL (this time you may use aggregates 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. Find the pid of all known actors.

Here distinct is optional(Only if you want unique result)

select distinct P.pid
from Person P, appearsIn A
where P.pid=A.pid
and A.role='starring';

a2. Find the pid of all known actors and directors.

Here distinct is optional(Only if you want unique result)

select distinct P.pid
from Person P, appearsIn A
where P.pid=A.pid
and A.role in ('starring','director');

b. Find the name and age of each actor who appeared in both movie released in 1995 and one released in 2005.

select P.name, P.age
from Person P, appearsIn A
where P.pid=A.pid
and A.role='starring'
and 1<=(select count(*) from Movie M where M.mid=A.mid and M.yrearReleased=1995)
and 1<=(select count(*) from Movie M where M.mid=A.mid and M.yrearReleased=2005);

Explanation: 2 conditions added. actors count for the movie released in 2005 and 1995 should be 1 or greater than 1.

c. Find the name of each person whose lifetime income exceeds the budget of all the movies that he or she appeared in.

select P.name
from Person P, appearsIn A
where P.pid=A.pid
and A.role='starring'
and
not exist (select * from Movie M where M.mid=A.mid and P.lifetimeIncome<M.budget)

d. Find the name of each person who directed the movie with largest budget.

select P.name
from Person P, appearsIn A
where P.pid=A.pid
and A.role='director'
and A.pid in (select director from Movie M where M.budget=(select max(budget) from Movie));

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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