좌충우돌 IT이야기

https://school.programmers.co.kr/learn/courses/30/lessons/133027?language=oracle

-- 코드를 입력하세요
SELECT
FLAVOR
FROM
(
    SELECT
    A.FLAVOR
    ,NVL(A.TOTAL_ORDER,0) + NVL(B.TOTAL_ORDER,0) AS RESULT
    FROM FIRST_HALF A, 
    (
        SELECT
        FLAVOR
        ,SUM(TOTAL_ORDER) AS TOTAL_ORDER
        FROM JULY
        GROUP BY FLAVOR
    )B
    WHERE 1=1
    AND A.FLAVOR = B.FLAVOR(+)
    ORDER BY 2 DESC
) WHERE ROWNUM < 4;

프로그래머스

• 문제 : 보호소에서 중성화한 동물
• URL : https://programmers.co.kr/learn/courses/30/lessons/59045

풀이

-- 코드를 입력하세요
SELECT
INS.ANIMAL_ID,
INS.ANIMAL_TYPE,
INS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID
AND INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME

프로그래머스

• 제목 : 입양 시각 구하기(2)
• 주소 : https://programmers.co.kr/learn/courses/30/lessons/59413?language=oracle 

풀이

SELECT
A.LV AS HOUR,
NVL(B.CNT,0) AS COUNT
FROM
(
    SELECT
    LEVEL-1 AS LV 
    FROM DUAL
    CONNECT BY LEVEL <= 24
) A, (
    SELECT
    HOUR,
    COUNT(*) AS CNT
    FROM(
        SELECT
        TO_CHAR(DATETIME,'HH24') HOUR
        FROM ANIMAL_OUTS
    ) OUTS 
    GROUP BY HOUR
    ORDER BY 1
) B
WHERE A.LV = B.HOUR(+)
ORDER BY 1

SELECT
DISTINCT CART_ID
FROM CART_PRODUCTS
WHERE CART_ID IN (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt')
AND NAME = 'Milk';