230621_IL(I_Learned)_SQL

DataCamp Sub QUERY 복습 및 심화

스칼라 서브 쿼리를 사용한 필터링

• matches_2013_2014 Table 에서 home_goal과 away_goal의 합의 평균의 3배의 값보다 큰 전체 골이 있던 data열과 home_goal, away_goal열을 반환하라

SELECT
	date,
	home_goal,
	away_goal
FROM matches_2013_2014
WHERE (home_goal + away_goal) >
	(SELECT 3 * AVG(home_goal + away_goal)
		FROM matches_2013_2014);

목록이 있는 서브 쿼리를 사용하여 필터링

• match Table 에서 중복 되지 않는 hometeam_id 열 값에 속하지 않는 team Table 의 team_api_id들의 team_lona_name 열과 team_shrot_name 열을 반환하라

SELECT
	team_long_name,
	team_short_name
FROM team
WHERE team_api_id NOT IN
	(SELECT DISTINCT hometeam_id FROM match);

더 복잡한 서브 쿼리 조건으로 필터링

• math Table 에서 home_goal 값이 8이상인 hometeam_id 열 값들 중에 team Table의 team_api_id 열의 값이 속하는 team_lone_name, team_short_name 열의 값을 반환하라

SELECT
	team_long_name,
	team_short_name
FROM team
WHERE team_api_id IN
	(SELECT hometeam_id
	FROM match
	WHERE home_goal >= 8);

FROM에서 하위 쿼리 조인

• match Table 에서 home_goal과 away_goal 합이 10이상인 coutry_id, id 열 값들을 sub Table 로 임시이름을 정하고 country Table 을 c 로 임시이름을 정하고 c.id 와 sub.country_id 가 일치하는 값을 교집합(Inner JOIN) 을 한다.
• c Table 의 name 을 country_name 으로 바꾸고 sub Table 의 id 값들을 matches로 바꾸며 카운팅 하여 그룹화 한다

SELECT
  name AS country_name,
  COUNT(sub.id) AS matches
FROM country AS c
Inner JOIN (SELECT country_id, id
  FROM match
  WHERE (home_goal + away_goal) >= 10) AS sub
ON c.id = sub.country_id
GROUP BY country_name;

FROM의 하위 쿼리를 기반으로 구축

• m(match Table).country_id 와 c(country Table).id 를 LEFT JOIN 하여 c.name 을 country 로 바꾸고 m Table의 date, home_goal, away_goal 과 두 goal 값을 합친 total_goals 로 subq Table 로 반환하여 그중 total_golas 가 10 이상인 country, date, home_goal, away_goal 을 출력한다

SELECT
	country,
	date,
	home_goal,
	away_goal
FROM
	(SELECT c.name AS country,
		m.date,
		m.home_goal,
		m.away_goal,
		(m.home_goal + m.away_goal) AS total_goals
	FROM match AS m
	LEFT JOIN country AS c
	ON m.country_id = c.id) AS subq
WHERE total_goals >= 10;

계산을 위해 SELECT 절에 서브 쿼리 추가

SELECT
	ROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,
	ROUND(AVG(m.home_goal + m.away_goal) -
		(SELECT AVG(home_goal + away_goal)
		FROM match
		WHERE season = '2013/2014'),2) AS diff
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
WHERE m.season = '2013/2014'
GROUP BY l.name;