You can find the solutions in the hw11.sql file.
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 hw11.sql
You should also check your work using
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
The former query would still be correct if the name
grover were changed to
hoover or a row was added with the name
Q1: Size of DogsThe Fédération Cynologique Internationale classifies a standard poodle as over 45 cm and up to 60 cm. The
sizestable describes this and other such classifications, where a dog must be over the
minand less than or equal to the
maxin height to qualify as a
size_of_dogs table with two columns, one for each dog's
another for its
-- The size of each dog CREATE TABLE size_of_dogs ASSELECT name, size FROM dogs, sizes WHERE height > min AND height <= max;
The output should look like the following:
sqlite> select * from size_of_dogs; abraham|toy barack|standard clinton|standard delano|standard eisenhower|mini fillmore|mini grover|toy herbert|mini
Use Ok to test your code:
python3 ok -q size_of_dogs
We know that at a minimum, we need information from both the
table. Finally, we filter and keep only the rows that make sense: a size that
corresponds to the size of the dog we're currently considering.
Q2: By Parent HeightCreate a table
by_parent_heightthat 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_parent_height ASSELECT child FROM parents, dogs WHERE name = parent ORDER BY -height;
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,
clinton should both appear at the end, but
either one can come before the other.
sqlite> select * from by_parent_height; herbert fillmore abraham delano grover barack clinton
Use Ok to test your code:
python3 ok -q by_parent_height
We need information from both the
parents and the
dogs table. This time, the
only rows that make sense are the ones where a child is matched up with their
parent. Finally, we order the result by descending height.
Q3: SentencesThere are two pairs of siblings that have the same size. Create a table that contains a row with a string for each of these pairs. Each string should be a sentence describing the siblings by their size.
-- Filling out this helper table is optional CREATE TABLE siblings ASSELECT a.child AS first, b.child AS second FROM parents AS a, parents AS b WHERE a.parent = b.parent AND a.child < b.child;-- Sentences about siblings that are the same size CREATE TABLE sentences ASSELECT first || " and " || second || " are " || a.size || " siblings" FROM siblings, size_of_dogs AS a, size_of_dogs AS b WHERE a.size = b.size AND a.name = first AND b.name = second;
Each sibling pair should appear only once in the output, and siblings should be
listed in alphabetical order (e.g.
"barack and clinton..." instead of
"clinton and barack..."), as follows:
sqlite> select * from sentences; abraham and grover are toy siblings barack and clinton are standard siblings
Hint: First, create a helper table containing each pair of siblings. This will make comparing the sizes of siblings when constructing the main table easier.
Use Ok to test your code:
python3 ok -q sentences
Roughly speaking, there are two tasks we need to solve here:
Figure out which dogs are siblings
A sibling is someone you share a parent with. This will probably involve the
It might be tempting to join this with
dogs, but there isn't any extra
information provided by a dogs table that we need at this time. Furthermore, we
still need information on sibling for a given dog, since the
just associates each dog to a parent.
The next step, therefore, is to match all children to all other children by joining the parents table to itself. The only rows here that make sense are the rows that represent sibling relationships since they share the same parent.
Remember that we want to avoid duplicates! If dog A and B are siblings, we don't want both A/B and B/A to appear in the final result. We also definitely don't want A/A to be a sibling pair. Enforcing ordering on the sibling names ensures that we don't have either issue.
Construct sentences based on sibling information
After determining the siblings, constructing the sentences just requires us to
get the size of each sibling. We could join on the
sizes tables as
we did in an earlier problem, but there's no need to redo that work. Instead,
we'll reuse our
size_of_dogs table to figure out the size of each sibling in
Q4: StacksSufficiently sure-footed dogs can stand on either other's backs to form a stack (up to a point). We'll say that the total height of such a stack is the sum of the heights of the dogs.
Create a two-column table describing all stacks of up to four dogs at least 170 cm high. The first column should contain 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_helper(dogs, stack_height, last_height);INSERT INTO stacks_helper SELECT name, height, height FROM dogs; INSERT INTO stacks_helper SELECT dogs || ", " || name, stack_height + height, height FROM stacks_helper, dogs WHERE height > last_height; INSERT INTO stacks_helper SELECT dogs || ", " || name, stack_height + height, height FROM stacks_helper, dogs WHERE height > last_height; INSERT INTO stacks_helper SELECT dogs || ", " || name, stack_height + height, height FROM stacks_helper, dogs WHERE height > last_height;CREATE TABLE stacks ASSELECT dogs, stack_height FROM stacks_helper WHERE stack_height >= 170 ORDER BY stack_height;
A valid stack of dogs includes each dog only once, and the dogs should be listed in increasing order of height within the stack. You may assume that no two dogs have the same height.
sqlite> select * from stacks; 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
You should use the provided helper table
stacks_helper. It has 3 columns: (1)
dogs - a stack of dogs as a comma separated list of dog names, (2)
stack_height - the height of the stack, and (3)
last_height - the height
of the last dog added to the stack (in order to ensure we have the right order
in the stack).
First, fill this table up by doing the following:
INSERT INTOto add stacks of just one dog into
stacks_helper. You can use this syntax to insert rows from a table called
t1into a table called
INSERT INTO t2 SELECT [expression] FROM t1 ...;
sqlite> CREATE TABLE t1 AS ...> SELECT 1 as a, 2 as b; sqlite> CREATE TABLE t2(c, d); sqlite> INSERT INTO t2 SELECT a, b FROM t1; sqlite> SELECT * FROM t2; 1|2
Now, use the stacks of one dog to insert stacks of two dogs. It's possible to
INSERT INTOa table rows selected from that same table. For example,
sqlite> CREATE TABLE ints AS ...> SELECT 1 AS n UNION ...> SELECT 2 UNION ...> SELECT 3; sqlite> INSERT INTO ints(n) SELECT n+3 FROM ints; sqlite> SELECT * FROM ints; 1 2 3 4 5 6
- Repeat step 3 to create stacks of three dogs, then of four dogs.
Once you've built up to stacks of four dogs in your
stacks_helper table, use
it to fill in the
Use Ok to test your code:
python3 ok -q stacks
In the solution, we follow the recommended procedure outlined in the problem above.
Here's some details to think about:
- Each iteration, we will generate the stack with n + 1 dogs, but we'll
also regenerate all the previous stacks! For example, the stacks of size
1 are still around to generate the stacks of size 2. As such there are
many duplicate rows of stack size 1, 2, and 3 in our
- This turns out not to be an issue: we got lucky since there weren't any
stacks of size less than 4 that were tall enough. But even if there
were, we could use
DISTINCTto remove duplicate rows.
- Is there a way we could be more space efficient? Think about how we could generate new rows without keeping around all the previous ones.
Once we have everything in our
stack_helper table, we just keep the rows
from it that correspond to the tallest stacks. We also no longer need the