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