CS 61A

Time: Thu 11/16/17 3 pm

Iterables & Iterators

Iterables implement the __iter__ method, and iterators (which are also iterables) additionally implement the __next__ method. In an iterator, the iteration halts at StopIterator exception.

Generators produce iterators.

SQL

Syntax

Refer to SQL As Understood By SQLite.

Create table create-table-stmt

Usage example:

CREATE TABLE cities AS
  SELECT "Berkeley" as city, "California" as state UNION
  SELECT "San Francisco",    "California"          UNION
  SELECT "Houston",          "Texas";

Diagram

Select select-stmt

Usage example:
```
SELECT * FROM cities
  WHERE state="California"
  ORDER BY city ASC;
```
A recursive with-clause will be introduced this Friday.

Diagram

Demo for Recursive Selects

-- Family

create table family as
  select "Bob" as member, "Danny" as parent union
  select "Gaby", "Bob" union
  select "Lila", "Danny";

-- Discover all the ancestors for each of the family member

with ancestors(member, ancestor, distance) as (
  select member,        parent,             1                      from family union
  select family.member, ancestors.ancestor, ancestors.distance + 1 from family, ancestors
    where family.parent = ancestors.person
) select * from ancestors;

-- With a better formatting

with ancestors(member, ancestor, distance) as (
  select member,        parent,             1                      from family union
  select family.member, ancestors.ancestor, ancestors.distance + 1 from family, ancestors
    where family.parent = ancestors.person
) select person || " is of distance " || distance || " from " || ancestor from ancestors;

The basic algorithm for computing the content of the recursive table is as follows:

Run the initial-select and add the results to a queue.
While the queue is not empty:
- Extract a single row from the queue.
- Insert that single row into the recursive table
- Pretend that the single row just extracted is the only row in the recursive table and run the recursive-select, adding all results to the queue.

More details about how with clause is executed: https://sqlite.org/lang_with.html