SQL to more advanced queries Note that you should have the movie database create

Question

SQL to more advanced queries

Note that you should have the movie database created for the previous

assignment. The movie database has three relations, with the schema listed

below.

Movie (MovieID, Title, Year, Score, Votes)

Actor(ActorID, Name)

Casting (MovieID, ActorID, Ordinal)

9. (10 points) List the names of actors, if any, that were cast in all the

movies that have votes between 3000 and 4000.

10. (10 points) From among the actors that have appeared in more than

ten movies, list their name and the average score of all the movies they

were cast in.

Explanation / Answer

9.

Select Name from Actor inner join Casting on Actor.ActorID = Casting.ActorID inner join Movie on Casting.MovieID = Movie.MovieID where Movie.votes between 3000 and 4000;

10.

Select Name,AVG(Score) from Actor inner join Casting on Actor.ActorID = Casting.ActorID inner join Movie on Casting.MovieID = Movie.MovieID group Actor.ActorID having count(Movie.MovieID) > 10;

Average score is computed for all movies done by the actor. So group by Actor.ActorID is used.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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