You will be working with a set of tables for a Diet database. You can access these tables by…

QUESTIONS

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

occupation.

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

(2 marks)

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.

(3 marks)

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.

Please provide:

a) Business question

b) SQL query

c) Visualisation using Excel, Tableau or Orange (attached image only to submission)

Attachments:

2020-s1-db-A1….pdf

Save your time - order a paper!

Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines

Order Paper Now

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"