[SQL] QCC - 2회차

• 진행 시간 : 2024. 11. 15. (금) 11:00 ~ 12:00
• 해설 : 2024. 11. 15. (금) 16:00 ~ 17:00

테이블 설명

world.city

국가에 대한 도시의 정보.

이름	데이터 타입	설명
ID	int	Primary Key (PK). 도시의 고유 식별자.
Name	varchar	도시의 이름.
CountryCode	char(3)	국가 코드를 나타내며, country 테이블의 Code와 Foreign Key 관계를 가집니다.
District	varchar	도시가 속한 구/행정 구역.
Population	int	도시의 인구 수.

world.country

전 세계 국가에 대한 정보.

이름	데이터 타입	설명
Code	char(3)	Primary Key (PK). 국가의 고유 식별자.
Name	varchar	국가의 이름.
Continent	varchar	국가가 속한 대륙.
Region	varchar	국가가 속한 지역.
SurfaceArea	float	국가의 면적(제곱 킬로미터 단위).
IndepYear	int	국가의 독립 연도.
Population	int	국가의 인구 수.
LifeExpectancy	float	국가의 평균 기대 수명.
GNP	float	국가의 국민총생산(Gross National Product).
GNPOld	float	이전 연도의 GNP.
LocalName	varchar	국가의 현지 이름.
GovernmentForm	varchar	국가의 정부 형태.
HeadOfState	varchar	국가의 국가 원수(예: 대통령, 왕 등).
Capital	int	수도 도시의 city ID.
Code2	char(2)	2자리 국가 코드.

• • city 테이블은 CountryCode를 통해 country 테이블과 외래 키 관계를 가집니다.
    각 도시는 특정 국가와 연결되어 있습니다.

문제 1

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

출력 값 예시

country_count (국가의 수)

+----------------+
| country_count  |
+----------------+
| ...            |
+----------------+

문제 2

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

출력 값 예시

CityName (도시 이름), CountryName (국가 이름), Continent (대륙 이름) Population (인구수)

+------------------+---------------------+---------------+------------+
| CityName         | CountryName         | Continent     | Population |
+------------------+---------------------+---------------+------------+
| Mumbai (Bombay)  | India               | Asia          | 10500000   |
| São Paulo        | Brazil              | South America | 9968485    |
| Ciudad de México | Mexico              | North America | 8591309    |
...
+------------------+---------------------+---------------+------------+

내 코드

SELECT co2.Name AS CityName, co2.Name AS CountryName, co2.Continent ,co2.Population 
FROM world.city ci JOIN (
		SELECT co1.Code, co1.Name, co1.Continent, co1.Population
        FROM country co1 JOIN (
        	SELECT Continent, MAX(Population) AS pop
	        FROM country
            GROUP BY Continent  ) sub 
            ON co1.Population  = sub.pop  AND co1.Continent = sub.Continent) co2
ON ci.CountryCode = co2.Code

터무니 없는 값이 나와버렸다. JOIN 도 제대로 되지 않았음.

틀려버렸다.

튜터님께서 해주신 해설 세션을 보면서 다시 풀어보았다.
내가 푼 것은 국가의 MAX 인구 수 였고 대륙 별 가장 인구수가 많은 도시의 인구수를 구해야 했기에 다르게 풀었어야 했다.

튜터님이 말씀처럼 알려주신 sql 문

SELECT co.Continent , MAX(ci.Population)
FROM city ci join country co
ON ci.CountryCode  = co.Code 
GROUP BY  Continent

다양한 접근 방법

1. RANK() over 를 사용한 방법

SELECT CityName, CountryName, Continent, Population
FROM(
	SELLECT ci.name, as CitiName, 
    co.name as CountryName, co.Continent, ci.Population,
    rank() over (partition by co.Continent order by ci.Population DESC) rnk
    FROM country co
    JOIN city ci
    ON co.code = ci.countrycode
) a
where rnk = 1
ORDER BY Population DESC

2. WITH() 사용법

with mp as(
	SELECT co.Continent, MAX(ci.populaion) max_population
	FROM country co JOIN city ci
    ON co.code = ci.countrycode
    GROUOP BY 1
)
SELCT ci.name as CityName, co.name AS CountryName,
co.Continent, ci.Population
FROM country co JOIN city ci
ON co.code = ci.countrycode
JOIN mp
ON co.Continent = mp.Continent
AND ci.population = mp.max_population
ORDER BY ci.Population DESC

3. WHERE 절 사용법

SELECT c.Name AS CityName, co.Name AS CountryName,
	co.Continent, c.Population
FROM city c JOIN country co
ON c.CountryCode = co.Code
WHERE c.Population = (
	SELECT MAX(c2.Population)
    FROM city c2 JOIN country co2
    ON c2.CountryCode = co2.Code
    WHERE co2.Continent = co.Continent
    )
ORDER BY c.Population DESC;

이 쿼리는 각 행에 전부 접근해 코드를 돌리기 때문에 성능이 좋지가 않아서 사용하길 권장되지 않는다.