CS 61A: Quiz 3

Due by 11:59pm on Thursday, 12/4

Instructions: Download, complete, and submit the quiz3.sql starter file before the deadline above. You must work alone, but you may talk to the course staff (see Asking Questions below). You may use any course materials, including an interpreter, course videos, slides, and readings. Please do not discuss these specific questions with your classmates, and do not scour the web for answers or post your answers online.

Submit with submit quiz3. You may submit more than once before the deadline; only the final submission will be scored. See Lab 1 for submission instructions.

Your submission will be graded automatically for correctness. Your implementations do not need to be efficient, as long as they are correct.

Asking Questions. If you believe you need clarification on a question, make a private post on Piazza. Please do not post publicly about the quiz contents. If the staff discovers a problem with the quiz or needs to clarify a question, we will email the class via Piazza. You can also come to office hours to ask questions about the quiz or any other course material, but no answers or hints will be provided in office hours.

Readings: You might find the following references useful:

Table of Contents

To complete this quiz, you will need to use Sqlite version 3.8.3 or greater. You can

After installing Sqlite, you can run the starter file using the command:

sqlite3 -init quiz3.sql

Data

In each question below, you will define a select statement that processes the following tables, which include an additional dog named Jackson!

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"        union
  select "delano"           , "jackson";

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           union
  select "jackson"        , "long"       , 43;

Your select statement should still perform correctly even if the values in these tables are changed. 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

(1 point) Write a SQL query that selects all possible combinations of three dogs with the same fur and lists them in order of increasing height.

-- All triples of dogs with the same fur that have increasing heights

select "=== Question 1 ===";
select "REPLACE THIS LINE WITH YOUR SOLUTION";

-- Expected output:
--   abraham|delano|clinton
--   abraham|jackson|clinton
--   abraham|jackson|delano
--   grover|eisenhower|barack
--   jackson|delano|clinton

Your output table should have three columns. You should assume that all dogs have different heights and different names. The rows of the output can appear in any order.

Question 2

(1 point) Write a SQL query that selects the sum of the heights of at least 3 dogs with the same fur, ordered by the total sum. Each dog should be used at most once in a sum.

-- The sum of the heights of at least 3 dogs with the same fur, ordered by sum

select "=== Question 2 ===";
select "REPLACE THIS LINE WITH YOUR SOLUTION";

-- Expected output:
--   long|115
--   short|115
--   long|116
--   long|119
--   long|136
--   long|162

You should assume that all dogs have different heights and different names. Rows with the same total sum may appear in any order.

Hint: This question is similar to Homework 10 Question 4.

Hint: A sum of 162 can be reached by adding the heights of abraham, clinton, delano, and jackson.

Question 3

(1 point) Recall the sequence from Homework 3:

g(n) = n,                                      if n <= 3
g(n) = g(n - 1) + 2 * g(n - 2) + 3 * g(n - 3), if n > 3

Write a query that lists out the first 20 terms of g in order. Use a recursive table; queries that explicitly list out terms of g other than 1, 2, and 3 will be marked as incorrect.

-- The terms of g(n) where g(n) = g(n-1) + 2*g(n-2) + 3*g(n-3) and g(n) = n if n <= 3

select "=== Question 3 ===";
select "REPLACE THIS LINE WITH YOUR SOLUTION";

-- Expected output:
--   1
--   2
--   3
--   10
--   22
--   51
--   125
--   293
--   696
--   1657
--   ...
--   9426875

Hint: This question is similar to the Fibonacci example from the textbook.

Final Note: Please do not add any statements that create output in addition to the expected output.