Please post your solutions to the March 18 In-Class Exercise to this thread.

Best,

Chris

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;

FOO||3

GOO|INDEX_2|4 1

GOO|sqlite_autoindex_GOO_1|'''4 1'''

for the bold portion: 1st How many index rows you have, 2nd a given value of the ‘D’ attribute would appear once, third, given ordered pair a,b appears on average once(not there)

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

Re-formatting transcript sec. Transcript:

SQLite version 3.30.0 2019-10-04 15:03:17 Enter ".help" for usage hints. 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 sqlite>

The second value probably keeps track of the primary key. I'm assuming that the '1' indicates that it's the first column that has the index (or that there's only one column)? I think the '3' in 'GOO|sqlite_autoindex_GOO_1|3 1' indicates the number of rows, and the '1' indicates the number of columns that is part of the index.

If we had two columns as the index, I think it would be 'GOO|sqlite_autoindex_GOO_2|3 2', but I'm not sure ¯_()_/¯

<pre> 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> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> SELECT * FROM sqlite_autoindex_GOO_1 ; Error: no such table: sqlite_autoindex_GOO_1 sqlite> .tables FOO GOO sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT); Error: table GOO already exists sqlite> DROP TABLE GOO; sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT); sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> INSERT INTO GOO VALUES (1, 2) ...> ; sqlite> INSERT INTO GOO VALUES (3, 4); sqlite> INSERT INTO GOO VALUES (5, 4); sqlite> INSERT INTO FOO VALUES (1, 2, 3); sqlite> INSERT INTO FOO VALUES (4, 5, 6); sqlite> INSERT INTO FOO VALUES (7, 8, 9); sqlite> SELECT * FROM GOO; C D

1 2
3 4
5 4
sqlite> CREATE INDEX goo_index ON GOO(D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; tbl idx stat

GOO goo_index 3 2
GOO sqlite_aut 3 1
sqlite>

</pre>

<nowiki>Microsoft Windows [Version 10.0.18362.720] (c) 2019 Microsoft Corporation. All rights reserved.

C:\Users\12066>sqlite3 m.db SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> create table m.FOO(A int B int C int) ...> create table m.Goo(c int primary key, D int) ...> ...> ...> ...> ...> ...> ^Z

Error: unknown database m

C:\Users\12066>sqlite3 SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT * FROM m.Foo ...> ...> ...> ^Z

Error: no such table: m.Foo

C:\Users\12066>m 'm' is not recognized as an internal or external command, operable program or batch file.

C:\Users\12066>sqlite.connect 'sqlite.connect' is not recognized as an internal or external command, operable program or batch file.

C:\Users\12066>sqlite3 m.db SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> CREATE TABLE FOO(A int, B int, C int) ...> ...> \ ...> ...> ...> ; Error: unrecognized token: "" sqlite> ; sqlite> ; sqlite> ; sqlite> sqlite> sqlite> sqlite> sqlite> SELECT * FROM FOO ...> ; Error: no such table: FOO sqlite> CREATE TABLE FOO(A int, B int, C int); sqlite> SELECT * FROM FOO ...> ; 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> sqlite> INSERT INTO FOO(A,B,C) VALUES (0,1,2); sqlite> INSERT INTO FOO(A,B,C) VALUES (1,2,3); sqlite> INSERT INTO FOO(A,B,C) VALUES (3,2,1); sqlite> INSERT INTO GOO(C,D) VALUES (0,1); sqlite> INSERT INTO GOO(C,D) VALUES (1,5); sqlite> INSERT INTO GOO(C,D) VALUES (1,10); Error: UNIQUE constraint failed: GOO.C sqlite> INSERT INTO GOO(C,D) VALUES (2,10); sqlite> ANALYZE FOO ...> ; 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 (4,4,4); sqlite> INSERT INTO GOO(C, D) VALUES (0, 2); Error: UNIQUE constraint failed: GOO.C sqlite> SELECT * FROM dqlite_stat1; Error: no such table: dqlite_stat1 sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite>

C:\Users\12066></nowiki>

<nowiki>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(1,2,3); sqlite> insert into foo values(2,3,4); sqlite> insert into foo values(4,5,6); sqlite> insert into goo values(1,2); sqlite> insert into goo values(2,3); sqlite> insert into goo values(4,5); sqlite> analyze foo; sqlite> select* from sqlite_stat1; foo||3 sqlite> analyze goo; sqlite> select* from sqlite_stat1; foo||3 goo|sqlite_autoindex_goo_1|3 1 sqlite> insert into goo values(3,3); sqlite> analyze goo; sqlite> select* from sqlite_stat1; foo||3 goo|sqlite_autoindex_goo_1|4 1 sqlite> create index goo_index on goo(d); sqlite> analyze goo; sqlite> select* from sqlite_stat1; foo||3 goo|goo_index|4 2 goo|sqlite_autoindex_goo_1|4 1 sqlite></nowiki>

<pre> 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; 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); sqlite> analyze FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||1 sqlite> insert into FOO (A, B, C) values (4, 5, 6); sqlite> SELECT * FROM sqlite_stat1; FOO||1 sqlite> analyze FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||2 sqlite> insert into FOO (A, B, C) values (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, 6); sqlite> analyze GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> insert into GOO (C, D) values (7, 6); sqlite> create index D_index on GOO(D); sqlite> analyze GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|D_index|4 2 GOO|sqlite_autoindex_GOO_1|4 1 sqlite> select * from GOO; 1|2 3|4 5|6 7|6

