하나둘셋넷

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

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

SQL SELECT] 재구매가 일어난 상품과 회원 리스트 구하기

GROUP BY USER_ID를 한다면, USER_ID와 PRODUCT_ID가 모두 같은 행을 출력 가능하고, 이때 COUNT(PRODUCT_ID)가 1보다 큰지 확인

프로그래머스_인기있는 아이스크림

SELECT FLAVOR
    FROM FIRST_HALF
    ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID;

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

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

프로그래머스_12세 이하인 여자 환자 목록 출력하기

SELECT PT_NAME, 
        PT_NO, 
        GEND_CD, 
        AGE, 
        IFNULL(TLNO, 'NONE') as TLNO
        
    FROM PATIENT
    
    WHERE AGE<= 12 AND GEND_CD = 'W'
    
    ORDER BY AGE DESC, PT_NAME;

3월에 태어난 여성 회원 목록 출력하기

-- 코드를 입력하세요
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d") AS DATE_OF_BIRTH

    FROM MEMBER_PROFILE
    
    WHERE MONTH(DATE_OF_BIRTH)=3 AND GENDER = 'W' AND TLNO IS NOT NULL
    
    ORDER BY MEMBER_ID ASC;