MySQL

MySQL 연습문제3

Posted by Seongkyun Yu on 2020-05-01
Estimated Reading Time 2 Minutes
Words 363 In Total
Viewed Times

Data set

https://dev.mysql.com/doc/index-other.html
위 링크에서 world, sakila, employee 데이터를 받아서 푼다.


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

1
2
3
4
5
use world;
SELECT name, population
FROM country
WHERE population > (SELECT population FROM country WHERE name = "mexico")
ORDER BY population DESC;

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

1
2
3
4
5
6
7
use world;
SELECT country.name, count(city.name) as count
FROM country, city
WHERE country.code = city.countrycode
GROUP BY country.name
ORDER BY count DESC
LIMIT 10;

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

1
2
3
4
5
6
7
8
use world;
SELECT language, sum(pop) as count
FROM (	SELECT countrylanguage.language, countrylanguage.countrycode, (countrylanguage.percentage / 100 * country.population) as pop
		FROM countrylanguage, country
		WHERE country.code =  countrylanguage.countrycode	) as langpop
GROUP BY language
ORDER BY count desc
LIMIT 10;

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

1
2
3
4
5
use world;
SELECT city.name, city.countrycode, country.name, (city.population / country.population) * 100 as percentage
FROM city, country
WHERE city.countrycode = country.code AND city.population >= country.population * 0.1 AND city.population >= 5000000
ORDER BY percentage DESC

Q5. 면적이 10000km^2 이상인 국가의 인구도(1km^2 당 인구수)를 구하고 인구도가 200이상인 국가들의 사용하고 있는 언어수가 5가지 이상인 나라를 조회 하세요.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
use world;
SELECT code, name, ROUND((population / surfaceArea)) as density
FROM country
WHERE surfaceArea >= 10000
ORDER BY density DESC;
SELECT den.name, count(countrylanguage.language) as language_count
FROM (	SELECT code, name, ROUND((population / surfaceArea)) as density
		FROM country
		WHERE surfaceArea >= 10000
		ORDER BY density DESC	) as den, countrylanguage
WHERE den.code = countrylanguage.countrycode AND den.density >= 200
GROUP BY den.name
HAVING language_count >= 5
ORDER BY language_count DESC;

Q6. 사용하는 언어가 3가지 이하인 국가중 도시인구가 300만 이상인 도시를 아래와 같이 조회하세요.

  • GROUP_CONCAT(LANGUAGE) 을 사용하면 group by 할때 문자열을 합쳐서 볼수 있습니다.
  • VIEW를 이용해서 query를 깔끔하게 수정하세요.
1
2
3
4
5
6
7
8
9
10
use world;
CREATE VIEW under_3lang AS
SELECT countrycode, count(language) as language_count, GROUP_CONCAT(LANGUAGE) as languages
FROM countrylanguage
GROUP BY countrycode
HAVING language_count <= 3;
select city.countrycode, city.name as city_name, city.population, country.name, under_3lang.language_count, under_3lang.languages
FROM city, under_3lang, country
WHERE city.countrycode = under_3lang.countrycode AND country.code = city.countrycode AND city.population >= 3000000
ORDER BY city.population DESC;

If you like this blog or find it useful for you, you are welcome to comment on it. You are also welcome to share this blog, so that more people can participate in it. If the images used in the blog infringe your copyright, please contact the author to delete them. Thank you !

FEATURED TAGS
MySQL
FRIENDS
ESC