21 November

• Database handout

Sample SQL statements

-- some tables

create table movie (
   id serial,
   title varchar(128),
   year int);

create table person (
  id serial,
  first varchar(80),
  last varchar(80),
  birthdate date);

create table role (
  movieID int, --references movie(id),
  personID int, --references person(id),
  character varchar(80)
  );


-- some queries

select * from person order by birthdate desc limit 1;

select movieID, personID, character, title, year, first, last, birthdate
from role, movie, person 
where role.movieID = movie.id and role.personID = person.id;

select movieID, personID, character, title, year, first, last, birthdate
from role, movie, person 
where role.movieID = movie.id and role.personID = person.id
  and movie.title = 'The Day The Earth Stood Still' and movie.year = 2008
  order by birthdate desc limit 1;