Homeworks

HW1: Email me your name, major (MS/MA?), and 1 sentence telling me how comfortable you are with SQL. Please include "CIS717.2" in email subject.

HW2: Install OracleXE. I suggest you set it up under its own "OS" (by first installing VirtualBox, or VMware, etc.).

As a simple review of SQL, do `Sample Questions' at the end of: sql2.pdf; For the same database, also answer the following questions:

1) Find the company with most employees.

2) Find companies with above average salaries.

3) Find the company with most non-managing employees.

4) Find the company with highest average difference between manager salary and non-manager employee salary.

Write answers in an email; put "CIS717 HW2" in email subject.

HW# 3 (this is more of a `project'; lets see whether anyone finishes this by next week): Load the ``Stock Ordrs'' data (ie: stockdata.zip) file into a database of your choice. Oracle recommended. There are two files in the data, "ordr", and "cxl", which represent stock orders and stock order cancelations. The column details for these are in the file. The file also has an `explanation.txt' file; which has a short example of how to pick out trades/quotes from the data.

Using select SQL query (no PL/SQL or T-SQL), create a trades table that represents trades between incoming orders. If the orders really came in at the times they came in, what would be the trading activity?

Obviously you'd do: create table trades as select ... from ...etc. and something similar for quotes.

Trade table columns: tdate symbol tim seq tid oid price qty, where tid is a unique trade id, oid is order id, seq is the sequence of the incoming order that's causing the trade, price is the price at which the trade took place (price of quote, usually), qty is the traded quantity for oid.

Similarly, create a quotes table, that represents a bid, bifqty, ofr, ofrqty (ie: bid and offer) at any given time (after a trade, or order arrival/cancelation).

Feel free to use analytical functions. Google for "oracle analytical functions".

I have an idea of how to attack this problem---so there's a chance it is possible to do using SQL.

If for some reason you are unable to do this homework, write a 2 page paper explaining exactly what difficulty is preventing you from doing it. The explanation must clearly explain the problem in first paragraph (so we can maybe resolve it), and why none of the common SQL solutions resolve it. Email me if you're planning this route, maybe I can provide some hints.

Submit the SQL queries, and the first 1000 records for trades and quotes tables (note that you can compare the results, everyone's output should be exactly the same).

For the few adventurous students, you can modify the gendata.pl file (in stockdata.zip), to increase volume (set it to ~1000000; and set number of symbols to ~3000). That will generate relatively realistic data sizes---run your query on that data set.

HW# 4: Write a stored procedure, in PL/SQL or T-SQL, that accepts an argument N, and populates table N with the first N prime numbers. Prime numbers are integers that are 2 or greater, divisible only by themselves and 1. The N table schema is: create table N (N int).

Test your algorithm on numbers going upto 1000000 (anyone can write a stupid prime number check---try to write an efficient one).

HW# 5: You have a representation of a 3D world in a database, with a billion or more objects. For every object you have:
thing(thingid, name, x,y,z, radius)
Write a SQL query to find all intersecting objects. Assuming that objects can't intersect with everything at the same time, setup your query such that finding intersections doesn't take O(N^2) time. Things you can assume: you have access to 100000 servers (you need to pick the distribution method to use).