Database Languages
eXercise #8
The FIRST FLEET
Database - a mSQL Investigation
As you probably
know, Australia was originally a penal colony (a place where criminals
were sent to complete their sentence). The first batch of people to
be sent out were called the first-fleeters. The following MySQL investigation
requires you to interrogate the database of information about this intrepid
band of settlers, criminals and military personnel, and formulate valid
queries.
The ONLINE Query Engine is made with PHP and MySQL
What follows is
a complete description of the database:
A word of warning:
This database contains some very LARGE tables (i.e. many rows).
If you choose to JOIN tables (which may in some cases be the only way
to a solution) my server may not have enough RAM to build the answer table
unless you work with a query in OPTIMAL form. Also, don't be too surprised
if some of your queries take a little while to complete - the sheer
volume of information being dealt with may cause my server to slow
down a little (blame Canada!).
Single Table Queries
PEOPLE table
- list the christian
names of all 'SMITH's
- list the number
of 'SMITH's in this table
- list details
for anyone with your surname
- list details
of all males over 50
- list names of
Free female settlers
- list names of
convicts that have surnames starting with 'J'
- list details
of anyone whom has no ship recorded
- list sexes and
the number of people in each
- list the number
of free males, free females, together with the number of convict males
and the number of convict females (use a group by)
*
- what was the
average age of transportees
CONVICTS table
- list details
of all who committed the crime of felony
- list details
of all crimes involving sheep
*
- list details
of anyone sentenced to other than 7 years
- list the id
of all who committed a crime involving theft of money
- list details
of all who committed a crime involving theft of money or lead *
- list details
of anyone involved in theft of lace
- list the sentence(s)
given for burglary
- list details
of anyone who had a previous conviction and sentence of death
- list details
of people convicted of assault & theft
- list details
of people convicted of a crime involving pigs
- list a tally
of the number of people that had a previous conviction
- list a tally
of the number of people that didn't have a recorded prior conviction
- list the trial
locations and the number of people tried at each
- list details
of people tried in May of any year
- list details
of people tried in the year 1786
- list details
of people tried in 'The Old Bailey'
- list the crimes
of people tried in 'Bristol'
DEATH_DETAILS table
- list the ids
of people who died at Sydney Cove
- list the ids
of people who died 'At sea'
- list the ids
of people for whom a death date is unknown
*
- list the number
of people who died in 1788
- list the details
of people who died at age 40
- what was the
average age
SHIPS table
- list in alphabetic
order the names of all ships
- list ship types
and the number of each
MARINES table
- list the ranks
and the number of each
- list the commanders
along with a tally of his crew
PRE_TRAN_OCCUPS
table
- list the ids
of any gardeners
- list the number
of people who used to be servants
- list the ids
of either 'Hawker's or 'SilverSmith's
SETTLEMENT_OCCUPS
table
- list the number
of builders
- list the ids
of either 'Carpenter's or 'Shingler's
- list the ids
of any 'Chaplain's
Multi-Table Queries
(YOU decide on
the base tables)
- list the ids
of anyone who persued the same occupation before and after being transported
- list the name(s)
of the chaplain(s)
- list the names
of the ships, along with the number of people that arrived in then
- list the names
and sex of anybody who died in 1790 that was male and a convict
- name the people
involved in a 'Companion' relationship
*
- Name the crime
that was committed by 'Thomas ACRES'
- What was the
occupation (if any) of 'Sarah AULT' after she settled, and when and
where did she die?
- 'George CLEAR'
- was he a criminal of a free settler - look for evidence in as many
places as you can.
- Name the people
that shared the same crime as 'William HOGG'
- Name the crew
of the ship commanded by a Commander named 'James CAMPBELL'
- What was Samuel
WIGFALL's pre and post transport occupation (if recorded)
- How long was
James MAPP sentenced, and for what?
- Did Charlotte
Sprigmore have any prior convictions, if so list them.
- how many 'SMITH's
came out on the HMAS 'Scarborough'
- how many people
(in total) were transported on the HMAS 'Fishburn'
- how many 'private's
sailed on the HMAS 'Friendship' and what was the Shipmaster's name?
- What was the
approximate population of Australia by 1800 (use conviction date)
and name them.
Harder Queries
(you need to decide
where the answers to these queries is BEFORE you start writing mSQL, they involve, usually, multiple tables and often the answer is an empty answer table)
- What was the
real (full) name, and date and place of death of someone who also
went under the name of 'ECCLES'?
- What was the
name of the ship and the bosuns name for that ship that a person who
had an alias of 'RAW' travelled on?
- list the names
of anybody that shared the same date_of_death as anybody else
- What was the
names of all convicts who were sent to Austraila for 'life', and list
their settlement_occups?
- How old were
each of the surgeons when they died?
SOLUTIONS
