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

ID: 3881108 • Letter: S

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)

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.

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

1) select Title from Movie,Actor,Casting
where Movie.MovieID = Casting.MovieID and
Casting.ActorID = Actor.ActorID and
Actor.Name = "Kevin Bacon";

2) select Name from Movie,Actor,Casting
where Movie.MovieID = Casting.MovieID and
Casting.ActorID = Actor.ActorID and
Votes>3000 and Votes<4000 ;

3) select Title from Movie where
Score > (select Score from Movie where title = "Titanic");

4) select Title from Movie where Score =
(select max(Score) from Movie);

5) select ActorID from Casting where count(distinct ActorID)>1
group by ActorID;

6) select Title from Movie,Casting where
Movie.MovieID = Casting.MovieID and
count(distinct ActorID)>5
group by Title;

7) select Title,Year from Movie group by Year having
Votes = max(Votes);

8) select Title,max(Score) from Movie as Score where
Score < (select Title,max(Score) from Movie) ;

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