Homework 10
Due by 11:59pm on Monday, 8/7
Instructions
Download hw10.zip.
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. Check that you have successfully submitted your code on
okpy.org.
See Lab 0
for more instructions on submitting assignments.
Using OK: If you have any questions about using OK, please refer to this guide.
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 13 for setup and usage instructions.
To check your progress, you can run sqlite3
directly by running:
sqlite3 --init hw10.sql
You should also check your work using ok
:
python3 ok
Homework Questions
Dogs
Dog Data
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
.
Question 1: Size of Dogs
The Fédération Cynologique Internationale classifies a standard poodle as over 45 cm and up to 60 cm. Thesizes
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 size_of_dogs
Question 2: By Height
Create a tableby_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 by_height
Question 3: Sentences
Create a single string for every pair of siblings that have the same size. Each value should be a sentence describing the siblings by their size, as shown in the expected output below.-- 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 thefrom
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 sentences
Question 4: Above Average
Create a table above_average
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
a height that is at least the average of all dogs that have the same 10's digit.
-- Heights and names of dogs that are above average in height among
-- dogs whose height has the same first digit.
create table above_average as
select "REPLACE THIS LINE WITH YOUR SOLUTION";
-- Example:
select * from above_average order by height;
-- Expected output:
-- 47|clinton
-- 35|eisenhower
-- 28|grover
Explanation: The average height of 20-something dogs is 27, of 30-something
dogs is 32.67, of 40-something dogs is 46.5, and of 50-something dogs is 52.
barack
is not included because there is only one 50-something dog.
Hint: You'll find it convenient to use a
with
clause to get a table of average heights for each first digit.Hint: A
group by
clause can contain arithmetic expressions as well as simple column names.
Test your solution with OK:
python3 ok -q above_average
Euclid Cafe Tycoon
Welcome to Northside! You are a connoisseur of coffee shops and wish to start a successful business here. 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).
Cafe Data
In each question below, you will define a new table based on the following tables.
-- 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
select 1 as n union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10;
Question 5: Open
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";
-- Example:
select * from open_locations where n >= 5;
-- Expected output:
-- 5
-- 7
-- 9
-- 10
Hint: Join
locations
andcafes
in thefrom
clause, so that you can compare a locationn
with thelocation
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
andmin
functions can be used to express this condition in ahaving
clause.
Test your solution with OK:
python3 ok -q open
Question 6: Allowed
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";
-- Example:
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
anditem_locations
, so that you can compare how far a locationn
is from thelocation
at which an item is already sold.Hint: Group by a combination of the location
n
and theitem
that might be sold atn
; use ahaving
clause to filter the groups.Hint: You do not need to use
open_locations
from the previous question.
Test your solution with OK:
python3 ok -q allowed