SQL query 구현 예제

 

Mondial_database.pdf

0.07MB

 

하... 다 작성했는데 4번부터 다 날아가서 딥빡... ㅡㅡ 다시 작성한다....

티스토리 개불안정하네 진짜 ㅡㅡ

 

 

[답]

1. distinct로 중복값은 제거하고 통계치를 구할 수 있다.

select avg(distinct population)
from country c join geodesert g on c.code=g.country;

2. max보다 작은 애들 중에서 max를 구하면 두번째 max가 구해진다.

select max(area)
from island
where area <
(select max(area)
from island);

3. 여러 테이블에 JOIN을 수행할때는 아래처럼 계속해서 붙여나가면 된다.

select c.code, c.area
from country c join religion r on c.code=r.country
	       join ethnicgroup e on c.code=e.country
where r.percentage > 50
and e.percentage > 50;

4. 이 문제부터는 상당히 까다롭다.

우선 필요한 테이블은,

deeper sea를 찾아내려면 depth를 알아야하므로 Sea가 필요하고, merge into정보를 알려면 mergesWith가 필요하다.

이 상태에서 deeper을 찾으려면, 즉 서로 depth를 비교하려면 sea 두개가 필요하다. 그래서 mergesWith의 각 sea1, sea2에 sea테이블을 두번 불러서 각각 join을 시켜준다.

 

그런데 테이블 정보에 나와있듯이, mergesWith는 대칭적이지가 않다. 즉 A바다와 B바다가 서로 인접하는 경우, mergesWith안에는 튜플A,B 그리고 튜플B,A중에 하나만 들어있다는 의미이다. 따라서 sea1.depth>sea2.depth에서 sea2과 sea1.depth<sea2.depth에서의 sea1을 각각 뽑아 union을 해주어야 전체 리스트를 도출할 수 있다.

select sea1
from mergesWith, sea s1, sea s2
where sea1=s1.name
and sea2=s2.name
and s1.depth < s2.depth
union
select sea2
from mergesWith, sea s1, sea s2
where sea1=s1.name
and sea2=s2.name
and s1.depth > s2.depth;

5.

select o.name, o.abbreviation
from organization o
where o.city not in (select c.capital
		     from country c
		     where c.code=o.country);

6. 인구수 백만명 이상인 경우와 강을 5개 가지고 있는 경우를 나눠서 쿼리를 구현한 후에 합치면 된다.

각 province별로 강 갯수를 세어야 하므로 group by는 province가 된다.

select g.province
from geoRiver g join province p on g.province = p.name
				and g.country = p.country
where p.population >= 1000000
group by g.province
having count(g.river)=5;

7. 모르겠어서 솔루션 보고 이해했다. with ~ as는 temporary view를 만드는 구문이고, with는 맨 처음 한번만 쓰면 그 이후에는 with없이 계속 여러개의 view를 만들어나갈 수 있다.

 

위의 4번 문제처럼 borders 테이블 또한 대칭이 아니기 때문에 대칭으로 만들어준다.

인접하는 국가명이 A로 시작하는 경우와 Z로 시작하는 경우의 view를 따로 만들어준 이유는 count를 각자 세기 위해서이다.

 

가장 까다로운 부분은 left outer join인데 그 이유는 인접국이 있는 국가리스트 전체는 유지하면서 해당 인접국이 A를 포함하는 경우의 갯수와 Z를 포함하는 경우의 갯수를 세고 싶은 경우이기 때문이다. 만약 그냥 join을 쓰게 되면 A를 포함하는 경우와 Z를 포함하는 경우 즉 match되는 튜플만 output으로 나오기 때문에 인접국이 있는 국가리스트 전체가 유지되지 않는다.

with neighborCountries(country1, country2) as(
select country1, country2
from borders
union
select country2, country1
from borders),

NumOfCountrystartingfromA(country1, country2) as(
select *
from neighborCountries
where country2 like 'A%'),

NumOfCountrystartingfromZ(country1, country2) as(
select *
from neighborCountries
where country2 like 'Z%')

select NC.country1, count(NA.country2), count(NZ.country2)
from neighborCountries NC left outer join NumOfCountrystartingfromA NA on NC.country1 = NA.country1
left outer join NumOfCountrystartingfromZ NZ on NC.country1=NZ.country1
group by NC.country1;

원래 맨 처음에 썼을 때는 이것보다 설명이 훨씬 길었는데 한번 날아가고 다시 쓰려니까 너무 빡쳐서 짧게 썼다.