main
November 20th, 2017    

CISC 7510X
Main
Files
Syllabus
Links
Homeworks

Notes
0001

DB1
Intro
SQL Intro
More SQL
Oracle Primer
MySQL Primer
PostgreSQL Primer
Data Loads
DB Design
DB Procs
Indexes/Joins
AnalyticFuncs
Hierarchical
Security

Past Tests
Sample Midterm Midterm Midterm anskey
ctsdata.20140211.tar
Stock Ordrs

SQLRunner

CISC 7510X (DB1) Homeworks

You should EMAIL me homeworks, alex at theparticle dot com. Start email subject with "CISC 7510X HW#". Homeworks without the subject line risk being deleted and not counted.

CISC 7510X HW# 1 (due by 2nd class;): Email me your name, prefered email address, IM account (if any), major, and year.


CISC 7510X HW# 2 (due by 3rd class;): For the below `store' schema:
product(productid,description,)
customer(customerid,username,fname,lname,street1,street2,city,state,zip)
purchase(purchaseid,purchasetimestamp,customerid,productid,quantity,price)

Using SQL, answer these questions (write a SQL query that answers these questions):

  1. What is the description of productid=42?
  2. What's the name and address of customerid=42?
  3. What products did customerid=42 purchase?
  4. List customer names who have never puchased anything.
  5. List product descriptions who have never been purcahsed by anyone.
  6. What products were purchased by customers with zip code 10001?
  7. What percentage of customers have ever purchased productid=42?
  8. Of customers who purchased productid=42, what percentage also purchased productid=24?
  9. What is the most popular (purchased most often) product in NY state?
  10. What is the most popular (purchased most often) product in North East?

CISC 7510X HW# 3 (due by 4th class;): Install PostgreSQL.
For the below schema for a company door:
doorlog(eventid,doorid,tim,username,event)

Where doorid represents the door for this event. e.g. Front door may be doorid=1, and bathroom may be doorid=2, etc. tim is timestamp, username is the user who is opening or closing the door. event is "E" for entry, and "X" for exit.

Using SQL, answer these questions (write a SQL query that answers these questions):

  1. How many users entered through doorid=1
  2. If doorid=2 is bathroom, how many people are currently in the bathroom?
  3. If doorid=1 is front entrance door, and doorid=3 is back entrance door, and these are the only doors in the building, how many people are currently in the building?
  4. How many people were in the building on July 4th, at 10PM? (watching fireworks)
  5. If doorid=7 is for floor 42, what's the daily occupancy of floor 42 for entire 2017 (give a number for every day in 2017)
  6. What is the daily average (and standard deveation) occupancy of floor 42 for 2017? (single number)
  7. What percentage of the people work on floor 42?
  8. What's the average number of times per day that people use the bathroom? (bathroom is doorid=2).
  9. What percentage of employees stayed after 5:15PM on July 3rd, 2017?
  10. List all employees who left work before 1PM on July 3rd, 2017.

CISC 7510X HW# 4 (due by Nth class;): Write a command line program to "join" .csv files. Use any programming language you're comfortable with. Your program should work similarly to the unix "join" utility (google for it). Unlike the unix join, your program will not require files to be sorted on the key. Your program must also accept the "type" of join to use---merge join, inner loop join, or hash join, etc. Test your program on "large" files (e.g. make sure it doesn't blow up on one million records, etc.)

Submit source code for the program.

Also... load all files in ctsdata.20140211.tar (link on the left) into Oracle or Postgres (or whichever works for you). The format of these files is: cts(date,symbol,open,high,low,close,volume), splits(date,symbol,post,pre), dividend(date,symbol,dividend). Submit (email) whatever commands/files you used to load the data into whatever database you're using, as well as the raw space usage of the tables in your database.


CISC 7510X HW# 5 (due by Nth class): If you haven't done so already, load all files in ctsdata.20140211.tar (link on the left) into Oracle or PostgreSQL (or whichever works for you; postgresql recommended!). The format of these files is: cts(date,symbol,open,high,low,close,volume), splits(date,symbol,post,pre), dividend(date,symbol,dividend). Submit (email) whatever commands/files you used to load the data into whatever database you're using, as well as the raw space usage of the tables in your database. (this was part of previous homework).

After loading the data, create another table with fields: TDATE,SYMBOL,PRCNT which will have the daily percentage gain/loss adjusted for dividends and splits.

