DB/마리아/2020-05-11/ 쿼리 테스트용 축구 soccer.sql

-- 다음 조건을 만족하는 선수명단을 출력하시오
-- 소속팀이 삼성블루윙즈이거나 
-- 드래곤즈에 소속된 선수들이어야 하고, 
-- 포지션이 미드필더(MF:Midfielder)이어야 한다. 
-- 키는 170 센티미터 이상이고 180 이하여야 한다.
 

가장 기본형태의 모습 (not ansi)

 

select * from player
where (team_id = (select team_id from team where team_name = '삼성블루윙즈')
    or team_id = (select team_id from team where team_name = '드래곤즈'))
    and position = 'MF'
    and height >= 170 and height <= 180;

sql 연산자 (between, in, is null, like) 사용한 모습 (ansi)

 

select * from player
where team_id in ((select team_id from team where team_name like '삼%'),
                   (select team_id from team where team_name like '드%'))
    and position like 'MF'
    and height between 170 and 180;

 

-- 문제 1-- 다음 조건을 만족하는 선수명단을 출력하시오
-- 소속팀이 삼성블루윙즈이거나 
-- 드래곤즈에 소속된 선수들이어야 하고, 
-- 포지션이 미드필더(MF:Midfielder)이어야 한다. 
-- 키는 170 센티미터 이상이고 180 이하여야 한다.
 
select * from player
where team_id = (select team_id from team where team_name = '삼성블루윙즈')
    or team_id = (select team_id from team where team_name = '드래곤즈')
    and position = 'MF'
    and height >= 170 and height <= 180;
-- 문제 2    
-- 전체 축구팀의 목록을 출력하시오
-- 단, 팀명을 오름차순으로 정렬하시오.   
select team_name from team
order by team_name
;
-- 문제 3
-- 포지션의 종류를 모두 출력하시오
-- 단, 중복은 제거합니다.
select distinct position from player
;
-- 문제 4
-- 포지션의 종류를 모두 출력하시오
-- 단, 중복은 제거합니다. 
-- 포지션이 없으면 신입으로 기재
select case
    when position like '' then '신입'
    else position
    end as position , player_name
from player
where team_id like 'K08'
;-- 문제 5
-- 수원을 연고지로 하는팀의 골키퍼는
-- 누구인가 ?
 
select player_name from player
where (team_id like (select team_id from team
                            where region_name like '수원')) 
    and (position like 'GK');
 
 
select player_name
from player
where team_id like (select team_id
from team
where region_name like '수원')
and position like 'GK';
-- 문제 6
-- 수원 연고팀에서 키가 170 이상 선수
-- 이면서 성이 고씨인 선수는 누구인가
select player_name from player
where (team_id like (select team_id
                            from team
                            where region_name like '수원')) 
and (height >= 170) 
and (player_name like '고%');
 
 
-- 문제 7
-- 광주팀 선수들 이름과
-- 키와 몸무게 목록을 출력하시오
-- 키와 몸무게가 없으면 "0" 표시하시오
-- 키와 몸무게는  내림차순으로 정렬하시오
 
select player_name as playerName , 
    concat(case when height like '' then '0' else height end, 'cm')as height, 
    concat(case when weight like '' then '0' else weight end,'kg') as weight from player
where (team_id like (select team_id from team 
                            where region_name like '광주'))
order by height desc, weight desc; 
-- 문제 8
-- 서울팀 선수들 이름과 포지션과
-- 키(cm표시)와 몸무게(kg표시)와  각 선수의 BMI지수를 출력하시오
-- 단, 키와 몸무게가 없으면 "0" 표시하시오
-- BMI는 "NONE" 으로 표시하시오(as bmi)
-- 최종 결과는 이름내림차순으로 정렬하시오
 
select player_name as playerName , position , 
    concat(case when height like '' then '0' else height end,'cm') as height , 
    concat(case when weight like '' then '0' else weight end, 'kg') as weight , 
    case when height like '' or weight like '' then 'NONE' else 
        round(weight / ((height/100)*(height/100)),2) end as bmi 
from player
where team_id like (select team_id from team 
                            where region_name like '서울')
order by player_name desc;
-- 문제 9
-- 4개의 테이블의 키값을 가지는 가상 테이블을 생성하시오 (join)
-- 카티전 프로덕트 8,195 행
select a.player_id, a.team_id, a.stadium_id, s.sche_date -- 8195 -- 10 컬럼
from (select a.player_id, a.team_id, a.stadium_id
        from (select p.player_id, t.team_id, t.stadium_id
                from (select player_id, team_id from player) p
                        join team t on p.team_id like t.team_id) a
       join stadium s on a.stadium_id like s.stadium_id) b
join schedule s on b.stadium_id like s.stadium_id
;
-- 카티전 프로덕트 8,195 행
select b.player_id, b.team_id, b.stadium_id, s.sche_date -- 8195 -- 10 컬럼
from (select a.player_id, a.team_id, a.stadium_id
        from (select p.player_id, t.team_id, t.stadium_id
                from (select player_id, team_id from player) p
                        join team t using(team_id)) a
       join stadium s using(stadium_id)) b
join schedule s using(stadium_id)
;
 
CREATE VIEW full_scan AS 
select t.team_id, t.team_name from team t  -- 8195, 41컬럼
join player p on t.team_id like p.team_id
join stadium s on t.stadium_id like s.stadium_id
join schedule sc on s.stadium_id like sc.stadium_id;
 
select * from full_scan
where team_id like 'K02'
; -- 480
 
-- 문제 10
-- 수원팀(K02) 과 대전팀(K10) 선수들 중 포지션이 골키퍼(GK) 인 
-- 선수를 출력하시오
-- 단 , 팀명, 선수명 오름차순 정렬하시오
 
