— Schema:
— Actors(id, familyName, givenNames, gender)
— AppearsIn(actor, movie, role)
— BelongsTo(movie, genre)
— Directors(id, familyName, givenNames)
— Directs(director, movie)
— Movies(id, title, year)
— Q1: How many movies are in the database?
select count(*) from Movies;
— Q2: What are the titles of all movies in the database?
select title from Movies;
— Q3: What is the earliest year that film was made (in this database)?
select min(year) from Movies;
— Q4: How many actors are there (in this database)?
select count(*) from Actors;
— Q5: Are there any actors whose family name is Zeta-Jones? (case-sensitive)
select givennames||’ ‘||familyname from actors where familyname=’Zeta-Jones’;
— Q6: What genres are there?
select distinct(genre) from belongsto;
— Q7: What movies did Spielberg direct? (title+year)
select m.title, m.year
from Movies m
join Directs s on (s.movie = m.id)
join Directors d on (s.director = d.id)
where d.familyname = ‘Spielberg’;
— or
select m.title, m.year
from Movies m, Directs s, Directors d
where d.familyname = ‘Spielberg’ and s.movie = m.id
and s.director = d.id;
— Q8: Which actor has acted in all movies (in this database)?
— strategy:
— for each actor A {
— M1 = all movies that A has acted in
— M2 = all movies
— check if M1 == M2 (using isEmpty(M2-M1))
—
— Unfortunately, there are no instances in the database
select a.givennames||’ ‘||a.familyname
from Actors a
where not exists (
select id from movies
except
select movie as id from AppearsIn where actor = a.id
);
— Q9: Are there any directors in the database who don’t direct any movies?
— strategy:
— use outer join to get info on all directors and movies directed
— (directors who directed nothing have a single entry with NULL movie)
— group by director and count size of each group (NULLs become count 0)
select d.givennames||’ ‘||d.familyname as name
from (select d.id as director, count(s.movie) as ntimes
from Directors d left outer join Directs s on (d.id = s.director)
group by d.id) as nd join Directors d on (nd.director = d.id)
where ntimes = 0;