QCC 1회차

1번

country 테이블을 기반으로, "전년도" 국민총생산(GNP)이 없거나 전년 대비 GNP가 감소한 국가 중 인구가 1천만 명 이상인 국가의 수를 조회하는 쿼리를 작성하세요.

내가 쓴 답 : 

# GNP = 0 or 전년대비 GNP 감소 한 나라 중
# 인구 1천만 이상 
# 국가 수 조회 
SELECT COUNT(Name) country_count
FROM country 
WHERE (GNP = 0 OR GNP < GNPOld) 
	  AND Population >= 10000000 ;

정답 : 

# **GNPOld IS NULL** or 전년대비 GNP 감소 한 나라 중  
# 인구 1천만 이상 
# 국가 수 조회 
SELECT COUNT(Name) country_count
FROM country 
WHERE (**GNPOld IS NULL** OR GNP < GNPOld) 
	  AND Population >= 10000000 ;

- 정신 안 차리고 문제 읽어서 전년도를 안 읽었다..^^.. 

---

2번

city 테이블과 country 테이블을 사용하여 각 대륙에서 인구가 가장 많은 도시를 찾아, 해당 도시와 국가, 그리고 대륙의 정보를 조회하세요. 결과는 인구를 기준으로 내림차순 정렬해야 합니다.

내가 쓴 답 :

# 각 대륙에서 인구 수 많은 도시 조회
# 도시이름, 국가이름, 대륙이름, 인구수 
# 인구수 기준 내림차순 
WITH a AS (
	SELECT 
		co.Continent,
		MAX(ci.Population) AS max_population
	FROM 
		country co JOIN city ci ON co.Code = ci.CountryCode 
	GROUP BY co.Continent )
SELECT 
	ci.Name AS CityName,
	co.Name AS CountryName,
	co.Continent,
	MAX(ci.Population) Population
FROM 
	country co JOIN city ci ON co.Code = ci.CountryCode
GROUP BY co.Continent 
ORDER BY Population DESC ;

- 이렇게 쓰면 with절이 필요없고 그냥 쿼리만 실행해도 답이 똑같다. (틀렸다는 말)

📍 내 의도 : with절에서 각 대륙별 인구 수 많은 도시 뽑아낸 다음 조건에 붙이려고 했다. 

📍 실행이 안 된 이유 : with절도 join 시킨 후에 ci.Population = a.max_population 이 조건을 줬어야 했는데, join 안 시키고 냅다 붙여서 실행이 안 됨

- 결과 : 얼렁뚱땅 with 필요없는 이상한 쿼리짬

내가 의도한 정답 : 

WITH a AS (
	SELECT 
		co.Continent,
		MAX(ci.Population) AS max_population
	FROM 
		country co JOIN city ci ON co.Code = ci.CountryCode 
	GROUP BY co.Continent )
SELECT 
	ci.Name AS CityName,
	co.Name AS CountryName,
	co.Continent,
	ci.Population
FROM 
	country co 
	JOIN city ci ON co.Code = ci.CountryCode
	JOIN a  ON co.Continent = a.Continent
WHERE ci.Population = a.max_population
ORDER BY Population DESC ;

윈도우 함수 이용한 정답 : 

# city 테이블과 country 테이블 조인한 후 필요한 값만 조회
# row_number 함수를 이용해서 대륙별 인구수 많은 도시 번호를 매김
SELECT
	ci.Name AS CityName,
	co.Name AS CountryName,
	co.Continent,
	ci.Population,
	ROW_NUMBER() over (partition by co.Continent order by ci.Population DESC) AS rownum
FROM 
	country co JOIN city ci ON co.Code = ci.CountryCode

📍 여기서 바로 조건을 줄 수 없는 이유 :  실행순서가 where이 먼저 실행되기 때문에 서브쿼리로 묶어줘야함. 아님 with로

# 서브쿼리 사용
SELECT 
	CityName,
	CountryName,
	Continent,
	Population
FROM
	(SELECT
		ci.Name AS CityName,
		co.Name AS CountryName,
		co.Continent,
		ci.Population,
		ROW_NUMBER() over (partition by co.Continent order by ci.Population DESC) AS rownum
	FROM 
		country co JOIN city ci ON co.Code = ci.CountryCode) a
WHERE 
	rownum = 1
ORDER BY Population DESC ;

# with 사용
WITH a AS (
	SELECT
		ci.Name AS CityName,
		co.Name AS CountryName,
		co.Continent,
		ci.Population,
		ROW_NUMBER() over (partition by co.Continent order by ci.Population DESC) AS rownum
	FROM 
		country co JOIN city ci ON co.Code = ci.CountryCode)
SELECT 
	CityName,
	CountryName,
	Continent,
	Population
FROM a
WHERE 
	rownum = 1
ORDER BY Population DESC ;

- 상관서브쿼리는.. 일단 보류....