MPJO-722-01: Data Reporting
Links
Assignment due March 8
Story Memo Idea: Send me some ideas, topics and/or questions you might like to answer using data.
Assignment due March 15 - Midterm
Midterm: The midterm consists of an Excel assignment and a SQLite assignment.
Excel: Download this CSV of Census data from U.S. congressional districts and open it in Excel. Each of you has been assigned a state to focus on. Using a filter, isolate the rows for your state, and then do the following:
- Create a column to calculate each district's percentage of the total population that the 20-24 year-old age range represents. Which district has the highest percentage of 20-24 year olds?
- Do the same thing for Veteran Status:Civilian Veterans. Which district has the lowest percentage of civilian veterans?
- Do the same for Male & Female. Which district has the largest percentage gap between men and women?
- Create a row for your state and calculate the average number of people with a bachelor's degree for the state as a whole, and then add a column to calculate the difference between each district and the average.
SQL: Continuing with the senate voting database, write SQL to answer the following questions. You will need to study the fields in your tables. Send me both your queries and your sqlite data file.
- How many votes were decided by less than 10 votes?
- Show each vote result and how many times it occurred.
- How many votes have all Republicans voting "No" and all Democrats voting "Yes"? (hint: all "No" votes means how many "Yes" votes?)
- How many votes on cloture motions were agreed to?
- What is the average number of Republican No votes on a vote where the question is On Passage of the Bill?
- Which senator has missed the most votes (the position is "Not Voting")? (you'll need a join for this one)
Email me the excel file with your "answers" in bold, the votes.sqlite file as an attachment (don't export the data) and the SQL from your queries by 11:59 p.m. on March 15.