-- 👉 sqlite3 22-SQL.db
-- SQLite version 3.28.0 2019-04-15 14:49:49
-- Enter ".help" for usage hints.
-- .tables
-- cones  sales
SELECT * FROM cones;
-- -- 1|strawberry|pink|3.55
-- -- 2|chocolate|light brown|4.75
-- -- 3|chocolate|dark brown|5.25
-- -- 4|strawberry|pink|5.25
-- -- 5|bubblegum|pink|4.75
-- -- 6|chocolate|dark brown|5.25
.help
-- .auth ON|OFF             Show authorizer callbacks
-- .backup ?DB? FILE        Backup DB (default "main") to FILE
-- .bail on|off             Stop after hitting an error.  Default OFF
-- .binary on|off           Turn binary output on or off.  Default OFF
-- .cd DIRECTORY            Change the working directory to DIRECTORY
-- .changes on|off          Show number of rows changed by SQL
-- .check GLOB              Fail if output since .testcase does not match
-- .clone NEWDB             Clone data into NEWDB from the existing database
-- .databases               List names and files of attached databases
-- .dbconfig ?op? ?val?     List or change sqlite3_db_config() options
-- .dbinfo ?DB?             Show status information about the database
-- .dump ?TABLE? ...        Render all database content as SQL
-- .echo on|off             Turn command echo on or off
-- .eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
-- .excel                   Display the output of next command in a spreadsheet
-- .exit ?CODE?             Exit this program with return-code CODE
-- .expert                  EXPERIMENTAL. Suggest indexes for specified queries
-- .fullschema ?--indent?   Show schema and the content of sqlite_stat tables
-- .headers on|off          Turn display of headers on or off
-- .help ?-all? ?PATTERN?   Show help text for PATTERN
-- .import FILE TABLE       Import data from FILE into TABLE
-- .imposter INDEX TABLE    Create imposter table TABLE on index INDEX
-- .indexes ?TABLE?         Show names of indexes
-- .limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
-- .lint OPTIONS            Report potential schema issues.
-- .log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
-- .mode MODE ?TABLE?       Set output mode
-- .nullvalue STRING        Use STRING in place of NULL values
-- .once (-e|-x|FILE)       Output for the next SQL command only to FILE
-- .open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
-- .output ?FILE?           Send output to FILE or stdout if FILE is omitted
-- .parameter CMD ...       Manage SQL parameter bindings
-- .print STRING...         Print literal STRING
-- .progress N              Invoke progress handler after every N opcodes
-- .prompt MAIN CONTINUE    Replace the standard prompts
-- .quit                    Exit this program
-- .read FILE               Read input from FILE
-- .restore ?DB? FILE       Restore content of DB (default "main") from FILE
-- .save FILE               Write in-memory database into FILE
-- .scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
-- .schema ?PATTERN?        Show the CREATE statements matching PATTERN
-- .selftest ?OPTIONS?      Run tests defined in the SELFTEST table
-- .separator COL ?ROW?     Change the column and row separators
-- .session ?NAME? CMD ...  Create or control sessions
-- .sha3sum ...             Compute a SHA3 hash of database content
-- .shell CMD ARGS...       Run CMD ARGS... in a system shell
-- .show                    Show the current values for various settings
-- .stats ?on|off?          Show stats or turn stats on or off
-- .system CMD ARGS...      Run CMD ARGS... in a system shell
-- .tables ?TABLE?          List names of tables matching LIKE pattern TABLE
-- .testcase NAME           Begin redirecting output to 'testcase-out.txt'
-- .timeout MS              Try opening locked tables for MS milliseconds
-- .timer on|off            Turn SQL timer on or off
-- .trace ?OPTIONS?         Output each SQL statement as it is run
-- .vfsinfo ?AUX?           Information about the top-level VFS
-- .vfslist                 List all available VFSes
-- .vfsname ?AUX?           Print the name of the VFS stack
-- .width NUM1 NUM2 ...     Set column widths for "column" mode
.headers on
SELECT * FROM cones;
-- -- Id|Flavor|Color|Price
-- -- 1|strawberry|pink|3.55
-- -- 2|chocolate|light brown|4.75
-- -- 3|chocolate|dark brown|5.25
-- -- 4|strawberry|pink|5.25
-- -- 5|bubblegum|pink|4.75
-- -- 6|chocolate|dark brown|5.25
SELECT Color, Price FROM cones;
-- Color|Price
-- pink|3.55
-- light brown|4.75
-- dark brown|5.25
-- pink|5.25
-- pink|4.75
-- dark brown|5.25
SELECT * FROM cones ORDER BY Price;
-- -- Id|Flavor|Color|Price
-- -- 1|strawberry|pink|3.55
-- -- 2|chocolate|light brown|4.75
-- -- 5|bubblegum|pink|4.75
-- -- 3|chocolate|dark brown|5.25
-- -- 4|strawberry|pink|5.25
-- -- 6|chocolate|dark brown|5.25
SELECT * FROM cones ORDER BY Price DESC;
-- Id|Flavor|Color|Price
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.25
-- 6|chocolate|dark brown|5.25
-- 2|chocolate|light brown|4.75
-- 5|bubblegum|pink|4.75
-- 1|strawberry|pink|3.55
SELECT * FROM cones ORDER BY Price DESC LIMIT 1;
-- Id|Flavor|Color|Price
-- 3|chocolate|dark brown|5.25




SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.25
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
SELECT * FROM cones WHERE Flavor IS 'chocolate';
-- Id|Flavor|Color|Price
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 6|chocolate|dark brown|5.25
SELECT * FROM cones WHERE Flavor IS 'Chocolate';
SELECT * FROM cones WHERE Flavor IS 'chocolate';
-- Id|Flavor|Color|Price
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 6|chocolate|dark brown|5.25
SELECT * FROM cones WHERE Flavor LIKE 'brown';
SELECT * FROM cones WHERE Flavor LIKE '%brown';
SELECT * FROM cones WHERE Flavor LIKE '%brown%';

SELECT * FROM cones;
-- 1|strawberry|pink|3.55
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.25
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25

SELECT * FROM cones WHERE Flavor IS 'chocolate';
-- Id|Flavor|Color|Price
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 6|chocolate|dark brown|5.25
SELECT * FROM cones WHERE Flavor IS 'Chocolate';
SELECT * FROM cones WHERE Price < 4;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
SELECT * FROM cones WHERE Color LIKE 'brown';
SELECT * FROM cones WHERE Color LIKE '%brown';
-- Id|Flavor|Color|Price
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 6|chocolate|dark brown|5.25
SELECT * FROM cones WHERE Color LIKE '%brown%';
-- Id|Flavor|Color|Price
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 6|chocolate|dark brown|5.25
SELECT * FROM cones WHERE Color LIKE 'dark%';
-- Id|Flavor|Color|Price
-- 3|chocolate|dark brown|5.25
-- 6|chocolate|dark brown|5.25
SELECT * FROM cones WHERE Flavor LIKE '%e%';
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.25
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
SELECT * FROM cones WHERE Flavor LIKE '%b%';
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
-- 4|strawberry|pink|5.25
-- 5|bubblegum|pink|4.75
SELECT * FROM cones WHERE Price > 3 and Price < 5;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
-- 2|chocolate|light brown|4.75
-- 5|bubblegum|pink|4.75

-- create table cones as
--    ...>     select 1 as ID, "strawberry" as Flavor, "pink" as Color, 3.55 as Price union
--    ...>     select 2, "chocolate", "light brown", 4.75 union
--    ...>     select 3, "chocolate", "dark brown", 5.25 union
--    ...>     select 4, "strawberry", "pink",5.25 union
--    ...>     select 5, "bubblegum", "pink",4.75 union
--    ...>     select 6, "chocolate", "dark brown", 5.25;
-- .tables
-- cones

INSERT INTO cones(ID, Flavor, Color, Price) VALUES (7, 'Vanilla', 'white', 3.95);
SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.25
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95

INSERT INTO cones(ID, Flavor, Color, Price) VALUES (7, 'Vanilla', 'white', 3.95);
SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.25
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95
INSERT INTO cones(ID, Flavor, Color, Price) VALUES (7, 'Vanilla', 'white', 3.95);
INSERT INTO cones(ID, Flavor, Color, Price) VALUES (7, 'Vanilla', 'white', 3.95);
SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.25
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95
-- 7|Vanilla|white|3.95
-- 7|Vanilla|white|3.95

