프로그래머스 SQL 코딩테스트 복습

안녕하세요~
오랜만에 "나는야맹구"입니다.
최근 SQL코딩테스트를 준비하면서 다시 보는데 자세히 기억이 안나더라구요 
그래서 복습할 겸 블로그에 기록해보았습니다!! 
(프로그래머스에서 제공하는 SQL코딩테스트를 풀어보며 막히는 문법 등을 기록함)

---

1. CEIL 함수 (올림)
소수점 이하 자릿수에 관계없이 항상 가장 가까운 큰 정수로 올림.
즉, 숫자가 정수가 아닌 경우 무조건 올림
예시:
CEIL(3.14) -> 4
CEIL(-2.7) -> -2
2. ROUND 함수 (반올림)
- 소수점 이하 자릿수를 기준으로 반올림합니다.
- 기본적으로 소수점 첫 번째 자리에서 반올림하며, 두 번째 인자로 반올림할 자릿수를 지정할 수 있음
-반올림할 자릿수의 값이 5이상이면 올림, 5미만일 경우 버림
-예시: ROUND(3.14) -> 3, ROUND(3.6) -> 4, ROUND(3.14159,2) -> 3.14 (소수점 둘째 자리까지 반올림)

*ROUND, CEIL 혼동하지 말것.
ROUND -> 3543.2이면 3543으로, 3543.7이면 3544로 변환
CEIL -> 3543.2이든 3543.7이든 3544로 변환
 
3. DISTINCT + GROUP BY
DISTINCT로도 중복 제거가 가능하고, GROUP BY로도 중복제거가 가능하다. 뭐가 더 효율적일까?
4. 출력되는 날짜 형식 바꾸고 싶을 때
DATE_FORMAT으로 가능

DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD

DATE_FORMAT써주고 괄호 안에 HIRE_YME(본 칼럼), 그리고 날짜 형식 등을 적어준다.
그리고 별칭 필요하면 'AS 별칭' 적기
여기서 주의 *
%Y는 대문자 
%m는 소문자
%d는 소문자
Q. 그럼 년,월,일에서 추가로 몇시몇분몇초까지 출력하게하려면 어떤 형식으로 써야할까?

DATE_FORMAT(HIRE_YMD, '%Y-%m-%d %H:%i:%s')

여기서 H는 hour의 약자인듯싶고,, i는 뭐지 분인데 왜 i이지? s는 second의 약자인듯싶은데.. 
%H는 0~23시이고
%h는 12시간 형식의 시간을 뜻한다.(ex. 01부터 12까지) 대신 그럼 am인지 pm인지를 나타내는 게 필요한데 %p를 쓰면 된다.
 
5. 서브쿼리 사용

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.

라는 질문이었는데 최소 날짜를 어떻게 뽑아할 지 고민이었다.

SELECT NAME
FROM ANIMAL_INS
WHERE MIN(DATETIME) AND NAME IS NOT NULL

이렇게 하니까 에러가 뜨더라,, 
정보 좀 찾아보니까 서브쿼리를 써야한다더라! 

SELECT NAME
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MIN(DATETIME) FROM ANIMAL_INS) AND NAME IS NOT NULL

위와 같이 WHERE절에 서브쿼리를 작성해주었다.
 
6. 특정 년도만 뽑을 때

SELECT..
FROM ..
WHERE DATE_FORMAT(JOINED, '%Y') = '2021'

위에서 DATE_FORMAT을 사용해서 위같은 코드로 작성했는데 더 효과적인 방법이 없는지 찾아보았다. 그랬더니

SELECT ..
FROM ..
WHERE YEAR(JOINED) = 2021

이렇게 나타났다.  YEAR(JOINED: 해당 칼럼) 이렇게 하면 더 쉽게 년도만 추출해서 할 수 있구나!
 
7. NULL값을 특정 값으로 채우고 싶을 때 -> IFNULL(컬럼명, '대체값') , CASE 문

SELECT IFNULL(컬럼명, '대체값') 
FROM 테이블명;

IFNULL함수를 사용해서 컬럼명과 대체값을 작성하면 된다. 
또는! CASE문을 작성할 수 있는데 아래와 같이 

SELECT COUNT(ID) AS FISH_COUNT, MAX(LENGTH) AS MAX_LENGTH, FISH_TYPE
FROM (SELECT ID, FISH_TYPE, 
      CASE
      WHEN LENGTH IS NULL THEN 10
      WHEN LENGTH <= 10 THEN 10
      ELSE LENGTH END AS LENGTH
      ,TIME
      FROM FISH_INFO) AS FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(LENGTH) >=33
ORDER BY FISH_TYPE

 
서브쿼리문을 넣어서 좀 복잡하긴하지만, CASE~~END쪽을 보시라.

SELECT CASE 
WHEN LENGHT IS NULL THEN 10 #LENGTH가 NULL값일 때 10을 넣어
WHEN LENGTH <10 THEN 10 #LENGTH가 10보다 작을 때 10을 넣어
ELSE LENGTH #위 경우에 해당하지 않으면, 그대로 LENGTH값 유지
END AS LENGTH #END라고해야 CASE문이 끝나는거고, 별칭을 LENGTH로 유지하자.

