Programming Assignment 1
Brandeis University, COSI 127b, Spring 2019
Instructor: Ryan Marcus
For this assignment, you¡¯ll be writing SQL statements against a schema containing information about hardware stores. We will provide a script that checks your answers.
To begin, download the database and the test script (they should both be in a folder called dist). For reference, here is the schema of the database:
CREATE TABLE customer (
id INTEGER PRIMARY KEY,
customer_name TEXT,
contact_number TEXT
);
CREATE TABLE supplier (
id INTEGER PRIMARY KEY,
supplier_name TEXT UNIQUE, contact_number TEXT
);
CREATE TABLE product (
id INTEGER PRIMARY KEY,
supplier_id INTEGER REFERENCES supplier(id), product_name TEXT,
product_price INTEGER,
UNIQUE(supplier_id, product_name)
);
CREATE TABLE purchase (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customer(id), purchase_date REAL,
store_id INTEGER REFERENCES store(id)
);
CREATE TABLE store (
id INTEGER PRIMARY KEY,
store_name TEXT,
store_address TEXT
);
CREATE TABLE purchase_product (
1
);
purchase_id INTEGER REFERENCES purchase(id), product_id INTEGER REFERENCES product(id), quantity INTEGER
The database in the dist folder is a SQLite database. If you use a *NIX (UNIX or Linux) platform (like MacOS or Ubuntu), you should be able to cd into the dist directory and enter the SQLite prompt like this:
$ sqlite3 pa1.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter “.help” for usage hints.
sqlite>
If you are on another platform (like Windows), you can either use one of the Mac computers in Vertica, SSH to one of the CS machines (e.g., diadem or tiara), or attempt to make things work in Windows (Ryan and the TAs can help you with all of these options but the last).
You can enter queries (terminated by a semicolon) and see the results, or you can type special SQLite commands (that begin with a period). For example, to see all the tables in the database, type .tables.
To practice, enter this query into the prompt and ensure you get the correct result:
sqlite> SELECT * FROM customer ORDER BY id LIMIT 10;
1|Travis Matthews|999.607.0279
2|Leon Kirby|951.476.9035
3|Jessenia Bruce|336.918.2698
4|Jarvis Compton|137-942-2079
5|Aleen Trevino|1-219-541-7474
6|Gary Burton|1-501-401-1323
7|Reynaldo Fernandez|086.908.9278
8|Perry Carney|(591) 264-5446
9|Pearly William|(456) 054-1783
10|Jere Foley|+1-(307)-819-7679
You should write your answer to each question in its own file, named after the question in the pattern q{question_number}.sql. For example, your answer to question 7 should go in a file called q7.sql. These SQL files must be placed in the dist directory.
You can then run grade.py script, which will print out whether or not each of your queries is generating the right answer. Keep in mind the script cares about the order of both the rows and columns of your answer, so read each problem carefully.
2
Questions
Use distinct when it semantically makes sense. If not specified, ordering should be ascending.
1. Find the contact number for customer Krysten Wallace
2. How many different types of screws does Metzger¡¯s sell (you can check to see if the product name starts with ¡°Screw¡±)?
3. When was customer Lezlie Clements¡¯s most recent purchase (as a Julian day)?
4. List the names of all the products purchased by customer Reid Mathews and their corrosponding supplier name and supplier contact numbers, ordered by product name.
5. List the names and contact numbers of all customers who purchased a product from supplier MagnaSolution, ordered by customer name.
6. List the number of products that are supplied by at least two different suppliers. You may not use a GROUP BY clause.
7. List the top 10 product names by sold quantity, across all suppliers and all purchases, along with the quantity sold. Sort the result by the quantity with the highest quantity first.
8. List the top 10 product names by gross income (quantity * price), across all suppliers and all purchases, along with the gross income. Sort the result by the gross income with the highest income first.
9. List all ladders with multiple suppliers, along with the supplier that offers the lowest price for the given product.
10. List the names of all customers who have purchased a product that could have been purchased from a supplier with a lower price by at least 5 dollars
(500 cents), ordered by customer_name
11. List all customers who have never made a purchase at the Diamond Drive store.
12. List the names of all customers who purchased Tool 1717 and then later purchased O-Ring 1736, ordered by customer name.
13. List the name and number of purchases made at each store, sorted by store name.
14. List the name and total number of (non-distinct) items purchased at each store, sorted by store name.
15. List the name and total number of (non-distinct) items purchased at each store, sorted by store name, after or on 2018-11-30.
3
16. List the name and total number of (non-distinct) items purchased at each store, sorted by store name, for the months of October (before 2018-11-01), November (between 2018-11-01 and 2018-12-01), and December (after 2018-12-01). Your output should look like this, with ??? replaced with correct values:
Los Alamos DD|October|???
Los Alamos DD|November|???
Los Alamos DD|December|???
Los Alamos TD|October|???
Los Alamos TD|November|???
Los Alamos TD|December|???
White Rock|October|???
White Rock|November|???
White Rock|December|???
17. List each customer and the store where they have made the most purchases, sorted by customer name.
Submission & Grading
To submit your work, create a .zip or .tar.gz of your dist folder, and upload it to LATTE.
Your grade for this assignment will be almost entirely determined by the grading script. Additionally, we will check:
1. that your work is unique to you, i.e. that you did not copy,
2. that you did not use any features that were forbidden for a particular
question, and
3. that you did not ¡°special case¡± any of the queries, i.e. that you wrote
your queries against the schema and not against the data. Only one query requires the use of CASE.
4