[SolveSQL/난이도 2] Advent of SQL 2024(MySQL, SQLite)

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

SELECT title FROM film
WHERE
  (
    rating = 'R'
    OR rating = 'NC-17'
  )
  AND (
    title NOT LIKE '%A'
    AND title NOT LIKE '%E'
    AND title NOT LIKE '%I'
    AND title NOT LIKE '%O'
    AND title NOT LIKE '%U'
  )

 

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

_에는 이스케이프 문자(\)를 추가해줘야 하는데, SQLite는 추가로 ESCAPE '\'를 붙여줘야 한다고 한다.

SELECT DISTINCT(page_location) AS `page_location` FROM ga
WHERE page_location NOT LIKE '%\_%' ESCAPE '\'
ORDER BY `page_location` ASC

 

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

SELECT (A.name) AS `name` FROM companies A
JOIN games B
ON A.company_id = B.publisher_id
GROUP BY A.company_id
HAVING COUNT(DISTINCT(game_id)) >= 10

 

기증품 비율 계산하기

100.0을 먼저 곱해줘야 소숫점 이하의 숫자들이 사라지지 않는다.

SELECT
  ROUND(
    100.0 * (
      SELECT COUNT(DISTINCT(artwork_id)) FROM artworks
      WHERE credit LIKE '%gift%'
    ) / COUNT(DISTINCT(artwork_id)), 3
  ) AS `ratio`
FROM artworks

 

최대값을 가진 행 찾기

SELECT id FROM (SELECT id, X FROM points
ORDER BY X DESC LIMIT 1)
UNION
SELECT id FROM (SELECT id, Y FROM points
ORDER BY Y DESC LIMIT 1)
ORDER BY id ASC

 

3년간 들어온 소장품 집계하기

처음에는 classification으로 GROUP BY를 하고, acquisition_date의 년도가 2014, 2015, 2016인 데이터의 개수를 추출한 테이블 3개를 3중 JOIN하여 구하려고 했으나 3가지의 년도 데이터 중 하나라도 0인 경우가 존재하면 아예 JOIN이 되지 않았다.

찾아보니 SUM과 WHEN을 사용해서 년도가 2014, 2015, 2016인 데이터면 1, 아니면 0을 추출하여 그 합계를 각각 구하면 되는 것이었다.

벽이 느껴진다

SELECT classification
, SUM(CASE WHEN CAST(STRFTIME('%Y', DATE(acquisition_date)) AS INTEGER) = 2014 THEN 1 ELSE 0 END) AS `2014`
, SUM(CASE WHEN CAST(STRFTIME('%Y', DATE(acquisition_date)) AS INTEGER) = 2015 THEN 1 ELSE 0 END) AS `2015`
, SUM(CASE WHEN CAST(STRFTIME('%Y', DATE(acquisition_date)) AS INTEGER) = 2016 THEN 1 ELSE 0 END) AS `2016`
FROM artworks
GROUP BY classification
ORDER BY classification ASC