===**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 [[Practice7 here]]