IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
exercise source
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 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:
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
 
  

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
.