PART B. Given the following relational schema Persondpid: integer PRIMARY KEY, n

Question

PART B. Given the following relational schema Persondpid: integer PRIMARY KEY, name: string, age: integer, liftimelncome: float); Movieímid: integer PRIMARY KEY, title: string, budget: real, yearReleased: integer, director: integer REFERENCES Person); appearsln(pid: integer REFERENCES Person, mid: integer REFERENCES Movie, role: arcbar 10), PRIMARY KEY (pid.mid)); -role is either 'starring'or 'supporting in at cueres belio, anhe anddirectara penon who was the director of a movie. Write the following queries in SQL (this time you may use aggregates, group By, etc.): al Find the pid of all known actors a2. Find the pid of all known actors and directors. (This is NOT meant 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 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.) [In other words, find people who directed 3 or more movies. then select from among them the one(s) whose sum of budgets of movies directed is maximall

Explanation / Answer

1. select P.pid from Person P join appearsIn A on P.pid = A.pid;

2. select P.pid from Person P join appearsIn A on P.pid = A.pid join Movie M on M.mid = A.mid

3. select name, age from Person P join appearsIn A on P.pid = A.pid join Movie M on M.mid = A.mid where M.yearReleased = 1995
INTERSECT
select name, age from Person P join appearsIn A on P.pid = A.pid join Movie M on M.mid = A.mid where M.yearReleased = 2005

4. select name from Person P where P.lifetimeIncome > (select SUM(budget) from Movie M join appearsIn A on M.mid = A.mid
join Person P on P.pid = A.pid)

5. SELECT NAME FROM Person P join appearsIn A on P.pid = A.pid join
Movie M on M.mid = A.mid where budget = (Select MAX(budget) from Movie)

6. SELECT * FROM Person P join appearsIn A on P.pid = A.pid join
Movie M on M.mid = A.mid where
A.mid = (select mid from (SELECT mid, COUNT(*) as val1 from Movie GROUP BY mid)t where val1 >3)
and A.mid in (
select mid from (Select top 1* from (Select mid, SUM(budget) x from Movie GROUP BY mid) a order by x desc)c)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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