Spring 2013 Assignment #9
Objectives
To learn to use aggregation functions to analyze data.
Assignment
For this assignment you will use the Food Database ( Case Study – Food Database ) Use the following criteria to break the foods into quartiles (4 groups). Once the food is in 4 quartiles, using ONLY foods in the first quartile rank the top foods in descending order of calorie counts. Your final query should have the top 10 healthiest foods based on being in the first quartile and low calorie count. Do the same thing for unhealthy
Healthy Criteria
1. High content of grains (first quartile) – a healthy food must be in the first quartile
2. High Content of Vegetables (first quartile) – a healthy food must be in the first quartile
3. Low calorie count – top 10 – for all eligible food (in first quartile) – order by low calorie counts
Unhealthy Criteria
1. High Added Sugars
2. High Saturated Fats
3. High Calorie Count – top 10
Show your final query (you may include stored function calls in your query, you may add columns to the database) and the results. You are going to turn in a screen shot of your query and results. If you have calls to function you created or any other methods – simply explain your process in the submitted word document or PDF file.
Information
You will need to view the lectures on aggregation, this is relatively challenging. Lecture – Database and Aggregations
Estimated Completion Time
If you are good with SQL you will be able to write these in 15-20 minutes, otherwise learning how to use aggregation functions and planning out a way to produce the final query will take a few hours.
Supporting Lectures
Lecture – Database and Aggregations
Lecture – Complex Queries with Registration System
Questions and Answers
External Resources
Grading Criteria
Partial credit will be awarded based on how well you are able to meet all requirements of the query.