In this second exercise, we will query three tables:
movie table describes movies, and contains the following fields:
id int(11), an identifier
title varchar(50), the title of the movie
yr int(11), the year the movie was released
director int(11), the identifier of the director
budget int(11), the movie's budget (we will not use it)
gross int(11), the movie's gross revenue (we will not use it)
actor table contains people (actors and directors) and contains
the following fields:
id int(11), an identifier
name varchar(50), the name of the person
casting describes actors starring in movies. It contains the
movieid int(11), the identifier of the movie in the
actorid int(11), the identifier of the actor in the
ord int(11), an integer describing the position of the actor in the film's starring list. The first actor (called the leading actor) has
ordvalue 1, the second actor has
ordvalue 2, and so on.
For instance, here are the first 5 movies of the
|10003||"Crocodile" Dundee II||1988||36||15800000||239606210|
|10004||'Til There Was You||1997||49||10000000|
|10005||'Til We Meet Again||1940||65|
Here are the
casting rows corresponding to movie
And here are the corresponding tuples in the
actor table. Note that the name
of the actor with id
6 has an encoding issue -- we will ignore
these issues in the sequel.
Warning: Again, the table names are case-sensitive and must be written in lowercase.
Compute the title of every Star Wars movie (starting with "Star Wars") and the name of its director.
SELECT title FROM movie WHERE title LIKE "Star Wars%"
SELECT title FROM movie, actor WHERE title LIKE "Star Wars%" AND director = actor.id
Compute the list of the names of the actors starring in the movie "Jurassic Park" (1993),
sorted by the
SELECT * FROM movie WHERE title = 'Jurassic Park'
SELECT name FROM movie,actor,casting WHERE movie.id = casting.movieid AND actor.id = casting.actorid AND title = 'Jurassic Park' ORDER BY ord
Compute the list of the titles of the movies where "George Clooney" appeared.
SELECT * FROM actor WHERE name = 'George Clooney'
SELECT title FROM movie,actor,casting WHERE movie.id = casting.movieid AND actor.id = casting.actorid AND name = 'George Clooney'
Compute the list of the titles of all 1920 movies together with the name of
actor (the one with
ord value of 1)
SELECT * FROM movie
SELECT title, name FROM movie, casting, actor WHERE yr = 1920 AND casting.movieid = movie.id AND casting.actorid = actor.id AND ord=1
Compute the list of the titles of all 1920 movies together with the name of their leading actor and with the name of their director
SELECT * FROM movie
SELECT title, SA.name, D.name FROM movie, casting, actor as SA, actor as D WHERE yr = 1920 AND casting.movieid = movie.id AND casting.actorid = SA.id AND ord=1 AND D.id = director
Compute the five movies in the database having the highest number of participating actors, sorted by decreasing number of actors.
SELECT title, COUNT(*) AS cnt FROM movie, casting WHERE casting.movieid = movie.id GROUP BY movie.id, title ORDER BY cnt DESC LIMIT 5
Compute the years where the actor "Rock Hudson" participated to more than one movie and the number of movies to which he participated on that year, sorted by decreasing number of movies, then by ascending year.
SELECT yr, COUNT(*) AS count FROM movie, casting, actor WHERE movie.id = casting.movieid AND actor.id = casting.actorid AND name = 'Rock Hudson' GROUP BY yr HAVING count > 1 ORDER BY COUNT(*) DESC, yr
Compute the names of actors who were the leading actor in a movie where Harrison Ford appeared (and were not Harrison Ford himself)
SELECT DISTINCT A2.name FROM actor AS A1, actor AS A2, casting AS C1, casting as C2 WHERE A1.name = 'Harrison Ford' AND A1.id = C1.actorid AND C1.movieid = C2.movieid AND C2.actorid = A2.id AND C2.ord = 1 AND A2.name <> 'Harrison Ford'
Compute the titles of movies which were both directed by Woody Allen and had Woody Allen appear as an actor, sorted in alphabetical order
SELECT title FROM movie, actor, casting WHERE casting.movieid = movie.id AND casting.actorid = actor.id AND actor.name = 'Woody Allen' AND movie.director = actor.id ORDER BY title
Compute the titles of movies which were directed by Woody Allen or had Woody Allen appear as an actor (or both), sorted in alphabetical order
SELECT title FROM movie, actor, casting WHERE casting.movieid = movie.id AND casting.actorid = actor.id AND actor.name = 'Woody Allen' UNION SELECT title FROM movie, actor WHERE movie.director = actor.id AND actor.name = 'Woody Allen' ORDER BY title
In which movie title did Alain Delon and Catherine Deneuve appear together?
SELECT title FROM movie,casting AS C1, casting AS C2, actor AS A1, actor AS A2 WHERE A1.id = C1.actorid AND A2.id = C2.actorid AND C1.movieid = movie.id AND C2.movieid = movie.id AND A1.name = 'Alain Delon' AND A2.name = 'Catherine Deneuve'
Find the only actor who appeared in all Star Wars movies. (Note: this query is complex; if you get an error about losing connection to the server, please try again.)
SELECT name FROM actor WHERE NOT EXISTS ( SELECT id FROM movie WHERE TITLE LIKE 'Star Wars%' AND NOT EXISTS ( SELECT * FROM casting WHERE movieid=movie.id AND actorid=actor.id))
Find the only actor which only appeared in movies where Harrison Ford appeared, and appeared in more than one movie.
SELECT name FROM actor, casting, movie WHERE casting.actorid = actor.id AND casting.movieid = movie.id AND name <> 'Harrison Ford' AND NOT EXISTS ( SELECT id FROM movie, casting AS C1 WHERE C1.movieid = movie.id AND C1.actorid = actor.id AND NOT EXISTS (SELECT 1 FROM casting AS C2, actor AS A2 WHERE A2.name = 'Harrison Ford' AND C2.actorid = A2.id AND C2.movieid = C1.movieid)) GROUP BY name HAVING COUNT(movie.id) > 1
For performance reasons, we limit ourselves to the movies released no later than 1930, and to the actor names starting with A, B, or C. We say that two actors X and Y are challengers if X was the leading actor in a movie where Y appeared and Y was the leading actor in a movie where X appeared. Compute all pairs X, Y of challengers (with X < Y in alphabetical order).
WITH oldmov AS (SELECT id, yr FROM movie WHERE yr <= 1930), aactor AS (SELECT id, name FROM actor WHERE name LIKE 'A%' OR name LIKE 'B%' OR name LIKE 'C%') SELECT DISTINCT A1.name, A2.name FROM aactor AS A1, aactor AS A2, casting AS C1a, casting AS C1b, casting AS C2a, casting AS C2b, oldmov AS Ma, oldmov AS Mb WHERE A1.id = C1a.actorid AND A1.id = C1b.actorid AND A2.id = C2a.actorid AND A2.id = C2b.actorid AND C1a.movieid = C2a.movieid AND C1b.movieid = C2b.movieid AND C1a.ord = 1 AND C2b.ord = 1 AND C1a.movieid = Ma.id AND C1b.movieid = Mb.id AND A1.name < A2.name