-- The table creations were done for you.
create table movie (
id serial primary key,
title varchar(128),
year integer
);
create table person (
id serial primary key,
first varchar(80),
last varchar(80),
birthdate date
);
create table role (
id serial primary key,
movieid integer references movie(id),
personid integer references person(id),
name varchar(80)
);
-- Some queries: you can try these.
-- Simple single-table selects
select * from movie;
select * from person;
select first, last from person;
select title, year from movie order by year desc;
select first, last, birthdate from person
order by birthdate desc limit 2;
-- The 'where' clause
select * from movie where year < 2000;
select * from person where birthdate > '1960/01/01';
-- Joining tables together
select name, first, last from role, person
where personID = person.ID;
select title, first, last, name from role, movie, person
where personID = person.ID and movieID = movie.ID;
-- Computations
select name, first, last, year - substr(birthdate,0,5) as age
from role, movie, person
where personID = person.ID and movieID = movie.ID
order by last;
PDF version