Database Languages eXercises #10

### 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:
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),

insert into temp
from candidates
where year = 1990
group by electorate

select c.electorate, party
from candidates c, temp
where temp.electorate = c.electorate