# 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