Exercises

Again, we are using the database system in Discussion 1. Please check out that page for SQLite installation and usage guide.

Here, we are working on a IMDB database, containing information about movies, actor/actress, directors, Oscar awards info, etc,. In our database, the Movie relation has the following attributes:

Attribute Name Data Type Description
id CHAR(7) a unique id assigned to the film
name VARCHAR(64) the name of the film
year INTEGER the year the film was released
rating VARCHAR(5) the MPAA rating of the film; if the film is unrated or has a non-standard rating, this value is NULL.
runtime INTEGER the running time of the film in minutes
genre VARCHAR(16) the genre(s) of the film, formed by concatenating one-letter genre codes. no assumptions should be made about the order in which these codes appear in a given genre string
earnings_rank INTEGER the earnings rank of the film (only included for the top 200; NULL otherwise)
Genre: action (A), adventure (V), animation (N), comedy (C), drama (D), family (F), fantasy (Y), horror (H), mystery (M), musical (L), romance (R), sci-fi (S), thriller (T), war (W), western (E);

And part of the actual table looks like this.

id name year rating runtime genre earnings_rank
1300854 Iron Man 3 2013 PG-13 130 A 13
1690953 Despicable Me 2 2013 PG 98 N 24
0770828 Man of Steel 2013 PG-13 143 A 54
1453405 Monsters University 2013   104 N 64
1905041 Furious 6 2013 PG-13 130 A 86
1623205 Oz the Great and Powerful 2013 PG 130 V 90
1408101 Star Trek Into Darkness 2013 PG-13 132 A 96
0816711 World War Z 2013 PG-13 123 A 135
0481499 The Croods 2013 PG 98 N 152
0848228 The Avengers 2012 PG-13 143 A 3

This is actually generated by the following SQL query, FYI.

select * from movie order by year desc limit 10;

This time we add more tables as follows.

The Person relation has the following attributes:

Attribute Name Data Type Description
id CHAR(7) a unique id assigned to the person
name VARCHAR(64) the person’s full name (first name first)
dob DATE the person’s date of birth, if known; NULL otherwise
pob VARCHAR(128) the person’s place of birth, if known; NULL otherwise

The Actor relation has the following attributes:

Attribute Name Data Type Description
actor_id CHAR(7) the id attribute of the actor; a foreign key that references Person(id)
movie_id CHAR(7) the id attribute of a movie in which the actor appeared; a foreign key that references Movie(id)

The Director relation has the following attributes:

Attribute Name Data Type Description
director_id CHAR(7) the id attribute of the director; a foreign key that references Person(id)
movie_id CHAR(7) the id attribute of a movie that he or she directed; a foreign key that references Movie(id)

The Oscar relation has the following attributes:

Attribute Name Data Type Description
movie_id CHAR(7) the id attribute of the movie for which the award was won; a foreign key that references Movie(id)
person_id CHAR(7) the id attribute of the actor, actress, or director who won the award; if type = ‘BEST-PICTURE’ (see below), this attribute is NULL; a foreign key that references Person(id)
type VARCHAR(23) the type of Oscar, which will be one of the following strings: ‘BEST-PICTURE’, ‘BEST-DIRECTOR’, ‘BEST-ACTRESS’, ‘BEST-ACTOR’, ‘BEST-SUPPORTING-ACTRESS’, ‘BEST-SUPPORTING-ACTOR’
year INTEGER the year in which the Oscar was won

For example, Tom Hanks won the Best Actor Oscar in 1994 for his performance in the movie Philadelphia, which was directed by Jonathan Demme. If Hanks had an id of 1234567, Demme an id of 9876543, and the movie an id of 2222222, then the following tuples would appear in the database:

  • (1234567, 2222222) in Actor
  • (9876543, 2222222) in Director
  • (2222222, 1234567, ‘BEST-ACTOR’, 1994) in Oscar
  • along with a tuple for the movie itself in Movie, and tuples for Hanks and Demme in Person.

Problems

  1. Cate Blanchett and Jennifer Lawrence are both receiving Oscar buzz for their latest roles. Write a query to find the places of birth and dates of birth for these two actors. The result of the query should be tuples of the form (name of person, place of birth, date of birth).

  2. Write a query to find all films that won Best Picture in the 1990s (i.e., from 1990-1999)? The tuples in your result should include both the name of the film and the year in which the award was given. Order the results in chronological order.

  3. Write a query to find the longest animated movie in the database, along with its runtime.

  4. Write a query to find all cases in the database in which the same person has won the same type of Oscar two years in a row. The tuples in the result should contain the name of the person, the type of Oscar, and the two years in which the award was won. Hint: The conditions in the WHERE clause can include the standard arithmetic operators (+, -, *, /, and %).

  5. Write a query to find all movies that have won at least 3 of the Oscars in the database. In the results of your query, include both the name of the film and the number of Oscars won. List the results in order of the number of awards won, from highest to lowest. You may assume that all films in the database have unique names.

  6. Write a query to determine how many of the movies that have won Best Picture have runtimes that are longer than the average runtime of all of the movies in the database. The result of your query should be a single number.

Solutions

select name as name_of_person, pob as place_of_birth, dob as date_of_brith
from person
where name = 'Cate Blanchett' or name = 'Jennifer Lawrence';
select name, oscar.year 
from oscar, movie 
where oscar.type='BEST-PICTURE' 
and oscar.year >= 1990 
and oscar.year < 2000 
and oscar.movie_id = movie.id
order by oscar.year;
select name, runtime
from movie
where runtime = (select max(runtime) from movie);
select name, o1.type, o1.year, o2.year 
from oscar as o1, oscar as o2, person 
where o1.person_id = o2.person_id 
and o1.type = o2.type 
and o1.year + 1 = o2.year 
and o1.person_id = person.id;
select name, count(name) as c
from movie, oscar
where oscar.movie_id = movie.id
group by name
having c >= 3
order by c desc, name;
select count(name)
from oscar, movie
where oscar.movie_id = movie.id
and oscar.type = 'BEST-PICTURE'
and movie.runtime > (select avg(runtime) from movie);