Lab 8: Exceptions and SQL

Due at 11:59pm on 11/29/2016.

Starter Files

Download lab08.zip. Inside the archive, you will find starter files for the questions in this lab, along with a copy of the OK autograder.

Submission

By the end of this lab, you should have submitted the lab using ok. You may submit more than once before the deadline; only the final submission will be graded.

• To receive credit for this lab, you must complete Questions 1-2 in lab08.py and 3-6 in lab08.sql and submit through OK. Please submit both lab08.py and lab08.sql to OK.
• The zip file does not contain lab08.sql. See Piazza for instructions on how to obtain lab08.sql.

Exceptions

Exceptions allow us to try a chunk of code, and then catch any errors that might come up. If we do catch an exception, we can run an alternative set of instructions. This construct is very useful in many situations.

try:
    <try suite>
except Exception as e:
    <except suite>
else:
    <else suite>
finally:
    <finally suite>

Notice that we can catch the exception as e. This assigns the name e to the exception object. This can be helpful when we want to give extra information on what happened. For example, we can print(e) inside the except clause.

Also, we have an optional else case. The else suite is executed if the try suite finishes without any exceptions.

We also have an optional finally clause, which is always executed, whether or not an exception is thrown. We generally don't need to use the else and finally controls in this class.

When we write exception statements, we generally don't just use the word Exception as above. Rather, we figure out the specific type of exception that we want to handle, such as TypeError or ZeroDivisionError. To figure out which type of exception you are trying to handle, you can type purposely wrong things into the interpreter (such as 'hi' + 5 or 1 / 0) and see what kind of exception Python spits out.

Question 1: No IndexErrors Allowed

You have seen that indexing a list with an index that is not contained in the list generates an IndexError exception. Use exception handling in the function avoid_indexerror to return the value at index and avoid IndexErrors when the index is invalid.

def avoid_indexerror(lst, index, missing=None):
    """Return lst[index] if it exists, otherwise missing.

    >>> avoid_indexerror([1,2,3], 1)
    2
    >>> avoid_indexerror([1,2,3], 4)
    >>> avoid_indexerror([1,2,3], 4, "Index out of range.")
    'Index out of range.'
    """
    "*** YOUR CODE HERE ***"
    try:
        return lst[index]
    except IndexError:
        return missing

Use OK to test your code:

python ok -q avoid_indexerror --local

Question 2: No KeyErrors Allowed

If we try to look up a key that does not exist in a dictionary, then Python will raise a KeyError. Write the function avoid_keyerror which returns returns the value mapped to key in the dictionary. If key does not exist, print 'Avoid Exception' and map key to the string 'no value'.

def avoid_keyerror(dictionary, key):
    """ Returns the value associated with key in dictionary. If key 
    does not exist in the dictionary, print out 'Avoid Exception' and
    map it to the string 'no value'.

    >>> d = {1: 'one', 3: 'three', 5: 'five'}
    >>> avoid_keyerror(d, 3)
    'three'
    >>> avoid_keyerror(d, 4)
    Avoid Exception
    >>> d[4]
    'no value'
    """
    "*** YOUR CODE HERE ***"
    try:
        return dictionary[key]
    except KeyError as e:
        print("Avoid Exception")
        dictionary[key] = 'no value'

Use OK to test your code:

python ok -q avoid_keyerror --local

SQL

The zip file you downloaded does not contain lab08.sql. See Piazza for instructions on how to obtain lab08.sql.

Usage

After writing your code in the .sql file, you can test and verify your output on the terminal with one of the two following commands.

1.) Runs your code and then exits SQLite immediately afterwards.

    sqlite3 < lab08.sql

2.) Runs your code and keeps SQLite open for further commands, which is similar to running Python code with the interactive -i flag. You can type .help to see some of the commands you can run.

    sqlite3 --init lab08.sql

To exit out of SQLite after using the second command, you can hit Ctrl-D type .exit or .quit.

SQL Basics

Creating Tables

Two ways of creating tables in SQL are from scratch or from existing tables.

Below creates the table from scratch, without referencing any other existing tables.

CREATE TABLE [table_name] as
  SELECT [val1] as [column1], [val2] as [column2], ... UNION
  SELECT [val3]             , [val4]             , ... UNION
  SELECT [val5]             , [val6]             , ...;

Note: You do not need to repeat the as keyword in subsequent SELECT statements when creating the table.

Here is an example where we construct a table with the CREATE TABLE statement:

