Revision history for PracticeQueriesAnswers
Additions:
7.
For an in-depth answer to this question, you can find it described [[Practice7 here]]
For an in-depth answer to this question, you can find it described [[Practice7 here]]
Additions:
**Intermediate Queries:**
1.
SELECT speech_id, title, speechdate, location, speechtext
JOIN speech_candidate USING (speech_id)
WHERE candidate_id = '53279' AND speechtype_id = '7'
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'
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'
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)
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
1.
SELECT speech_id, title, speechdate, location, speechtext
JOIN speech_candidate USING (speech_id)
WHERE candidate_id = '53279' AND speechtype_id = '7'
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'
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'
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)
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
