Question

SQL (no outer joins or windows, please):

Assume a database with schema ACTOR(name, age, address, nationality) MOVIE(title, year,genre, 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. Attribute genre is one of ’action', 'comedy', 'drama', 'sci-fi', 'horror' and so on. 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:

1. Find the number of movies per country (using nationality of any actor in the movie). Note: you shouldn’t count movies twice; if two or more actors from a country have appeared in a given movie, make sure to count that movie only once.

Answer 1

The SQL command is given below. I have not used outer join but have used Inner join. I even made the tables and ran the query, it is giving the correct result. Keyword distinct is used so that movies are not counted more than once.

select nationality as Country, count(distinct(movie.title)) as NumberOfMovies from ACTOR join MOVIE join APPEARS on ACTOR.name = APPEARS.name and MOVIE.title = APPEARS.title group by nationality;

If you have any doubt, let me know in comments.