select t.team_id,t.team_name, p.player_name, p.position 
from team t join player p using(team_id)
where (t.team_id like 'K02' or t.team_id like 'K10')
and p.position like 'GK'
order by t.team_name, p.player_name
;
 

 

-- 문제 11
-- 팀과 연고지를 연결해서 출력하시오
-- [팀 명]             [홈구장]
-- 수원[ ]삼성블루윙즈 수원월드컵경기장 
select concat(region_name, ' ',team_name)  as '팀 명' , stadium_name as 홈구장 
from stadium s join team t using (stadium_id);
 
-- 문제 12
-- 수원팀(K02) 과 대전팀(K10) 선수들 중
-- 키가 180 이상 183 이하인 선수들
-- 키, 팀명, 사람명 오름차순
 
select height,team_name,player_name 
from player p join team t using(team_id)
where (team_id like 'K02' or team_id like 'K10')
and height between 180 and 183
order by 1,2,3;
 
-- 문제 13
-- 모든 선수들 중 포지션을 배정 받지 못한 선수들의 
-- 팀명과 선수이름 출력 둘다 오름차순
select team_name, player_name
from team t join player p using (team_id)
where position like ''
order by 1, 2
 
-- 문제 14
-- 팀과 스타디움, 스케줄을 조인하여
-- 2012년 3월 17일에 열린 각 경기의
-- 팀이름, 스타디움, 어웨이팀 이름 출력
-- 다중테이블 join 을 찾아서 해결하시오.
 
SELECT t.team_name AS , s.stadium_name, 
(select team_name from team where team_id like sc.awayteam_id) as AWAYTEAM
from team t 
join stadium s using(stadium_id)
join schedule sc using(stadium_id)
where sc.sche_date like '20120317';
 
-- 문제 15 
-- 2012년 3월 17일 경기에
-- 포항 스틸러스 소속 골키퍼(GK)
-- 선수, 포지션,팀명 (연고지포함),
-- 스타디움, 경기날짜를 구하시오
-- 연고지와 팀이름은 간격을 띄우시오(수원[]삼성블루윙즈)
SELECT P.PLAYER_NAME,P.POSITION,
CONCAT(T.TEAM_NAME ,'   ' ,T.REGION_NAME) AS 팀명,S.STADIUM_NAME,C.SCHE_DATE
FROM STADIUM S
JOIN SCHEDULE C USING (STADIUM_ID)
JOIN TEAM T USING (STADIUM_ID)
JOIN PLAYER P USING(TEAM_ID)
WHERE C.SCHE_DATE LIKE '20120317'
AND T.REGION_NAME LIKE '포%'
AND P.POSITION LIKE 'GK';
-- 문제 16 
-- 홈팀이 3점이상 차이로 승리한 경기의
-- 경기장 이름, 경기 일정
-- 홈팀 이름과 원정팀 이름을
-- 구하시오
select s.stadium_name, sch.sche_date, 
    (select team_name from team where team_id like sch.hometeam_id) as 홈팀, 
    (select team_name from team where team_id like sch.awayteam_id) as 원정팀
from team t 
    join stadium s using (stadium_id)
    join schedule sch using (stadium_id)
where ((select sch.home_score)-(select sch.away_score)) >= 3;
 
 
-- 문제 17 
-- STADIUM 에 등록된 운동장 중에서
-- 홈팀이 없는 경기장까지 전부 나오도록
-- 카운트 값은 19
-- 힌트 : LEFT JOIN 사용해야함
 
select s.stadium_name, t.team_name
from stadium s left join team t using (stadium_id);

 

-- 문제 18 (페이지네이션) limit 0부터, 5개
 
select player_name from player
order by player_name limit 0, 5
; 
 
-- 문제 19 (그룹바이: 집계함수 - 딱 5개 min, max, count, sum, avg)
-- 평균키가 인천 유나이티스팀('K04')의 평균키  보다 작은 팀의
-- 팀ID, 팀명, 평균키 추출
-- 인천 유나이티스팀의 평균키 -- 176.59  
-- 키와 몸무게가 없는 칸은 0 값으로 처리한 후 평균값에 
-- 포함되지 않도록 하세요.
 
update player set height = '0', weight = '0'
where height like '' or weight like '';
-- 수정후 쿼리
 
select t.team_id 팀ID, t.team_name 팀명, round(avg(p.height), 2) 평균키
from team t join player p using (team_id)
where p.height != '0'
group by team_id
having round(avg(p.height), 2) < (select round(avg(height), 2) a
                                    from team t join player p using (team_id)
                                    where team_id like 'K04' and height != '0')
;

 

-- 문제 20
-- 포지션이 MF 인 선수들의 소속팀명 및  선수명, 백넘버 출력
 
select t.team_name 팀명, p.player_name 선수명, p.back_no 백넘버 from team t 
join player p using(team_id)
where position like 'MF'
 
-- 문제 21
-- 가장 키큰 선수 5명 소속팀명 및  선수명, 백넘버 출력, 
-- 단 키  값이 없으면 제외
 
select t.team_name 팀명, p.player_name 선수명, p.back_no 백넘버 from team t 
join player p using(team_id)
order by height desc limit 0,5
 
 
-- 문제 22
-- 선수 자신이 속한 팀의 평균키보다 작은  선수 정보 출력
-- (select round(avg(height),2) from player)
 
select p.*
from player p 
where height < (select round(avg(p2.height), 2) 
                        from player p2
                        where p.team_id like p2.team_id
                        and p2.height != '0'
                        group by p2.team_id);
 
-- 문제 23
-- 2012년 5월 한달간 경기가 있는 경기장  조회
select sche_date 날짜 , stadium_name 경기장
from stadium s join schedule sc using(stadium_id)
where  sche_date like '201205%'
;