Homework 11: SQL

• hw11.zip

Due by 11:59pm on Friday, December 5

Instructions

Download hw11.zip.

Submission: When you are done, submit the assignment to Gradescope. 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 Gradescope. 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:

• Section 4.3

Grading: Homework is graded based on correctness. Each incorrect problem will decrease the total score by one point. This homework is out of 6 points.

To check your progress, you can run sqlite3 directly by running:

python3 sqlite_shell.py --init hw11.sql

You should also check your work using ok:

python3 ok

Required Questions

Dog Data

In each question below, you will define a new table based on the following tables about dogs.

CREATE TABLE parents (parent TEXT, child TEXT);

INSERT INTO parents VALUES
  ('ace', 'bella'),
  ('ace', 'charlie'),
  ('daisy', 'hank'),
  ('finn', 'ace'),
  ('finn', 'daisy'),
  ('finn', 'ginger'),
  ('ellie', 'finn');

CREATE TABLE dogs (name TEXT, fur TEXT, height INTEGER);

INSERT INTO dogs VALUES
  ('ace',     'long',  26),
  ('bella',   'short', 52),
  ('charlie', 'long',  47),
  ('daisy',   'long',  46),
  ('ellie',   'short', 35),
  ('finn',    'curly', 32),
  ('ginger',  'short', 28),
  ('hank',    'curly', 31);

CREATE TABLE sizes (size TEXT, min INTEGER, max INTEGER);

INSERT INTO sizes VALUES
  ('toy',      24, 28),
  ('mini',     28, 35),
  ('medium',   35, 45),
  ('standard', 45, 60);

The parents table contains one row for each parent-child relationship; for example, the first row describes that the dog "ace" is the parent of the dog "bella". The dogs table contains the name, fur type, and height for each dog. The sizes table has one row for each classification of dog. A dog matches a particular classification if its height is greater than min and less than or equal to max.

Your queries 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 name LIKE "h%";

The % sign matches any number of characters, and patterns in sqlite are case in-sensitive, so the pattern "h%" matches all strings that begin with h or H. This query would still be correct if a row was added with the name harry. Contrastingly, writing a query like SELECT "hank"; would not, and only works for the example input.

Q1: By Parent Height

Create a table by_parent_height that has a column of the names of all dogs that have a parent, ordered by the height of the parent dog from tallest parent to shortest parent.

-- All dogs with parents ordered by decreasing height of their parent
CREATE TABLE by_parent_height AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

For example, finn has a parent ellie with height 35, and so should appear before ginger who has a parent finn with height 32. The names of dogs with parents of the same height should appear together in any order. For example, bella and charlie should both appear at the end, but either one can come before the other.

For our example tables, the by_parent_height table should look like this:

+----------+
| chil     |
+----------+
| hank     |
| finn     |
| ace      |
| daisy    |
| ginger   |
| bella    |
| charlie  |
+----------+

Use Ok to test your code:

python3 ok -q by_parent_height
Copy
✂️

Q2: 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 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";

The size_of_dogs table should look like this:

+----------+----------+
| name     | size     |
+----------+----------+
| ace      | toy      |
| bella    | standard |
| charlie  | standard |
| daisy    | standard |
| ellie    | mini     |
| finn     | mini     |
| ginger   | toy      |
| hank     | mini     |
+----------+----------+

Use Ok to test your code:

python3 ok -q size_of_dogs
Copy
✂️

Q3: Sentences

Siblings are pairs of dogs that have the same parent. Create a table that contains a row for each pair of siblings that have the same size classification, with a single column that contains a sentence describing the siblings by their size.

-- [Optional] Filling out this helper table is recommended
CREATE TABLE siblings AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

-- 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 the output, and siblings should be listed in alphabetical order (e.g. "bella and charlie..." instead of "charlie and bella..."), as follows:

sqlite> SELECT * FROM sentences;
The two siblings, bella and charlie, have the same size: standard
The two siblings, ace and ginger, have the same size: toy

Hint: First, create a helper table containing the names of each pair of siblings. This will make comparing the sizes of siblings when constructing the main table easier. Make sure to not pair a child with themselves and do not include duplicate pairs.

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, e.g. SELECT "hello" || "world"; will return helloworld.

Use Ok to test your code:

python3 ok -q sentences
Copy
✂️

Q4: Low Variance

We want to create a table that contains the height range (defined as the difference between maximum and minimum height) of all dogs that share a fur type. However, we'll only consider fur types where each dog with that fur type is within 30% of the average height of all dogs with that fur type; we call this the low variance criterion.

For example, if the average height for short-haired dogs is 10, then in order to be included in our output, all dogs with short hair must have a height of at most 13 and at least 7 (inclusive).

Hint: MIN, MAX, and AVG will be useful here.

Hint: You may want to first find the average height and make sure that:

* There are no heights smaller than 0.7 (i.e. 70%) of the average.
* There are no heights greater than 1.3 (i.e. 130%) of the average.

-- Height range for each fur type where all of the heights differ by no more than 30% from the average height
CREATE TABLE low_variance AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

Your output should have two columns, in this order: the fur type and the height_range for the fur types that meet this criteria. For the example tables, it should look like this:

+------------+--------------+
| fur        | height_range |
+------------+--------------+
| Curly      | 1            |
+------------+--------------+

Explanation

Use Ok to test your code:

python3 ok -q low_variance
Copy
✂️

Check Your Score Locally

You can locally check your score on each question of this assignment by running

python3 ok --score

This does NOT submit the assignment! When you are satisfied with your score, submit the assignment to Gradescope to receive credit for it.

Submit Assignment

Submit this assignment by uploading any files you've edited to the appropriate Gradescope assignment. Lab 00 has detailed instructions.

Make sure to submit hw11.sql to the autograder!