Home

Welcome to the SQLJourney wiki!

This journey is documented with sections for the different SQL variants. Each variant will have its own sub-heading in each section. These are the variants so far:

1. SQLite3
2. PostGRE-SQL

Launching

SQLite3

In the terminal, simply type sqlite3 database_name.db to open the translator and connect to a database. The prompt will look as follows:

sqlite>_ 

To change databases, type sqlite>DETACH [DATABASE] database_name; then sqlite>ATTACH [DATABASE] 'filename' AS database_name;.

A Few Commands

• .databases -- list attached databases
• .help -- list all of the .commands
• .quit -- quit sqlite & return to terminal
• .load some_script.sql -- load script file
• .tables -- list the tables in the connected db
• .timer [on/off] -- toggle the timer

Populating a Database

SQLite3

create table random (
    id integer primary key autoincrement,
    booly boolean check(booly = 0 or booly = 1), -- Input restriction check to 1, 0
    first_name varchar(20) NOT NULL,
    cost numeric(precision, scale) -- Float definition
    age integer,
    date DATE, -- Date type, stored in generic date format
    fkey integer references products(id), -- Foreign key
    quantity integer not null default 0,
    constraint name_unique unique (first_name) -- Input constraints
);
/* 
    Multi line comment
*/

Table Commands:

create, alter, drop, view, select, insert, truncate, union [all], except, intercept

Manual Entry

SQLite3

insert into tablename (col1_name, col2_name, col3_name) values
    (col1_value col2_value col3_value),
    (col1_value col2_value col3_value);

Or for all columns in order:

insert into tablename values
    (col1_value col2_value col3_value ... colN_value),
    (col1_value col2_value col3_value ... colN_value);