하나둘셋넷

SQL 이론] UNION_ALL

-- 코드를 입력하세요
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM (
        SELECT O.SALES_DATE, O.PRODUCT_ID, O.USER_ID, O.SALES_AMOUNT
        FROM ONLINE_SALE O
        WHERE O.SALES_DATE >= '2022-03-01' AND O.SALES_DATE <'2022-04-01'
        
        UNION ALL
        
        SELECT F.SALES_DATE, F.PRODUCT_ID, NULL as USER_ID, F.SALES_AMOUNT
        FROM OFFLINE_SALE F
        WHERE F.SALES_DATE >= '2022-03-01' AND F.SALES_DATE <'2022-04-01'
    ) AS S
    ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

SQL JOIN] 있었는데요 없었습니다

-- 코드를 입력하세요
SELECT I.ANIMAL_ID, I.NAME
    FROM ANIMAL_INS I
    INNER JOIN(
        SELECT ANIMAL_ID,DATETIME
        FROM ANIMAL_OUTS
    ) AS O ON I.ANIMAL_ID = O.ANIMAL_ID
    WHERE I.DATETIME > O.DATETIME
    ORDER BY I.DATETIME

SQL IS NULL] 이름이 없는 동물의 아이디

-- 코드를 입력하세요
SELECT ANIMAL_ID
    FROM ANIMAL_INS
    WHERE NAME IS NULL

SQL GROUP BY] 카테고리 별 도서 판매량 집계하기

-- 코드를 입력하세요
SELECT b.CATEGORY, sum(s.SALES) as TOTAL_SALES
    FROM BOOK b
    INNER JOIN(
        SELECT BOOK_ID, SALES, SALES_DATE
        FROM BOOK_SALES
    ) as s on b.BOOK_ID = s.BOOK_ID
    WHERE s.SALES_DATE >= '2022-01-01' AND s.SALES_DATE < '2022-02-01'
    GROUP BY b.CATEGORY
    ORDER BY b.CATEGORY

SQL String, Date] 조건에 맞는 사용자 정보 조회하기

-- 코드를 입력하세요
SELECT u.USER_ID, 
    u.NICKNAME, 
    CONCAT(u.CITY, ' ',u.STREET_ADDRESS1,' ', u.STREET_ADDRESS2) as 전체주소, 
    CONCAT(SUBSTRING(u.TLNO, 1,3), 
           '-',SUBSTRING(u.TLNO, 4,4), '-', 
           SUBSTRING(u.TLNO, 8) ) as  전화번호
    FROM USED_GOODS_BOARD b
    INNER JOIN USED_GOODS_USER u on b.WRITER_ID = u.USER_ID

    GROUP BY b.WRITER_ID
    HAVING COUNT(b.WRITER_ID) >= 3
    ORDER BY u.USER_ID DESC;

SQL JOIN] 없어진 기록 찾기

-- 코드를 입력하세요
SELECT o.ANIMAL_ID, o.NAME
    FROM ANIMAL_OUTS o # 보호소에 들어온 기록
    LEFT JOIN ANIMAL_INS b on b.ANIMAL_ID = o.ANIMAL_ID
    WHERE b.ANIMAL_ID IS NULL # 입양을 갔는데 보호소에 들어온 기록이 없다.

SQL IS NULL] 경기도에 위치한 식품창고 목록 출력하기

-- 코드를 입력하세요
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, COALESCE(FREEZER_YN, 'N') as FREEZER_YN
    FROM FOOD_WAREHOUSE
    WHERE ADDRESS LIKE '경기%'
    ORDER BY WAREHOUSE_ID

SQL GROUP BY] 조건에 맞는 사용자와 총 거래금액 조회하기

-- 코드를 입력하세요
SELECT u.USER_ID,	u.NICKNAME,	SUM(b.PRICE) as TOTAL_SALES
    FROM USED_GOODS_BOARD b # 중고 거래 게시판
    INNER JOIN USED_GOODS_USER u on u.USER_ID = b.WRITER_ID
    WHERE b.STATUS = 'DONE'
    GROUP BY u.USER_ID, u.NICKNAME
    HAVING SUM(b.PRICE) >= 700000
    ORDER BY TOTAL_SALES ASC

-- 코드를 입력하세요
SELECT MIN(DATETIME)
    FROM ANIMAL_INS