0%

mySQL 공부 퀴즈 정리

Posted on Edited on In , Disqus:

world.sql 데이터로 푼 퀴즈

quiz1 : country 테이블에서 중복을 제거

1
2
SELECT DISTINCT(Continent)
FROM country;

quiz2 : 한국 도시 중에서 인구가 100만명 이상인 도시를 내림차순 으로 정리

1
2
3
4
SELECT Name, Population
FROM city
WHERE CountryCode = "KOR" AND Population > 1000000
ORDER BY Population DESC;

quiz3 : city 테이블에서 인구가 800만에서 1000만 사이의 도시이름, 나라 코드를 출력하고, 인구수순 내림차순으로 정리

1
2
3
4
SELECT Name, CountryCode, Population
FROM city
WHERE Population BETWEEN 8000000 AND 10000000
ORDER BY Population DESC;

quiz4: country 테이블에서 1940년 부터 1950년까지 독립한 국가를 조회하고 인구수순 내림차순으로 정리

NameIndep이란 컬럼을 새로 만들어서 이름(독립연도)가 들어가도록 만들었음 

1
2
3
4
SELECT Code, concat(Name,"(",IndepYear,")") as NameIndep, Continent, Population
FROM country
WHERE IndepYear BETWEEN 1940 AND 1950
ORDER BY IndepYear ASC, Population DESC;

quiz5 : countrylanguage 테이블에서 영어, 스페인어, 한국어를 95%이상 사용하는 나라를 오름차순으로 정리

1
2
3
4
5
SELECT CountryCode, Language, Percentage 
-- / 100 붙여서 브로드캐스팅 연산으로 1 = 100% 로 변경 가능
FROM countrylanguage
WHERE Language in ("English","Spanish","Korean") AND Percentage >= 95.0
ORDER BY Percentage;

quiz6 : country테이블에서 code가 A로 시작하고 governmentform에 Republic이 포함되는 데이터를 조회

1
2
3
SELECT Code, Name, Continent, GovernmentForm, Population
FROM country
WHERE Code like "A%" AND GovernmentForm like '%Republic%';

city 테이블에서 나라별 도시의 갯수를 출력

1
2
3
SELECT CountryCode, count(CountryCode)
FROM city
GROUP BY CountryCode;

countrylanguage 테이블에서 전체 언어의 갯수를 출력

1
2
SELECT count(distinct(Language))
FROM countrylanguage;

대륙별 인구수와 GNP의 최대값을 출력

1
2
3
SELECT continent, sum(population)
FROM country
GROUP BY continent;

대륙별 전체인구를 구하고 5억 이상인 대륙만 출력

1
2
3
4
SELECT continent,sum(population) as TotalPopulation
FROM country
GROUP BY continent
HAVING TotalPopulation > 500000000;

HAVING : GROUP BY가 실행되고 난 결과에 조건을 추가, 즉 GROUP BY 밑에(WHERE는 GROUP BY전에(위에)필터링)

world.sql 데이터로 푼 퀴즈

멕시코보다 인구가 많은 나라이름과 인구수를 조회후, 인구수 순으로 내림차순 정리

1
2
3
4
5
6
use world;

select name ,population
from country
order by population DESC
limit 0,10;

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

1
2
3
4
5
6
7
select country.name, count(city.countrycode) as count
from city
join country
on city.countrycode = country.code
group by country.name
order by count DESC
limit 0,10;

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

언어별 퍼센트가 있어서 계산후 합산 했었어야 함

1
2
3
4
5
6
7
select countrylanguage.Language,  round(sum(country.population * countrylanguage.Percentage)) as count
from countrylanguage
join country
on country.Code = countrylanguage.CountryCode
group by countrylanguage.Language
order by count DESC
limit 0,10;

나라 전체 인구의 10% 이상인 도시에서 도시인구가 500만이 넘는 도시를 아래와 같이 조사

1
2
3
4
5
6
7
8
9
select city.name, city.countrycode, country.name, round((city.population / country.population * 100),2) as percentage
from country
join (
	select name,countrycode, population
	from city
	where population >= 5000000) as city
on city.countrycode = country.code
order by percentage DESC
limit 0,6;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select name, count(language) as language_count
from countrylanguage
join (

	select country.code, country.name, round((country.population /surfacearea.SurfaceArea)) as density
	from country
		join (
			select code, name, surfacearea
			from country
			where surfacearea > 10000) as surfacearea
			
	on country.code = surfacearea.code
	having density >= 200
	order by density DESC) as test

on test.code = countrylanguage.countrycode
group by name
having language_count >= 5
order by language_count DESC;

가장 돈을 많이 지불한 상위 5명의 고객의 이름과 지불 금액 출력

1
2
3
4
5
6
7
select customer.customer_id,concat(customer.first_name,' ',customer.last_name) as full_name,sum(payment.amount) as amount
from customer
join payment
on payment.customer_id = customer.customer_id
group by customer.customer_id
order by amount DESC
limit 0,5;

배우별 영화 출연 횟수 출력 (상위 10개)

  • 컬럼: 배우이름, 출연횟수
  • 출연횟수 순으로 내림차순 
    1
    2
    3
    4
    5
    6
    7
    select actor.actor_id, concat(actor.first_name, ' ', actor.last_name) as full_name, count(concat(actor.first_name, ' ', actor.last_name)) as count
    from actor
    join film_actor
    on actor.actor_id = film_actor.actor_id
    group by actor.actor_id
    order by count DESC, full_name
    limit 0,10;
    #### 영화 카테고리별 수입 데이터를 내림차순으로 정렬 payment,rental,inventory,film_category,category 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    select merged_rental_id.name, sum(payment.amount) as total_amount
    from payment
    join
    		(select rental.rental_id, merged_inventory_id.name
    		from rental
    		join
    				(select inventory.inventory_id, inventory.film_id, merged_film_id.name
    				from inventory
    				join (
    						select category.category_id, category.name, film_category.film_id
    						from category
    						join film_category
    						on category.category_id = film_category.category_id) as merged_film_id
    						# as에서 지정해준 이름으로 안의 컬럼을 찾아 들어간다
    				on inventory.film_id = merged_film_id.film_id) as merged_inventory_id
    		on merged_inventory_id.inventory_id = rental.inventory_id) as merged_rental_id
    on merged_rental_id.rental_id = payment.rental_id
    group by name
    order by total_amount DESC
    limit 0,11;