UPDATE cones SET Price = Price * 2;
SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|7.1
-- 2|chocolate|light brown|9.5
-- 3|chocolate|dark brown|10.5
-- 4|strawberry|pink|10.5
-- 5|bubblegum|pink|9.5
-- 6|chocolate|dark brown|10.5
-- 7|Vanilla|white|7.9
-- 7|Vanilla|white|7.9
-- 7|Vanilla|white|7.9
UPDATE cones SET Price = Price / 2;
SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.55
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.25
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95
-- 7|Vanilla|white|3.95
-- 7|Vanilla|white|3.95
UPDATE cones SET Price = Price + 0.25 WHERE Flavor IS 'strawberry';
SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.8
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.5
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95
-- 7|Vanilla|white|3.95
-- 7|Vanilla|white|3.95
SELECT * FROM cones WHERE Flavor LIKE '%berry';
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.8
-- 4|strawberry|pink|5.5
DELETE FROM cones WHERE Id = 7;
SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.8
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.5
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25

INSERT INTO cones(ID, Flavor, Color, Price) VALUES (7, 'Vanilla', 'white', 3.95);

SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.8
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.5
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95

SELECT COUNT() FROM cones GROUP BY Flavor;
COUNT()
1
1
3
2














SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.8
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.5
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95
SELECT COUNT() FROM cones;
COUNT()
7
SELECT COUNT() FROM cones WHERE Price < 4;
COUNT()
2
SELECT COUNT() FROM cones WHERE Flavor IS 'chocolate';
COUNT()
3
SELECT COUNT() FROM cones GROUP BY Flavor;
COUNT()
1
1
3
2
SELECT COUNT(), Flavor FROM cones GROUP BY Flavor;
COUNT()|Flavor
-- 1|Vanilla
-- 1|bubblegum
-- 3|chocolate
-- 2|strawberry
SELECT COUNT(), Flavor FROM cones GROUP BY Flavor ORDER BY COUNT DESC;
Error: no such column: COUNT
SELECT COUNT() as count, Flavor FROM cones GROUP BY Flavor ORDER BY count DESC;
count|Flavor
-- 3|chocolate
-- 2|strawberry
-- 1|Vanilla
-- 1|bubblegum
SELECT MAX(Price), Flavor FROM cones GROUP Flavor;
Error: near "Flavor": syntax error
SELECT MAX(Price), Flavor FROM cones GROUP BY Flavor;
MAX(Price)|Flavor
3.-- 95|Vanilla
4.-- 75|bubblegum
5.-- 25|chocolate
5.-- 5|strawberry
SELECT DISTINCT Flavor FROM cones;
Flavor
strawberry
chocolate
bubblegum
Vanilla
SELECT DISTINCT Color FROM cones;
Color
pink
light brown
dark brown
white
^D
(env)
michael at Catalina-Pro in ~/Dropbox/Projects/cs88/cs88-website/src/assets/slides/fa20 on master [!?]
👉 sqlite3 22-SQL.db
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
.headers ON


.tables
cones  sales
SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.65
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.35
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95
SELECT * FRON sales;
Error: near "FRON": syntax error
SELECT * FROM sales;
Cashier|TID
Baskin|1
Baskin|3
Baskin|4
Robin|2
Robin|5
Robin|6






