SQL
[ SQL ] SHDS 모의코테 대비 간단정리 (ㄹㅇ간단)
hanjuCoding
2024. 7. 26. 17:37

BETWEEN
SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE (AGE BETWEEN 20 AND 29) AND JOINED LIKE '2021%'
BETWEEN x AND y : x이상, y이하
DATE_FORMAT
SELECT DATE_FORMAT(DATETIME,'%H')AS HOUR ,COUNT(*)AS 'COUNT'
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR > 8 AND HOUR < 20
ORDER BY 1
DATE_FORMAT( A , '%B')
A날짜에 대하여 B로 처리
'%H'는 시간만 출력
ex) 2024-07-09
DATE_FORMAT(date, '%y-%m-%d')
UNION vs UNION ALL
SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d')AS SALES_DATE,PRODUCT_ID,USER_ID,SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE,'%Y-%M-%D')AS SALES_DATE,PRODUCT_ID,NULL,SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;
UNION은 두 SELECT문을 하나의 결과로 합쳐줌
조건: SELECT문 끼리 데이터형식과 열이 같아야함
- UNION: 중복 제거
- UNION ALL: 중복제거X
IF문
SELECT BOARD_ID ,WRITER_ID, TITLE, PRICE,
IF(STATUS='SALE','판매중',IF(STATUS='RESERVED','예약중','거래완료'))AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE ='2022-10-05'
ORDER BY 1 DESC
if(A,B,C) -> A면 B, 아니면 C
날짜 차이 계산
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,
IF(DATEDIFF(END_DATE, START_DATE)+1 >=30,'장기 대여','단기 대여')AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY 1 DESC
30일 이상 차이 나는가? -> IF(DATEDIFF(END_DATE, START_DATE)+1 >=30,'장기 대여','단기 대여')
HAVING절
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 AVERAGE_DURATION >= 7
ORDER BY 2 DESC ,1 DESC
- having 절은 Group By 절 뒤에 사용
- Where 절은 Group By 절 앞에 사용
그룹 연산의 결과를 가지고 select하고 싶을때 사용 (집계함수 사용시 사용)
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'
ELSE '4Q'
END AS QUARTER,
COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY 1
버림