Data for Journalists Chapter Six

Chapter Six

Database Managers, Part 1: Searching and Summarizing

Here are exercises for this chapter and links to resources for using database managers to search and summarize data. 

Also, here are links to some of the datasets. The tables (files) are Candidates and Givers from U.S. campaign finance data.

You will want to download the software DB Brower for SQLite on https://sqlitebrowser.org/ choosing either the Windows, Mac or Linux version.

You can import the candidate and giver files into DB Browser or open the two files in the SQL file.

To import a CSV file into DB Browser, first create a new database. Click “New Database” and give it a name, then choose “Cancel”  for the “Edit table definition” prompt. Now you have a database ready to import the CSV data. To do so, click on the “File” menu in the top left corner, highlight “Import” and choose the “Table from CSV file” option.

Alternatively, to create a database from an SQL file, click on the “File” menu in the top left corner, highlight “Import” and choose the “Database from SQL file” option.

Also included is a dataset of gun dealers in Missouri.

  • Exercises

Suggested exercises include:

1. Download and import a database of registered gun dealers from the U.S. state from the Bureau  of Tobacco, Alcohol, and Firearms from the bureau’s website.

https://www.atf.gov/firearms/updated-atf-federal-firearms-licensee-ffl-listings-page

2. Do a query in the data search for city.

3. Do a query in which you group and count the gun dealers in each city in the state. Sort the records from the highest to lowest number of gun dealers in each city.

4. Do a query in which you  group and count gun dealers by ZIP code in the state. Sort from highest to lowest.

  • Data

CSV file for Chapter 6 data: Candidates

CSV file for Chapter 6 data: Givers

SQL file for Chapter 6 data: Political Contributions

Alternative Downloads:

CSV file for Chapter 6 data: Candidates (Alternative Download)

CSV file for Chapter 6 data: Givers (Alternative Download)

SQL file for Chapter 6 data: Political Contributions (Alternative Download)

© Brant Houston