Question

Consider a database schema with three relations:

Provide

Movies (movie_id:integer, title:string, year:integer, studio:string)
Actors (actor_id:integer, name:string, nationality:string)
StarsIn(actor_id:integer, movie_id:integer, character:string)

SQL statements for the following:

1.find the nationalities of actors that worked for all studios. Ensure that a

nationality appears only once in the result.

2. Find for each year the number of distinct actors that played a character that starts with letter“A”

and has at least three letters in the character name.

Answer 1

Question 1.

Answer : SELECT nationality
FROM Actors INNER JOIN starsln ON actor_id
INNER JOIN movies ON movie_id
GROUP BY studio;

Question 2.

Answer : SELECT COUNT(DISTINCT actors)
FROM Actors
WHERE name LIKE A__% ;