SELECT * FROM cones;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.65
-- 2|chocolate|light brown|4.75
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.35
-- 5|bubblegum|pink|4.75
-- 6|chocolate|dark brown|5.25
-- 7|Vanilla|white|3.95
.tables
cones  sales
SELECT * FROM sales;
Cashier|TID
Baskin|1
Baskin|3
Baskin|4
Robin|2
Robin|5
Robin|6
SELECT COUNT() FROM sales;
COUNT()
6
SELECT COUNT() FROM cones;
COUNT()
7
SELECT * FROM sales, cones;
Cashier|T-- Id|-- Id|Flavor|Color|Price
Baskin|-- 1|-- 1|strawberry|pink|3.65
Baskin|-- 1|-- 2|chocolate|light brown|4.75
Baskin|-- 1|-- 3|chocolate|dark brown|5.25
Baskin|-- 1|-- 4|strawberry|pink|5.35
Baskin|-- 1|-- 5|bubblegum|pink|4.75
Baskin|-- 1|-- 6|chocolate|dark brown|5.25
Baskin|-- 1|-- 7|Vanilla|white|3.95
Baskin|-- 3|-- 1|strawberry|pink|3.65
Baskin|-- 3|-- 2|chocolate|light brown|4.75
Baskin|-- 3|-- 3|chocolate|dark brown|5.25
Baskin|-- 3|-- 4|strawberry|pink|5.35
Baskin|-- 3|-- 5|bubblegum|pink|4.75
Baskin|-- 3|-- 6|chocolate|dark brown|5.25
Baskin|-- 3|-- 7|Vanilla|white|3.95
Baskin|-- 4|-- 1|strawberry|pink|3.65
Baskin|-- 4|-- 2|chocolate|light brown|4.75
Baskin|-- 4|-- 3|chocolate|dark brown|5.25
Baskin|-- 4|-- 4|strawberry|pink|5.35
Baskin|-- 4|-- 5|bubblegum|pink|4.75
Baskin|-- 4|-- 6|chocolate|dark brown|5.25
Baskin|-- 4|-- 7|Vanilla|white|3.95
Robin|-- 2|-- 1|strawberry|pink|3.65
Robin|-- 2|-- 2|chocolate|light brown|4.75
Robin|-- 2|-- 3|chocolate|dark brown|5.25
Robin|-- 2|-- 4|strawberry|pink|5.35
Robin|-- 2|-- 5|bubblegum|pink|4.75
Robin|-- 2|-- 6|chocolate|dark brown|5.25
Robin|-- 2|-- 7|Vanilla|white|3.95
Robin|-- 5|-- 1|strawberry|pink|3.65
Robin|-- 5|-- 2|chocolate|light brown|4.75
Robin|-- 5|-- 3|chocolate|dark brown|5.25
Robin|-- 5|-- 4|strawberry|pink|5.35
Robin|-- 5|-- 5|bubblegum|pink|4.75
Robin|-- 5|-- 6|chocolate|dark brown|5.25
Robin|-- 5|-- 7|Vanilla|white|3.95
Robin|-- 6|-- 1|strawberry|pink|3.65
Robin|-- 6|-- 2|chocolate|light brown|4.75
Robin|-- 6|-- 3|chocolate|dark brown|5.25
Robin|-- 6|-- 4|strawberry|pink|5.35
Robin|-- 6|-- 5|bubblegum|pink|4.75
Robin|-- 6|-- 6|chocolate|dark brown|5.25
Robin|-- 6|-- 7|Vanilla|white|3.95
SELECT COUNT() FROM sales, cones;
COUNT()
42
SELECT * FROM sales, cones WHERE TID = ID;
Cashier|T-- Id|-- Id|Flavor|Color|Price
Baskin|-- 1|-- 1|strawberry|pink|3.65
Baskin|-- 3|-- 3|chocolate|dark brown|5.25
Baskin|-- 4|-- 4|strawberry|pink|5.35
Robin|-- 2|-- 2|chocolate|light brown|4.75
Robin|-- 5|-- 5|bubblegum|pink|4.75
Robin|-- 6|-- 6|chocolate|dark brown|5.25
SELECT COUNT() FROM sales, cones WHERE TID = ID;
COUNT()
6
SELECT COUNT(), Flavor, Cashier FROM sales, cones WHERE TID = ID GROUP BY Flavor, Cashier;
COUNT()|Flavor|Cashier
-- 1|bubblegum|Robin
-- 1|chocolate|Baskin
-- 2|chocolate|Robin
-- 2|strawberry|Baskin







SELECT * FROM sales WHERE Cashier is 'Baskin';
Cashier|TID
Baskin|1
Baskin|3
Baskin|4
SELECT TID FROM sales WHERE Cashier is 'Baskin';
TID
1
3
4
SELECT * FROM cones WHERE Id IN (SELECT TID FROM sales WHERE Cashier is 'Baskin');
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.65
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.35
SELECT * FROM cones WHERE Id = 1 OR Id = 3 OR Id = 4;
-- Id|Flavor|Color|Price
-- 1|strawberry|pink|3.65
-- 3|chocolate|dark brown|5.25
-- 4|strawberry|pink|5.35
