You will be working with a set of tables for a Diet database. You can access these tables by
using the DIET database on the mysql server (mo.its.rmit.edu.au). You are to prepare 11 SQL
query statements and 3 visualisations that will provide answers to the following 12 requests.
(1 marks per question)
1. There is an expected increased consumption of PIEs. The dieticians needs to keep an eye on
the pie consumption. Create a view that lists all the people that have eaten any type of pie.
Show the persons full name and title, with their “age – weight – height” in brackets, (eg.
“Mr Joe Bloggs (53-60kg-172cm)”) and the person’s total consumption of pies.
2. There is a secret police investigation at the dieticians and they need a list all people from a
suburb that has “EAST” somewhere in the suburb name, who has an occupation whose
assessment authority is “ACS”. Show the persons full name, phone contact details and their
3. The Dieticians were worried about a call made by someone. The requested a trace on the
call and were give the following area coordinates, latitude from -34.4 to -34.2 and longitude
from 145.8 to 147. List all the people who belong to this location. Show the full name
(include title), their occupation, phone number, suburb the name of the credit card they have
and their height in centimetres and inches (show only 1 decimal place).
4. A number of new foods have been entered into the diet database. Provide a list of all the
foods that has not been consumed by any people. Just show the name of the food.
5. The Dieticians wants to have an idea of the number of people whose birthday it is each
month. Show the month and the number of people born in that month. Can you also show
the average weight and height of people born in that month? Please show it in month order.
6. The Dietician is looking for a person, but cannot remember their exact name. Produce a
report that shows details of people, which have a first name of ‘Amy’. Include their full
name (with their title and middle initial), occupation, credit card type, year of birth, age and
the country they are from. Hopefully, the dietician will recognise the person from this list.
7. The business is interested in targeting the OLDEST and YOUNGEST mothers in the Diet
people database in order to setup some sort of promotional video. Who are the OLDEST
and YOUNGEST mothers? Include the person’s date of birth.
8. Create a view that lists ALL foods with a total number of times it has been consumed. Show
the food id and name, along with the count and show total fat consumed. (Hint: similar to
query on slide 20 in lecture 5).
9. Show the occupation that has the most people. Please show the name of the occupation, the
count of number of people in that occupation and the assessment authority (Hint1: slide17 in
lecture3; Hint2: Slide6 in lecture5; )
10. List the people (kids) who are TALLER and HEAVIER than their mother. Only selecting
the Mum’s who have a blood type of O- and the kids are located in Victoria. Show the Kids
name and son or daughter, and their mother’s name
11. Choose two questions (from questions 1-10 of this assignment) and create a visualisation,
using Excel, Tableau or Orange. Attach the created image ONLY to your submission.
12. Produce a report of your own design and write a query to solve it. Marks will be awarded
for report design (ie. How useful is the report), complexity of the query and originality.
a) Business question
b) SQL query
c) Visualisation using Excel, Tableau or Orange (attached image only to submission)