List all occupation where the assessing authority is the acs

Assignment Help Computer Engineering

Reference no: EM131903879

Business Data Management and Analytics Assignment: SQL Queries

SPECIFICATIONS

Read the following questions carefully. You will be asked to specify SQL queries to answer these questions.

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. You are to prepare 10 SQL query statements that will provide answers to the following 10 requests for information.

1. Show a list of all the female people, that are more than 100kg, and has a blood type of AB positive. Show only the people who work in a company with the word "store" in the name. Show the FULL name of the person, using this specific format: given middle-initial surname (for example: Jeremy J. Kavanagh). Also show the person's age and company name.

2. Create a list of PIE foods that have more than 2000 calories of energy. Show the pie's name, fat, energy, and carbohydrates; show this list in order of energy with the highest energy first.

3. List all occupations where the assessing authority is the ACS, show this in ascending order of the ANZSCO codes. Show all the fields available.

4. The dietician wants to target all the people between 45 and 55 who have a high BMI that is classified in the Obese classes III. Generate a report that provide the full name, bmi (only show up to 2 decimal places) and email address of these people.

Body Mass Index Formula: The formulas to calculate BMI: BMI = weight(kg)/height2(m2) (Metric Units)

BMI Table for Adults: The World Health Organization's (WHO) recommended body weight based on BMI values for adults. It is used for both men and women, age 18 or older.

Category BMI range - kg/m2
Severe Thinness < 16
Moderate Thinness 16 - 17
Mild Thinness 17 - 18.5
Normal 18.5 - 25
Overweight 25 - 30
Obese Class I 30 - 35
Obese Class II 35 - 40
Obese Class III > 40

5. Provide a list of people who live in a street called "Settlement"; Show the name of the people and just the street number they live at in the street, sorted in street number order.

6. Create a list of each blood type and how many people in our database belong to each blood type. Show the blood type and the count of people only.

7. Show list of foods consumed between 10pm and 6am (night). Only show the food_id and the number of times consumed.

8. Display all the unit of measure (uom) and the number of foods that are measured by this uom. Only count foods that have ZERO cholesterol. Also, remove any uom groups that have only 1 food in the group.

9. List for each MONTH of the year, show how many people registered a consumed food and show how many items have been consumed. Sort this in month order.

10. The dieticians in the business want to get an idea of which states do not have many people involved in order to do more advertising and attract more people to the dietician service. Show only the state name and how many people live in that state. Only show the count of states with less than 200 people.

REQUIREMENTS

10 SQL queries that answer the questions asked, based on the data model and implemented database (on mo.its.rmit.edu.au) provided.

ASSESSMENT

Assessment of the data model will be based on the following areas (by the tutor):

• How well the query answers the questions, in relation to the case study provided.
• Understanding of data structure
• Efficiency and simplicity of resulting query

SUBMISSION

• SQL queries (output not required) only.
• Assignment will be submitted online using the canvas.
• By submitting online you are observing adhering to all the requirements specified on a standard assignment coversheet.

Attachment:- SQL-Queries.rar

Reference no: EM131903879

Questions Cloud

Constant growth-assuming the market is in equilibrium : CONSTANT GROWTH-Assuming the market is in equilibrium, what does the market believe will be the stock price at the end of 3 years?

What is the present value of these payments : What is the present value of these payments if the discount rate is 13 percent.

The pump will have no salvage value : Assume, after either scenario, nothing will be left and the pump will have no salvage value. Should you buy the pump? Why or Why Not?

Savings off paper route : How much will Dr. Phillips have after four years from his savings off his paper route?

List all occupation where the assessing authority is the acs : List all occupations where the assessing authority is the ACS, show this in ascending order of the ANZSCO codes. Show all the fields available.

Write a brief report on existing malwares for mobile phones : Write brief report on existing Malwares for Mobile phones. Your report should cover viruses, worms, botnet and whatever is applicable in an Android environment.

What is the irr of the following set of cash flows : An investment project has annual cash inflows of $6,000, $7,100, $7,900, and $9,200, and a discount rate of 16 percent.

Analyze the profitability problems encountered : As the new financial analyst for Peterson's Chemicals, you have been asked to analyze the profitability problems encountered during the last two years.

Write an object class with just one instance variable : Write an object class with just one instance variable, an integer array. Your constructor should take an integer array as its only parameter.

User Account

All Pages