하나둘셋넷

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

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

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

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 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