A Database with Two Tables

cd ~/Desktop
pwd
sqlite3 cities.db

States

Why should we create the states before the cities?

sqlite> create table states (
           abbreviation text primary key,
           name text,
           nickname text
        );

sqlite> insert into states (abbreviation, name, nickname) values ('NY', 'New York', 'The Empire State');
sqlite> insert into states (abbreviation, name, nickname) values ('NJ', 'New Jersey', 'The Garden State');
sqlite> insert into states (abbreviation, name, nickname) values ('IL', 'Illinois', 'Land of Lincoln');
sqlite> select * from states;

Cities

sqlite> create table cities (
           name text,
           state text
        );

sqlite> insert into cities (name, state) values ('New York', 'NY');
sqlite> insert into cities (name, state) values ('Yonkers', 'NY');
sqlite> insert into cities (name, state) values ('Newark', 'NJ');
sqlite> insert into cities (name, state) values ('Chicago', 'IL');
sqlite> select * from cities;

A database join

sqlite> select * from cities, states;	--4 × 3 = 12 lines of output

sqlite> select name from cities;
New York
Yonkers
Newark
Chicago
sqlite> select cities.name from cities;

sqlite3> select cities.name, states.name from cities, states where cities.state == states.abbreviation;
New York|New York
Yonkers|New York
Newark|New Jersey
Chicago|Illinois

sqlite> .separator ''
sqlite> .show

sqlite> select cities.name, ', ', cities.state, ' (', states.name, '--', states.nickname, ')'
   from cities, states
   where cities.state == states.abbreviation;

New York, NY (New York--The Empire State)
Yonkers, NY (New York--The Empire State)
Newark, NJ (New Jersey--The Garden State)
Chicago, IL (Illinois--Land of Lincoln)

Exit

sqlite> .exit
ls -l cities.db
sqlite3 cities.db .dump | more