하나둘셋넷

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

SQL SELECT] 서울에 위치한 식당 목록 출력하기

-- 코드를 입력하세요
SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, ROUND(AVG(R.REVIEW_SCORE),2) as SCORE
    FROM REST_INFO I
    INNER JOIN(
        SELECT REST_ID, REVIEW_SCORE
        FROM REST_REVIEW
        ) AS R on I.REST_ID = R.REST_ID
    WHERE I.ADDRESS LIKE "서울%"
    GROUP BY REST_ID
    ORDER BY SCORE DESC, FAVORITES DESC

프로그래머스_평균 일일 대여 요금 구하기

SELECT ROUND(SUM(DAILY_FEE) / COUNT(*),0 )  as AVERAGE_FEE
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE CAR_TYPE = 'SUV'