테이블 설명

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 테이블과 외래 키 관계를 가집니다.
  • 외래 키를 가지고 있는 테이블을 하위 테이블, 기본 키를 가지고 있는 테이블을 참조 테이블 또는 상위 테블이라고 함
    • city는 하위 테이블, country는 상위 테이블
  • 참고
• 각 도시는 특정 국가와 연결되어 있습니다.

문제 1

스파르타(주)의 글로벌 확장을 준비 중인 팀은 신규 시장에 진출하기 위한 전략을 수립하고 있습니다. 국가 타게팅를 통해 잠재적 고객층을 확대하고자 합니다. 이에 따라, 대상이 되는 국가의 수를 먼저 정확히 파악하기 위해 데이터를 활용해 보고서를 작성하고 있습니다.
country 테이블을 기반으로, 전년도 국민총생산(GNP)이 없거나 전년 대비 GNP가 감소한 국가 중 인구가 1천만 명 이상인 국가의 수를 조회하는 쿼리를 작성하세요.

출력 값 예시

• country_count (국가의 수)
  

해설

전년도 국민총생산(GNP)이 없거나 전년 대비 GNP가 감소한 국가 중 인구가 1천만 명 이상인 국가의 수
→ WHERE (GNPOld IS NULL OR GNP < GNPOld) AND population >= 10000000

문제 2

스파르타(주) 마케팅 팀은 지역별 시장 특성을 분석하기 위해 각 대륙에서 인구가 가장 많은 도시를 조사하고자 합니다. 이를 통해 각 대륙의 주요 도시를 타겟으로 한 마케팅 전략을 수립하려고 합니다. city 테이블과 country 테이블을 사용하여 각 대륙에서 인구가 가장 많은 도시를 찾아, 해당 도시와 국가, 그리고 대륙의 정보를 조회하세요. 결과는 인구를 기준으로 내림차순 정렬해야 합니다.

출력 값 예시

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

해설

• 윈도우 함수 없이 푸는 것도 가능

WITH mp AS(
  SELECT
    co.Continent, max(ci.population) max_population
  FROM
    country co
    JOIN city ci
    ON co.code = ci.countrycode
  GROUP BY 1
)
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 mp
  on co.Continent = mp.Continent
  AND ci.population = mp.max_population
ORDER BY 4 DESC
;

• WHERE절에 조건을 걸어도 됨
  

해설 코드

1번 문제

SELECT COUNT(DISTINCT code) AS country_count
FROM country
WHERE (GNP - GNPOld < 0 or GNPOld is null) 
and population >= 10000000; -- 34

2번 문제

---- 방법 1. 
SELECT 
    CityName,
    CountryName,
    Continent,
    Population
FROM (
    SELECT 
        c.Name AS CityName,
        co.Name AS CountryName,
        co.Continent,
        c.Population,
        RANK() OVER(PARTITION BY co.Continent ORDER BY c.Population DESC) AS PopulationRank
    FROM 
        city c
    JOIN 
        country co ON c.CountryCode = co.Code
) ranked_cities
WHERE 
    PopulationRank = 1
ORDER BY 
    Population DESC;
   
-- 방법 2.  
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;


-- 방법 3. 
SELECT 
    c.Name AS CityName,
    co.Name AS CountryName,
    co.Continent,
    c.Population
FROM 
    city c
JOIN 
    country co ON c.CountryCode = co.Code
JOIN (
    SELECT 
        co.Continent,
        MAX(c.Population) AS MaxPopulation
    FROM 
        city c
    JOIN 
        country co ON c.CountryCode = co.Code
    GROUP BY 
        co.Continent
) max_pop ON co.Continent = max_pop.Continent AND c.Population = max_pop.MaxPopulation
ORDER BY 
    c.Population DESC;

내가 작성한 쿼리

문제 1

SELECT 
  COUNT(*) AS country_count
FROM 
  country 
WHERE 
  (GNPOld IS NULL OR 0)
  OR (GNP < GNPOld AND Population >= 10000000)
;

문제 2

WITH continent_city_population AS (
  SELECT 
	city.Name AS CityName
	, country.Name AS CountryName
	, Continent
	, city.Population AS Population
	, DENSE_RANK () OVER (PARTITION BY Continent ORDER BY city.Population DESC) AS ranking
  FROM 
	city 
    JOIN country 
	ON city.CountryCode = country.Code 
)
SELECT
  CityName
  , CountryName
  , Continent
  , Population
FROM
  continent_city_population
WHERE 
  ranking = 1
ORDER BY 
  Population DESC 
;

# 대륙 7개인데 출력은 6개네 → Antarctica에 도시가 없나? 아니면 사람이 없나?
# city 테이블에 Antarctica 코드 가진 도시 없음

# 추가: 호기심 해결 → 대륙별로
WITH continent_city_population AS (
  SELECT 
	city.Name AS CityName
	, country.Name AS CountryName
	, Continent
	, city.Population AS Population
	, DENSE_RANK () OVER (PARTITION BY Continent ORDER BY city.Population DESC) AS ranking
  FROM 
	country 
	LEFT JOIN city
	ON country.Code = city.CountryCode
)
SELECT
  CityName
  , CountryName
  , Continent
  , Population
FROM
  continent_city_population
WHERE 
  ranking = 1
ORDER BY 
  Population DESC 
;

참고: 다른 사람 풀이

다른 사람 풀이라 비공개로~

KeyPoint

• SQL 실행 순서 잘 기억하기!
  1. FROM : 각 테이블 확인
  2. ON : 조인 조건 확인
  3. JOIN : 테이블 조인 (병합)
  4. WHERE : 데이터 추출 조건 확인
  5. GROUP BY : 특정 칼럼으로 데이터 그룹화
  6. HAVING : 그룹화 이후 데이터 추출 조건 확인
  7. SELECT : 데이터 추출
  8. DISTINCT : 중복 제거
  9. ORDER BY : 데이터 정렬

해설 추가

• db가 여러 개일 때에는 use (db이름);으로 사용할 db 지정해주어야 함!

Feedback

• 전년도 국민총생산(GNP)이 없거나 전년 대비 GNP가 감소한 국가 중 인구가 1천만 명 이상인 국가의 수
  가 아니고
  전년도 국민총생산(GNP)이 없거나 전년 대비 GNP가 감소한 국가 중 인구가 1천만 명 이상인 국가의 수
  🡆 한국어… 어렵다…

다시 풀어보기

문제 1

SELECT 
  COUNT(*) AS country_count
FROM 
  country 
WHERE 
  (GNPOld IS NULL
  OR GNP < GNPOld)
  AND Population >= 10000000
;

문제 2

WITH continent_city_population AS (
  SELECT 
	ci.Name AS CityName
	, co.Name AS CountryName
	, co.Continent
	, ci.Population AS Population
	, RANK() OVER (PARTITION BY co.Continent ORDER BY ci.Population DESC) AS rnk
  FROM 
	city ci
    JOIN country co 
	ON ci.CountryCode = co.Code 
)
SELECT
  CityName
  , CountryName
  , Continent
  , Population
FROM
  continent_city_population
WHERE 
  rnk = 1
ORDER BY 
  Population DESC 
;