The ELECTION DATABASE - Some Solutions

1.
select sitting, e.electorate
from canditadtes c, electorates e
where c.year = 1990
and e.year = c.year
and c.electorate = e.electorate
and candidate = sitting

2.
select candidate
from candidates
group by candidate
  having count(year) > 1

3.
select candidate, electorate
from candidate
group by candidate, electorate
  having count(year) > 1

4.
select electorate
from electorates
where year = 1987
and informal =
   (select max(informal)
    from electorates
    where year = 1987)

5.
select electorate
from electroates
where year = 1987
and 100.0 * informal/roll >= all
   (select 100.0*informal/roll
    from electorates
    where year = 1987)

alternative:
select electorate
from electroates
where year = 1987
and 100.0 * informal/roll = 
   (select max(100.0*informal/roll)
    from electorates
    where year = 1987)

6.
select e.electorate, 100.0*(sum(votes) + informal)/roll
from candidates c, electorates e
where c.year = 1990
and e.year = c.year
and e.electorate = c.electorate
group by e.electorate, roll,informal

alternative:
select e.electorate, 100.0*(sum(votes)+avg(informal))/avg(roll)
from candidates c, electorates e
where c.year = 1990
and e.year = c.year
and e.electorate = c.electorate
group by e.electorate

7.
select distinct electorate
from candidates
where year = 1990
and electorate not in
   (select electorate
    from candidates
    where year = 1990
    and party = 'Ind')

8.
select distinct electorate
from candidates
where year = 1990
and party = 'ALP'
and electorate in
   (select electorate
    from candidates
    where year = 1990
    and party = 'Lib'
    and electorate not in
       (select electorate
        from candidates
        where year = 1990
        and party in ('Dem', 'NP')))

9.
select candidate
from candidates
where year = 1990
and party = 'Ind'
and candidate in
   (select candidate
    from candidates
    where year = 1987
    and party <> 'Ind')

10.
select electorate, count(candidate)
from candidates
where year = 1990
and party = 'Ind'
group by electorate
  having count(candidate) > 1

11.
select state, sum(roll)
from electorates e, states s
where e.electorate = s.electorate
and year = 1990
group by state

12.
select state, party, count(*)
from candidates c, states s
where c.electorate = e.electorate
and year = 1990
and party <> 'Ind'
group by state, party

13.
select state, year, count(*)
from electorates e, states s
where e.electorate = s.electorate
and year in (1987, 1990)
group by state, year

14.
create table temp
(electorate char(30),
maxVotes dec(6))

insert into temp
select electorate, max(votes)
from candidates
where year = 1990
group by electorate

select c.electorate, party
from candidates c, temp
where temp.electorate = c.electorate
and votes = maxVotes
and year = 1990

drop table temp