/* SQL Fundamentals
Aggregates - Assignment
*/


/* Show how many evaluations were done for each team. 
Show the team's ID and full name and the evaluation count.  
Use a column alias for the aggregate function display.
HINT:  You can join the students table to the evaluations 
table through either evaluatorID or evaluateeID.  */

select teamID, team_name as "Team", count(eval_ID) as 'Evaluation Count'
from teams join students
  on teams.teamID = students.std_teamID
  join evaluations
    on students.stdid = evaluations.evaluatorID
group by teamID, team_name;


/* Show how many evaluations each student in the ITPROS teams
has done as the evaluator.  Show the student's ID, last name, 
team ID and show the evaluation count.  Use a column alias for the
aggregate function display.
Sort by student ID.
*/


select stdid, stdlname, std_teamID, count(*) as 'Evaluations Done'
from students, evaluations
where students.stdid = evaluations.evaluatorID
and std_teamID = 'ITPROS'
group by stdid, stdlname, std_teamID 
order by stdid;

/* For the ITPROS teams, get the average, minimum, and maximum for 
evaluation items scores.  Display the team ID, the evaluation item ID 
(such as CONTRIBUTE and RELIABLE), average, minimum and maximum for
each of the evaluation items.  Use column aliases for aggregate 
functions.  Sort by evaluation item ID.  
HINT:  You can join the students table to the evaluations 
table through either evaluatorID or evaluateeID.
*/

select std_teamid, eval_item_ID, 
  avg(score) as "Average", 
  min(score) as "Minimum", 
  max(score) as "Maximum"
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 
order by eval_item_ID;