-- Mar 18 In-Class Exercise

 sqlite> CREATE TABLE FOO(A INT, B INT, C INT);
 sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT);
 sqlite> SELECT * FROM sqlite_stat1;
 Error: no such table: sqlite_stat1
 sqlite> ANALYZE FOO;
 sqlite> SELECT * FROM sqlite_stat1;
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 sqlite> INSERT INTO FOO VALUES(0,0,0);
 sqlite> INSERT INTO FOO VALUES(1,1,1);
 sqlite> INSERT INTO FOO VALUES(2,2,2);
 sqlite> SELECT * FROM sqlite_stat1;
 sqlite> SELECT * FROM sqlite_stat1;
 sqlite> ANALYZE FOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 sqlite> INSERT INTO GOO VALUES(0,0);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|1 1
 sqlite> INSERT INTO GOO VALUES(1,1);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|2 1
 sqlite> INSERT INTO GOO VALUES(2,2);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|3 1
 sqlite> INSERT INTO GOO VALUES(2,2);
 Error: UNIQUE constraint failed: GOO.C
 sqlite> CREATE INDEX GOO_INDEX ON GOO(D);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|GOO_INDEX|3 1
 GOO|sqlite_autoindex_GOO_1|3 1
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|GOO_INDEX|4 2
 GOO|sqlite_autoindex_GOO_1|4 1

sqlite> CREATE TABLE FOO(A INT, B INT, C INT); sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT); sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> INSERT INTO FOO VALUES(0,0,0); sqlite> INSERT INTO FOO VALUES(1,1,1); sqlite> INSERT INTO FOO VALUES(2,2,2); sqlite> SELECT * FROM sqlite_stat1; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 sqlite> INSERT INTO GOO VALUES(0,0); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> INSERT INTO GOO VALUES(1,1); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 sqlite> INSERT INTO GOO VALUES(2,2); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> INSERT INTO GOO VALUES(2,2); Error: UNIQUE constraint failed: GOO.C sqlite> CREATE INDEX GOO_INDEX ON GOO(D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|GOO_INDEX|3 1 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|GOO_INDEX|4 2 GOO|sqlite_autoindex_GOO_1|4 1 the first number indicates the number of rows.the second number is the average number of times the first column appears

-- Mar 18 In-Class Exercise

 sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT);
 sqlite> SELECT * FROM sqlite_stat1;
 Error: no such table: sqlite_stat1
 sqlite> ANALYZE FOO;
 sqlite> SELECT * FROM sqlite_stat1;
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 sqlite> insert into FOO(A, B, C) VALUES(1, 2, 3), (4,5,6), (7,8,9);
 sqlite> ANALYZE FOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 sqlite> insert into GOO(C,D) values(1,2);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|1 1
 sqlite> insert into GOO(C,D) values(3,4);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|2 1
 sqlite> insert into GOO(C,D) values(5,4);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|3 1
 sqlite> insert into foo(A,B,C) values(1,2,3);
 sqlite> analyze foo;
 sqlite> SELECT * FROM sqlite_stat1;
 GOO|sqlite_autoindex_GOO_1|3 1
 FOO||4
 The numbers after sqlite_autoindex specify the number of columns in the table and the particular columns to use for the index that's created

sqlite> create table FOO(A INT, B INT, C INT); sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT); sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> insert into FOO(A, B, C) VALUES(1, 2, 3), (4,5,6), (7,8,9); sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 sqlite> insert into GOO(C,D) values(1,2); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> insert into GOO(C,D) values(3,4); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 sqlite> insert into GOO(C,D) values(5,4); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> insert into foo(A,B,C) values(1,2,3); sqlite> analyze foo; sqlite> SELECT * FROM sqlite_stat1; GOO|sqlite_autoindex_GOO_1|3 1 FOO||4 The numbers after sqlite_autoindex specify the number of columns in the table and the particular columns to use for the index that's created

-- Mar 18 In-Class Exercise

sqlite> create table FOO(A int, B int, C int); sqlite> create table GOO(C int PRIMARY KEY, int D); sqlite> select * from sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> insert into FOO values(10, 10, 10); sqlite> insert into FOO values(11, 11, 11); sqlite> insert into FOO values(12, 12, 12); sqlite> insert into GOO values(1, 1); sqlite> insert into GOO values(2, 2); sqlite> insert into GOO values(3, 3); sqlite> ANALYZE FOO; sqlite> ANALYZE GOO;sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> insert into GOO values(4, 3); sqlite> ANALYZE GOO ; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|4 1 sqlite> CREATE INDEX INDEX_2 ON GOO(C); sqlite> SELECT * FROM sqlite_stat1;