MySQL 연습문제 풀기

1. 멕시코(Mexico)보다 인구가 많은 나라이름과 인구수를 조회하시고 인구수 순으로 내림차순하세요. (O)

select name, population
from country
where population > (
	select population 
	from country
	where code = 'MEX'
)
order by population desc;

--> where절에 서브쿼리 이용 (멕시코의 인구 가져오기)

 

 

2. 국가별 몇개의 도시가 있는지 조회하고 도시수 순으로 10위까지 내림차순하세요. (O)

select country.name, count(city.name) as count
from country
join city
on country.code = city.countrycode
group by country.name
order by count desc
limit 10;

 

3. 언어별 사용인구를 출력하고 언어 사용인구 순으로 10위까지 내림차순하세요. (△)

select countrylanguage.language, sum(country.population) as count
from country
join countrylanguage
on country.code = countrylanguage.countrycode
group by countrylanguage.language
order by count desc
limit 10;

--> population에 언어 사용 비율을 곱하지 않아서 틀림

select countrylanguage.language, 
		round(sum(country.population * (countrylanguage.percentage / 100)),0) as count
from country
join countrylanguage
on country.code = countrylanguage.countrycode
group by countrylanguage.language
order by count desc
limit 10;

모범답안 :

select sub.language, sum(sub.count) as count
from(
	select cl.countrycode, cl.language, cl.percentage, ct.population
    		, round(ct.population * cl.percentage * 0.01 ) as count
	from country as ct
	join countrylanguage as cl
	on ct.code = cl.countrycode
    ) as sub
group by language
order by count desc
limit 10;

--> from절에서 서브쿼리 사용

--> from절과 join절에서 alias를 써서 편하게 컬럼명을 쓸 수 있게 함

--> country테이블과 countrylanguage테이블을 countrycode가 같은 조건으로 inner join을 하고,

     countrycode, language, percentage, population, 언어별 나라인구(count로 alias)을 가져온 후

     이를 sub으로 alias한다.

--> from절에 이 sub절을 써준 뒤, 최종적으로 필요한 language와 아까 count로 alias한 것을 가져온다.

 

 

4. 나라 전체 인구의 10%이상인 도시에서 도시인구가 500만이 넘는 도시를 아래와 같이 조회 하세요. (O)

select sub.city_name, sub.countrycode, sub.country_name, round(sub.percentage, 2) as percentage
from
(
  select city.name as city_name, city.countrycode
  		  , city.population as city_population
		  , country.name as country_name
 		  , (city.population / country.population * 100) as percentage
  from city
  join country
  on city.countrycode = country.code
  having percentage >= 10
  )as sub
where sub.city_population >= 500 * 10000
order by percentage desc;

모범답안 1 (필터링 후 JOIN) :

select city.name, city.countrycode, country.name
		, round(city.population / country.population * 100, 2) as percentage
from (select * from city where population >= 500 * 10000) as city
join country
where city.countrycode = country.code
having percentage > 10
order by percentage desc;

--> 인구가 500만 이상인 도시를 city테이블에서 먼저 가져온 후 (필터링) country테이블과 조인

 

모범답안 2 (조인 이후 필터링) :

select city.name, city.countrycode, country.name
		, round(city.population / country.population * 100, 2) as percentage
from (select * from city where population >= 500 * 10000) as city, country
where city.countrycode = country.code
having percentage > 10
order by percentage desc;

 

5. 면적이 10000km^2 이상인 국가의 인구밀도(1km^2 당 인구수)를 구하고 인구밀도(density)가 200이상인
국가들의 사용하고 있는 언어가 2가지인 나라를 조회 하세요. (X) --- 주중에 또 다시 풀어보기!

출력 : 국가 이름, 인구밀도, 언어 수 출력

 

모범답안 :

select ct.name, max(ct.density) as density, count(cl.language) as language_count
	, group_concat(cl.language) as language_list
  from(
      #인구밀도 쿼리
      select code, name, round(population / surfacearea) as density 
      from country
      where surfacearea >= 10000
      having density >= 200
      order by density
  )as ct
