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

Question 2: By Height

Create a table 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 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 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 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 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.

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

Test your solution with OK:

python3 ok -q allowed