/* SQL Fundamentals Aggregates */ /* Show how many students are on each team. */ select std_teamID, count(*) as 'Student Count' from students group by std_teamID; /* Show how many students are on each team. Show the full team name. */ select teams.teamID, team_name, count(*) as 'Student Count' from teams, students where teams.teamID = students.std_teamID group by teams.teamID, team_name; /* Show each evaluatee's average evaluation score, maximum evaluation score, and minimum evaluation score for the 3 evaluation items (reliability, contribution, interpersonal). */ select students.stdid, eval_item_ID, avg(score) as "AVG", max(score) as "MAX", min(score) as "MIN" from students join evaluations on students.stdid = evaluations.evaluateeID join eval_items_scores on evaluations.eval_ID = eval_items_scores.eval_ID group by students.stdid, eval_item_ID order by students.stdid; /* Compare detailed data versus aggregate data for one student: BKRY */ select students.stdid, evaluations.eval_ID, eval_item_ID, score from students join evaluations on students.stdid = evaluations.evaluateeID join eval_items_scores on evaluations.eval_ID = eval_items_scores.eval_ID where stdid = 'BKRY' order by stdid; select students.stdid, eval_item_ID, avg(score) as "AVG", max(score) as "MAX", min(score) as "MIN" from students join evaluations on students.stdid = evaluations.evaluateeID join eval_items_scores on evaluations.eval_ID = eval_items_scores.eval_ID where stdid = 'BKRY' group by students.stdid, eval_item_ID order by students.stdid; /* Using a few more aggregate functions: SUM and COUNT(DISTINCT ...) */ select students.stdid, eval_item_ID, sum(score) as "Total Pts", count(distinct evaluations.eval_ID) as "# of Evals", avg(score) as "AVG", max(score) as "MAX", min(score) as "MIN" from students join evaluations on students.stdid = evaluations.evaluateeID join eval_items_scores on evaluations.eval_ID = eval_items_scores.eval_ID where stdid = 'BKRY' group by students.stdid, eval_item_ID order by students.stdid;