nab_players.sql

\o proj2-pdzonga.out



-- Put your SQL statement under the following lines:


--1. Find all the coaches who have coached exactly ONE team. List their first names followed by their last names;
Select Distinct c.firstname, c.lastname
From coaches_season c, teams t, teams t2
where c.tid = t.tid and t.tid <> t2.tid;

--2. Find all the players who played in a Boston team and a Denver team (this does not have to happen in the same season). List their first names only. 
Select r1.firstname
From player_rs r1, teams t1
where r1.tid = t1.tid and t1.location = initcap('boston')
Union 
Select r2.firstname
From player_rs r2, teams t2
where r2.tid = t2.tid and t2.location = initcap('denver');

--3. Find those who happened to be a coach and a player in the same team in the same season. List their first names, last names, the team where this happened, and the year(s) when this happened. 
Select Distinct c1.firstname, c1.lastname, t1.name
From coaches_season c1, teams t1, player_rs r1
where c1.tid = t1.tid and r1.tid = t1.tid and c1.year = r1.year;


--4. Find the average height (in centimeters) of each team coached by Phil Jackson in each season. Print the team name, season and the average height value (in centimeters), and sort the results by the average height. 
Select temp.name, temp.year, temp.avheight
From ( Select  Distinct t1.name as name, AVG(r1.h_inches * 2.54 + r1.h_feet * 30.48) as avheight, c1.year as year
From teams t1, coaches_season c1, players r1, player_rs rs1
Where c1.tid = t1.tid and 
c1.firstname = 'Phil' and c1.lastname = 'Jackson'
and rs1.ilkid = r1.ilkid
Group By t1.name, c1.year
Order by avheight desc) as temp;

--5. Find the coach(es) (first name and last name) who have coached the largest number of players in year 2004.
Select temp.firstname, temp.lastname
From(
Select c1.firstname as firstname, c1.lastname as lastname, Count(r1) as count
From coaches_season c1, player_rs r1
Where c1.year = r1.year and c1.year = '1999'  and c1.tid = r1.tid 
Group by c1.firstname, c1.lastname
Order by count desc) as temp;

--6. Find the coaches who coached in ALL leagues. List their first names followed by their last names. 
Select Distinct c1.firstname, c1.lastname
From coaches_season c1, teams t1, teams t2
Where  c1.tid = t1.tid and t2.league <> t1.league;

--7. Find those who happened to be a coach and a player in the same season, but in different teams. List their first names, last names, the season and the teams this happened. 
Select temp1.firstname, temp1.lastname, temp1.name, temp1.year
From 
( Select c1.firstname, c1.lastname, t1.name, c1.year, c1.tid
From teams t1, coaches_season c1
Where t1.tid = c1.tid) as temp1
Join
(select r1.firstname, r1.lastname, t2.name, r1.year, r1.tid
from teams t2, player_rs r1
where t2.tid = r1.tid) as temp2
on temp1.year = temp2.year and temp1.firstname = temp2.firstname and temp1.lastname = temp2.lastname and temp1.tid <> temp2.tid;


--8. Find the players who have scored more points than Michael Jordan did. Print out the first name, last name, and total number of points they scored.
Select r1.firstname, r1.lastname, r1.pts
From player_rs_career r1
Where r1.pts > ( Select r2.pts
From player_rs_career r2
Where r2.firstname = 'Michael' and r2.lastname = 'Jordan');

--9. Find the second most successful coach in regular seasons in history. The level of success of a coach is measured as season_win /(season_win + season_loss). Note that you have to count in all seasons a coach attended to calculate this value. 
Select c1.lastname, c1.firstname, c1.wins/b1.games ::float As win_rate
From 
(Select cid, firstname, lastname, Sum(season_win) as wins
From coaches_season
Group by cid, firstname, lastname) as c1 Join
(select cid, firstname, lastname, sum(season_win + season_loss) as games
from coaches_season
Group by cid, firstname, lastname) as b1 on c1.cid = b1.cid
order by win_rate desc 
limit 1 offset 1;


--10. List the top 10 schools that sent the largest number of drafts to NBA. List the name of each school and the number of drafts sent. Order the results by number of drafts (hint: use "order by" to sort the results and 'limit xxx' to limit the number of rows returned); 
Select temp.name
From (
Select d1.draft_from as name, Count(p1) as count
From draft d1, players p1
Where d1.draft_from = p1.college and d1.ilkid = p1.ilkid
Group by d1.draft_from
Order by count desc limit 10) as temp;





-- redirecting output to console
\o