프로그래머스 SQL 오답 정리

프로그래머스 SQL고득점Kit의 1~2레벨 문제
오답 개념 정리

SELECT

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

• DATE_FORMAT(컬럼명, 날짜형식)

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY = '인문' AND YEAR(PUBLISHED_DATE) = 2021
ORDER BY PUBLISHED_DATE;

조건에 부합하는 중고거래 댓글 조회하기

• '2022년 10월에 작성된 게시글 제목'을 찾을 때, 댓글 테이블이 아닌 게시글 테이블에 조건 걸기

WHERE A.CREATED_DATE LIKE '2022-10%'	--B.CREATED_DATE가 아님

평균 일일 대여 요금 구하기

• 반올림 함수 : ROUND(소수, N번째 자리까지 출력) _N번째 자리 이하로는 반올림

12세 이하인 여자 환자 출력하기

• 검색결과가 NULL인 경우 출력값 : IFNULL(컬럼값, '출력단어')

SELECT  IFNULL(TLNO, 'NONE') AS TLNO

ORACLE : NVL()
MYSQL : IFNULL()

재구매가 일어난 상품과 회원 리스트 구하기

• 동일한 회원이 동일한 상품을 재 주문한 경우 : GROUP BY에 컬럼 2개를 사용

GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2

어린 동물 찾기

• ~가 아닌 컬럼 검색 : NOT, NOT IN, !=

WHERE NOT INTAKE_CONDITION = 'Aged'
WHERE INTAKE_CONDITION NOT IN ('Aged')
WHERE INTAKE_CONDITION != 'Aged'

상위 n개 레코드

• 행 n개 추출하기 : LIMIT

WHERE LIMIT 1

ORACLE : ROWNUM
MYSQL : LIMIT

* ROWNUM과 LIMIT 차이

WHERE ROWNUM <= 1 -- 정렬 전 수행
ORDER BY ID;

ORDER BY ID
LIMIT 1; -- 정렬 후 수행

업그레이드 된 아이템 구하기

• 서브쿼리(WHERE절)
  * 조인 후,
  WHERE (서브쿼리: item_info에서 RARE인 item_id)를 item_tree의 parent_item_id로 갖는 컬럼을 SELECT

