SQL Practice Queries Answers
Beginner Queries:
1.
SELECT *
FROM candidate
WHERE religion = 'Christian'
2.
SELECT *
FROM additional_candidate
WHERE additional_id = '47'
3.
SELECT tag_id
FROM tag
WHERE name LIKE 'Affordable-Care-Act'
4.
SELECT nickname, capital, motto, bird, flower
FROM state
WHERE state_id = 'NH'
5.
SELECT sig_id, address, url
FROM sig
WHERE name = 'National Rifle Association'
6.
SELECT candidate_id, firstname, lastname, state_id
FROM candidate
WHERE candidatestatus_id = '3'
7.
SELECT *
FROM candidate
JOIN education USING (candidate_id)
WHERE school = ''
8.
SELECT candidate_id, firstname, lastname, degree
FROM candidate
JOIN education USING (candidate_id)
WHERE degree LIKE '%.%'
9.
SELECT COUNT (candidate_id)
FROM election_candidate
JOIN election e USING (election_id)
WHERE electionyear = '2016' AND e.state_id = 'NH'
10.
SELECT o.office_id, officetype_id, name, oc.state_id
FROM office o
JOIN office_candidate oc USING (office_id)
WHERE officetype_id = 'S' AND state_id = 'PA' AND officecandidatestatus_id = '1'
GROUP BY office_id, oc.state_id
11.
SELECT COUNT (speech_id)
FROM speech
JOIN speech_tag USING (speech_id)
JOIN tag t USING (tag_id)
WHERE t.name = 'Affordable-Care-Act'
Intermediate Queries:
1.
SELECT speech_id, title, speechdate, location, speechtext
FROM speech
JOIN speech_candidate USING (speech_id)
WHERE candidate_id = '53279' AND speechtype_id = '7'
2.
SELECT candidate_id, firstname, 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.committee_id = '11541' AND officecandidatestatus_id = '1'
3.
SELECT COUNT (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.committee_id = '11541' AND officecandidatestatus_id = '1'
4.
SELECT candidate_id, firstname, lastname, com.name
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' AND officecandidatestatus_id = '1'
ORDER BY com.name
5.
SELECT candidate_id, firstname, lastname, l.name
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN leadership_candidate lc USING (office_candidate_id)
JOIN leadership l USING (leadership_id)
WHERE officecandidatestatus_id = '1' AND oc.state_id = 'DE' AND office_id IN (7,8,9)
6.
SELECT candidate_id, firstname, lastname, ecs.name
FROM candidate c
JOIN election_candidate ec USING (candidate_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
JOIN election e ON ec.election_id = e.election_id
JOIN electioncandidatestatus ecs USING (electioncandidatestatus_id)
WHERE e.state_id = 'IL' AND electionyear = '2010' AND office_id = '8'
GROUP BY candidate_id, ecs.name
ORDER BY ecs.name DESC
7.
For an in-depth answer to this question, you can find it described here