join countrylanguage as cl
on ct.code = cl.countrycode
group by ct.name
having language_count = 2
order by language_count desc;

 --> country테이블과 countrylanguage 테이블을 조인해야 하므로,

     우선 country테이블로부터 인구밀도를 구하는 서브쿼리를 만들면서 country테이블의 컬럼인 code, name를

     인구밀도와 함께 구한다.

--> 작성한 서브쿼리를 ct로 alias하고 from절에 넣는다.

--> 서브쿼리인 ct와 countrylanguage를 cl로 alias하면서 국가코드가 같은 조건으로 join을 한다.

--> 조인 후 필요한 컬럼들을 가져온다. 나라의 이름, 인구밀도, 언어개수, 언어리스트를 가져오는 것이다.

     이 때 언어 리스트는 group_concat 함수로 language컬럼에 있는 데이터들을 합쳐 가져온다.

--> 국가별로 여러 언어가 있으니, 국가별로 그룹핑을 한 다음 문제의 조건에 맞게 having절을 통해 언어 개수가 2개인       조건을 준다.

 

 

6. 사용하는 언어가 3가지 이하인 국가중 도시인구가 300만 이상인 도시를 아래와 같이 조회하세요.  (△)
* GROUP_CONCAT(LANGUAGE) 을 사용하면 group by 할때 문자열을 합쳐서 볼수 있습니다.
* VIEW를 이용해서 query를 깔끔하게 수정하세요.

(countrycode, city_name, population, name, language_count, languages 컬럼 출력)

select ct.countrycode, ct.name as city_name, ct.population, country.name
		, cl.language_count, cl.languages
from (
	select countrycode, count(language) as language_count
			, group_concat(language) as languages
	from countrylanguage
	group by countrycode
	having language_count <= 3
    )as cl
join (select * from city where population >= 300 * 10000) as ct
on cl.countrycode = ct.countrycode
join country
on cl.countrycode = country.code
order by population desc;

--> language테이블에서 사용하는 언어가 3가지 이하인 국가들을 먼저 뽑아 서브쿼리로 만들어 from절에 넣기

--> join을 2번해 language테이블에서 가져온 서브쿼리와 city테이블, country테이블을 차례로 합쳐준다

 

여기서 view를 이용해 쿼리문을 짧게 다듬어보자.

create view city_language as
select ct.countrycode, ct.name as city_name, ct.population
		, cl.language_count, cl.languages
from (
	select countrycode, count(language) as language_count
			, group_concat(language) as languages
	from countrylanguage
	group by countrycode
	having language_count <= 3
    )as cl
join (
	select * from city where population >= 300 * 10000
) as ct
on cl.countrycode = ct.countrycode;

- 위에서 countrylanguage테이블에서 가져온 쿼리문과 city테이블을 조인한 구문을 city_language라는 이름의 view를 만들어 저장한다.

select city_language.countrycode
		, city_language.city_name
        , city_language.population
		, country.name
		, city_language.language_count
        , city_language.languages
from city_language
join country
on city_language.countrycode = country.code
order by population desc;

- 뷰를 이용해 뷰에 해당하는 부분을 싹 지우고 쿼리문을 다듬는다.

 

7. 한국와 미국의 인구와 GNP를 세로로 아래와 같이 나타내세요. (쿼리문에 국가 코드명을 문자열로 사용
해도 됩니다.) (O)

select "population" as "category"
		, round((select population from country where code = 'KOR')) as 'KOR'
		, round((select population from country where code = 'USA')) as 'USA'
union
select "gnp"
		, round((select gnp from country where code = 'KOR')) as 'KOR'
        , round((select gnp from country where code = 'USA')) as 'USA';

쿼리문의 결과는 다음과 같다.

 

8. sakila 데이터 베이스의 payment 테이블에서 수입(amount)의 총합을 아래와 같이 출력하세요. (X)

 

모범답안 : 