SELECT A.ITEM_ID, A.ITEM_NAME, A.RARITY
FROM ITEM_INFO AS A
JOIN ITEM_TREE AS B
ON A.ITEM_ID = B.ITEM_ID
WHERE B.PARENT_ITEM_ID IN (SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE')
ORDER BY A.ITEM_ID DESC;

조건에 맞는 개발자 찾기

• 비트 연산자

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#')
OR SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python')
ORDER BY ID;

* WHERE절 설명: DEVELOPERS.SKILL_CODE & SKILLCODES.CODE WHERE NAME = 'C#'의 결과를 가지고 있는 (개발자의 코드와 이름이 C#인 코드를 & 연산 시켰더니, 나온 코드를 가지고 있는)
또는 PYTHON인 경우 마찬가지

특정 물고기를 잡은 총 수 구하기

• 서브쿼리 : 처음으로 혼자 푼 문제

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
-- BASS 또는 SNAPPER인 컬럼 찾기
-- FISH_INFO에서 출력
WHERE FISH_TYPE = (SELECT FISH_TYPE FROM FISH_NAME_INFO WHERE FISH_NAME = 'BASS')
OR FISH_TYPE = (SELECT FISH_TYPE FROM FISH_NAME_INFO WHERE FISH_NAME = 'SNAPPER');

특정 형질을 가지는 대장균 찾기

• 비트 연산자

---

SUM, MAX, MIN

잡은 물고기 중 가장 큰 물고기의 길이 구하기

• 결과값에 'cm' 더해서 출력하기 : CONCAT('문자열1','문자열2')

SELECT CONCAT(LENGTH, 'cm') AS MAX_LENGTH

연도별 대장균 크기의 편차 구하기

GROUP BY

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

• HAVING은 묶여진 컬럼들에 대해서 선택, 따라서 WHERE절 사용
• 'IN'은 와일드카드 사용불가, 따라서 'LIKE' 사용

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS 
-- IN ('%통풍시트%', '%열선시트%', '%가죽시트%') 불가능
LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;

성분으로 구분한 아이스크림 총 주문량

• JOIN한 테이블에 대해서 GROUP BY 실행하기
• 맞췄는데 참고로 기록

-- 문제 : 성분타입, 성분타입에 대한 총주문량
SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF AS A
JOIN ICECREAM_INFO AS 
ON A.FLAVOR = B.FLAVOR
-- 조인한 다음에, 조인된 테이블에서 B테이블에 있던 컬럼으로 조건걸기
GROUP BY B.INGREDIENT_TYPE
ORDER BY A.TOTAL_ORDER ASC;

입양 시각 구하기(1)

• 시간으로 검색, 묶고, 정렬 : HOUR(DATE)

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME);

가격대 별 상품 개수 구하기

• 컬럼의 숫자를 범위 별로, 출력 묶기 정렬 : 숫자형 함수(음수로 써서 정수범위에 대해 지정)

SELECT TRUNCATE(PRICE, -4) AS PRICE, COUNT(*) AS PRODUCTS
FROM PRODUCT
-- 가격대 별로, 상품갯수
-- 가격대 정보 표시(10000~19999 : 10000)
-- 가격대로 오름차순
GROUP BY TRUNCATE(PRICE, -4)
ORDER BY TRUNCATE(PRICE, -4);

조건에 맞는 사원 정보 조회하기

• GROUP BY로 묶은 다음에, MAX값 하나 조회하기
  : MAX값을 HAVING절로 찾는게 아닌, 정렬 후 LIMIT으로 조회
  (맞췄는데 기록용)

SELECT SUM(C.SCORE) AS SCORE, B.EMP_NO, B.EMP_NAME, B.POSITION, B.EMAIL
-- 평가점수가 가장 높은 사원들
-- 평가점수 : 상+하반기 점수
FROM HR_DEPARTMENT AS A
JOIN HR_EMPLOYEES AS B ON A.DEPT_ID = B.DEPT_ID
JOIN HR_GRADE AS C ON B.EMP_NO = C.EMP_NO
GROUP BY EMP_NO
ORDER BY SCORE DESC
LIMIT 1;

IS NULL

잡은 물고기의 평균 길이 구하기

• 10cm 이하의 물고기들은 10cm 로 취급하여 평균 구하기
  : AVG() 사용하지 않고, 직접 평균 구하기 : SUM(IFNULL(LENGTH, 10))/COUNT(*)

SELECT ROUND(SUM(IFNULL(LENGTH, 10))/COUNT(*), 2) AS AVERAGE_LENGTH
FROM FISH_INFO;

String, Date

조건에 부합하는 중고거래 상태 조회하기

• 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 로 출력하기
  : CASE 문 사용

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE WHEN STATUS = 'SALE' THEN '판매중'
WHEN STATUS = 'RESERVED' THEN '예약중'
WHEN STATUS = 'DONE' THEN '거래완료'
END AS STATUS
FROM USED_GOODS_BOARD
-- WHERE 2022년 10월 5일
WHERE CREATED_DATE = '2022-10-05'
ORDER BY BOARD_ID DESC;

자동차 대여 기록에서 장기/단기 대여 구분하기

• DATE로 기간 계산하기 : DATEDIFF()

SELECT HISTORY_ID, 
CAR_ID, 
DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, 
DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
CASE WHEN ABS(DATEDIFF(START_DATE, END_DATE)) >= 29 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
-- 2022년 9월
-- 기록ID로 내림차순
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC;

자동차 평균 대여 기간 구하기

• DATEDIFF()에 +1한 값이 기간임 _SELECT절과 HAVING절에서 모두 적용
• ORDER BY에 사용하는 컬럼을, SELECT절과 정확히 같은 값으로 사용하기

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(ABS(DATEDIFF(START_DATE, END_DATE)))+1 >= 7
ORDER BY ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) DESC, CAR_ID DESC;
-- 여기서 ORDER BY절에서 ROUND()를 생략할 시 오답

분기별 분화된 대장균의 개체 수 구하기

