Due by 11:59pm on Friday, November 29.

Starter Files

Download lab13.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 with python3 ok --submit. You may submit more than once before the deadline; only the final submission will be graded. Check that you have successfully submitted your code on okpy.org.

THIS LAB IS OPTIONAL!! We encourage you to do it if you have time, but it is not worth any points.

SQLite

Usage

First, check that a file named sqlite_shell.py exists alongside the assignment files. If you don't see it, or if you encounter problems with it, scroll down to the Troubleshooting section to see how to download an official precompiled SQLite binary before proceeding.

You can start an interactive SQLite session in your Terminal or Git Bash with the following command:

python3 sqlite_shell.py

While the interpreter is running, you can type .help to see some of the commands you can run.

To exit out of the SQLite interpreter, type .exit or .quit or press Ctrl-C. Remember that if you see ...> after pressing enter, you probably forgot a ;.

You can also run all the statements in a .sql file by doing the following:

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

   python3 sqlite_shell.py < lab13.sql

2. Runs your code and then opens an interactive SQLite session, which is similar to running Python code with the interactive -i flag.

   python3 sqlite_shell.py --init lab13.sql

Topics

SQL Aggregation

Previously, we have been dealing with queries that process one row at a time. When we join, we make pairwise combinations of all of the rows. When we use WHERE, we filter out certain rows based on the condition. Alternatively, applying an aggregate function such as MAX(column) combines the values in multiple rows.

By default, we combine the values of the entire table. For example, if we wanted to count the number of flights from our flights table, we could use:

sqlite> SELECT COUNT(*) from FLIGHTS;
13

What if we wanted to group together the values in similar rows and perform the aggregation operations within those groups? We use a GROUP BY clause.

Here's another example. For each unique departure, collect all the rows having the same departure airport into a group. Then, select the price column and apply the MIN aggregation to recover the price of the cheapest departure from that group. The end result is a table of departure airports and the cheapest departing flight.

sqlite> SELECT departure, MIN(price) FROM flights GROUP BY departure;
AUH|932
LAS|50
LAX|89
SEA|32
SFO|40
SLC|42

Just like how we can filter out rows with WHERE, we can also filter out groups with HAVING. Typically, a HAVING clause should use an aggregation function. Suppose we want to see all airports with at least two departures:

sqlite> SELECT departure FROM flights GROUP BY departure HAVING COUNT(*) >= 2;
LAX
SFO
SLC

Note that the COUNT(*) aggregate just counts the number of rows in each group. Say we want to count the number of distinct airports instead. Then, we could use the following query:

sqlite> SELECT COUNT(DISTINCT departure) FROM flights;
6

This enumerates all the different departure airports available in our flights table (in this case: SFO, LAX, AUH, SLC, SEA, and LAS).

Questions

Bank of SQL

You've just been put in hired as an intern at the bank of SQL, which is using a SQL table to run all of its operations. As an intern, your job is to write and run various SQL queries to make sure that the bank's normal operations can be performed (unfortunately, the bank of SQL is a bit behind the times and has not automated this process).

Q1: Give Interest

First off, you need to give 2% interest to each account. For example, if an account starts off with $100, it should end up with $100 + 0.02 * $100 = $102. Make sure to modify the accounts table in place.

-------------------------------------------------------------------------
------------------------ Give Interest- ---------------------------------
-------------------------------------------------------------------------

-- replace this line with your solution
update accounts set amount = amount * (1.02);

Use Ok to test your code:

python3 ok -q give-interest

Q2: Split Accounts

Next, your manager tells you that the bank has a new initiative. In this initiative, every account holder will have their account split into two accounts, a savings and a checking account, each with an equal share of the original account's money. For example, if an account has the name "John" and amount 100, the new accounts will have the name "John's Savings Account" and "John's Checking Account", each with amount 50.

Hint: you may need to create a temporary table.

-------------------------------------------------------------------------
------------------------ Split Accounts ---------------------------------
-------------------------------------------------------------------------

-- replace this line with your solution
create table temporary as
    select name || "'s Checking account" as name, amount * 0.5 as amount from accounts union
    select name || "'s Savings account"         , amount * 0.5           from accounts;

delete from accounts;
insert into accounts select * from temporary;

Use Ok to test your code:

python3 ok -q split-accounts

Q3: Whoops!

Unfortunately, the Bank of SQL has lost all its customers' money ¯\_(ツ)_/¯. To make sure that the customers don't ask for their money, you need to make sure that all the records are removed! After running this command, the table should not exist.

-------------------------------------------------------------------------
-------------------------------- Whoops ---------------------------------
-------------------------------------------------------------------------

