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)

1. (10 points) List the titles of movies that cast the actor Kevin Bacon.

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

that has votes between 3000 and 4000.

3. (10 points) List the titles of movies that have a higher score than some

movie in the database.

4. (10 points) List the titles of movies that have the highest score in the

database.

5. (10 points) Find the IDs of actors who were cast in more than one

movie.

6. (10 points) List the titles of movies that have a cast of more than 5

actors.

7. (10 points) For each year, list the title of the movie with the most

votes.

8. (10 points) List the titles of both the movie with the highest score and

the movie with the second highest score.

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

movies that have votes between 3000 and 4000.

2

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