IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
IIS home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 

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

wonko@wonko.info
©Copyright t 1992..2017+. Edition 25.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.