2. Assume a database with schema ACTOR name,age,address,nationality) MOVIE(title

Question

2. Assume a database with schema ACTOR name,age,address,nationality) MOVIE(title,year,budget, director-name,studio) APPEARS(name,title,salary) where the table ACTOR contains information about actors and actresses, and name is the primary key: MOVIE contains information about movies (their title, the year they were made, their budget, the name of the person who directed the movie and the studio who produced it) and title is the primary key; and APPEARS tells us which actor appeared in which movie (so name and title are foreign keys in APPEARS), and how much s/he was paid for acting in the movie. Note that an actor may have appeared in several movies, and a movie may have several actors appearing on it; the key of APPEARS is (name,title) Write the following questions in SQL: (a) List the names of actors that have never appeared in any movie produced by studio 'MGM. (b) For each director, list the number of movies (i.e. how many) with budget over $1,000,000 that s/he has directed. (c) List the studios that produced more movies in the 90's (1990-1999) then in the 80's (1980-1989) (d) The payroll of a movie is the sum of salaries of all actors in that movie. Find movies where the payroll is larger than the budget. (e) Find the number of Australian actors on each movie but only if there are at least 5 actors (of any nationality) on that movie

Explanation / Answer

1:
list names of actors that have never apperaed in any movie produced by
Studio MGM

select distinct a.name
from actor a
join appears app
on a.name = app.name
join movie m
on m.title = app.title
where studio <> 'MGM'

2. for each director , list the nubmber of movies with budget over $1000000
that he has directed

select director_name, count(title)
from movie
where budget >= 1000000
group by director_name

3. list the studios that produced more movies in 90's (1990-1999) than inthe 80's(1980-1989)

select movies_90.studio , movies_90.movie_count
from
(select studio, count(title) movie_count
from movie
where year between 1990 and 1999
group by studio) movies_90
left outer join
(select studio, count(title) movie_count
from movie
where year between 1980 and 1989
group by studio) movies_80
on movies_90.studio = movies_80.studio
where coalesce(movies_90.movie_count,0) > coalesce(movies_80.movie_count,0)

4. The payroll of a movie is sum of sallaries of
all actors in that movie. Find movies where the payroll
is larger than the budget

select title
from (
select m.title, budget, sum(salary) payroll
from movie m
join appears app
on m.title = app.title
group by m.title, budget
having sum(salary) > budget
)

5. Find the number of Australian actors on each movie but only if there are atlest 5 actors
(of any nationality) on that movie

select australian_actors.title, australian_actors.actor_Australian_nation_count
from
(select title, sum(
case
when nationality == 'AUS' then 1
else 0
end
) actor_Australian_nation_count
from actor a
join appears app
on a.name = app.name
group by title
) australian_actors
join
(select title, count(app.name) actor_count
from actor a
join appears app
on a.name = app.name
group by title
having count(app.name) >= 5
) actor_aleast_5_nationality
on australian_actors.title = actor_aleast_5_nationality.title

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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