SQL 쿼리 연습문제

SQL을 연습해보기 위해서 연습용 DB와 연습문제 10개를 풀어봅니다.

sqlite3을 이용하며 사용한 연습문제와 연습용 DB는 전부 이 링크에서 가지고 왔습니다. CMU의 Intro to Database 수업의 과제로 나온 것입니다.

DB 스키마를 그림으로 나타낸 것은 아래와 같습니다.

해당 스키마에 대한 설명은 링크로 가시면 좀 더 자세하게 알아볼 수 있습니다.

1. SAMPLE

문제 : List all types of work ordered by type ascendingly.

work라는 것의 type을 type의 오름차순으로 출력해야 합니다. 위 스키마를 보면 work_type이라는 테이블? 릴레이션?이 있는데 name을 attribute로 가지고 있습니다. 이 name을 기준으로 오름차순으로 출력하라는 것이겠죠?

select name
from work_type
order by name;

work_type 테이블에서 name을 가져오고, 가져온 릴레이션을 order by로 오름차순 출력해줍니다. order by는 기본적으로 오름차순이기 때문에 따로 써줄 것은 없습니다. 쿼리 결과는 아래와 같습니다.

2. LONG NAME

문제 : List works with longest name of each type.

For each work type, find works that have the longest names. There might be cases where there is a tie for the longest names - in that case, return all of them. Display work names and corresponding type names, and order it according to work type (ascending) and use work name (ascending) as tie-breaker.

문제를 좀 더 자세하게 설명한 내용을 살펴봅시다. 각 work_type 별로 가장 긴 이름을 가진 work와 work_type을 출력하되 가장 긴 이름을 가진 work가 여러 개 있으면 전부 출력해야 합니다. 그리고 출력할 시에는 work_type의 오름차순, 그리고 work의 name 오름차순으로 출력하라고 합니다.

일단 work와 work_type 테이블을 사용해서 진행하면 될 거 같습니다.

각 work_type 별로 이름이 가장 긴 것의 길이부터 타입별로 구해야 할 거 같습니다. Aggregation을 잘 해야 될 거 같습니다. work 테이블에서 그룹화 해야될 속성은 work_type입니다. 아래와 같이 쿼리를 날리면 타입별로 가장 긴 길이인 이름의 길이를 얻을 수 있습니다.

select type, max(length(name))
from work
group by type

group by로 aggregation을 진행하고 나면 work_type의 name과 work의 name이 사라져버립니다.

join을 하든 조건문을 걸어서 찾아내든 해야 될 거 같습니다. 먼저 생각나는건 조건문입니다.

select work.name, work.type
from work
where (length(work.name), work.type) in (
    select max(length(name)), type
    from work
    group by type
)

이렇게 하면 일단 work의 이름은 나올 거 같은데 where (a, b) in (subquery) 처럼 tuple로 묶어서 조건문 in을 하는게 안된다고 한다. join을 차근차근 해야할 거 같다.

일단 work의 name과 work_type의 name이 최종적으로 원하는 것이니까 이 두 테이블은 join을 해야 될 것이다. 그리고 work 테이블은 타입별 최장 길이를 갖는 튜플들을 걸러내야 한다.

그래서 work와 group by를 한 것을 join하고 그 결과와 work_type을 join 하면 될 거 같다.

이를 반영하면 아래와 같이 쿼리문을 작성할 수 있다.

select work.name, work_type.name
from work
    join (
        select type, max(length(name)) as max_len
        from work
        group by type
    ) as tmp on length(work.name) = tmp.max_len and work.type = tmp.type
    join work_type on work_type.id = work.type
order by work.type, work.name

출력 순서도 order by로 까먹지 말고 체크하자.

3. OLD MUSIC NATIONS

문제 : List top 10 countries with the most classical music artists (born or started before 1850) along with the number of associated artists.

Print country and number of associated arists before 1850. For example, Russia|191. Sort by number of artists in descending order.

1850년 전에 태어나거나 활동한 예술가가 가장 많은 나라 10개를 예술가 수의 내림차순으로 출력하자. aritst의 begin_date_year로 1850년 전인지 판단할 수 있겠다.

출신 나라는 area라는 테이블을 통해서 확인하면 되겠다.

따라서, artist와 area를 area의 id 기준으로 조인해주고 거기서 begin_date_year가 1850 이전인 애들만 걸러준 다음에 artist의 area로 그룹화를 진행히주면 우리가 원하는 릴레이션이 나오게 된다.

이제 이 릴레이션을 예술가의 수의 내림차순으로 출력하되, 10개만 출력하면 된다. order by와 limit을 사용하면 된다.

select area.name, count(1) as cnt
from artist join area on artist.area = area.id
where artist.begin_date_year < 1850
group by artist.area
order by cnt desc
limit 10;

결과는 아래와 같다.

4. DUBBED SMASH

문제 : List the top 10 dubbed artist names with the number of dubs.

Count the number of distinct names in artist_alias for each artist in the artist table, and list only the top ten who's from the United Kingdom and started after 1950 (not included). Print the artist name in the artist table and the number of corresponding distinct dubbed artist names in the artist_alias table.

영국 출신의 1950년 이후에 활동한 예술가 중에서 예명? 가명?을 제일 많이 가진 10명의 이름과 예명의 수를 출력해야 한다.

출신 국가는 area 테이블이 가지고 있던 정보니까 일단 United Kingdom의 ID를 찾아보자.

select *
from area
where name like "United Kingdom";

United Kingdom의 ID는 221이다. 그러면 artist 중에서 영국 출신이면서 1950년 이후로 활동한 예술가를 걸러낸 다음에 artist_alias 테이블과 join을 해주면 1950년 이후로 활동한 영국 출신 예술가와 그 예명이 묶여 있는 테이블을 만들 수 있다.

그리고 artist의 name을 출력해야 되기 때문에 위에서 join한 테이블을 artist.id로 그룹핑 해준다음에 artist와 다시 join해서 name을 붙여놔야 한다.

select artist.name, cnt
from (select tmp.id as tmp_id, count(distinct artist_alias.name) as cnt
      from (select *
            from artist
            where artist.area = 221 and artist.begin_date_year > 1950) as tmp
           join
            artist_alias on artist_alias.artist = tmp.id
      group by tmp.id) join artist on artist.id = tmp_id
order by cnt desc
limit 10;

5. VINYL LOVER

문제 : List the distinct names of releases issued in vinyl format by the British band Coldplay.

Vinyl format includes ALL vinyl dimensions excluding VinylDisc. Sort the release names by release date ascendingly.

DB 구조를 다시 살펴보자. 콜드플레이가 vinyl format으로 발매한 것들의 이름을 출시 순으로 나열해야 한다.

일단 release를 살펴보면 artist_credit이란 정보를 들고 있는데 이는 곡을 발매한 주체 쯤으로 보면 된다. 곡을 발매한 주체는 여러명 있을 수도 있고 그렇기 때문에 artist_credit 또한 하나의 릴레이션으로 관리되고 있다.

그리고 artist_credit_name이란 릴레이션으로 credit에 등장하는 aritst의 이름을 알 수 있다.

그럼 release 중에서 Coldplay가 발매한 것들을 찾는 과정은 아래와 같이 이 세 테이블을 join하면 될 거 같다.

select *
from release as r
    join artist_credit as ac on r.artist_credit = ac.id
    join artist_credit_name as acn on acn.artist_credit = ac.id
where acn.name = "Coldplay"

위와 같이 작성하면 Coldplay가 발매한 release를 전부 가지고 올 수 있다.

이제 Vinyl format이 뭔지 살펴보자. Medium이란 테이블이 release와 관계가 있는데 아마 테이블명 그대로 발매 매체인듯 하다. 그리고 medium은 format이란 속성으로 medium_format과 관계가 있다. 정확히 어떤 건지는 모르겠으나 medium_format을 조회해보면 이게 발매 매체의 종류 같다.

medium_format의 name에서 Vinyl을 포함하고 있는 튜플들을 조회해보자.

select *
from medium_format
where name like "%Vinyl%"

결과는 아래와 같다.

VinylDisc를 빼라고 했으니 Vinyl 뒤에 어떤 문자가 오는 것은 제외하면 될 거 같다. 그러면 이제 Vinyl format으로 출시된 Coldplay의 release까지 갖고 오는 것을 할 수 있게 됐다.

추가적으로 출력 시에 release한 순서대로 발매하라고 했다. 이건 또 release_info에 담겨 있는 정보다. 이 테이블과도 join해서 이 정보를 가지고 와야 한다.

Distinct한 release name이라고 했으니 이것까지 잊지 않고 써주면 아래와 같은 쿼리로 해결 가능하다.

select distinct r.name
from release as r
    join artist_credit as ac on r.artist_credit = ac.id
    join artist_credit_name as acn on acn.artist_credit = ac.id
    join medium as m on r.id = m.release
    join medium_format as mf on m.format = mf.id
    join release_info as ri on r.id = ri.release
where acn.name = "Coldplay" and mf.name like "%Vinyl"
order by ri.date_year, ri.date_month, ri.date_day

글이 너무 길어지는거 같아서 제가 편하려고 일단 끊습니다. SQL 쿼리 짜는게 쉬울 줄 알았는데 처음 하는거라 은근히 헷갈리는 부분이 많군요