[프로그래머스/SQL] SQL 고득점 Kit

LIMIT

문제 - 상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME 
LIMIT 1

• 인덱스 지정

SELECT *
FROM 테이블명
LIMIT 0,3 0번부터 3개 뽑기

COUNT

문제 - 동물 수 구하기

• NULL은 숫자로 세지 않는다.

SELECT COUNT(*)
FROM ANIMAL_INS

MIN(), MAX(), AVG(), SUM(), LENGTH()

• select에서 사용
• 집계함수

CEILING(),ROUND(), FLOOR(), TRIM()

• 올림, 반올림, 버림, 내림

UPPER(), LOWER()

• 대문자로, 소문자로

SELECT UPPER(MEMBER_ID)
FROM REST_REVIEW
-- MIN09125@NAVER.COM

REPLACE

• 문자열 치환
• REPLACE('문자열', '치환할 문자열', '대체할 문자열')
• REPLACE('ABCDE', 'A', '1') -> '1BCDE'

DISTINCT

문제 - 중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS

CONCAT

• String 붙이기

SELECT CONCAT('문자열', ' ', '붙이기') 
FROM 테이블
-- 문자열 붙이기

GROUP BY

문제 - 고양이와 개는 몇 마리 있을까

• ASC 오름차순
• DESC 내림차순

SELECT ANIMAL_TYPE, count(*)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE 
ORDER BY ANIMAL_TYPE ASC

HAVING

문제 - 동명 동물 수 찾기

SELECT NAME, COUNT(NAME) 
FROM ANIMAL_INS
GROUP BY NAME 
HAVING COUNT(NAME) > 1
ORDER BY NAME

IF

• SELECT, WHERE절에서 사용 가능

SELECT IF(10>5, '크다', '작다') AS RESULT;

HOUR(DATETIME), MINUTE(),SECOND()

문제 - 입양 시각 구하기(1)

SELECT HOUR(DATETIME) , COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE 9 <= HOUR(DATETIME) and HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

SET

문제 - 입양 시각 구하기(2)

• SET: 어떤 변수에 특정 값을 할당할때 쓰는 명령어
• SET 사용시 대입 연산자 = 를 사용하고 그 외에는 := 를 사용해야 한다
• HOUR 변수를 -1로 선언하여서 22까지 +1씩 더해주었다. -1부터 더했기 때문에 0부터 23까지 생성이 된다.
• ANIMAL_OUTS 테이블에 있는 DATETIME 변수와 @HOUR 변수가 동일한 순간 카운트를 진행한다.

SET @HOUR = -1;
SELECT (@HOUR := @HOUR +1) AS HOUR,
    (SELECT COUNT(HOUR(DATETIME)) 
    FROM ANIMAL_OUTS 
    WHERE HOUR(DATETIME)=@HOUR) AS COUNT 
FROM ANIMAL_OUTS
WHERE @HOUR < 23;

IS NOT NULL

문제 - 이름이 있는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

IFNULL

문제 - NULL 처리하기

• NAME이 NULL일 때 No name 삽입

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

LEFT OUTER JOIN

문제 - 없어진 기록 찾기

SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS 
LEFT OUTER JOIN ANIMAL_INS
ON ANIMAL_OUTS.ANIMAL_ID = ANIMAL_INS.ANIMAL_ID
WHERE ANIMAL_INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_OUTS.ANIMAL_ID

SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)
ORDER BY ANIMAL_ID

문제 - 있었는데요 없었습니다.

• 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회
• 7월 > 6월

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS LEFT OUTER JOIN ANIMAL_OUTS 
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS , ANIMAL_OUTS 
WHERE ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
AND ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME

문제 - 오랜 기간 보호한 동물(1)

• where절 NULL 주의!

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS 
LEFT OUTER JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME
LIMIT 3 

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY INS.DATETIME
LIMIT 3

문제 - 보호소에서 중성화한 동물

• where절 조건 주의!

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS LEFT OUTER JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.SEX_UPON_OUTCOME != INS.SEX_UPON_INTAKE

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.ANIMAL_TYPE, ANIMAL_INS.NAME
FROM ANIMAL_INS, ANIMAL_OUTS
WHERE ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
AND ANIMAL_OUTS.SEX_UPON_OUTCOME != ANIMAL_INS.SEX_UPON_INTAKE

IN

문제 - 루시와 엘라 찾기

• where절 내 여러 값을 성정하고자 할 때 사용
• or와 유사한 효과

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ("Lucy","Ella","Pickle","Rogan","Sabrina","Mitty")
ORDER BY ANIMAL_ID

BETWEEN ~ AND

• where절 내 검색 조건으로 범위를 지정하고자 할 때 사용
• ~ 이상 ~ 이하

SELECT *
FROM PRODUCTS
WHERE PRICE BETWEEN 10 AND 20

LIKE

문제 - 이름에 el이 들어가는 동물 찾기

