Due by 11:59pm on Wednesday, 4/29
Download hw09.zip. Inside the archive, you will find a file called hw09.sql, along with a copy of the OK autograder.
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 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 hw09.sql
You should also check your work using ok
:
python3 ok
If OK is not able to use sqlite3
to verify your work directly, OK
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 hw09.sql
select name from size_of_dogs where size="toy" or size="mini";
EXPECTED (order does not matter):
abraham
eisenhower
fillmore
grover
herbert
OUTPUT:
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).
In each question below, you will define a new table based on the following tables.
create table parents as
select "abraham" as parent, "barack" as child union
select "abraham" , "clinton" union
select "delano" , "herbert" union
select "fillmore" , "abraham" union
select "fillmore" , "delano" union
select "fillmore" , "grover" union
select "eisenhower" , "fillmore";
create table dogs as
select "abraham" as name, "long" as fur, 26 as height union
select "barack" , "short" , 52 union
select "clinton" , "long" , 47 union
select "delano" , "long" , 46 union
select "eisenhower" , "short" , 35 union
select "fillmore" , "curly" , 32 union
select "grover" , "short" , 28 union
select "herbert" , "curly" , 31;
create table sizes as
select "toy" as size, 24 as min, 28 as max union
select "mini", 28, 35 union
select "medium", 35, 45 union
select "standard", 45, 60;
Your tables should still perform correctly even if the values in these tables change. For example, if you are asked to list all dogs with a name that starts with h, you should write:
select name from dogs where "h" <= name and name < "i";
Instead of assuming that the dogs
table has only the data above and writing
select "herbert";
The former query would still be correct if the name grover
were changed to
hoover
or a row was added with the name harry
.
sizes
table describes this and other such
classifications, where a dog must be over the min
and less than or equal to
the max
in height to qualify as a size
.
Create a size_of_dogs
table with two columns, one for each dog's name
and
another for its size
.
-- The size of each dog
create table size_of_dogs as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
-- Example:
select name from size_of_dogs where size="toy" or size="mini";
-- Expected output:
-- abraham
-- eisenhower
-- fillmore
-- grover
-- herbert
Test your solution with OK:
python3 ok -q small
by_height
that has a column of the names of all dogs that have
a parent
, ordered by the height of the parent from tallest parent to shortest
parent.
-- All dogs with parents ordered by decreasing height of their parent
create table by_height as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
For example, fillmore
has a parent (eisenhower
) with height 35, and so
should appear before grover
who has a parent (fillmore
) with height 32.
The names of dogs with parents of the same height should appear together in any
order. For example, barack
and clinton
should both appear at the end, but
either one can come before the other.
-- Example:
select * from by_height;
-- Expected output:
-- herbert
-- fillmore
-- abraham
-- delano
-- grover
-- barack
-- clinton
Test your solution with OK:
python3 ok -q parent-height
-- Sentences about siblings that are the same size
create table sentences as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
Each sibling pair should appear only once in alphabetical order.
-- Example:
select * from sentences;
-- Expected output:
-- barack and clinton are standard siblings
-- abraham and grover are toy siblings
Hint: First use a with
clause to create a local table of siblings.
Comparing the size of siblings will be simplified.
Hint: If you join a table with itself, use as
within the from
clause to
give each table an alias.
Hint: In order to concatenate two strings into one, use the ||
operator.
Test your solution with OK:
python3 ok -q size-siblings
Create a two-column table describing all stacks of dogs at least 170 cm high. The first column should contains a comma-separated list of dogs in the stack, and the second column should contain the total height of the stack. Order the stacks in increasing order of total height.
-- Ways to stack 4 dogs to a height of at least 170, ordered by total height
create table stacks as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
A valid stack of dogs includes each dog only once, and the dogs should be listed in increasing order of height within the stack. Assume that no two dogs have the same height.
-- Example:
select * from stacks;
-- Expected output:
-- abraham, delano, clinton, barack|171
-- grover, delano, clinton, barack|173
-- herbert, delano, clinton, barack|176
-- fillmore, delano, clinton, barack|177
-- eisenhower, delano, clinton, barack|180
Hint: Use a with
clause to create a recursive table with additional
columns, such as the number of dogs that have been stacked and information
about the last dog added (to control the dog order). Then, select the
rows and columns from this larger table to generate the final solution.
Hint: Use height comparisons to ensure that dogs are not repeated in a stack.
Hint: Generating the comma-separated list of dogs is easier if your base case includes the name of one dog without any commas before or after it, rather than no dogs at all.
Test your solution with OK:
python3 ok -q stack
Create a table tallest
that includes the height
and name
of every dog
that shares the 10's digit of its height
with at least one other dog and has
the highest 1's digit of all dogs that have the same 10's digit.
create table tallest as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
-- Example:
select * from tallest;
-- Expected output:
-- 28|grover
-- 35|eisenhower
-- 47|clinton
Explanation: grover
is the tallest 20-something dog. eisenhower
is the
tallest 30-something dog. clinton
is the tallest 40-something dog. barack
is not included because there are no other 50-something dogs.
Test your solution with OK:
python3 ok -q tallest
This question is optional but recommended for practice. You can receive full credit for the homework without attempting this problem.
A non-parent relation is either an ancestor that is not a parent (such as a grandparent or great-grandparent) or a descendent that is not a child (such as a grandchild or great-grandchild). Siblings are not relations under this definition.
Select all pairs that form non-parent relations ordered by the difference in height between one dog and the other.
-- All non-parent relations ordered by height difference
create table non_parents as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
The shortest paired with the tallest should appear first, and the tallest paired with the shortest should appear last. If two pairs have the same height difference, they may appear together in any order.
-- Example:
select * from non_parents;
-- Expected output:
-- fillmore|barack
-- eisenhower|barack
-- fillmore|clinton
-- eisenhower|clinton
-- eisenhower|delano
-- abraham|eisenhower
-- grover|eisenhower
-- herbert|eisenhower
-- herbert|fillmore
-- fillmore|herbert
-- eisenhower|herbert
-- eisenhower|grover
-- eisenhower|abraham
-- delano|eisenhower
-- clinton|eisenhower
-- clinton|fillmore
-- barack|eisenhower
-- barack|fillmore
Test your solution with OK:
python3 ok -q relations