• If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Whenever you search in PBworks, Dokkio Sidebar (from the makers of PBworks) will run the same search in your Drive, Dropbox, OneDrive, Gmail, and Slack. Now you can find what you're looking for wherever it lives. Try Dokkio Sidebar for free.


Spring 2014 COP4709 Assignment 9

Page history last edited by Dr. Ron Eaglin 8 years, 8 months ago

 Assignment 9 - Aggregation




To learn to use aggregation functions to analyze data.




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.




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.


Comments (0)

You don't have permission to comment on this page.