solvesql 난이도2 SQL _2탄

제목이 모음으로 끝나지 않는 영화

정답률 32.86% (893회 / 2718회)

DVD Rental Store 데이터베이스는 DVD 대여점의 관리 프로그램이 사용하는 데이터베이스입니다. 그 중 film 테이블은 DVD 대여점에서 취급하는 영화 정보를 담고 있습니다.

rating 컬럼에는 미국영화협회(Motion Picture Association, MPA)에서 운영하는 영화 등급 제도 데이터가 들어있습니다. 이는 영화의 내용을 연령대별로 분류하여 관람 적합성을 판단하는 시스템인데 각 등급은 아래 의미를 가집니다.

• G (General Audience): 모든 연령 관람 가능
• PG (Parental Guidance Suggested): 아동 관람 시 부모 지도 필요
• PG-13 (Parents Strongly Cautioned): 13세 미만 관람 시 부모의 주의 필요
• R (Restricted): 17세 미만은 부모 또는 21세 이상과 동반 관람만 가능
• NC-17 (No One 17 and Under Admitted): 18세 미만 관람 불가

17세 미만 학생이 DVD 대여점에 혼자와서는 대여할 수 없는 영화 중 제목이 모음(’A’, ‘E’, ‘I’, ‘O’, ‘U’)로 끝나지 않는 영화를 찾고 싶습니다. 조건에 맞는 영화 제목(title)을 출력하는 쿼리를 작성해주세요. 다른 컬럼은 출력되지 말아야 합니다.

 

 

select distinct

  (title)

from

  film

where

  rating in ('NC-17', 'R')

  and substring(title, -1) not in('A', 'E', 'I', 'O', 'U')

 

 

 

언더스코어(_)가 포함되지 않은 데이터 찾기

정답률 60.47% (702회 / 1161회)

데이터리안 블로그 GA 로그 (2022년 1월) 데이터베이스는 2022년 1월 데이터리안 웹사이트에서 Google Analytics 4 (GA4)를 이용해 수집한 사용자 행동 데이터입니다.

ga 테이블의 page_location 컬럼은 페이지 뷰, 클릭, 스크롤 등의 사용자 행동이 수집된 페이지 경로를 담고 있습니다. page_location 컬럼의 값이 언더스코어('_')를 포함하지 않는 경우만 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 page_location 컬럼만 있어야 하고, 중복되는 값은 1번만 나와야 하며 오름차순으로 정렬되어 있어야 합니다.

 

 

 

 

SELECT

  distinct page_location

FROM

  ga

WHERE

  page_location not like '%a_%' escape 'a'

ORDER BY

  page_location

;

 

 

게임을 10개 이상 발매한 퍼블리셔 찾기

정답률 57.18% (613회 / 1072회)

Video Game Sales with Ratings 데이터베이스에는 2016년까지 발매된 게임의 주요 정보와 판매량, 평점 정보를 담고 있습니다. 처음 개발한 게임의 출시를 앞두고 있는 당신은 경험 많은 퍼블리셔와 협업하기 위해 퍼블리셔로 참여한 게임이 10개 이상인 회사에게 협업 제안을 보내려고 합니다.

데이터베이스를 조회해 퍼블리셔로 참여한 게임이 10개 이상인 회사의 이름을 출력하는 쿼리를 작성해주세요. 쿼리 결과는 아래 컬럼을 포함하고 있어야 합니다.

• name: 퍼블리셔 이름

select

  name

from

  (

    select

      name,

      ct

    from

      companies as a

      left outer JOIN (

        select

          publisher_id,

          count(game_id) as ct

        from

          games

        group by

          publisher_id

      ) as b on a.company_id = b.publisher_id

  ) as c

where

  ct >= 10

 

 

기증품 비율 계산하기

정답률 45.56% (565회 / 1240회)

Museum of Modern Art Collection 데이터베이스는 미국 뉴욕의 근현대 미술관인 MoMA의 작품 정보를 담고 있습니다. artworks 테이블에서는 MoMA가 소장한 작품의 정보가 있습니다. 작품 정보 중 credit 컬럼에 'gift'라는 단어가 등장하는 작품은 MoMA가 다른 사람이나 단체로 부터 기증 받은 기증품이라는 뜻입니다.

이 테이블의 데이터를 활용해 소장품 중 부분 기증품을 포함한 기증품의 비율이 얼마나 되는지 계산하는 쿼리를 작성해주세요. 기증품의 비율은 백분율로 계산되어야하고, 소수점 아래 넷째 자리에서 반올림 해 셋째 자리까지 표시 되어야 합니다. 예를 들어, 전체 소장품 중 1/3의 작품이 기증품이라면 제출된 답안은 33.333 이어야 합니다.

결과 데이터 예시

ratio

33.333

 

 

select

  round(

    100.0 * (

      select

        count(distinct (artwork_id))

      from

        artworks

      where

        credit like '%gift%'

    ) / count(distinct (artwork_id)),

    3

  ) as ratio

from

  artworks

 

 

 

최대값을 가진 행 찾기

정답률 69.98% (366회 / 523회)

points 테이블은 프란시스 앤스컴이 만든 Anscombe's quartet 데이터를 담고 있습니다. 이 데이터는 quartet 컬럼에 의해 4개의 서브셋으로 나뉘어지고, 각 서브셋은 평균, 표본 분산, 상관계수 등이 거의 동일하나 데이터의 분포를 시각화하면 전혀 다른 분포를 가지는 특징이 있습니다.

points 테이블에서 가장 큰 x 값을 가지는 데이터와 가장 큰 y 값을 가지는 데이터의 id를 출력하는 쿼리를 작성해주세요. 결과는 오름차순으로 정렬해주세요.

 

 

select p.id

from points  as p

right outer join

(select max(x)as x ,max(y)as y

from points ) as x

on p.x=x.x or p.y=x.y

order by id