• 'a%': a로 시작되는 것
• 'a%%': a로 시작 되고 최소 3이상의 길이를 가진 것
• '_a%': 두번째 자리에 a가 들어가는 것
• '%R%R%' : R이 2개 있는건 다 가져오기
• where절과 함께 특정 패턴을 검색할 때 사용

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS
WHERE NAME LIKE '%EL%' AND ANIMAL_TYPE = "Dog"
ORDER BY NAME

LEFT,RIGHT, SUBSTRING

문제 - 카테고리 별 상품 개수 구하기

SELECT LEFT(PRODUCT_CODE,2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY ASC

CASE

문제 - 중성화 여부 파악하기

• SELECT절에 많이 쓰임
• CASE ~ END AS
• WHEN THEN ~ ELSE

SELECT ANIMAL_ID, NAME, 
CASE 
	WHEN (SEX_UPON_INTAKE LIKE "Neutered%" 
	OR SEX_UPON_INTAKE LIKE "Spayed%") THEN "O" 
    ELSE "X" 
END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

문제 - 가격대 별 상품 개수 구하기

• 10 <= a < 0 (❌)
• 10 <= a and a < 0 (⭕️)

SELECT 
    CASE 
        WHEN 0 <= PRICE AND PRICE < 10000 THEN 0
        WHEN 10000 <= PRICE AND PRICE < 20000 THEN 10000
        WHEN 20000 <= PRICE AND PRICE < 30000 THEN 20000
        WHEN 30000 <= PRICE AND PRICE < 40000 THEN 30000
        WHEN 40000 <= PRICE AND PRICE < 50000 THEN 40000
        WHEN 50000 <= PRICE AND PRICE < 60000 THEN 50000
        WHEN 60000 <= PRICE AND PRICE < 70000 THEN 60000
        WHEN 70000 <= PRICE AND PRICE < 80000 THEN 70000
        WHEN 80000 <= PRICE AND PRICE < 90000 THEN 80000
    END AS PRICE_GROUP
    , COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

DATETIME

문제 - 오랜 기간 보호한 동물(2)

• (입양일 - 보호 시작일)을 비교하면 가장 큰게 가장 오래 보호한 것
• order by에서 더하기 빼기로 날짜 비교 가능하다

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS LEFT OUTER JOIN ANIMAL_INS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
ORDER BY OUTS.DATETIME - INS.DATETIME DESC
LIMIT 2

SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_INS, ANIMAL_OUTS
WHERE ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
ORDER BY ANIMAL_OUTS.DATETIME - ANIMAL_INS.DATETIME DESC
LIMIT 2

문제 - DATETIME에서 DATE로 형 변환

• DATE 형변환
• 2018-01-22 14:32:00 -> 2018-01-22
• %Y %y(년도), %D %d(날짜), %M %m(월), %S(초), %T(hh:mm:ss형태), %hh
• 대문자,소문자 별로 출력 형식 다름 주의
  

SELECT ANIMAL_ID, NAME,  DATE_FORMAT(DATETIME,'%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

MONTH(), YEAR(), DAY()

문제 - 3월에 태어난 여성 회원 목록 출력하기

• 특정 월(月)만 뽑아내기
• DATE_OF_BIRTH = 1992-03-16 00:00:00 여기에서 3월만 뽑아내기
• YEAR(), DAY()

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3
AND TLNO IS NOT NULL
AND GENDER = 'W'
ORDER BY MEMBER_ID ASC

• 데이터가 2022-05-04 이런 형식일 때 2022년 5월 데이터만 뽑는 방법

  1. YEAR(PRODUCE_DATE) = 2022 AND MONTH(PRODUCE_DATE) = 5
  2. PRODUCE_DATE BETWEEN "2022-05-01" AND "2022-05-31"
  3. WHERE PRODUCE_DATE LIKE '2022-05%'
  4. PRODUCE_DATE LIKE '2022-05-%'

DATEDIFF

문제 - 조건별로 분류하여 주문상태 출력하기

• DATEDIFF(OUT_DATE,'2022-05-01'): OUT_DATE와 '2022-05-01' 날짜 차이를 int형으로 반환

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE,'%Y-%m-%d') AS OUT_DATE,
CASE 
    WHEN DATEDIFF(OUT_DATE,'2022-05-01') > 0 THEN '출고대기'
    WHEN DATEDIFF(OUT_DATE,'2022-05-01') <= 0 THEN '출고완료'
    WHEN OUT_DATE IS NULL THEN '출고미정'
END 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID ASC

https://moonsbeen.tistory.com/375
https://paris-in-the-rain.tistory.com/100
https://hello-i-t.tistory.com/119
https://hyunsix.tistory.com/entry/SQL-SQL-%EC%BD%94%EB%94%A9%ED%85%8C%EC%8A%A4%ED%8A%B8-%EB%8C%80%EB%B9%84-%ED%95%A8%EC%88%98-%EC%A0%95%EB%A6%AC
https://dallae7.tistory.com/121

https://gent.tistory.com/436