</pre>

C:\Users\1ninj>sqlite3 foo.sqlite

SQLite version 3.31.1 2020-01-27 19:55:54

Enter ".help" for usage hints.

sqlite> create table foo(A int, B int, C int);

sqlite> select * from foo;

sqlite> create table foo(A int,B int, C int);

Error: table foo already exists

sqlite> select * from foo;

sqlite> insert into foo values(1,1,2);

sqlite> select * from foo;

1|1|2

sqlite> delete from foo;

sqlite> select * from foo;

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(1,1,2);

sqlite> insert into foo values(2,31,2);

sqlite> insert into foo values(27,31,22);

sqlite> select * from sqlite_stat1;

sqlite> analyze foo;

sqlite> select * from sqlite_stat1;

foo||3

sqlite> insert into goo values(1,1);

sqlite> analyze goo;

sqlite> select * from sqlite_stat1;

foo||3

goo|sqlite_autoindex_goo_1|1 1

sqlite> insert into goo values(2,12);

sqlite> analyze goo;

sqlite> select * from sqlite_stat1;

foo||3

goo|sqlite_autoindex_goo_1|2 1

sqlite> insert into goo values(3,31);

sqlite> analyze goo;

sqlite> select * from sqlite_stat1;

foo||3

goo|sqlite_autoindex_goo_1|3 1

sqlite> insert into goo values(1,1);

Error: UNIQUE constraint failed: goo.C

sqlite> insert into goo values(1,2);

Error: UNIQUE constraint failed: goo.C

sqlite> insert into goo values(2,1);

Error: UNIQUE constraint failed: goo.C

sqlite> insert into goo values(5,1);

sqlite> analyze goo;

sqlite> select * from sqlite_stat1;

foo||3

goo|sqlite_autoindex_goo_1|4 1

.

the first number is the number of elements.the second number is the average number of times the other attribute is reused

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 (1, 2, 3); sqlite> select * from sqlite_stat1; sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; FOO||1 sqlite> insert into FOO values (2, 3, 4); sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; FOO||2 sqlite> insert into FOO values (3, 4, 5); sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; FOO||3 sqlite> insert into GOO values (1, 2); sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> insert into GOO values (5, 2); sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 sqlite> insert into GOO values (3, 3); sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> CREATE INDEX ind on GOO (D); sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; FOO||3 GOO|ind|3 2 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> CREATE INDEX ind2 on FOO (A); sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; GOO|ind|3 2 GOO|sqlite_autoindex_GOO_1|3 1 FOO|ind2|3 1 sqlite> CREATE INDEX ind3 on FOO (C); sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; GOO|ind|3 2 GOO|sqlite_autoindex_GOO_1|3 1 FOO|ind3|3 1 FOO|ind2|3 1

The additional fields are for the number of rows and the avg number of rows a unique value is in.