Do not write procedural code (Java, C#, C/C++, etc.) for this homework (all code must be SQL, etc.).

HINT: That is, if stock XYZ today (20140211) issued dividend of $0.25, previous day's (20140210) close was $50 and today's (20140211) close is $51, then today's percentage gain is: 1.5%, since 50 + 0.25 + 50*0.015 = 51. So your table will have: 20140211,XYZ,1.5
Loss is just a negative percentage. Similar accounting for splits.

Submit query used to construct the table. We'll do more stuff with this dataset in subsequent homeworks.


CISC 7510X HW# 6 (due by Nth class): Your buddy stops over for lunch and tells you about his wonderful idea of building software for junk yards. Junk yards are places that aquire cheap old cars and sell individual parts---a $1k old junky car may have 100 parts in it that each can be sold for $20-$50, etc. A typical junk yard may have dozens to hundreds of old cars, and if you need a part, you drive by and ask... the attendant would know what car/part you're looking for and would know whether they have anything compatible in the inventory. (e.g. a "left side mirror from a white 2013 Ford Mustang" may be repainted to be compatible with a red 2014 Ford Mustang, etc.).

Now, the attendant would likely know these things (they have enormous domain knowledge). But it's still a major inventory hassle to find compatible parts... Your buddy has an idea of building such an `inventory management system' for junk yards... so anyone can start a junk yard, and junk yards can get much bigger. The idea is that the customer would drive in, type in the car/part they're looking for, and the system would tell them if there's a compatible car/part available (and where it is), or can be made compatible with minor tweaks (such as repainting, etc.). If part is not available locally, the software should be internet enabled to find the compatible parts in other junk yards running the same software. License per junk yard, $20k, with $2k/year maintenance, and your buddy thinks he can immediately sell it to at least 10 junk yards near major city centers, and perhaps a few hundred over the next few years. So now you have a case for a lucrative business... your task is to build it.

Go through the process of designing this inventory system. What are objects? What are events? Create a database schema, etc. How would the search process work? (e.g. go through the motions of: new junky car arrives, how is it inventoried? new customer arrives looking for a part, how does the system find a compatible part? where can humans be eliminated from this process?).

Submit writeup of the design (nothing too complicated, just a 1 page description---something that would convince me that you're the right contractor for this project---that you know what you're doing). Also submit database schema (DDL, create table statements), and query statements/process to find a compatible part.


CISC 7510X HW# 7 (due by Nth class;): Doing something useful with the data from HW5: Background: Pairs trading. Using the percentage returns table you built in HW5: Your task is to identify potential symbol pairs that have HIGH correlation, and are suitable for pairs trading. While everyone agrees that this strategy works, nobody agrees on the best way to identify correlation---especially when considered in relation to the rest of the market.

For this homework, feel free to use whatever you think is appropriate for correlation (if not sure, try Pearson; Take a log of the percentage gain, and apply pearson on top of that. Yes, you can do all this in SQL.).

Submit 10 "best" symbol pairs, each of which trades at least ~$10m a day, suitable for pairs trading in December 2013 (yah, I know it's an old date). Along with the pairs, submit their correlation coefficients for previous year, and the month of December 2013. (assume you were trading $1m worth, and you traded those exact 10 pairs, how much would you have gained/lost during that period?). Also submit the sql code to get those 10 symbols from the dataset.


CISC 7510X HW# 8 (due by Nth class;): Doing something useful with the data from HW5: Background: portfolio theory. The gist is that you can lower risk by investing in things that have LOW correlation. While a single stock will go up and down, the *average* returns from say 20 will be a lot steadier---provided of course that they're not all correlated (don't move in the same direction at the same time). These are the kinds of funds your retirement account is (or should be) invested in.

Build a portfolio of 20 symbols, each of which has daily average volume over $10m (avg taken over last year), has paid dividends every year (no skipping) for the last 10 years, has returned at least a cumulative 2% a year for the last 15 years (including dividends/splits and stock price), and each of which have the lowest correlation with the rest of the symbols in your portfolio.

Calculate the return on those 20 symbols for 2013. Is that better or worse than S&P500 for same period? (You can use "SPY" symbol as stand in for S&P500) How about last 10 years? Last 20?

Submit 20 symbols, along with their aggregate 2013 return, compared to S&P500 return for the same time period. ...and the SQL code.

If you get pairs that have almost -1 correlation, then something is very wrong (you're likely not using profitable stocks; make sure all your stock give a POSITIVE net yearly return of at least 2%---of that set, you shouldn't have any that have -1 correlation).

Note that most folks in the class should end up with more or less the same list (depending on how everyone defines correlation). Feel free to collaborate with classmates---but everyone must submit the homework (no group submissions).

Don't forget: write all code in SQL only (use analytical functions, etc.)... let the database do the data crunching. Breakup large steps into smaller steps using temp tables. Test query on a subset of symbols, etc.

Submit everything in an email; put "CISC 7510X HW8" in email subject.



































© 2006, Particle