-- replace this line with your solution
drop table accounts;

Use Ok to test your code:

python3 ok -q whoops

Scheme

Q4: Quine

Note: this question is out of scope, but should be fun!

Write a nonempty scheme list that, when evaluated, evaluates to itself.

Precisely, your task is to produce a quine quine that satisfies (and (not (null? quine)) (list? quine) (equal? quine (eval quine))).

(define quine
    'your-code-here)
    ((lambda (x) `(,x ',x)) '(lambda (x) `(,x ',x))))

Use Ok to test your code:

python3 ok -q quine-scheme

Cyber Monday

Q5: Price Check

After you are full from your Thanksgiving dinner, you realize that you still need to buy gifts for all your loved ones over the holidays. However, you also want to spend as little money as possible (you're not cheap, just looking for a great sale!).

Let's start off by surveying our options. Using the products table, write a query that creates a table average_prices that lists categories and the average price of items in the category (using MSRP as the price).

You should get the following output:

sqlite> SELECT category, ROUND(average_price) FROM average_prices;
computer|109.0
games|350.0
phone|90.0

CREATE TABLE average_prices AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
  SELECT category as category, AVG(msrp) as average_price FROM products GROUP BY category;
  -- alternate solution
  -- SELECT category, SUM(msrp)/COUNT(*) FROM products GROUP BY category;

Use Ok to test your code:

python3 ok -q cyber-monday-part1

Q6: The Price is Right

Now, you want to figure out which stores sell each item in products for the lowest price. Write a SQL query that uses the inventory table to create a table lowest_prices that lists items, the stores that sells that item for the lowest price, and the price that the store sells that item for.

You should expect the following output:

sqlite> SELECT * FROM lowest_prices;
Hallmart|GameStation|298.98
Targive|QBox|390.98
Targive|iBook|110.99
RestBuy|kBook|94.99
Hallmart|qPhone|85.99
Hallmart|rPhone|69.99
RestBuy|uPhone|89.99
RestBuy|wBook|114.29

CREATE TABLE lowest_prices AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
  SELECT * FROM inventory GROUP BY item HAVING MIN(price);

Use Ok to test your code:

python3 ok -q cyber-monday-part2

Q7: Bang for your Buck

You want to make a shopping list by choosing the item that is the best deal possible for every category. For example, for the "phone" category, the uPhone is the best deal because the MSRP price of a uPhone divided by its ratings yields the lowest cost. That means that uPhones cost the lowest money per rating point out of all of the phones.

Write a query to create a table shopping_list that lists the items that you want to buy from each category.

After you've figured out which item you want to buy for each category, add another column that lists the store that sells that item for the lowest price.

You should expect the following output:

sqlite> SELECT * FROM shopping_list;
GameStation|Hallmart
uPhone|RestBuy
wBook|RestBuy

CREATE TABLE shopping_list AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
  SELECT name, store FROM products AS p, lowest_prices AS l
    WHERE l.item = p.name
    GROUP BY category HAVING MIN(MSRP/rating);

Hint: You should use the lowest_prices table you created in the previous question.

Use Ok to test your code:

python3 ok -q cyber-monday-part3

CREATE TABLE what(name) AS (
  SELECT name FROM products GROUP BY category HAVING MIN(MSRP / rating)
);

CREATE TABLE shopping_list AS
  SELECT p.name, l.store FROM what AS p, lowest_prices AS l WHERE p.name = l.item;

Q8: Driving the Cyber Highways

Using the Mb (megabits) column from the stores table, write a query to calculate the total amount of bandwidth needed to get everything in your shopping list.

CREATE TABLE total_bandwidth AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";
  SELECT SUM(s.mbs) FROM stores AS s, shopping_list AS sl WHERE s.store = sl.store;

Hint: You should use the shopping_list table you created in the previous question.

Use Ok to test your code:

python3 ok -q cyber-monday-part4

Troubleshooting/Advanced SQLite

Troubleshooting

Python already comes with a built-in SQLite database engine to process SQL. However, it doesn't come with a "shell" to let you interact with it from the terminal. Because of this, until now, you have been using a simplified SQLite shell written by us. However, you may find the shell is old, buggy, or lacking in features. In that case, you may want to download and use the official SQLite executable.

If running python3 sqlite_shell.py didn't work, you can download a precompiled sqlite directly by following the following instructions and then use sqlite3 and ./sqlite3 instead of python3 sqlite_shell.py based on which is specified for your platform.

    $ sqlite3
    SQLite version 3.8.10.2

$ sudo apt install sqlite3
$ sqlite3 --version
3.8.6 2014-08-15 11:46:33 9491ba7d738528f168657adb43a198238abde19e