Due by 11:59pm on Thursday, 12/3

Instructions

Download quiz04.zip. Inside the archive, you will find a file called quiz04.sql, along with a copy of the OK autograder.

Complete the quiz and submit it before 11:59pm on Thursday, 12/3. You must work alone, but you may talk to the course staff (see Asking Questions below). You may use any course materials, including an interpreter, course videos, slides, and readings. Please do not discuss these specific questions with your classmates, and do not scour the web for answers or post your answers online.

Your submission will be graded automatically for correctness. Your implementations do not need to be efficient, as long as they are correct. We will apply additional correctness tests as well as the ones provided. Passing these tests does not guarantee a perfect score.

Asking Questions: If you believe you need clarification on a question, make a private post on Piazza. Please do not post publicly about the quiz contents. If the staff discovers a problem with the quiz or needs to clarify a question, we will email the class via Piazza. You can also come to office hours to ask questions about the quiz or any other course material, but no answers or hints will be provided in office hours.

Submission: When you are done, submit with python3 ok --submit. You may submit more than once before the deadline; only the final submission will be scored.

Using OK

The ok program helps you test your code and track your progress. The first time you run the autograder, you will be asked to log in with your @berkeley.edu account using your web browser. Please do so. Each time you run ok, it will back up your work and progress on our servers. You can run all the doctests with the following command:

python3 ok

To test a specific question, use the -q option with the name of the function:

python3 ok -q <function>

By default, only tests that fail will appear. If you want to see how you did on all tests, you can use the -v option:

python3 ok -v

If you do not want to send your progress to our server or you have any problems logging in, add the --local flag to block all communication:

python3 ok --local

When you are ready to submit, run ok with the --submit option:

python3 ok --submit

Readings: You might find the following references useful:

• Section 4.3

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 "REPLACE THIS LINE WITH YOUR SOLUTION";

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

Test your solution with OK:

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
select "REPLACE THIS LINE WITH YOUR SOLUTION";

-- 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.

Test your solution with OK:

python3 ok -q options