• GROUP BY 절의 컬럼을 임의로 설정하기 : SELECT문에서 CASE문을 사용한 Alias를 GROUP BY절에 사용 _이건 맞춤
• 아래 둘 차이 (위 오답, 아래 정답) : 날짜를 사용해야 할 때는 날짜 포맷으로 사용하기

SELECT
CASE 
WHEN DIFFERENTIATION_DATE LIKE '%/01/%' OR DIFFERENTIATION_DATE LIKE '%/02/%' OR DIFFERENTIATION_DATE LIKE '%/03/%' THEN '1Q'
WHEN DIFFERENTIATION_DATE LIKE '%/04/%' OR DIFFERENTIATION_DATE LIKE '%/05/%' OR DIFFERENTIATION_DATE LIKE '%/06/%' THEN '2Q'
WHEN DIFFERENTIATION_DATE LIKE '%/07/%' OR DIFFERENTIATION_DATE LIKE '%/08/%' OR DIFFERENTIATION_DATE LIKE '%/09/%' THEN '3Q'
WHEN DIFFERENTIATION_DATE LIKE '%/10/%' OR DIFFERENTIATION_DATE LIKE '%/11/%' OR DIFFERENTIATION_DATE LIKE '%/12/%' THEN '4Q'
END AS QUARTER,
COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
-- 분기별로, 총 수
GROUP BY QUARTER
ORDER BY QUARTER;

SELECT
CASE 
WHEN MONTH(DIFFERENTIATION_DATE) IN (1, 2, 3) THEN '1Q'
WHEN MONTH(DIFFERENTIATION_DATE) IN (4, 5, 6) THEN '2Q'
WHEN MONTH(DIFFERENTIATION_DATE) IN (7,8,9) THEN '3Q'
WHEN MONTH(DIFFERENTIATION_DATE) IN (10, 11, 12) THEN '4Q'
END AS QUARTER,
COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
-- 분기별로, 총 수
GROUP BY QUARTER
ORDER BY QUARTER;

---

참고

문자열 함수 _문자를 반환

• LOWER
• UPPER
• CONCAT
• SUBSTR
• TRIM
• 참고 - IF(결과값) 출력값 : CASE문 사용

CASE
WHEN 컬렴명(SIZE) 조건문(=) 결과값('SMALL') THEN 출력값('BAD')
WHEN 컬럼명(SIZE) 조건문(=) 결과값('BIG') THEN 출력값('GREAT')
ELSE 출력값('NO VALUE')	-- ELSE 생략가능
END AS 컬럼명

숫자형 함수 _숫자를 반환

• CEIL
• FLOOR
• ROUND
• TRUNC (음수로 가면 소수점 위에 대해서도 가능)

DATE 관련 함수

• YEAR(DATE), MONTH(DATE), HOUR(DATE) : DATE 표현 함수
• DATEDIFF(DATE, DATE) : 두 DATE 사이의 일수 계산
  (앞의 DATE에서 뒤의 DATE 빼기 _ABS()로 절댓값 사용하면 순서 상관X)
  ex) 두 기간이 30일 이상인 경우

ABS(DATEDIFF(START_DATE, END_DATE)+1) >= 30
-- DATEDIFF()+1 한 값 = 기간

기타

• IN()에서는 와일드 카드 사용 불가 -> LIKE 사용
• 조인할지 서브쿼리쓸지 -> SELECT문에 두 테이블의 컬럼을 다 쓰면 조인
  (반드시 그런건 아님)
• 모든 행에 대해, 모든 행의 최댓값 빼기
  : 서브쿼리 이용 _SELECT문에서 (최댓값을 구한 서브쿼리)-컬럼명 가능

SELECT ID, ABS(SIZE_OF_COLONY - (SELECT SIZE_OF_COLONY FROM ECOLI_DATA ORDER BY SIZE_OF_COLONY DESC LIMIT 1)) AS DEV
FROM ECOLI_DATA
ORDER BY DEV;

• 기타 함수..더 찾아보기

---

위 프로그래머스 사이트의 SQL문제에서
오라클로 작성시, 마지막줄에 주석을 작성하면 이유불명의 에러가 발생함