Assignments for April 29

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.

Reading: This story on nursing home spending in California. Check out the explainer and related graphics, too. Then let’s talk about it in class.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s