pi:~> sqlite3
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .o
sqlite> .quit
pi:~> script HelpME.txt
Script started, file is HelpME.txt
pi:~> sqlite3 test1.bin
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> CREATE TABLE english (
   ...> id INTEGER PRIMARY KEY,
   ...> lexeme varchar (255) UNIQUE not null
   ...> );
sqlite> .schema
CREATE TABLE english (
id INTEGER PRIMARY KEY,
lexeme varchar (255) UNIQUE not null
);
sqlite> CREATE TABLE french (
   ...> id INTEGER PRIMARY KEY,
   ...> lexeme VARCHAR (255) UNIWUE NOT NULL
   ...> );
Error: near "UNIWUE": syntax error
sqlite> lexeme VARCHAR (255) UNIQUE NOT NULL
   ...> );
Error: near "lexeme": syntax error
sqlite> CREATE TABLE french (
   ...>    id INTEGER PRIMARY KEY,
   ...>    lexeme VARCHAR (255) UNIQUE NOT NULL
   ...> );
sqlite> .schema
CREATE TABLE english (
id INTEGER PRIMARY KEY,
lexeme varchar (255) UNIQUE not null
);
CREATE TABLE french (
   id INTEGER PRIMARY KEY,
   lexeme VARCHAR (255) UNIQUE NOT NULL
);
sqlite> CREATE TABLE translate (
   ...>    id INTEGER PRIMARY KEY,
   ...>    en INTEGER NOT NULL,
   ...>    fr INTEGER NOT NULL,
   ...>    UNIQUE(en, fr) ON CONFLICT ABORT,
   ...>    FOREIGN KEY(en) REFERENCES english(id),
   ...>    FOREIGN KEY(fr) REFERENCES french(id)
   ...> );
sqlite> insert into english (lexeme) values ('good');
sqlite> select * from english;
1|good
sqlite> insert into french values ('bonne');
Error: table french has 2 columns but 1 values were supplied
sqlite> insert into french (lexeme) values ('bonne');
sqlite> select * from french;
1|bonne
sqlite> .quit
pi:~> sqlite3 test1.bin
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .tables
english    french     translate
sqlite> insert into translate (en, fr) values (1, 1);
sqlite> insert into english (lexeme) values ('tomorrow');
sqlite> insert into french (lexeme) values ('demain');
sqlite> select * from english;
1|good
2|tomorrow
sqlite> insert into translate (en, fr) values (2, 2);
sqlite> select * from english join translate join french;
1|good|1|1|1|1|bonne
1|good|1|1|1|2|demain
1|good|2|2|2|1|bonne
1|good|2|2|2|2|demain
2|tomorrow|1|1|1|1|bonne
2|tomorrow|1|1|1|2|demain
2|tomorrow|2|2|2|1|bonne
2|tomorrow|2|2|2|2|demain
sqlite> .mode column
sqlite> select * from english join translate join french;
1           good        1           1           1           1           bonne
1           good        1           1           1           2           demain
1           good        2           2           2           1           bonne
1           good        2           2           2           2           demain
2           tomorrow    1           1           1           1           bonne
2           tomorrow    1           1           1           2           demain
2           tomorrow    2           2           2           1           bonne
2           tomorrow    2           2           2           2           demain
sqlite> .mode list
sqlite> select * from english join translate join french
   ...> where french.lexeme = 'bonne' and translate.fr = french.id
   ...> and translate.en = english.id;
1|good|1|1|1|1|bonne
sqlite> select lexeme from english join translate join french
   ...> where french.lexeme = 'bonne' and translate.fr = french.id
   ...> and translate.en = english.id;
Error: ambiguous column name: lexeme
sqlite> select english.lexeme from english join translate join french
   ...> where french.lexeme = 'bonne' and translate.fr = french.id
   ...> and translate.en = english.id;
good
sqlite> select french.lexeme from english join translate join french
   ...> where english.lexeme = 'tomorrow' and translate.fr = french.id
   ...> and translate.en = english.id;
demain
sqlite> .quit