SQL examples

-- 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;