Spring 2014 COP4709 Assignment 9


 Assignment 9 - Aggregation

 

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

 

(Note: I liked this one last time I did the class so I am repeating it)

 

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

Case Study - Food Database

 

Questions and Answers

 

Here are the healthy foods

 

 

External Resources

 

 

 

Grading Criteria

 

Partial credit will be awarded based on how well you are able to meet all requirements of the query.