QCC 2회차

https://query-test-mu.vercel.app/?set=2&problem=7

Q1.

내가 쓴 답안

select count(*) country_count
from country
where GNPOLD > GNP and 
      population >= 10000000 and 
      GNPOLD is not null and
      GNPOLD <> 0
      
      
      

튜터님 답안

SELECT COUNT(DISTINCT code) AS country_count
FROM qcc.country
WHERE GNPOld <> 0
and GNP - GNPOld < 0
and population >= 10000000

Q2.

내가 쓴 답안

select district,round(AVG(population),0) as average_population
from city
group by district
-- having count(*)>3
order by average_population DESC

튜터님 답안

SELECT district, ROUND(AVG(Population)) AS average_population
FROM qcc.city
GROUP BY district
HAVING COUNT(ID) >= 3
ORDER BY average_population DESC

도시가 3개 이상 존재하는 District를 구해야하는데 count(*)>3으로 해버렸다..

Q3.

내가 쓴 답안

select a.name as city_name,
       b.name as country_name,
       b.continent,
       max(a.population) as population
from city a
join country b
on a.countrycode = b.code
where a.name is not null
group by b.continent
order by 4 DESC

튜터님 답안

• RANK() 사용
  
• WITH구문 사용
  
• WITH구문 사용(원도우 함수 없이)
  

QCC2회차 녹화본

->20분 25초부터 3번문제 설명

중복이 없다면 RANK() 사용
중복이 있다면 ROW NUMBER() 사용