Introduction to SQLite
https://www.sqlite.org/index.html

Command Line Shell For SQLite
https://www.sqlite.org/cli.html

These notes are taken and modified from sqlite.pdf by Dr. Andrew Mertz.

** CAUTION **
with copying from pdf to terminal; some symbols are not copied correctly.

Start the command line interface with the command
$ sqlite3

Once you are in SQLite, you will see: sqlite> instead of $.

A complete list of commands can be obtained with
sqlite> .help

Close the SQLite shell with
sqlite> .exit

If you already have a database
sqlite> .open filename.db

Create a database named test.db at the same time as you login
$ sqlite3 test.db

Create a table with two columns (called id and value) in the new database with the command (don't forget the semicolon)
sqlite> create table test (id integer primary key, value text);

Each table includes information that uniquely identifies a row in that table (known as the primary key) along with additional information needed to describe the entity completely.

Add a few rows to the table:
sqlite> insert into test (id, value) values(1, 'bob');
sqlite> insert into test (id, value) values(2, 'sam');
sqlite> insert into test (value) values('jo');
sqlite> insert into test (value) values('mo');

To display the table
sqlite> select * from test;

To display the table nicely
sqlite> .mode column
sqlite> .headers on
sqlite> select * from test;

To see your tables in your database
sqlite> .tables

To see your database
sqlite> .databases

Add an index and a view to the database:
sqlite> create index test_idx on test (value);
sqlite> create view schema as select * from sqlite_master;

With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database.
sqlite> .schema
sqlite> .schema [filename]

The ".schema" command is roughly the same as setting list mode, then entering the following query:
sqlite> SELECT sql FROM sqlite_master
sqlite> ORDER BY tbl_name, type DESC, name;

ALTERING TABLES
First let's create the table 'contacts'

sqlite> create table contacts (id integer primary key, name text not null collate nocase, phone text not null default ĠUNKNOWNĠ, unique (name,phone));

You can rename a table or add columns with alter table. (I got an error)
sqlite> alter table table_name {rename to name | add column column_definition};
sqlite> alter table contacts add column email text not null default '' collate nocase;
sqlite> .schema contacts

The output of one select statement can be fed into another.
sqlite> select name from (select name, type_ip from (select * from foods));

IMPORT/EXPORT

You can export database objects as SQL commands with .dump
In shell mode, the output is directed to the screen by default
Use .output, if you want to redirect to a file
Export the current database to file.sql.
sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout

Use the ".import" command to import CSV data into an SQLite table. The ".import" command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted.

Note that it is important to set the "mode" to "csv" before running the ".import" command. 

sqlite> .mode csv
sqlite> .import filename.csv [tablename]

To export an SQLite table (or part of a table) as CSV, set the "mode" to "csv" then run a query to extract the desired rows of the table.

sqlite> .header on
sqlite> .mode csv
sqlite> .once dataout.csv
sqlite> SELECT * FROM [tablename];
sqlite> .system dataout.csv

What happens if you remove the lines containing the commands "once" or "system"?

To remove the existing table and view and recreate then read:
sqlite> drop table test;
sqlite> drop view schema;
sqlite> .read file.sql



QUERY CLAUSES
CLAUSE NAME	PURPOSE
------------	----------------------------
SELECT		determine which columns to include in the query's result set
FROM		identifies the tables from which to draw data and how the tables should be joined
WHERE		restricts the number of rows in the final result set
GROUP BY	used to group rows together by common column values
HAVING		restricts the number of rows in the final result set using grouped data
ORDER BY	sorts the rows of the final result set by one or more columns
LIMIT		specifies only this many records be returned
OFFSET 		specifies the number of records to skip


EXAMPLE WITH FOODS

sqlite> create table episodes(id integer primary key, season integer, name text);
sqlite> create table foods(id integer primary key, type_id integer, name text);
sqlite> create table food_types(id integer primary key, name text);
sqlite> create table foods_episodes(food_id integer, episode_id integer);

The main table is foods with each row corresponding to a distinct food.
The type_id references the food_types table, which stores the various food classifications (e.g. baked goods, drinks, or junk food).
The foods_episodes table links foods with the episodes.

Create the example database by downloading foods.sql and running:
$ sqlite3 foods.db < foods.sql

The commands we will be discussing are in test.sql.

For readability, run
sqlite> .mode column
sqlite> .headers on 
sqlite> .nullvalue NULL

sqlite> select * from foods where name='JujyFruit' and type_id=9;
sqlite> select f.name name, types.name type from foods f inner join (select * from food_types where id=6) types on f.type_id=types.id;

INNER JOIN is a mechanism to bring two tables together in the same query.
Notice SQL reads a lot like a natural language;

and commands are terminated by a semicolon.
sqlite> select id, name from foods;
sqlite> insert into foods values (null, null, 'Whataburger');
sqlite> select * from foods;
sqlite> delete from foods where id=413;
sqlite> select * from foods;

String Matching
Both double quotes and single quotes should work the same.
The % will match any sequence of characters.
The glob and like  are similar relational operators.
sqlite> select name from foods where name like 'J%';
sqlite> select name from foods where name like '%ac%P%' and name not like '%Sch%';
sqlite> select name from foods where name glob 'P*';

To ignore upper or lower case use collate nocase.
sqlite> select name from foods where name like 'J%'  collate nocase;

Use the desc qualifier for descending order
sqlite> select * from food_types order by id;
sqlite> select * from food_types order by id limit 3;
sqlite> select * from food_types order by id limit 3 offset 2;
sqlite> select * from foods where name glob 'B*' order by type_id desc, name limit 10;

The following two queries return the same thing
sqlite> select * from foods where name glob 'B*' order by type_id desc, name limit 3 offset 2;
sqlite> select * from foods where name glob 'B*' order by type_id desc, name limit 2,3;

Slides 37-66:
Functions and Aggregates
Grouping
Removing Duplicates
Joining Tables
Inner, Natural, Cross and Outer Joins
Aliases
Self-Joins
Subqueries, Compound Queries
Union, Intersection, Difference
Conditional Results
Inserting and Updating Records
Indexes and Transactions


EXAMPLE WITH ZILLOW

First import the Zillow csv file into an SQLite table.
sqlite> .mode csv
sqlite> .import Zip_Zillow.csv ziptable

To view the first five lines 
sqlite> select * from zip table limit 5;

You can select several columns, given some constraint.
sqlite> select State, City from ziptable where County= "Cook" limit 5;

The query below returns column data type.
sqlite> pragma table_info(ziptable)

Error with inequality; data entries all text.
sqlite> select State, City from ziptable where Zhvi>200000 limit 10;

The commands below did not work.
sqlite> update ziptable set Zhvi=cast(Zhvi as numeric);

The following two queries worked, but there doesnĠt seem to be an easy way to change the column type from text to numeric.
sqlite> select State, Zhvi from ziptable where Zhvi*1.0 > 200000 limit 10;
sqlite> select State, Zhvi from ziptable where cast(Zhvi as numeric) > 200000 limit 10;

You can count the number of rows in your table.
sqlite> select count(*) from ziptable;
sqlite> select count(State) from ziptable;

The command "group by" is similar to "tapply" in R.
sqlite> select City, County, State,Zhvi from ziptable where Zhvi*1.0 > 800000 group by County limit 10;
sqlite> select City, County, State,PctFallFromPeak from ziptable where PctFallFromPeak<0 group by County limit 10;

ERROR? Possibly that SQLite does not like table or column names beginning with numbers.
Column name should include quotes.
Aggregate functions (I am getting an error with 5Year but not Zhvi)
sqlite> select Date, City, State,  max(MoM) MoM, min(QoQ) QoQ, avg(YoY) YoY, sum("5Year") "5Year", count(*) City from ziptable group by State;