programmers 연습문제

https://school.programmers.co.kr/learn/challenges

없어진 기록 찾기

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_OUTS A LEFT OUTER JOIN ANIMAL_INS B
ON (A.ANIMAL_ID=B.ANIMAL_ID)
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.ANIMAL_ID ASC;


SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_OUTS A LEFT OUTER JOIN ANIMAL_INS B
ON (A.ANIMAL_ID=B.ANIMAL_ID)
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.ANIMAL_ID ASC;

SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS AS O
WHERE NOT EXISTS (
    SELECT I.ANIMAL_ID
    FROM ANIMAL_INS AS I
    WHERE O.ANIMAL_ID = I.ANIMAL_ID
)


SELECT A.ANIMAL_ID, A.NAME FROM ANIMAL_OUTS A 
LEFT JOIN ANIMAL_INS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY B.ANIMAL_ID ASC



SELECT o.ANIMAL_ID, o.NAME
from animal_outs o, animal_ins i
where o.animal_id = i.animal_id(+)
and i.animal_id is null

있었는데요 없었습니다

inner join에서 where, on 조건절 사용 여부와 순서없이 모두 동일한 결과값을 출력한다.

-- where절 사용
SELECT i.animal_id, i.name
from animal_ins i, animal_outs o
where i.animal_id = o.animal_id
and i.datetime > o.datetime
order by i.datetime

-- on, where절 사용
SELECT i.animal_id, i.name
from animal_ins i join animal_outs o
on i.animal_id = o.animal_id
where i.datetime > o.datetime
order by i.datetime

-- on절 사용
SELECT i.animal_id, i.name
from animal_ins i join animal_outs o
on i.animal_id = o.animal_id
and i.datetime > o.datetime
order by i.datetime

outer join에서는 on절과 where절을 어떻게 사용하는지에 따라 결과값이 달라진다.

SELECT i.animal_id, i.name
from animal_ins i 
right outer join animal_outs o
on i.animal_id = o.animal_id
where i.datetime > o.datetime
order by i.datetime

오랜 기간 보호한 동물(1)

outer join

SELECT i.name, i.datetime
from animal_ins i left join animal_outs o
on i.animal_id = o.animal_id
where o.animal_id is null
order by i.datetime
limit 3

헤비 유저가 소유한 장소

음........?????

SELECT A.ID, A.NAME, A.HOST_ID
FROM PLACES A,
    ( 
        SELECT
            COUNT(*) AS CNT, HOST_ID
            FROM 
                PLACES 
            GROUP BY HOST_ID
    ) B 
WHERE 
    B.CNT >=2
AND
    B.HOST_ID = A.HOST_ID
ORDER BY A.ID





-- SUB QUERY
SELECT * FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(HOST_ID) >= 2)
ORDER BY ID;

-- INNER JOIN
SELECT A.* FROM PLACES A
JOIN (SELECT * FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(HOST_ID) >= 2) B
ON A.HOST_ID = B.HOST_ID
ORDER BY A.ID;

-- SELF JOIN
SELECT A.*
FROM PLACES A, PLACES B
WHERE A.ID <> B.ID AND A.HOST_ID = B.HOST_ID
GROUP BY A.ID
ORDER BY A.ID;

오랜 기간 보호한 동물(2)

inner join 사용

SELECT i.animal_id, i.name
from animal_ins i, animal_outs o
where i.animal_id = o.animal_id
order by o.datetime - i.datetime desc
limit 2

-----------------------

SELECT i.animal_id, i.name
from animal_ins i join animal_outs o
on i.animal_id = o.animal_id
order by o.datetime - i.datetime desc
limit 2

using()절 사용

SELECT i.animal_id, i.name
from animal_ins i join animal_outs o
using (animal_id)
order by o.datetime - i.datetime desc
limit 2

datediff 함수 사용
datediff : 두개의 날짜/시간값의 차이를 int로 반환하는 내장함수

order by datediff(o.datetime, i.datetime)

rownum 사용
특정 개수만큼 데이터를 출력하고 싶을 때
데이터에 index를 설정하고 싶을 때

-- *참고 코드* 
SELECT ANIMAL_ID, NAME
FROM(
    SELECT I.ANIMAL_ID, I.NAME
    FROM ANIMAL_INS I, ANIMAL_OUTS O
    WHERE I.ANIMAL_ID = O.ANIMAL_ID
    ORDER BY O.DATETIME - I.DATETIME DESC
    )
WHERE ROWNUM <=2

우유와 요거트가 담긴 장바구니

select a.cart_id 
from
    (select * from cart_products
        where name = 'Milk') a
    join
    (select * from cart_products
        where name = 'Yogurt') b
using (cart_id)
order by a.cart_id


------------------------------

select a.cart_id 
from
    (select * from cart_products
        where name = 'Milk') a, 
    (select * from cart_products
        where name = 'Yogurt') b
where a.cart_id = b.cart_id
order by a.cart_id

입양 시각 구하기(2)

프로그래머스 문제중 나에게 가장 어려운 문제인 것 같다...
내 아이디어로는 도저히 풀 수 없어 다른 사람들의 코드를 참고했다.

테이블이 가지고 있는 HOUR데이터는 7~19까지 뿐이다.
문제는 0~23까지의 HOUR데이터를 모두 출력함으로 데이터를 따로 생성해 주어야 한다.

SET 함수 사용하기

• SET명령어는 변수에 특정 값을 할당할 때 사용
• 변수명 앞에 @을 붙여야 함
• := 오라클의 대입 연산자

set @i_hour = -1;

select ( @i_hour := @i_hour +1) as HOUR
from ANIMAL_OUTS 
where @i_hour <23;

set @i_hour = -1;

select ( @i_hour := @i_hour +1) as 'HOUR',
       ( select count(*) from animal_outs 
         where hour(datetime) = @i_hour) as 'COUNT'
from animal_outs 
where @i_hour <23;

보호소에서 중성화한 동물

SELECT i.animal_id, i.animal_type, i.name
from 
    (select * from animal_ins
        where sex_upon_intake like '%Intact%') i
    join
    (select * from animal_outs
        where sex_upon_outcome like '%Spayed%' 
        or sex_upon_outcome like '%Neutered%') o
using (animal_id)
order by i.animal_id

------------------------
SELECT i.animal_id, i.animal_type, i.name
from animal_ins i, animal_outs o
where i.animal_id = o.animal_id
and i.sex_upon_intake <> o.sex_upon_outcome
order by i.animal_id