SQL homework

~/desktop/turing/mod-4/lessons/fundamental_sql |master ✓|
→sqlite3 example.sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
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 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>
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>
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;
id          fruit_id    created_at
----------  ----------  -------------------
1           1           2016-12-04 20:01:13
2           3           2016-12-04 20:01:22
3           1           2016-12-04 20:01:34
sqlite> SELECT fruits.name, sales.created_at FROM fruits INNER JOIN sales ON fruits.id=sales.fruit_id;
name        created_at
----------  -------------------
apples      2016-12-04 20:01:13
bananas     2016-12-04 20:01:22
apples      2016-12-04 20:01:34
sqlite> CREATE TABLE customers(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(63));
sqlite> INSERT INTO customers(name) VALUES ('Jeff');
sqlite> INSERT INTO customers(name) VALUES ('Violet');
sqlite> INSERT INTO customers(name) VALUES ('Vincent');
sqlite> SELECT * FROM customers;
id          name
----------  ----------
1           Jeff
2           Violet
3           Vincent
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-04 20:01:13  2
2           3           2016-12-04 20:01:22  1
3           1           2016-12-04 20:01:34  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
----------  ----------  -------------------
Violet      apples      2016-12-04 20:01:13
Jeff        bananas     2016-12-04 20:01:22
Violet      apples      2016-12-04 20:01:34
sqlite> .exit