notnull
select nation, count(player_id)
from player
where nation is not null
group by nation
group by (소속팀별 평균 몸무게)
select region_name, team_name, avg(weight), avg(height)
from player natural join team
group by region_name, team_name;
서브쿼리 - 키가 큰 선수순
select *
from (
select player_name,height
from player
where height is not null
order by height desc)
where rownum <=5
order by height desc;
서브쿼리 - 스코어 차이 큰 순서 5개 팀
select sche_date, stadium_name,ht ,team_name, home_score, away_score
from (
select sche_date, stadium_name,team_name ht, awayteam_id, home_score, away_score
from schedule,stadium,team
where home_score is not null and schedule.hometeam_id=team.team_id and
schedule.stadium_id=stadium.stadium_id
order by abs(home_score-away_score) desc
) sub1, team t
where sub1.awayteam_id=t.team_id and rownum<=5;
널값 대체 nvl
select team_name, player_name, nvl(nation,'한국')
from team natural join player
where position='FW'
order by team_name, player_name desc;
