Question

Write the following questions as queries in SQL. Use only the operators discussed in class (in particular, no outer joins or windows). Type your answers. Before starting, make sure you understand the schema of the database. If you are in doubt about it, please ask the instructor. Assume a database with schema ACTOR(name,age,address,nationality) MOVIE(title,year,genre,budget,director-name,studio) APPEARS(name,title,salary)

1. Find the title, director and studio of the most expensive movie of 2010 (note: there can be ties!).

2. Find the title, director and studio of the most expensive movie of each year (note: again, there can be ties in any given year!).

3. Find, for each actor, the number of movies by studio MGM that the actor has appeared in. Note: if an actor has not appeared in any movie by MGM, there is no need to list the actor.

4. 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.

5. Find the names of directors who have never directed a comedy.

6. List the titles of movies where all actors were British.

7. List the titles and director names of movies where the sum of the salaries of all actors in the movie was more than the movie budget.

8. Find out, in each movie, what percentage of the budget went to pay the top paid actor in that movie. You can assume there is only 1 top paid actor per movie.

Answer 1

SQL QUERIES:

1.

SELECT title, director-name, studio, MAX(BUDGET)  

FROM movie

WHERE year= 2010;

EXPLANATION:

Maximum budget is selected using MAX() function where year= 2010

2.

SELECT title, director-name, studio, year, MAX(BUDGET)  

FROM movie

GROUP BY year;

EXPLANATION:

Group by year is used to result maximum budget for each year.

3.

SELECT a.name, COUNT(a.title)

FROM Appears a, movie m

WHERE a.title= m.title

AND m.studio= 'MGM';

4.

SELECT ac.nationality, COUNT(DISTINCT ap.title) AS "No. Of movies"

FROM ACTOR ac, APPEARS ap

WHERE ac.name= ap.name;

GROUP BY ac.nationality;