SQL is the Structured Query Language. We’ll try some examples below.

On Windows

Download both of the following files into the same folder. Drag movies.db onto the sqlite3 icon to start it.

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

On Mac

Download just the database file, into your Downloads folder.

• SQLite database file – movies.db

Then open Applications » Utilities » Terminal and type the following:

cd Downloads
sqlite3 movies.db

Sample SQL statements

-- Schema setup: these were done for you.
create table movie (
  id         integer primary key autoincrement,
  title      varchar(128),
  year       integer
  );

create table person (
  id         integer primary key autoincrement,
  first      varchar(80),
  last       varchar(80),
  birthdate  date
  );

create table role (
  movieid    integer references movie(id),
  personid   integer 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;