Classnotes

Database Languages

eXercise #10

---

The Election Database

The Questions that follow relate to a database that contains partial records for election results. The Database is as follows:

You must assume that all the votes have been counted for the 1990 election.

Table: Candidates

Year	Candidate	Electorate	Party	NumVotes
KEY	KEY	ma	ma	op
1987	Bill	Sommerset	Independant	12455
1990	Frank	Gold Coast	ALP	33841
1990	John	Gold Coast	Lib	28459
1990	Sam	Gold Coast	Nat	15050
:	:	:	:	:

Table: Electorates

Year	Electorate	NumVoters	MemberSitting	InformalVotes
KEY	KEY	ma	op	op
1987	Gold Coast	107408	Frank	2335
1990	Gold Coast	113250		3071
1990	Sommerset	111643	Terry	1905
:	:	:	:	:

Table: States

Electorate	State
KEY	ma
Fischer	QLD
Gold Coast	QLD
North Sydney	NSW
Bendigo	VIC
:	:

Important:All electorates in the Candidates or Electorates tables must appear in this States table.

Firstly, CREATE THIS DATABASE IN ACCESS, ENSURE YOU DO THE RELATIONSHIPS CORRECTLY then - insert the data you see here.

The following questions are challenging, and exercise a wide range of query skills

1. List the sitting members who contested their electorates in the 1990 election, together with the name of their electorate (use a join).
2. List candidates who have contested more than one election (use a group by having).
3. List candidates who have contested the same electorate more than once (use a group by having).
4. List the electorate(s) with the highest informal count in the 1987 election (use a subquery).
5. List the electorate(s) with the highest percentage of informal votes in the 1987 election (use a sub-query).
6. Suppose vote counting is progressing on the evening of the 1990 election. List, for each electorate, the percentage of the vote counted in that electorate (use a join and a group by).
7. List Electorates contested in 1990 but not by independents.
8. List Electorates contested by both the ALP and the Liberal Party but neither the Democrats nor the National Party in 1990.
9. List any candidate who belonged to a party in 1987 but who sought election as an independent (in any electorate) in 1990.
10. List Electorates contested by two or more independents in 1990.
11. For each state, list the total population on the roll in 1990.
12. For each state, list the number of electorates contested by each party in 1990. (Exclude independents from the result.)
13. For each state, list the number of electorates in 1987 and the number in 1990.
14. For each electorate, list the party which received the greatest primary vote in 1990. Hint: create and then use a temporary table:
    
    Temp (electorate, max votes).

---

Solutions