MySQL 프로젝트 쿼리문 정리 페이지

- 현재 소속팀(Current_Team) 이 AtleticoMadrid 인 선수들의 이름(game_played.Player) 과 출전경기수(game_played.Game_played)

SELECT game_played.Player,game_played.Game_played FROM laliga.game_played left join laliga.player on Game_played.Player = Player.Name where Current_Team = 'AtleticoMadrid';

 

- Per_SGoW가 20 이상이며 포지션이 defender인 선수명과 포지션 Per_SGoW 출력하기

( player 테이블 , game_played 테이블 조인 ) 

SELECT  game_played.Player,player.Position, game_played.Per_SGoW FROM laliga.game_played left join laliga.player on game_played.Player = Player.Name where Position = 'Defender' AND Per_SGoW >20;

 

 

매트릭스 1

팀이 이기고 있을 시 교체투입률이  30% 이상인 선수 중, 포지션이 수비수이며 Successful tackles(태클성공)횟수와 recovery(수비성공), Interceptions(가로채기) 의 총합이 가장 높은 선수가 속한 팀의 감독명  

SELECT team.Coach 
 FROM laliga.team 
 WHERE team.Team = ( SELECT player.Current_Team 
 FROM laliga.player
 WHERE player.Name = (SELECT  player.Name
  FROM laliga.game_played inner join laliga.player on game_played.Player = player.Name inner join laliga.player_record on player.Name = player_record.Player 
  where Position = 'Defender' AND Per_SGoW >30
  ORDER BY player_record.Successful_tackles + player_record.Recovery + player_record.Interceptions desc limit 1  )); 

 

매트릭스 2

상위 5개팀에 속한 선수중, 포지션이 공격수 또는 미드필더이며 총 경기 출장횟수가 30회 이상이고 (골 / 어시스트 / 페널티유도성공)의 합이 가장 높은 선수의 이름과 소속팀, 등번호 

 

SELECT player.Name, player.Current_Team, player.Shirt_number 
FROM laliga.player  inner join laliga.player_record on player.Name = player_record.Player
where player.Name = any(        
SELECT player.Name 
                        FROM laliga.team inner join laliga.player on team.Team = player.Current_Team inner join laliga.game_played on player.Name = game_played.Player
                        where team.Rank <=5 AND ( player.Position = 'Midfielder' OR player.Position = 'Forward') AND game_played.Game_played >30   
                         )  
order by player_record.Goal + player_record.Assists + player_record.Penalties_won desc;