CASE문을 이번에 처음 알게 되었는데 유용하게 잘 사용할 수 있을 것 같다.
 
8. groupby 시 주의

SELECT COUNT(CATCH.ID) AS FISH_COUNT, NAME.FISH_NAME AS FISH_NAME
FROM FISH_INFO AS CATCH LEFT JOIN 
FISH_NAME_INFO AS NAME
ON CATCH.FISH_TYPE = NAME.FISH_TYPE
GROUP BY CATCH.FISH_TYPE
ORDER BY FISH_COUNT DESC

위 코드에서 다음과 같은 오류가 발생했다. 
실패 (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'programmers.NAME.FISH_NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
문제 원인을 살펴보니
현재 쿼리의 에러 원인은 NAME.FISH_NAME 이 GROUP BY 절에 포함되지 않았거나 집계 함수로 묶여있지 않기 때문이라고 한다. 그래서 DISTINCT(NAME.FISH_NAME)이라고 해보았지만, 'DISTINCT'는 함수가 아니라 SQL의 키워드라 사용이 안된다고 한다. 따라서, GROUP BY 절에 NAME.FISH_NAME을 추가했다. 

SELECT COUNT(CATCH.ID) AS FISH_COUNT, NAME.FISH_NAME AS FISH_NAME
FROM FISH_INFO AS CATCH LEFT JOIN 
FISH_NAME_INFO AS NAME
ON CATCH.FISH_TYPE = NAME.FISH_TYPE
GROUP BY CATCH.FISH_TYPE, NAME.FISH_NAME
ORDER BY FISH_COUNT DESC

완성
 
9. 고양이를 개보다 먼저 조회해주세요

ORDER BY ANIMAL_TYPE ASC

이렇게 해서 나는 정렬을 했으나, 더 나은 방법이 없으려나해서 AI한테 물어봤다.

SELECT *
FROM ANIMAL_INS AS A
WHERE ... -- 기존 WHERE 조건 유지
ORDER BY 
    CASE 
        WHEN A.ANIMAL_TYPE = 'Cat' THEN 1 
        WHEN A.ANIMAL_TYPE = 'Dog' THEN 2
        ELSE 3 
    END ASC, 
    A.PUBLISHED_DATE DESC;

ORDER BY절에 CASE문을 사용해서 숫자로 바꿔주더라. 오 천재인데~
이렇게 숫자를 이용하면 확실히 정렬이 되겠구나!! 
지식 +1됨
10. 날짜 차이 구하기위해서는?-> 기간: DATEDIFF(끝날짜, 전날짜)

DATEDIFF(END_DATE, START_DATE): 두 날짜 사이의 일수 차이를 계산합니다.

END_DATE를 먼저 써주고, 그 다음 START_DATE작성
11. 정수를 백분률로 바꿔주는 코드
-> DURATION_FEE / 100 
12. 문자열에서 숫자만 추출 -> REGEXP_SUBSTR([컬럼명], '[0-9]+') 함수 이용

REGEXP_SUBSTR(DURATION_TYPE, '[0-9]+')

 regexp_substr(컬럼명, ‘[0-9]+‘)
13. 일부 문자 추출
- 왼쪽에서 추출할 때 left(컬럼명, n 몇글자추출할지)

SELECT LEFT(PRODUCT_CODE,2) #왼쪽에서 2자리만 추출

- 오른쪽에서 추출할 때->right(컬럼명, n)

SELECT RIGHT('A1000011', 3) AS result;  -- 결과: '011'

-가운데에서 추출 -> SUBSTRING 또는 MID함수 이용

SELECT SUBSTRING('A1000011', 3, 4) AS result;  -- 결과: '0000'
SELECT MID('A1000011', 3, 4) AS result;       -- 결과: '0000'

 

• SUBSTRING(문자열, 시작 위치, 추출할 길이): 주어진 문자열의 특정 위치부터 지정된 길이만큼 문자열을 추출합니다.
• MID(문자열, 시작 위치, 추출할 길이): SUBSTRING과 동일한 기능

14. 상위 2개 추출 -> LIMIT 2

SELECT LEFT(column_name, 2) AS result
FROM your_table
LIMIT 2;

 
15. UNION과 UNION ALL차이
UNION은 중복된 값을 자동으로 제거해서 띄어주고, 
UNION ALL은 중복된 값을 제거 없이 그대로 띄어줌.
16. WITH절
- 임시 저장 사용 용도
- 개발자 입장에서는 편하나, WITH절이나 SUB QUERY는 VIEW라는 임시 저장을 만드는 행위이기 때문에 SQL문 작성에 좋은 습관은 아님.
 

WITH 임시테이블명1 AS(
SELECT 
FROM ~~)


SELECT  ~~
FROM 임시테이블명1,

WITH TB1 AS(
SELECT EMPNO, YEAR, AVG(SCORE) AS SCORE
FROM HRGRADE
GROUP BY 1,2) #여기서 1,2는 EMPNO, YEAR을 의미!!

 
 
17. concat함수 + order by 주의점

SELECT 
ROUTE, 
CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'km') AS TOTAL_DISTANCE, 
CONCAT(ROUND(AVG(D_BETWEEN_DIST),2),'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY TOTAL_DISTANCE DESC;

이렇게 했는데 오류가 떴다. 이유를 찾아보니, order by에서 오류가 발생햇는데 그 이유가 total_distance였다. total_distance는 현재 concat함수로 숫자 + 문자로 되어있어 문자열 칼럼으로 존재한다. 따라서 

ORDER BY ROUND(SUM(D_BETWEEN_DIST),1) DESC; #concat함수로 데이터 안에 문자열이 들어있으므로 정렬 시 문제가 생길 수 있기에 숫자로만 정렬되도록 한다.

위와 같이 수정해주었다. 그랬더니 됐다!
18.비트연산자 -> 어렵다

SELECT DISTINCT(ID), EMAIL, FIRST_NAME, LAST_NAME 
FROM DEVELOPERS
WHERE SKILL_CODE& (
(SELECT CODE 
 FROM SKILLCODES 
 WHERE NAME = 'Python')  # 파이썬 기술 코드
    +
(SELECT CODE #C# 기술 코드
 FROM SKILLCODES
 WHERE NAME = 'C#')
)!= 0 #0은 거짓, 1은 참을 의미
ORDER BY ID ASC;

• 여기서 != 0 조건을 사용하는 이유는 AND 연산 결과가 0이 아닐 때(즉, 특정 기술을 보유하고 있을 때)만 데이터를 선택하기 위해서이다.
• 만약 != 0 조건이 없다면, 기술을 보유하지 않은 개발자들도 결과에 포함될 수 있다.
• != 0 조건이 없다면, 쿼리는 AND 연산 결과가 0인 경우까지도 포함하게 된다. AND 연산의 결과가 0이라는 것은, SKILL_CODE가 해당 비트(기술)를 전혀 포함하고 있지 않다는 의미

19. 다중컬럼 IN 사용방법 별표!!⭐

SELECT A.ID, B.FISH_NAME, A.LENGTH
FROM FISH_INFO A
INNER JOIN FISH_NAME_INFO B
ON A.FISH_TYPE = B.FISH_TYPE
WHERE (A.FISH_TYPE,A.LENGTH) IN(
SELECT FISH_TYPE, MAX(LENGTH)
FROM FISH_INFO
GROUP BY FISH_TYPE)

(A.FISH_TYPE, A.LENGTH) IN (
SELECT FISH_TYPE, MAX(LENGTH)
FROM FISH_INFO, GROUP BY FISH_TYPE)
하나만 되는 줄 알았는데 N개도 되는구나.. 안되는줄알고 겁나 복잡하게 쿼리짰다.ㅠㅠ
 
20 . 복잡한 쿼리문

SELECT YEAR(DIFFERENTIATION_DATE) YEAR, 
    (SELECT MAX(SIZE_OF_COLONY) FROM ECOLI_DATA WHERE YEAR(DIFFERENTIATION_DATE) = YEAR) - SIZE_OF_COLONY as YEAR_DEV,
    ID
FROM ECOLI_DATA 
ORDER BY YEAR, YEAR_DEV

 
 
21.10진수를 2진수로 BIN()함수

SELECT BIN(8); #1000

22. 사분위수  -> PERCENT_RANK()문법

SELECT ID,
CASE
WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY) < 0.25 THEN 'LOW'
WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY) < 0.50 THEN 'MEDIUM'
WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY) < 0.75 THEN 'HIGH'
ELSE 'CRITICAL' END AS COLONY_NAME
FROM ECOLI_DATA
ORDER BY ID

 
23. WITH절 2개 연결할 때

WITH W1 AS (
SELECT ID, PARENT_ID, 1 AS GENERATION 
FROM ECOLI_DATA 
WHERE PARENT_ID IS NULL) , #쉼표
W2 AS( #WITH W2 AS가 아니라 W2 AS 바로 입력
SELECT A.ID , A.PARENT_ID , W1.GENERATION +1 
FROM ECOLI_DATA AS A JOIN W1 
ON A.PARENT_ID = W1.ID)

24. ROW_NUMBER() 
행 번호 1번부터 할당하는 함수
 
25. JOIN 'TABLE' USING('동일 칼럼 이름')

SELECT * 
FROM USED_GOODS_BOARD
JOIN USED_GOODS_FILE USING(BOARD_ID);

JOIN할 때 두 칼럼에 동일한 이름이 있으면 해당 문법 사용가능
 
26. SUBSTRING 함수
substring(tlno,1,3)이거의 의미는 TLNO열에서 1부터 시작에서 3글자 빼오는거
EX) SUBSTRING(TLNO, 4,3)은 4글자부터 시작해서 3글자 빼오는거를 의미함.