Answers to Training Practice Queries


Beginner:

The point of the beginner queries is to familiarize newcomers to some of our many different tables.

Display all of the candidates who are "Christian".
  • Selecting just the candidate_id would satisfy this query, but displaying just a bunch of cells with numbers isn't very meaningful in this situation.
  • "=" really means "=" and capitalization, spelling, and the exact value in the cell matter. That is, this query does not show records for 'Christain', 'christian', 'Catholic', 'Roman Catholic', 'Presbyterian' or other denominations or forms that one would normally expect fall under the umbrella of 'Christianity'.
SELECT candidate_id, firstname, lastname, religion
FROM candidate
WHERE religion = 'Christian' ;

Display all the quotes in our database.
  • This is a very simple query and a fun one into additional bio collection. Play around with the additional_id to see what other fun things bios collects.
SELECT actext
FROM additional_candidate
WHERE additional_id = 47;

Display the tag_id for the Affordable-Care-Act tag.
  • Knowing how to get a tag_id when you only know the tag's name is a good query to know for future referencing.
SELECT tag_id
FROM tag
WHERE name = 'Affordable-Care-Act';

Display the nickname, capital, motto, bird, and flower of New Hampshire.
  • Yes, this data is somewhere on our site!
SELECT nickname, capital, motto, bird, flower
FROM state
WHERE state_id = 'NH';

Display the sig_id, address, and URL of the National Rifle Association.
  • While not many of us work in sigs/ratings, it is still good to know that these tables do exist.
SELECT sig_id, address, url
FROM sig
WHERE name = 'National Rifle Association';

Display all the dead people in Admin; include their candidate_id, first name, last name, and state. Order it by state.
  • While this isn't important information, it's still an entertaining (in a morbid way) query to do.
SELECT candidate_id, firstname, lastname, state_id
FROM candidate
WHERE candidatestatus_id = 3
ORDER BY state_id;

Display all degrees that have periods (B.A., M.S., J.D., etc.) in them, also give the candidate's id number, their first and last name, and what degree they received.
  • This is just in general a good query for Bios QC.
  • Periods in degrees should never be a thing in Admin.
  • The easiest way to do this query is to the left, but it also turns up some other random entries in degree; it's a good project to work on when bios has less things to work on than normal.
SELECT c.candidate_id, c.firstname, c.lastname, e.degree
FROM candidate c
JOIN education e
USING (candidate_id)
WHERE e.degree LIKE '%.%';

Display all active statewide offices in the great commonwealth of Pennsylvania.
  • This just gives people an introduction into the office table.
  • If you were to run this without the officetype_id = 'S', the number would be much higher because this would account for all offices that are active in PA, not just in Statewide.
SELECT o.office_id, o.name
FROM office o
JOIN office_candidate oc
USING (office_id)
WHERE oc.state_id = 'PA' AND o.officetype_id = 'S' AND officecandidatestatus_id = 1;

Display all the speech_ids's from speeches that have been tagged with the Affordable Care Act. Take it a bit further and get the query to get the COUNT of them.
  • For the COUNT, sure you could just go to the bottom of the results and get your number there, but sometimes it's easier to just write into the query that you want the count.
  • This query takes into account the above query about getting the tag_id of the ACA.
SELECT COUNT (s.speech_id)
FROM speech s
JOIN speech_tag st
USING (speech_id)
WHERE st.tag_id = 254;


Intermediate:
Display Joe Biden's Issue Positions.
  • Notice that we want to be sure to get Joe Biden's IPs and not Beau Biden's.
  • There are two ways to approach this query:
    • The first makes use of all relevant tables and a longish list of limiting factors.
SELECT s.speech_id, s.title, s.speechtext
FROM candidate c 
JOIN speech_candidate sc USING (candidate_id) 
JOIN speech s USING (speech_id) 
JOIN speechtype st USING (speechtype_id)
WHERE c.lastname = 'Biden' AND c.firstname = 'Joseph' AND c.nickname = 'Joe' AND st.name = 'Issue Position' ;

    • The second decreases the length and complexity of our query by reducing limiting factors to simpler but still specific expressions.
    • This decreases the need for additional tables.
SELECT s.speech_id, s.title, s.speechtext
FROM speech s 
JOIN speech_candidate sc USING (speech_id)
WHERE sc.candidate_id = '53279' AND s.speechtype_id = '7' ;


Display all the members of West Virginia's House Rules Committee.
This query could have been shortened and more simple by using the same principles behind the above query. Sometimes this requires a couple of small prep-queries.
SELECT c.candidate_id, c.firstname, c.lastname
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN committee_candidate cc USING (office_candidate_id)
JOIN committee com USING (committee_id)
WHERE com.state_id = 'WV' AND com.committeetype_id = 'H' AND com.name = 'Rules' AND oc.officecandidatestatus_id = 1 ;


Display the number of candidates who are in West Virginia's House Rules Committee.
Notice the aggregate function "COUNT."
There are a number of aggregate functions as well as basic mathematical operators available to us in SQL.
SELECT COUNT (c.candidate_id)
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN committee_candidate cc USING (office_candidate_id)
JOIN committee com USING (committee_id)
WHERE com.state_id = 'WV' AND com.committeetype_id = 'H' AND com.name = 'Rules' AND oc.officecandidatestatus_id = 1 ;


Display all active members of state legislative leadership (Minority Whip, Majority Leader, Speaker, etc.) in Delaware.
Not sure if anybody would ever call about this, but if you're ever curious about it... now you know how to do it.
SELECT l.name, oc.candidate_id, oc.office_id
FROM leadership l
JOIN leadership_candidate lc
USING (leadership_id)
JOIN office_candidate oc
USING (office_candidate_id)
JOIN officecandidatestatus ocs
USING (officecandidatestatus_id)
WHERE oc.state_id = 'DE' AND ocs.officecandidatestatus_id = 1 AND oc.office_id IN (8, 9)
ORDER BY oc.office_id, l.name, oc.candidate_id;



Display all of the members of New York Senate's committees in order of committee name.
Notice the ORDER BY clause.
SELECT  c.candidate_id, c.firstname, c.lastname, com.name, com.state_id
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN committee_candidate cc USING (office_candidate_id)
JOIN committee com USING (committee_id)
WHERE com.state_id = 'NY' AND com.committeetype_id = 'S'
ORDER BY com.name



Find all candidates who ran for IL State Senate in 2010; include whether or not they won; list those who won first.
SELECT c.candidate_id, c.firstname, c.lastname, o.name, oc.state_id, e.electionyear, ecs.name
FROM candidate c
JOIN office_candidate oc ON (oc.candidate_id = c.candidate_id)
JOIN office o ON (o.office_id = oc.office_id)
JOIN election_candidate ec ON (ec.candidate_id = c.candidate_id)
JOIN election e ON (e.election_id = ec.election_id)
JOIN electionstage_candidate esc ON (esc.election_candidate_id = ec.election_candidate_id)
JOIN electioncandidatestatus ecs ON (ecs.electioncandidatestatus_id = esc.electioncandidatestatus_id)
WHERE e.electionyear = 2010 AND ec.state_id = 'IL' AND o.name = 'State Senate'
GROUP BY c.candidate_id, c.firstname, c.lastname, o.name, oc.state_id, e.electionyear, ecs.name
ORDER BY ecs.name DESC ;



From an actual research query: I'm preparing a chart for Bloomberg Businessweek on the incoming 113th Congress and I would love to get some data from you. Specifically - for each member of the incoming congress (when available):
Help for Practice Query 7


CategoryTechnicalSkills
CategoryResearch
There are no comments on this page.
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki