Daily Routine

CodeKata

SQL: CODEKATA

41. 조건에 맞는 도서 리스트 출력하기

• 작성한 코드

SELECT
 book_id
 , DATE_FORMAT(published_date, '%Y-%m-%d') AS PUBLISHED_DATE
FROM
  book
WHERE
  YEAR(published_date) = '2021'
  AND category = '인문'
ORDER BY
  published_date
;

• 테이블 published_date 확인해 보면 YYYY-MM-DD HH:MI:SS 형식인데 결과에는 YYYY-MM-DD만 필요함

  • DATE_FORMAT 부분을 SUBSTR(published_date, 1, 10)로 해도 돌아감

• 인라인 뷰로 풀어보기

  • 인라인 뷰(Inline View) 서브쿼리: 서브쿼리에서 조회한 결과를 하나의 테이블처럼 사용하고 싶을 때 사용

SELECT
  b.book_id
  , DATE_FORMAT(b.published_date,'%Y-%m-%d') AS PUBLISHED_DATE
FROM
  (
  SELECT
      book_id
      , published_date
  FROM
      book
  WHERE
      YEAR(published_date) = '2021'
      AND category = '인문'
  ) b
ORDER BY
 b.published_date
;

31. 오랜 기간 보호한 동물(1)

• 작성한 코드

SELECT
  ai.name
  , ai.datetime
FROM
  animal_ins ai LEFT JOIN animal_outs ao USING(animal_id)
WHERE
  ao.datetime IS NULL
ORDER BY
  ai.datetime
LIMIT 3
;

-추가: IN 연산자 서브쿼리 이용

SELECT
  name
  , datetime
FROM
  animal_ins
WHERE
  animal_id
  NOT IN (
      SELECT
        animal_id
      FROM
        animal_outs
  )
ORDER BY
  datetime
LIMIT
  3
;

과제 풀기

문제 1: 조건절 where 구문의 활용

조건1) first_login_date 컬럼이 2023-01-01 초과인 날짜의

• game_account_id, game_actor_id, serverno 를 추출

SELECT
  game_account_id 
  , game_actor_id 
  , serverno 
FROM
  basic.users
WHERE
  first_login_date > '2023-01-01'
;

• WHERE절 문자열 비교가 가능한 이유 → first_login_date 컬럼의 정보가 문자열 타입이면서 형식만 날짜형임(로그정의서 보면 schema가 string인 것을 확인 가능)

  • 참고: 문자열 형태의 날짜 비교하기

  --- date컬럼에 정보가 문자열 타입이면서 형식만 날짜형이라면
  SELECT * FROM BOARD
  WHERE date <= date_format(now(),'%Y-%m-%d');
  
  -- date컬럼 정보가 datetime이고 형식이 %Y-%m-%d 이라면
  SELECT * FROM BOARD
  WHERE date <= str_to_date(now(),'%Y-%m-%d');
  
  -- date컬럼 정보가 date일 경우
  SELECT * FROM BOARD
  WHERE date <= NOW();

문제 2: 조건절 where 구문의 응용

조건1) level 컬럼이 10 초과이고
조건2) serverno 컬럼이 1이 아니며
조건3) 아이템 이름컬럼이 레벨업 패키지 또는 시즌패스이고
조건4) 아이템 획득 경로가 상점에서 구매한 경우의

• first_login_date, ip_addr, exp, zone_id 를 추출하고 결과값을 first_login_date, ip_addr 기준 내림차순으로 정렬

SELECT 
  first_login_date 
  , ip_addr 
  , exp 
  , zone_id 
FROM 
  basic.users
WHERE 
  `level` > 10
  AND serverno <> 1
  AND etc_str2 IN ('레벨업 패키지', '시즌패스')
  AND etc_str1 = '상점에서 구매'
ORDER BY 
  first_login_date 
  , ip_addr DESC
;

• 컬럼명이 예약어이거나 띄어쓰기나 특수문자를 포함할 때 해당 컬럼을 억음부호(grave accent; backtick)로 감싸서 사용할 수 있음

  • 예약어나 공백 포함 컬럼명을 참조해야 할 때 사용
    • 일반적으로 컬럼명에 공백이나 특수 문자를 포함시키는 것은 가독성을 해치고 복잡성을 증가시킬 수 있으므로 지양해야 함
  • 억음부호를 사용하여 컬럼명을 감싸면 MySQL은 해당 이름을 하나의 식별자로 인식함

• level이 예약어인가 찾아봤더니 "CONNECT BY level"로 증가하거나 감소하는 데이터를 동적으로 생성할 때 사용한다고 함

  • 그런데 억음부호 빼고 쿼리 돌려도 돌아가긴 함... 뭘까...

문제 3: 조건절 case when 구문의 활용

조건1) case when 구문을 사용하여 레벨구간을 아래와 같이 구분해주시고, as 를 사용하여 컬럼이름을 ‘levelgroup’ 으로 설정해주세요.

<레벨구간>
◦ 1~10Lv 이하
◦ 11~20Lv 이하
◦ 21~30Lv 이하
◦ 31~40Lv 이하
◦ 41~50Lv 이하
◦ 51~60Lv 이하
◦ 61~70Lv 이하
◦ 71~80Lv 이하
◦ 81~90Lv 이하
◦ 91~100Lv

• game_actor_id, level, levelgroup, first_login_date 컬럼을 추출해주시고, first_login_date를 기준으로 내림차순 정렬

SELECT 
  game_actor_id 
  , `level`
  , CASE
  	WHEN `level` BETWEEN 1 AND 10 THEN '1~10Lv 이하'
  	WHEN `level` BETWEEN 11 AND 20 THEN '11~20Lv 이하'
  	WHEN `level` BETWEEN 21 AND 30 THEN '21~30Lv 이하'
  	WHEN `level` BETWEEN 31 AND 40 THEN '31~40Lv 이하'
  	WHEN `level` BETWEEN 41 AND 50 THEN '41~50Lv 이하'
  	WHEN `level` BETWEEN 51 AND 60 THEN '51~60Lv 이하'
  	WHEN `level` BETWEEN 61 AND 70 THEN '61~70Lv 이하'
  	WHEN `level` BETWEEN 71 AND 80 THEN '71~80Lv 이하'
  	WHEN `level` BETWEEN 81 AND 90 THEN '81~90Lv 이하'
  	ELSE '91~100Lv'
  END AS levelgroup
  , first_login_date 
FROM 
  basic.users 
ORDER BY
  first_login_date DESC 
;