Due by 11:59pm on Thursday, 4/30
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, 4/30. 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.
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:
To complete this quiz, 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
If OK is not able to use sqlite3
to verify your work directly, it
will at least show you the output of the test cases, like the
following:
size > Suite 1 > Case 1
Unfortunately, OK is unable to use sqlite3 to test your code directly.
Here is a transcript of what your code does in the sqlite3 interpreter.
TEST:
.read quiz04.sql
select * from open_locations where n >= 5;
EXPECTED (order does not matter):
5
7
9
10
OUTPUT:
5
7
9
10
Please manually check if your solution's output is correct.
As long as the displayed OUTPUT
matches the EXPECTED
output, your
solution will be considered correct (even though OK will report a test
failure).
A block of 10 store locations, numbered 1 through 10, already has some cafes. Cafes can serve espresso, bagels, coffee, muffins, and eggs (but nothing else).
-- Locations of each cafe
create table cafes as
select "nefeli" as name, 2 as location union
select "brewed" , 8 union
select "hummingbird" , 6;
-- Menu items at each cafe
create table menus as
select "nefeli" as cafe, "espresso" as item union
select "nefeli" , "bagels" union
select "brewed" , "coffee" union
select "brewed" , "bagels" union
select "brewed" , "muffins" union
select "hummingbird" , "muffins" union
select "hummingbird" , "eggs";
-- All locations on the block
create table locations as
with locations(n) as (
select 1 union
select n+1 from locations where n < 10
)
select * from locations;
You would like to open a new cafe. Create a table open_locations
that
has a single column n
where each row is a location that is not already
occupied by an existing cafe.
-- Locations without a cafe
create table open_locations as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
-- select * from open_locations where n >= 5;
-- Expected output:
-- 5
-- 7
-- 9
-- 10
Hint: Join locations
and cafes
in the from
clause, so that you can
compare a location n
with the location
of an existing cafe.
Hint: A location is open if its minimum distance from an existing cafe is
more than 0. The built-in abs
and min
functions can be used to express
this condition in a having
clause.
You can test your solution interactively using sqlite3
by comparing with the expected output above.
$ sqlite3 -init quiz04.sql
sqlite3> select * from open_locations where n >= 5;
You can also test your solution using ok
:
python3 ok -q open
To limit competition, the block mandates that no cafe can have a menu item that is sold by another cafe within 2 locations. So, if location 2 already sells bagels, then a cafe located at 1, 3, or 4 cannot sell bagels. You may assume that every item is sold by at least one cafe.
Create a table allowed
that has two columns, a location n
and a menu
item
. The rows should contain every item that can be sold at every
location, based on these constraints. It should include occupied
locations that could expand their current menus.
-- Items that could be placed on a menu at an open location
create table allowed as
with item_locations(item, location) as (
select item, location from cafes, menus where name = cafe
)
select "REPLACE THIS LINE WITH YOUR SOLUTION";
-- select * from allowed where n >= 5;
-- Expected output:
-- 5|bagels
-- 5|coffee
-- 5|espresso
-- 6|espresso
-- 7|espresso
-- 8|espresso
-- 9|eggs
-- 9|espresso
-- 10|eggs
-- 10|espresso
Hint: Join locations
and item_locations
, so that you can compare how
far a location n
is from the location
at which an item is already sold.
Hint: Group by a combination of the location n
and the item
that might
be sold at n
; use a having
clause to filter the groups.
Hint: You do not need to use open_locations
from the previous question.
You can test your solution interactively using sqlite3
by comparing with the expected output above.
$ sqlite3 -init quiz04.sql
sqlite3> select * from allowed where n >= 5;
You can also test your solution using ok
:
python3 ok -q allowed
This question is optional and ungraded. It is strongly recommended for practice! You will need to answer the previous two questions successfully to answer this question.
You decide you will sell as many menu items as possible in your new location.
Create a table full
that has three columns, an open location n
, a
comma-separated list of items
that you would sell, and the length of that
list. The items
should be in alphabetical order, and only the longest
possible list of items
for each location
should appear as a row.
-- Open locations and their maximum-length menus
create table full as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
-- select n, items from full where n >= 5;
-- Expected output:
-- 5|bagels, coffee, espresso
-- 7|espresso
-- 9|eggs, espresso
-- 10|eggs, espresso
You can test your solution interactively using sqlite3
by comparing with the expected output above.
$ sqlite3 -init quiz04.sql
sqlite3> select n, items from full where n >= 5;
You can also test your solution using ok
:
python3 ok -q full