use sakila;
select sum(sub.date1) as "2005-05"
	, sum(sub.date2) as "2005-06"
	, sum(sub.date3) as "2005-07"
	, sum(sub.date4) as "2005-08"
	, sum(sub.date5) as "2006-02"
from(
    select
        sum(if(date_format(payment_date, "%Y-%m")="2005-05", amount, 0)) as date1,
        sum(if(date_format(payment_date, "%Y-%m")="2005-06", amount, 0)) as date2,
        sum(if(date_format(payment_date, "%Y-%m")="2005-07", amount, 0)) as date3,
        sum(if(date_format(payment_date, "%Y-%m")="2005-08", amount, 0)) as date4,
        sum(if(date_format(payment_date, "%Y-%m")="2006-02", amount, 0)) as date5,
        "tmp"
    from payment
    group by date_format(payment_date, "%Y-%m")
)as sub
group by tmp;

--> 여기서 from절에 들어간 서브쿼리문만 출력하면,

if구문에서 date_format으로 출력한 날짜가 "200X-0X"과 일치하면 amount를, 그렇지 않으면 0을 가져온 후,

가져온 amount값을 sum함수로 모두 더해 date1, date2, ... , date5로 alias하여 출력한 뒤,

date_format으로 그룹핑을 하여 이런 결과를 낸다.

 

--> 이러한 서브쿼리문을 가져와서 다시 컬럼들을 각각 sum함수로 다시 더해 date_format과 일치하는 날짜로 alias를 해준다.

 

 

==> 여기서 tmp 기능은 딱히 모르겠다.. 마지막에 group by를 tmp로 안해줘도 똑같은 결과가 출력되는데...?

 

9. 위의 결과에서 payment 테이블에서 월별 렌트 횟수 데이터를 추가하여 아래와 같이 출력하세요. (O)

select 
	"amount" as "category"
	,sum(sub.date1) as "2005-05"
	, sum(sub.date2) as "2005-06"
	, sum(sub.date3) as "2005-07"
	, sum(sub.date4) as "2005-08"
	, sum(sub.date5) as "2006-02"
from(
    select
        sum(if(date_format(payment_date, "%Y-%m")="2005-05", amount, 0)) as date1,
        sum(if(date_format(payment_date, "%Y-%m")="2005-06", amount, 0)) as date2,
        sum(if(date_format(payment_date, "%Y-%m")="2005-07", amount, 0)) as date3,
        sum(if(date_format(payment_date, "%Y-%m")="2005-08", amount, 0)) as date4,
        sum(if(date_format(payment_date, "%Y-%m")="2006-02", amount, 0)) as date5
    from payment
    group by date_format(payment_date, "%Y-%m")
)as sub
union
select
	"rent" as "category"
	, sum(sub2.rent1)
	, sum(sub2.rent2)
	, sum(sub2.rent3)
	, sum(sub2.rent4)
	, sum(sub2.rent5)
from(
select
	sum(if(date_format(payment_date, "%Y-%m")="2005-05", 1, 0)) as rent1
	, sum(if(date_format(payment_date, "%Y-%m")="2005-06", 1, 0)) as rent2
	, sum(if(date_format(payment_date, "%Y-%m")="2005-07", 1, 0)) as rent3
	, sum(if(date_format(payment_date, "%Y-%m")="2005-08", 1, 0)) as rent4
	, sum(if(date_format(payment_date, "%Y-%m")="2006-02", 1, 0)) as rent5
from payment
group by date_format(payment_date, "%Y-%m")
)as sub2;

--> 해내긴 했는데, 정말 정신없다.. 쿼리문을 쓸 때는 무조건 길게 쓰기보다 이렇게 정렬을 맞추어 쓰면 훨씬 가독성이 높아진다. 유지보수의 용이성을 위해 콤마는 데이터 단위로 앞에 붙여줘서 지울 때 한 줄을 통째로 지워도 따로 콤마작업을 하지 않아도 되게끔 한다.

 

--> amount에 해당하는 결과물과 rent에 해당하는 결과물을 각각 출력한다음 union으로 합쳐준다.