Solutions: You can find the file with solutions for all questions here.

Quiz submissions were graded automatically for correctness. Implementations did not need to be efficient, as long as they were correct.

In addition to the doctests provided to students, we also used extra doctests to check for corner cases. These extra test cases are highlighted below.

To complete this homework assignment, you will need to use SQLite version 3.8.3 or greater. See Lab 12 for setup and usage instructions.

To check your progress, you can run `sqlite3` directly by running:

``sqlite3 -init quiz04.sql``

You should also check your work using `ok`:

``python3 ok``

### Data

In each question below, you will define a new table based on the following tables. The first defines the names, opening, and closing hours of great pizza places in Berkeley. The second defines typical meal times (for college students). A pizza place is open for a meal if the meal time is at or within the `open` and `close` times.

``````create table pizzas as
select "Pizzahhh" as name, 12 as open, 15 as close union
select "La Val's"        , 11        , 22          union
select "Sliver"          , 11        , 20          union
select "Cheeseboard"     , 16        , 23          union
select "Emilia's"        , 13        , 18;

create table meals as
select "breakfast" as meal, 11 as time union
select "lunch"            , 13         union
select "dinner"           , 19         union
select "snack"            , 22;``````

Your tables should still perform correctly even if the values in these tables were to change. Don't just hard-code the output to each query.

### Question 1

If two meals are more than 6 hours apart, then there's nothing wrong with going to the same pizza place for both, right? Create a `double` table with three columns. The first columns is the earlier meal, the second is the later meal, and the third is the name of a pizza place. Only include rows that describe two meals that are more than 6 hours apart and a pizza place that is open for both of the meals. The rows may appear in any order.
``````-- Two meals at the same place
create table double as
select a.meal, b.meal, name
from meals as a, meals as b, pizzas
where open <= a.time and a.time <= close and
open <= b.time and b.time <= close and
b.time > a.time + 6;

-- Example:
select * from double where name="Sliver";
-- Expected output:
--   breakfast|dinner|Sliver``````

``python3 ok -q double``

### Question 2

For each meal, list all the pizza options. Create a table `options` that has one row for every meal and three columns. The first column is the meal, the second is the total number of pizza places open for that meal, and the last column is a comma-separated list of open pizza places in alphabetical order. Assume that there is at least one pizza place open for every meal. Order the resulting rows by meal time.
``````-- Pizza options for every meal
create table options as
with
lists(meal, time, names, last, n) as (
select meal, time, name, name, 1
from pizzas, meals
where open <= time and time <= close union
select meal, time, names || ", " || name, name, n+1
from lists, pizzas
where open <= time and time <= close and name > last
)
select meal, max(n), names from lists group by meal order by time;

-- Example:
select * from options where meal="dinner";
-- Expected output:
--   dinner|3|Cheeseboard, La Val's, Sliver``````

Hint: Define a recursive table in a `with` statement that includes all partial lists of options, then use the `max` aggregate function to pick the full list for each meal.

``python3 ok -q options``