[SQL] sqlzoo 04 SELECT in SELECT - 2, 6, 7, 8, 9번 (subquery)

* 본 포스팅은 기존 네이버블로그에서 2021-05-03에 작성한 글을 정리한 것입니다. 
* sqlzoo 사이트에서 문제 원문을 확인하실 수 있습니다. 

---

문제에서는 다음의 table을 사용하여 쿼리를 작성하면 됩니다. 

---

2. Show the countries in Europe with a per capita GDP greater than 'United Kingdom'

SELECT name 
FROM world 
WHERE continent = 'Europe' 
AND gdp/population > ( 
  SELECT gdp/population
  FROM world 
  WHERE name = 'United Kingdom' 
);

 

위 문제에서는 조건 두 가지를 생각해야 합니다. 

 

1. Europe 안에 있는 국가여야할 것 -> where로 걸러냄

2. United Kingdom의 gdp/population보다 gdp/population이 커야 함 -> where로 걸러냄,

하지만 이때 United Kingdom의 gdp/population 값을 참조?해야하므로 서브 쿼리 사용

(약간 수학에서 변수 써야될 상황이랑 비슷)

​

영상 참고: https://www.youtube.com/watch?v=RELAqb4DlvY 

---

 

6. Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

 

null값일 수도 있다는 건, 값/0 형태가 될 수 있다는 뜻이므로 where gdp > 0 .. 이런 식으로 예외 처리를 해줘야 합니다. 

SELECT name 
FROM world 
where gdp > ALL (SELECT gdp 
                 FROM world 
                 where gdp > 0 
                 and continent = 'Europe'

 

---

7. Find the largest country (by area) in each continent, show the continent, the name and the area

SELECT continent, name, area FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0) /* correlated or synchronized sub-query  */

 

 

correlated query가 작동하는 원리는 다음과 같습니다(출처

• subquery를 포함하는 별도의 column이 생성됨 -> 이때 밖에서 온 x 값을 치환함

name        continent   area    population  gdp          subquery
Afghanistan Asia        652230  25500100    20343000000  (select area FROM world y WHERE y.continent='Asia' AND area>0)
Albania     Europe      28748   2831741     12960000000  (select area FROM world y WHERE y.continent='Europe' AND area>0)
Algeria     Africa      2381741 37100000    188681000000 (select area FROM world y WHERE y.continent='Africa' AND area>0)
Andorra     Europe      468     78115       3712000000   (select area FROM world y WHERE y.continent='Europe' AND area>0)
Angola      Africa      1246700 20609294    100990000000 (select area FROM world y WHERE y.continent='Africa' AND area>0)

subquery에 의해서 리턴된 값은 다음과 같습니다.

(해당 국가가 포함된 continent에서의 모든 국가의 area 값을 담아옴)

name        continent   area    population  gdp          subquery
Afghanistan Asia        652230  25500100    20343000000  (652230)
Albania     Europe      28748   2831741     12960000000  (28748,468)
Algeria     Africa      2381741 37100000    188681000000 (2381741,1246700)
Andorra     Europe      468     78115       3712000000   (28748,468)
Angola      Africa      1246700 20609294    100990000000 (2381741,1246700)

• ALL function에 의해, subquery로 리턴된 값 <- -> area column과 비교하게 됨
• where 구문으로는, ALL function에서 true가 리턴된 row만 걸러냄

정리하자면,

WHERE area >= ALL(~)
해당 국가의 area와 subquery로 리턴된 area 값들을 비교
subquery 리턴값에 들어있는 모든 area 값보다 해당 국가의 area 값이 커야 true가 반환됨

---

 

8. List each continent and the name of the country that comes first alphabetically.

SELECT continent, name FROM world x 
WHERE name = (
  SELECT min(name) FROM world y 
  WHERE y.continent = x.continent 
);

이건 min 함수를 써도 되고, order by limit 1으로 해도 가능 (상위 1개만 반환되게)

---

9. Find the continents where all countries have a population <= 25000000.

Then find the names of the countries associated with these continents. Show name, continent and population

SELECT name, continent, population
FROM world x 
WHERE 25000000 >= (
  SELECT max(population) 
  FROM world y 
  WHERE y.continent = x.continent 
);

 

---

출처: sqlzoo