SQL 코딩테스트 cheat sheet !

SQL 코딩테스트에서 문자열이 출시되면 주로 나오는 것들, 알아야 하는 함수들, 풀어봤던 문제들 리스트업하는 포스팅이다. 코테가 참  야속한 것이 실무에서 내가 쓰는 것만 쓰다 보면 몇 달 전만 해도 코테에서 너무 쉽게 풀었던 것들인데 다시 봤을 땐 생소해서 가끔 이렇게 복습이 필요하다.. !! 

 

 

조건 걸 땐 WHERE ~ IN / NOT IN 

 

파이썬의 IN 과 굉장히 유사하다. 조건 여러개 걸때 유용!

 

*참조: 프로그래머스 "대여 횟수가 많은 자동차들의 월별 대여 횟수"

--틀린 답
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE start_date >= "2022-08-01" AND start_date < "2022-11-01"
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 4
ORDER BY MONTH, CAR_ID DESC;

--정답
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE start_date >= "2022-08-01" AND start_date < "2022-11-01" AND 
CAR_ID IN (
    SELECT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE start_date >= "2022-08-01" AND start_date < "2022-11-01" 
    GROUP BY CAR_ID 
    HAVING COUNT(HISTORY_ID) >= 5
) 
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH, CAR_ID DESC;

 

*컬럼 여러개 중에서 IN 걸기 

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE 'python' IN (SKILL_1, SKILL_2, SKILL_3)
ORDER BY ID ASC

 

상위 N 개 선택하기 LIMIT 

LIMIT 을 쓰면 되는데, 바로 메인쿼리에 쓰지 말고 FROM 절 서브쿼리에 쓰는 걸 추천. 복잡하고 양 많은 쿼리일수록 서브쿼리를 잘 써야 효율성이 늘어남

 

SELECT NAME
FROM 
(
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1
) as a

 

 

FROM 서브쿼리 

 

두개 테이블 합칠 때 FROM 절 서브쿼리로 조건 걸고 합치기. 합치고 나서 조건걸면 따로따로 조건걸기는 못하겠죠? 혹은 효율성이 나락감.. 

 

JOIN (SELECT ~~) ON ~ 

SELECT USER_ID, NICKNAME,TOTAL_SALES
FROM USED_GOODS_USER as a 
JOIN (SELECT SUM(PRICE) AS TOTAL_SALES, WRITER_ID
      FROM USED_GOODS_BOARD
      WHERE STATUS = 'DONE'
      GROUP BY WRITER_ID
      HAVING TOTAL_SALES >= 700000
      ORDER BY TOTAL_SALES
      ) as b
      ON a.USER_ID = b.WRITER_ID

 

 

문자열 짜집기 SUBSTRING / CONCAT / REPLACE 

 

SUBSTRING 문자열 잘라내기 

SELECT SUBSTRING(NAME, 2, 4) // NAME의 2번째글자부터 4글자 출력
     , SUBSTRING(NAME, 1, 3) // NAME의 1번째글자부터 3글자 출력

 

 CONCAT 문자열 합치기 

 

select concat('(', name,  ,')') // (name) 출력

 

 

REPLACE 문자열 치환하기 

select first_name as fm,
       replace(first_name, 'e', '*') as REPLACE //e 를 모조리 * 로 치환
from employees;

 

 

수치형 모듈 

 

CEILING, FLOOR, ROUND   올림, 내림, 반올림 

CEILING([숫자])  //올림
FLOOR([숫자])   //내림
ROUND([숫자], [반올림 기준])   //반올림

 

 

POW, SQRT 제곱, 제곱근 

POW([숫자], 지수)  //제곱 
SQRT([숫자])   //제곱근

 

 

 

 

윈도우 함수 

 

윈도우 함수는 OVER 과 같이 쓰는 경우가 많다. 순위를 메기거나 특정 범위 내에서 뭔갈 구할 때 쓰는데, window = 창 이 그 범위를 정해주는거라고 이해하면 된다. 

 

 RANK() , OVER ~ PARTITION BY()  순위 메기기 /범위 자르기  

 

보통 이렇게 두개를 같이 쓰는 경우가 많음 

 

SELECT
  department_id,
  employee_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank // 급여 순으로 순위 메기기 
FROM
  employees;


SELECT
  department_id,
  employee_id,
  salary,
  SUM() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank // 급여 파티션 별 윈도우의 합
FROM
  employees;

 

 

 

카테고리형 만들어내기  CASE WHEN 

 

조건에 따라서 카테고리 컬럼을 부여하고 싶을 때 사용한다. SELECT 절에서 사용하면 됨 

 

CASE WHEN 조건 ~ THEN ELSE~ END 

 

SELECT
  department_id,
  employee_id,
  salary,
  CASE WHEN salary > 500 THEN 'MVP' 
  CASE WHEN salary > 300 THEN 'normal'
  ELSE 'unknown' 
  END as RANK // salary 기준으로 카테고리 부여해서 RANK 컬럼 만들기 
FROM
  employees;

 

 

 

 

날짜형 내맘대로 다루기 DATE 모듈들 

 

# 날짜추출 
YEAR(날짜), MONTH(날짜), DAY(날짜), DATE(날짜)

# 날짜/시간 간 차이 구하기
DATEDIFF(날짜1, 날짜2) // Date 차이 
TIMESTAMPDIFF(단위, 날짜1, 날짜2) //내가 지정한 단위 기준 

# 형 변환
DATE_FORMAT(날짜, format) ex. %Y-%m-%d
TO_DATE(날짜, format)//날짜가 아닌걸 날짜로 바꿈

# 날짜 더하기/빼기
DATE_ADD(DATE, INTERVAL 1 HOUR) //1시간 더하기
DATE_SUB(DATE, INTERVAL 3 HOUR) //3시간 빼기