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.

Q4: Stacks

Sufficiently 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); -- Add your INSERT INTOs here 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

We recommend using the following procedure to create your table:

Start with the provided empty stack_helper table. This will store all the stacks of dogs as a comma separated list, along with the total height and the height of the last dog added in order to ensure we have the right order in the stack.

Use an INSERT INTO to initialize stack_helper with stacks of just one dog. You may find the following syntax helpful for inserting rows into a table from another table:

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

Now, use the stacks of one dog to insert stacks of two dogs. Repeat this process until you have stacks of up to four dogs.

Remember to fill in the stacks table to use your results from stack_helper!

Hint: Generating the comma-separated list of dogs is easier if you start with no commas for one dog.