# In-Class Exercise 12: SQLite

In this exercise, we will look at the query compiler of SQLite, which compiles queries to bytecode. The command `explain <query>` shows the bytecode for a query. You can find the bytecode documentation here: https://sqlite.org/opcode.html

1. Start sqlite3 (either locally or on lxhalle) and create a schema, e.g. using:

    create table foo(x int, y int);

2. Use `explain select * from foo;` to see the bytecode for this simple query. Look at the documentation to understand the bytecode.

3. Run `explain select distinct * from foo;`. What is different?

4. Try a more complex query like `explain select * from foo a, foo b where a.x == b.y;`. What happens? Which execution model does SQLite use for code generation? What data structure does SQLite use for storing intermediate data?

5. What happens when sorting is required? `explain select * from foo order by x;`

6. What is the result of an aggregation? `explain select count(*) from foo group by x;`

7. How do transactions behave differently on write queries? `explain update foo set x = 1 where x = 2;`

8. Try more complex queries of your interest and analyze the result.