2 April

• Database handout (PDF)

Save both of these onto the Desktop or some other folder. Drag the movies.db onto the sqlite3 icon to start it.

• SQLite application – sqlite3.exe
• SQLite database file – movies.db

Sample SQL statements

-- Schema setup: these were done for you.
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: you can try these.

-- First, Configure sqlite:
.mode column
.header on

-- 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 character, first, last from role, person
  where personID = person.ID;

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

-- Computations

select character, first, last, year - substr(birthdate,0,5) as age
  from role, movie, person
  where personID = person.ID and movieID = movie.ID
  order by last;