Provide a list of all the foods that has not been consumed

Assignment Help PL-SQL Programming

Reference no: EM131952531

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

Reference no: EM131952531

Questions Cloud

Calculate the distance to the water : What is the acceleration and What horizontal force. including that to overcome friction (rolling resistance), is the car exerting on the road

What are the elemental design differences between products : List five products with low reliability. List five that have high reliability. What are the elemental design differences between these products?

Environmental science that interests you : What is a current or "hot" topic in the news that related to environmental science that interests you?

What is the indifference point : Simuelson industries can produce its own motors for a $32,000 fixed monthly cost and a $40 variable cost per unit.

Provide a list of all the foods that has not been consumed : 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.

What is the overall reliability for the product : A product is made up of 20 components in a series. Ten of the components have a 1>10,000 chance of failure. Five have a 3>10,000 chance of failure.

Characteristics of dementia in general and alzheimer : Describe the characteristics of dementia in general and Alzheimer's disease (AD) in particular.

How does singapore airlines uses it to achieve cost : How does Singapore Airlines uses IT to achieve cost leadership? i.e. reduce costs of business processes, suppliers or consumers

What is the mean time between failures : Dellana company tested 50 products for 75 hours each. In this time, they experienced four breakdowns. Compute the number of failures per hour.

User Account

All Pages