CREATE TABLE Football as
  SELECT 30 as Berkeley, 7 as Stanford, 2002 as Year UNION
  SELECT 28,             16,            2003         UNION
  SELECT 17,             38,            2014;

Here we have created a table called Football, which has three attributes (columns): Berkeley, Stanford, and Year. We can later access the values from this table by referencing the table's columns.

To create tables from existing tables, the SELECT command references another table.

Selecting From Tables

More commonly, we will create new tables by selecting specific columns that we want from existing tables. SELECT statements can include optional clauses such as:

• FROM: tells SQL which tables to select values from
• WHERE: filters by some condition
• ORDER BY: enforces an ordering by some attribute or attributes (usually a column or columns)

• LIMIT: limits the number of rows in the output table

  SELECT [columns] FROM [tables] WHERE [condition] ORDER BY [attributes] LIMIT [limit]

Notes about the arguments:

• [columns]: a comma separated list of the columns to select, * can be used to select all of them
• [tables]: a comma separated list of tables to select values from
• [condition]: a Boolean expression
• [attributes]: a comma separated list of attributes, which are usually columns, but could also be named aggregates (which we will learn later)
• [limit]: an integer

We can select all the values of an attribute from a table with the SELECT statement. In addition, we can apply a filter using the WHERE clause. Here, we filter by Year > 2002, which makes the SELECT statement keep only the rows in the table whose Year value is greater than 2002.

sqlite> SELECT Berkeley FROM Football WHERE Year > 2002;
17
28

Here we selected Berkeley's score for all years after 2002.

Expressions in SQL

Here are some fundamental operations you can perform:

• comparisons: =, >, <, <=, >=, <> ("not equal")
• booleans: and, or
• arithmatic: +, -, *, /

We can also perform string concatenation using ||, which behaves similarly to + on strings in Python.

sqlite> select "hello" || " " || "world"
hello world

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.

Get started with sqlite

Following the instructions in the usage section above, start up sqlite3 initializing it with lab08.sql. Try out .help and then .tables and .schema parents. These . commands are particular to .sqlite; they are not part of SQL. Try out some SQL statements.

How do you get the whole parents table? Give it a try.

select * from parents;

Looking at the data, which dogs are children of "abraham" ?

"barack" and "clinton"

Write a SQL statement to get the children of the dog "abraham".

select child from parents where parent = "abraham";

Write a SQL statement to select the parents of curly-furred dogs.

select parent from parents, dogs
   where child = name and fur = "curly";

Try it out. Did you get:

eisenhower
delano

For the following questions, update lab08.sql with your solution.

Question 3

-- All short dogs
create table short_dogs as
-- REPLACE THIS LINE
  select name, fur, height as size from dogs
    where height < 40;

-- Example:
select name from short_dogs;
-- Expected output:
--   abraham
--   eisenhower
--   fillmore
--   grover
--   herbert

Test your solution with OK:

python ok -q short --local

Question 4

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
-- REPLACE THIS LINE
  select name, size from dogs, sizes
    where height > min and height <= max;

-- Example:
select name from size_of_dogs where size="toy" or size="mini";
-- Expected output:
--   abraham
--   eisenhower
--   fillmore
--   grover
--   herbert

Test your solution with OK:

python ok -q small --local

Question 5

-- All dogs with parents ordered by decreasing height of their parent
create table by_height as
-- REPLACE THIS LINE
  select child from parents, dogs where name = parent order by -height;

For example, 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, barack and clinton should both appear at the end, but either one can come before the other.

-- Example:
select * from by_height;
-- Expected output:
--   herbert
--   fillmore
--   abraham
--   delano
--   grover
--   barack
--   clinton

Test your solution with OK:

python ok -q parent_height --local

Question 6

Create a table tallest that includes the height and name of every dog that shares the 10's digit of its height with at least one other dog and has the highest 1's digit of all dogs that have the same 10's digit.

-- Height and name of every dog that shares height 10's digit  
-- with at least one other dog and has the highest 1's digit of all dogs 
-- that have the same 10's digit
create table tallest as
-- REPLACE THIS LINE
  select max(height), name from dogs group by height/10 having count(*) > 1;

-- Example:
select * from tallest;
-- Expected output:
--   28|grover
--   35|eisenhower
--   47|clinton

Explanation: grover is the tallest 20-something dog. eisenhower is the tallest 30-something dog. clinton is the tallest 40-something dog. barack is not included because there are no other 50-something dogs.

Test your solution with OK:

python ok -q tallest --local