http://tutorials.jumpstartlab.com/topics/sql/fundamental_sql.html

Fundamental SQL Tutorial

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> CREATE TABLE fruits(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(31), quantity INT);
sqlite> INSERT INTO fruits(name, quantity) VALUES ('apples', 6);
sqlite> INSERT INTO fruits(name, quantity) VALUES ('oranges', 12);
sqlite> INSERT INTO fruits(name, quantity) VALUES ('bananas', 18);
sqlite> SELECT * FROM fruits;
1|apples|6
2|oranges|12
3|bananas|18
sqlite> .mode column
sqlite> .header on
sqlite> SELECT * FROM fruits;
id          name        quantity
----------  ----------  ----------
1           apples      6
2           oranges     12
3           bananas     18
sqlite> SELECT * FROM fruits WHERE name='apples';
id          name        quantity
----------  ----------  ----------
1           apples      6
sqlite> SELECT * FROM fruits WHERE quantity=18;
id          name        quantity
----------  ----------  ----------
3           bananas     18
sqlite> SELECT * FROM fruits WHERE LENGTH(name)=7;
id          name        quantity
----------  ----------  ----------
2           oranges     12
3           bananas     18
sqlite> SELECT name FROM fruits WHERE id=3;
name
----------
bananas
sqlite> DELETE FROM fruits WHERE name='oranges';
sqlite> SELECT * FROM fruits;
id          name        quantity
----------  ----------  ----------
1           apples      6
3           bananas     18
sqlite> INSERT INTO fruits(name, quantity) VALUES ('grapes', 128);
sqlite> SELECT * FROM fruits;
id          name        quantity
----------  ----------  ----------
1           apples      6
3           bananas     18
4           grapes      128
sqlite> UPDATE fruits SET quantity=17 WHERE name='bananas';
sqlite> SELECT * FROM fruits;
id          name        quantity
----------  ----------  ----------
1           apples      6
3           bananas     17
4           grapes      128
sqlite> ALTER TABLE fruits ADD COLUMN country_of_origin VARCHAR(127);
sqlite> SELECT * FROM fruits;
id          name        quantity    country_of_origin
----------  ----------  ----------  -----------------
1           apples      6
3           bananas     17
4           grapes      128
sqlite> UPDATE fruits SET country_of_origin='Mexico';
sqlite> SELECT * FROM fruits;
id          name        quantity    country_of_origin
----------  ----------  ----------  -----------------
1           apples      6           Mexico
3           bananas     17          Mexico
4           grapes      128         Mexico
sqlite> SELECT * FROM fruits ORDER BY name;
id          name        quantity    country_of_origin
----------  ----------  ----------  -----------------
1           apples      6           Mexico
3           bananas     17          Mexico
4           grapes      128         Mexico
sqlite> SELECT * FROM fruits ORDER BY name DESC;
id          name        quantity    country_of_origin
----------  ----------  ----------  -----------------
4           grapes      128         Mexico
3           bananas     17          Mexico
1           apples      6           Mexico
sqlite> CREATE TABLE sales(id INTEGER PRIMARY KEY AUTOINCREMENT, fruit_id INTEGER, created_at DATETIME);
sqlite> SELECT * FROM sales
   ...>
   ...> INSERT INTO sales(fruit_id, created_at) VALUES(1, CURRENT_TIMESTAMP);
INSERT INTO sales(fruit_id, created_at) VALUES(1, CURRENT_TIMESTAMP);
Error: near "INSERT": syntax error
sqlite> INSERT INTO sales(fruit_id, created_at) VALUES(1, CURRENT_TIMESTAMP);
sqlite> INSERT INTO sales(fruit_id, created_at) VALUES(3, CURRENT_TIMESTAMP);
sqlite> INSERT INTO sales(fruit_id, created_at) VALUES(1, CURRENT_TIMESTAMP);
sqlite> SELECT * FROM sales
   ...> SELECT * FROM sales;
Error: near "SELECT": syntax error
sqlite> SELECT * FROM sales;
id          fruit_id    created_at
----------  ----------  -------------------
1           1           2016-12-05 00:31:23
2           3           2016-12-05 00:31:33
3           1           2016-12-05 00:31:40
sqlite> SELECT fruits.name, sales.created_at FROM fruits INNER JOIN sales ON fruits.id=sales.fruit_id;
name        created_at
----------  -------------------
apples      2016-12-05 00:31:23
bananas     2016-12-05 00:31:33
apples      2016-12-05 00:31:40
sqlite> CREATE TABLE customers(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(63));
sqlite> INSERT INTO customers(name) VALUES ('Casey');
sqlite> INSERT INTO customers(name) VALUES ('Joshua');
sqlite> INSERT INTO customers(name) VALUES ('Denise');
sqlite> SELECT * FROM customers;
id          name
----------  ----------
1           Casey
2           Joshua
3           Denise
sqlite> ALTER TABLE sales ADD COLUMN customer_id INTEGER;
sqlite> UPDATE sales SET customer_id=2 WHERE id=1;
sqlite> UPDATE sales SET customer_id=2 WHERE id=3;
sqlite> UPDATE sales SET customer_id=1 WHERE id=2;
sqlite> SELECT * FROM sales;
id          fruit_id    created_at           customer_id
----------  ----------  -------------------  -----------
1           1           2016-12-05 00:31:23  2
2           3           2016-12-05 00:31:33  1
3           1           2016-12-05 00:31:40  2
sqlite> SELECT customers.name, fruits.name, sales.created_at FROM fruits INNER JOIN sales ON fruits.id=sales.fruit_id INNER JOIN customers ON sales.customer_id=customers.id;
name        name        created_at
----------  ----------  -------------------
Joshua      apples      2016-12-05 00:31:23
Casey       bananas     2016-12-05 00:31:33
Joshua      apples      2016-12-05 00:31:40
sqlite> .exit