Business Data Management and Analytics Assignment- SQL Queries
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. 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.
11. Choose two questions (from questions 1-10 of this assignment) and create a visualisation, using 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.
Provide:
a) Business question
b) SQL query
c) Visualisation using Orange (attached image only to submission).