main
February 23rd, 2024    

CISC 7512X
Main
Files
Syllabus
Links
Homeworks

Notes
0001

DB2
Intro
SQL Intro
More SQL
Oracle Primer
MySQL Primer
PostgreSQL Primer
AnalyticFuncs
Indexes/Joins
DB Design
Normal Forms
Data Loads


Past Tests
F2023 Final(key)
F2023 Midterm
S2023 Final(key)
S2023 Midterm(key)
F2022 Final(key)
F2022 Midterm(key)
S2020 Final(key)
F2021 Midterm
F2021 Final
S2022 Midterm(key)
S2022 Final(key)
S2020 Final(key)
S2021 Midterm
S2021 Final(key)
F2020 Midterm (key)
F2020 Final Exam (key)
S2019 Midterm(key) S2019 Midterm2(key)
F2019 Midterm(key)
S2018 Midterm(key)
S2018 Midterm2(key)
S2018 Final(key)
S2018 Final2(key)
S2017 Midterm
F2017 Midterm(key)
F2017 Final(key)
S2017 Midterm(key)
S2017 Midterm2(key)
S2017 Final(key)
S2017 Final2(key)
F2015 Midterm
S2014 Midterm
S2014 Final
F2016 Final


Sample Data
ctsdata.20140211.tar
Stock Ordrs


SQLRunner

CISC 7512X (DB2) Homeworks

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

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


CISC 7512X HW# 2 (due by 3rd class;): For the below `bank' schema:
customer(customerid,username,fname,lname,street1,street2,city,state,zip)
account(accountid,customerid,description,)
transaction(transactionid,trantimestamp,accountid,amount)

A customer may have several accounts, and each account may participate in many transactions. Each transaction will have at least two records, one deducting amount from an account, and one adding amount to an account (for a single transactionid, the sum of amounts will equal zero).
Using SQL, answer these questions (write a SQL query that answers these questions):

  1. What is the balance of accountid=42?
  2. What was the transaction amount of transactionid=42?
  3. Which transactionids do not sum up to zero (are invalid)?
  4. List of customers without accounts?
  5. What is the balance (total across all accounts) for customerid=42?
  6. What is the total balance of all customers living in zip code 10001?
  7. Which zip code has the highest balance?
  8. List the top 1% of customers (ordered by total balance).
  9. Using balances for previous two months, predict what the balances will be next month. (tip: find slope of a line; x-axis is days, y-axis is balance. 2 previous months means you have 2 points, finding slope is easy. Use slope to predict where next month's balance will be.)
  10. List top 10 fastest growing accounts (using previous 2 months). (tip: same as above, fastest growing means steepest slope).
  11. For each account, what was the closing balance on December 31, 2023?
  12. What percentage of bank's money is held by people in the tri-state area today? (NY, NJ, CT)
  13. Write a query to add 0.01% to each savings account (note that the money has to be accounted for).
  14. Find all accounts with 30-day moving average balance less than $1500.
  15. Find all accounts with less than 2 transactions in the previous 30-days.
  16. Find all accounts with negative balances for the entire previous 30-days.
  17. Find all accounts that meet all conditions from previous 3 bullet points.
  18. Find customers who move money between their accounts too frequencly (e.g. transaction count among accounts that they own is above 99-th percentile of all customers in the bank).
  19. We (the bank) define a round-trip as a transaction from account A to account B, that is followed by a transaction from account B back to account A within 30 days, for an amount that is within 10% of the original amount. Find all round-trip transactions in the last year. Make sure not to count transactions twice. [e.g. A-to-B, and then B-to-A, followed by another B-to-A, etc., shouldn't count it as two round-trips, since the 2nd B-to-A isn't paired up with its own A-to-B].
  20. Generate a list of customers who engaged in at least 10 round-trips in the last year.
  21. We (the bank) define a loop as any set of transactions, less than 4, that create a loop, e.g.: A-to-B, and B-to-C and C-to-A... within 30 days, and within 10% of amount, etc., This is just a bigger version of a round-trip, involving more than 2 participants. Find all loops in the last year. Again, do not overcount.

CISC 7512X HW# 3 (due by Nth class): Your buddy stops over for lunch and tells you about this wonderful idea of building apps for phones (for profit!). The gist of the idea: ride sharing! (``Urgh, not again!'', you think). Unlike other ride-sharing ideas, this app is designed for the usual commuter who uses the car to get to work---and is willing to share the ride with someone else to lower their costs. Going out of the way to pickup folks is out of the question (the driver also needs to get to work themselves). Also, the driver prefers the fastest possible route (highways, etc.,) even if it means not picking up someone. Since everyone (including the driver) are benefitting from the ride, the goal is to lower the commute cost for everyone (including driver and passenger [passenger would use their own car if it costs them less]). The business takes a small slice of the money saved (so it's a win-win for everyone involved). Also, folks will be able to pay for the ride in bitcoins. This all seems like crazy talk until your buddy mentions there's a potential $10m investment (from the same folks who seeded Uber), and all they need from you is a working prototype and a write-up of the architecture by next week.

Your task: Design and build a database to run this business. What tables would you need? What events would you capture? Etc. Write up what interface and functionality would be needed to interact with the database. Make the investors see that this is a real viable idea that will actually work. Produce a business plan, design document, whitepaper, architecture, prototype, etc., whatever it takes to get that investment.






































© 2006, Particle