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 #8

The FIRST FLEET Investigation - Solutions

Presented are some solutions. Numbers in brackets represent approximate numbers of rows in answer tables.

PEOPLE TABLE:
1.
select christian_names
from people
where surname = 'smith'	(23)

2.
select count(*)
from people
where surname = 'smith'	(23)

3.
select *
from people
where surname = 'moncrieff'

4.
select *
from people
where sex = 'm'
and age > 50	(5)

5.
select *
from people
where sex = 'f'
and status = 'f'	(43)

6.
select Christian_names, surname
from people
where surname like 'J%'
and status = 'C'	(28)

7.
select *
from people
where ship_id null	(122)

8.
select sex, count(*)
from people
group by sex	(f=246,m=1074)

9.
select sex, status, count(*)
from people
group by sex, status


CONVICTS TABLE: 1. select * from convicts where crime = 'felony' (17) 2. select * from convicts where crime like '%sheep%' (16) 3. select * from convicts where tran_yrs <> '7' (30 ish) 4. select id from convicts where crime like '%theft%' and crime like '%money%' (57) 5. select id from convicts where crime like '%theft%' and (crime like '%money%' or crime like '%lead%') (68) 6. select id from convicts where crime like '%theft%' and crime like '%lace%' (4) 7. select distinct tran_yrs from convicts where crime like '%burglary%' (14,7,life) 8. select * from convicts where pre_tran_sentence like '%death%' (105) 9. select * from convicts where crime like '%assault%' and crime like '%theft%' (70) 10. select * from convicts where crime like '%pigs%' (2) 11. sel count(*) from convicts where pre_tran_sentence not null (798) 12. sel count(*) from convicts where pre_tran_sentence = ' ' (568) 13. sel trial_location, count(*) from convicts group by trial_location (158) 14. select * from convicts where trial_date like '%may%' (34) 15. select * from convicts where trial_date like '%1786' (169) 16. select * from convicts where trial_location = 'the old bailey' (324) 17. select * from convicts where trial_location like '%bristol%' (25) 18. select avg(age) from people
DEATH DETAILS: 1. select id from death_details where place_of_death = 'Sydney Cove' (43) 2. select id from death_details where place_of_death = 'at sea' (11) 3. select id from death_details where place_of_death = ' ' (42) 4. select count(*) from death_details where date_of_death like '%1788' (1) 5. select count(*) from death_details where age_at_death = 40 (1) 6. select avg(age_at_death) from death_details
SHIPS TABLE: select ship_name from ships order by 1 asc (11) select ship_type, count(*) from ships group by ship_type (2)
MARINES TABLE: 1. select rank, count(*) from marines group by rank (12) 2. select commander_id, count(*) from marines group by commander_id (5)
PRE_TRAN_OCCUPS TABLE: 1. select id from pre_tran_occups where pre_tran_occup = 'gardener' (2) 2. select count(*) from pre_tran_occups where pre_tran_occup = 'servant' (71) 3. select id from pre_tran_occups where pre_tran_occup in ('hawker','silversmith') (10)
SETTLEMENT_OCCUPS TABLE: 1. select count(*) from settlement_occups where settlement_occup = 'builder' (4) 2. select id from settlement_occups where settlement_occup in ('carpenter','shingler') (16) 3. select id from settlement_occups where settlement_occup = 'chaplain'
MULTI-TABLE QUERIES 1. select A.id from pre_tran_occups A, settlement_occups B where A.id = B.id and pre_tran_occup = settlement_occup 2. select christian_names, surname from people where id in (select id from pre_tran_occups where pre_tran_occup = 'Chaplain') or id in (select id from settlement_occups where settlement_occup = 'Chaplain') or id in (select id from others where job_on_voyage = 'Chaplain') 3. select ship_name, count(*) from people P, ships S where P.ship_name = S.ship_name group by ship_name 4. select christian_names, surname, sex from people where sex = 'm' and status = 'C' and id in (select id from death_details where date_of_death like '%1790%') 5. select christian_names, surname from people where id in (select id from relations where related_as ='Companion') or id in (select relation_id from relations where related_as ='Companion') 6. .... and 'ugly' query if ACRES turns out to be an alias select crime from convicts where id in (select id from people where surname = 'ACRES' and christian_names like 'THOMAS%') 7. ... problematic if she is not in one of the tables involved select settlement_occup, place_of_death, date_of_death from settlement_occups S, death_details D where S.id = D.id and S.id in (select id from people where surname = 'AULT" and christian_names like 'Sarah%') 8. .... tricky, as clear could be an alias(deciding to ignore that for the mo)
- note the 'text' answer select "convict "
from people
where surname="clear"
and christian_name="george"
and id in
(select id
from convicts)

union

select "free person"
from people
where surname="clear"
and christian_name="george"
and id not in
(select id
from convicts) 9. .... he shouldn't appear in the list select christian_names, surname from people where surname <> 'HOGG' and not(christian_names like 'William%') and id in (select id from convicts where crime = (select crime from convicts where id in (select id from people where surname = 'HOGG' and christian_names like 'William%'))) 10. ... another ugly query if CAMPBELL is an alias select christian_names, surname from people where id in (select id from marines where commander_id in (select id from people where surname = 'CAMPBELL' and christian_names like 'James%')) 11. select 'pre ', pre_tran_occup from pre_tran_occups where id in (select id from people where surname = 'WIGFALL' and surname like 'Samuel%') union select 'post', settlement_occup from settlement_occups where id in (select id from people where surname = 'WIGFALL' and surname like 'Samuel%') 12. select tran_years, crime from convicts where id in (select id from people where surname = 'MAPP' and christian_names like 'James%') 13. select 'yes',pre_tran_sentence from convicts where pre_tran_sentence not null and id in (select id from people where surname = 'SPRIGMORE' and christian_names like 'Charlotte%') union select 'no ','no prior conviction' from convicts where pre_tran_sentence null and id in (select id from people where surname = 'SPRIGMORE' and christian_names like 'Charlotte%') 14. select count(*) from people where surname = 'SMITH' and ship_id = (select ship_id from ships where ship_name = 'Scarborough') 15. select count(*) from people where ship_id = (select ship_id from ships where ship_name = 'Fishburn') 16. ... ugly query to try to do as a single process select Christian_Names, Surname, count(*) from People P, Ships S, Marines M where P.ship_id = S.ship_id and P.id = S.Shipmaster and M.rank = 'Private' and S.ship_Name='Friendship' group by Christian_Names,Surname 17. impossible due to the way trial_date is stored - as text text comparisons make a nonsense of chronology when '12' is less than '7' for example.
HARDER QUERIES (ouch, some of these are really nasty - sorry) 1. select christian_names, surname, date_of_death, place_of_death from people P, death_details D where P.id = D.id and P.id in (select id from aliases where alias = 'ECCLES') 2. select ship_name, christian_names, surname from ships S, people P where S.ship_id = P.ship_id and ship_id in (select ship_id from people where id in (select id from aliases where alias = 'RAW')) and id in (select id select bosun_id from ships) 3. ... tricky without a multi-command solution ... single query solution: select christian_names, surname from people where id in (select id from death_details where date_of_death in (select date_of_death from death_details where date_of_death not null group by date_of_death having count(*) >= 2)) ... multiple process solution (a) create table temp (date_of_death char(17) not null, tally dec(2) not null, primary key (date_of_death)) (b) insert into temp select date_of_death, count(*) from death_details group by date_of_death (c) select christian_names, surname from people where id in (select id from death_details where date_of_death in (select date_of_death from temp where tally >= 2)) (d) drop table temp 4. select christian_names, surname, settlement_occup from people P, settlement_occups S where P.id = S.id and P.id in (select id from convicts where tran_yrs="life') union select christian_names, surname, 'no recorded occup' from people where id in (select id from convicts where tran_yrs="life') and id not in (select id from settlement_occups) 5. select age_at_death from death_details where id in (select id from pre_tran_occups where pre_tran_occup = 'Surgeon') or id in (select id from settlement_occups where settlement_occup = 'Surgeon') or id in (select id from others where job_on_voyage = 'Surgeon')

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
.