하나둘셋넷

SQL SELECT] 조건에 맞는 도서 리스트 출력하기

SQL JOIN] 오랜 기간 보호한 동물

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

SQL GROUP BY] 저자 별 카테고리 별 매출액 집계하기

SQL SUM, MAX, MIN] 동물 수 구하기

-- 코드를 입력하세요
SELECT COUNT(ANIMAL_ID) as count
    FROM ANIMAL_INS I

SQL String, Date] 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 LIMIT 1

-- 코드를 입력하세요
SELECT CONCAT('/home/grep/src/',B.BOARD_ID,'/',F.FILE_ID,F.FILE_NAME,F.FILE_EXT) as FILE_PATH
    FROM USED_GOODS_BOARD B
    INNER JOIN(
        SELECT *
        FROM USED_GOODS_FILE
    ) as F on B.BOARD_ID = F.BOARD_ID
    WHERE B.BOARD_ID = (
        SELECT BOARD_ID
        FROM USED_GOODS_BOARD
        ORDER BY VIEWS DESC
        LIMIT 1
    )
    
    ORDER BY F.FILE_ID DESC

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 SELECT] 오프라인/온라인 판매 데이터 통합하기

-- 코드를 입력하세요
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