MySQL) SELECT문 연습

1. 전체 도시 수는 몇 개인가?

select count(*) from city;

2. 인구가 100만 이상인 도시의 수는?

select count(*) from city where population >=1000000;

3. 가장 인구가 많은 도시는?

select name from city order by population desc limit 1;

4. 인구가 500만 이상인 도시들의 이름과 인구수는?

select name,population from city where population >= 5000000;

-----------------------

5. 국가 테이블에서 아시아(Asia) 국가의 수는?

select count(*) from country where Continent='Asia';

6. 평균 기대수명이 75세 이상인 국가 수는? 

select count(*) from country where  LifeExpectancy>=75  ;

7. 기대수명(LifeExpectancy)이 가장 높은 국가는?order by, limit)

select name  from country order by LifeExpectancy desc limit 1;

8. 국가 테이블에서 전체 평균 인구수는?

select avg(population) from country;

9. 국가의 총 인구 수는?

select sum(population) from country;

10. 아프리카(Africa) 대륙의 총 인구 수는?

select sum(population) from country where Continent='Africa';

11. 면적이 1,000,000 이상인 국가들의 이름과 면적은?

select name,surfacearea from country where surfacearea>=1000000;

---------------------

12. 영어(English)를 사용하는 나라의 수는?

select   count(CountryCode) from countrylanguage where language='english';

13. 공식 언어가 영어인 국가 수는?

select  count(*) from countrylanguage where language='english' and IsOfficial='T';

14. 국가별 언어 수를 구하라. (group by, count 사용) 

select countryCode , count(*) from countrylanguage group by Countrycode ;

15. 언어 사용 비율이 90% 이상인 언어 목록

select  Language, Percentage  from countrylanguage where Percentage>=90;

----------------------

16. 도시 테이블에서 인구가 가장 적은 도시 5개는? (order by, limit 사용)

select name,population from city order by Population limit 5;

17. 도시 테이블에서 'Seoul'이라는 도시가 몇 개인가?

select count(*) from city where name = 'seoul';

18. 도시 테이블에서 국가 코드가 'KOR'인 도시의 평균 인구는?

select avg(population) from city where CountryCode='kor';

19. 도시 테이블에서 각 국가(CountryCode)별 도시 수는?(group by, count 사용)

select CountryCode, count(*) from city group by CountryCode ;

20. 도시 테이블에서 인구가 가장 많은 도시 10개의 이름과 인구 수를 내림차순 정렬하시오. (order by, limit 사용)

select name , population from city order by Population desc limit 10;