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')