SQLite: Create a database in SQLite Manager and save it to your desktop. Call it spending.
Download and combine the expenditures (Schedule B data) from First Quarter 2010 filings into a single CSV file with headers, then import that file into SQLite. Use the filings for these committees:
- C00464339 – Sean Duffy
- C00017830 – Dave Obey
- C00445999 – Dan Mielke
Remember to set short, meaningful headers without spaces, and on import to set number fields as REAL (if the field contains decimals) or INTEGER (if it contains whole numbers).
Write queries to answer the following questions:
1. How many total records? How many expenditures of more than $5,000?
2. How many records list a purpose that includes fundraising?
3. How many records list an address in DC, MD or VA? (This requires using a WHERE clause that includes OR, such as WHERE name = ‘this’ or name = ‘that’)
Please send your queries and your spending.sqlite file as an email attachment (not the original CSV), and be sure to have that file with you in class next week. If you need a refresher on SQL syntax, SQLZoo is good, as is this tutorial.