/*
  Sample Query Examples on the NFL database
*/

use NFL;

/* Teams and their Coaches */
select t.name as 'Team', c.name as 'Coach', c.position
from Coaches c, Teams t
where c.teamName = t.name
order by t.name asc


/* Coaches without Teams */
select c.name as 'Coach', t.name as 'Team'
from Coaches c left outer join Teams t
on c.teamName = t.name
where t.name is null
order by c.name asc


/* Teams without Coaches */
select t.name as 'Team', c.name as 'Coach'
from Coaches c right outer join Teams t
on c.teamName = t.name
where c.name is null
order by t.name asc


/* Coaching and Availability and Vacancies */
select t.name as 'Team', c.name as 'Coach'
from Coaches c left outer join Teams t
on c.teamName = t.name

union

select t.name as 'Team', c.name as 'Coach'
from Coaches c right outer join Teams t
on c.teamName = t.name

order by team asc


/* Players in New York */
select p.name, t.city
from Teams t, Players p
where p.teamName = t.name and city = 'New York'
order by p.name


/* Teams per City */
select count(t.name) as "Number of Teams", t.city
from Teams t
group by t.city
order by count(t.name) desc


/* Players per City */
select count(p.name) as "Number of Players", t.city
from Teams t, Players p
where p.teamName = t.name
group by t.city
order by count(p.name) desc


/* Players without Coaches */
select p.name, t.name, c.name
from Players p left outer join Teams t on t.name = p.teamName
               left outer join Coaches c on t.name = c.teamName
where c.name is null


/* Overview of Teams, Coaches, and Players */
select t.name, c.name, c.position, p.name
from Teams t, Players p, Coaches c
where t.name = p.teamName and t.name = c.TeamName
order by t.name, c.name