/* SQL Fundamentals
Aggregates and the HAVING clause
*/




/* Show the average evaluation scores for each student in 
each team.  Show the team ID, student ID, evaluation item 
and score.
*/

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 = evaluateeID
  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;


/* 
Show the average evaluation scores for each student in 
each team only if the average score is ABOVE 95.
Show the team ID, student ID, evaluation item and score.
*/


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 = evaluateeID   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;



/* 
Show the average evaluation scores for each student in 
each team only if the average score is BELOW 80.
Show the team ID, student ID, evaluation item and score.
*/


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 = evaluateeID   join eval_items_scores
  on evaluations.eval_ID = eval_items_scores.eval_ID
group by std_teamid, 
  stdid, eval_item_ID
HAVING avg(score) < 80 
order by std_teamid, 
  stdid, eval_item_ID;



/* 
WHERE & HAVING clauses:  
Show the average evaluation scores for each student in 
the SYSDES team if the evaluation average score is BELOW 80.
Show the team ID, student ID, evaluation item and score.
*/


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 = evaluateeID   join eval_items_scores
  on evaluations.eval_ID = eval_items_scores.eval_ID
group by std_teamid, 
  stdid, eval_item_ID
HAVING avg(score) < 80 
order by std_teamid, 
  stdid, eval_item_ID;