MEC_SQL_Fund_Assignment_AGGREGATES_HAVING_solution

Document Sample
MEC_SQL_Fund_Assignment_AGGREGATES_HAVING_solution Powered By Docstoc
					Microsoft Enterprise Consortium                                                           Assignment


SQL Fundamentals – AGGREGATES … HAVING
Create SQL commands to provide information for the following problems.


1. S-T Database: Show the average evaluation scores each student has given for their teammates as an
   evaluator. Show these fields with column aliases: team ID as "Team", student ID as "Student",
   evaluation item as "Eval Item" and score "Avg Score". Sort by team, student then evaluation item. .
2. S-T Database: Show the average evaluation scores each student has given for their teammates as an
   evaluator if the score is above 95. Show these fields with column aliases: team ID as "Team",
   student ID as "Student", evaluation item as "Eval Item" and score "Avg Score". Sort by team,
   student then evaluation item.
3. S-T Database: Show the average evaluation scores for each team for the three evaluation items.
   Show the team ID, the evaluation item (contribute, reliable, interpers) and the average team score.
   Sort by team then evaluation item.
4. S-T Database: Show the average evaluation scores for each team for the three evaluation items if
   the average score is below 85. Show the team ID, the evaluation item (contribute, reliable,
   interpers) and the average team score. Sort by team then evaluation item.
5. S-T Database: Show the average evaluation scores for ITPROS for the three evaluation items if the
   average score is below 85. Show the team ID, the evaluation item (contribute, reliable, interpers)
   and the average team score. Sort by team then evaluation item.



What to do:
  1. In one file write all the SQL commands.
  2. Before each command add the problem statement as a comment line.
  3. The file must be a simple text file with a TXT or SQL file extension.
  4. Test your commands and make sure they are error-free before submitting the solution file.


Solutions:

/* S-T Database: Show the average evaluation scores each student has given for their teammates as an
evaluator. Show these fields with column aliases: team ID as "Team", student ID as "Student",
evaluation item as "Eval Item" and score "Avg Score". Sort by team, student then evaluation item. */

select std_teamid as "Team", stdid as "Student", eval_item_ID as "Eval Item",
 avg(score) as "Avg Score"
from students join evaluations
 on stdid = evaluatorID
 join eval_items_scores
  on evaluations.eval_ID = eval_items_scores.eval_ID
group by std_teamid, stdid, eval_item_ID
order by std_teamid, stdid, eval_item_ID;
Microsoft Enterprise Consortium                                                             Assignment


/*
S-T Database: Show the average evaluation scores each student has given for their teammates as an
evaluator if the score is above 95. Show these fields with column aliases: team ID as "Team",
student ID as "Student", evaluation item as "Eval Item" and score "Avg Score". Sort by team, student
then evaluation item. */


select std_teamid as "Team ID", stdid as "Student ID", eval_item_ID as "Eval Item",
 avg(score) as "Average Score"
from students join evaluations
 on stdid = evaluatorID join eval_items_scores
 on evaluations.eval_ID = eval_items_scores.eval_ID
group by std_teamid, stdid, eval_item_ID
HAVING avg(score) > 95
order by std_teamid, stdid, eval_item_ID;


/*
S-T Database: Show the average evaluation scores for each team for the three evaluation items. Show
the team ID, the evaluation item (contribute, reliable, interpers) and the average team score. Sort by
team then evaluation item.
*/


select std_teamid as "Team ID", eval_item_ID as "Eval Item", avg(score) as "Avg Team Score"
from students join evaluations
 on stdid = evaluateeID join eval_items_scores
 on evaluations.eval_ID = eval_items_scores.eval_ID
group by std_teamid, eval_item_ID
order by std_teamid, eval_item_ID;


/*
S-T Database: Show the average evaluation scores for each team for the three evaluation items if the
average score is below 85. Show the team ID, the evaluation item (contribute, reliable, interpers) and
the average team score. Sort by team then evaluation item.
*/

select std_teamid as "Team ID", eval_item_ID as "Eval Item", avg(score) as "Avg Team Score"
from students join evaluations
 on stdid = evaluateeID join eval_items_scores
 on evaluations.eval_ID = eval_items_scores.eval_ID
group by std_teamid, eval_item_ID
HAVING avg(score) < 85
order by std_teamid, eval_item_ID;


/*
Microsoft Enterprise Consortium                                                            Assignment


S-T Database: Show the average evaluation scores for ITPROS for the three evaluation items if the
average score is below 85. Show the team ID, the evaluation item (contribute,
reliable, interpers) and the average team score. Sort by team then evaluation item.
*/


select std_teamid as "Team ID", eval_item_ID as "Eval Item", avg(score) as "Avg Team Score"
from students join evaluations
 on stdid = evaluateeID join eval_items_scores
 on evaluations.eval_ID = eval_items_scores.eval_ID
WHERE std_teamid = 'ITPROS'
group by std_teamid, eval_item_ID
HAVING avg(score) < 85
order by std_teamid, eval_item_ID;

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:10/26/2011
language:English
pages:3