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.
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:
To complete this quiz, you will need to use Sqlite version 3.8.3 or greater. You can
sqlite3
from an instructional machine using your course accountAfter installing Sqlite, you can run the starter file using the command:
sqlite3 -init quiz3.sql
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
.
(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.
(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.
(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.