/* Advanced SQL
One-sided outer joins
*/


/* Show how many students are on each team.  Show the
full team name. */

select teams.teamID, team_name, 
  count(*) as 'Student Count'
from teams join students
  on teams.teamID = students.std_teamID
group by teams.teamID, team_name;


/* Show how many students are on each team.  Show the
full team name. Show all students even if they aren't on 
a team. */

select teams.teamID, team_name, 
  count(*) as 'Student Count'
from teams right join students
  on teams.teamID = students.std_teamID
group by teams.teamID, team_name;


/* Show how many students are on each team.  Show the
full team name. Show all teams if they don't have 
students assigned.  */

select teams.teamID, team_name, 
  count(*) as 'Student Count'
from teams left join students
  on teams.teamID = students.std_teamID
group by teams.teamID, team_name;


/* count(*) versus count(<field>)  */

select teams.teamID, team_name, 
  count(stdid) as 'Student Count'
from teams left join students
  on teams.teamID = students.std_teamID
group by teams.teamID, team_name;


/*  Full outer join.  Show ALL students and ALL 
teams.  */

select teams.teamID, team_name, stdid, stdlname
from teams full join students
  on teams.teamID = students.std_teamID;



/*  Show teams without students assigned.  */

select teams.teamID, team_name, stdid, stdlname
from teams left join students
  on teams.teamID = students.std_teamID
where students.std_teamID is null;