[ SQL ] SHDS 모의코테 대비 간단정리 (ㄹㅇ간단)

전세계 모든 데이터가 저장되어 있는 에그헤드

 

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문 끼리 데이터형식과 열이 같아야함

1. UNION: 중복 제거
2. 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하고 싶을때 사용 (집계함수 사용시 사용)

 

CASE WHEN END

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

 

TRUNCATE

SELECT TRUNCATE(3456.1234567 ,4) FROM DUAL;// 3456.1234

 

버림