0%

mySQL quiz

Posted on Edited on In , Disqus:

world.sql 데이터로 푼 퀴즈

QUIZ

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

1
2
3
4
5
6
select country.code, city_population_languages.city_name, city_population_languages.population, country.name, city_population_languages.language_count, city_population_languages.languages
from country
join
	city_population_languages
on country.code = city_population_languages.countrycode
order by city_population_languages.population DESC;

VIEW를 이용한 분할관리 

1
2
3
4
5
6
7
8
9
10
11
12
create view city_population_languages as
select city_population.name as city_name, countrylanguage.countrycode,
	count(countrylanguage.language) as language_count, 
	group_concat(countrylanguage.language) as languages, 	city_population.population
	from countrylanguage
	join
		(select name, population, countrycode
		from city
		where population >= 3000000) as city_population
	on countrylanguage.countrycode = city_population.countrycode
	group by countrylanguage.CountryCode, city_population.population, city_population.name 
	having language_count <= 3;

group by 할때 ,로 여러개 동시에 group by 할수 있음 group by countrylanguage.CountryCode, city_population.population, city_population.name 처럼 여러개로 했었으면 더 빨리 풀수 있었음

QUIZ

,로 구분해서 좌측부터 겹치는 부분만 걸러가면서 오른쪽으로 진행 

1
2
3
4
5
6
7
8
select category.name, sum(payment.amount) as total_amount
from payment, rental, inventory, film_category, category
where payment.rental_id = rental.rental_id
	and rental.inventory_id = inventory.inventory_id
		and inventory.film_id = film_category.film_id
			and film_category.category_id = category.category_id
group by category.name
order by total_amount DESC;