[MySQL] 프로그래머스 SQL

모든 레코드 조회하기

SELECT * FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

 

역순 정렬하기

SELECT NAME, DATETIME from ANIMAL_INS
ORDER BY ANIMAL_ID desc

 

아픈 동물 찾기

SELECT ANIMAL_ID,NAME FROM ANIMAL_INS
where INTAKE_CONDITION='Sick'

 

어린 동물 찾기

SELECT ANIMAL_ID,NAME FROM ANIMAL_INS
where INTAKE_CONDITION!="Aged"
order by ANIMAL_ID

동물의 아이디와 이름

SELECT ANIMAL_ID,NAME from ANIMAL_INS
order by ANIMAL_ID

 

여러 기준으로 정렬하기

SELECT ANIMAL_ID,NAME,DATETIME from ANIMAL_INS
order by NAME asc, DATETIME desc

상위 n개의 레코드

SELECT NAME from animal_ins
order by DATETIME 
limit 1

 

최댓값 구하기

SELECT DATETIME from animal_ins
order by DATETIME desc limit 1

 

최솟값 구하기

SELECT DATETIME from animal_ins
order by datetime limit 1

 

동물 수 구하기

SELECT COUNT(*) from animal_ins

 

중복 제거하기

SELECT COUNT(distinct NAME) from animal_ins

 

고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE,count(*) from animal_ins
group by ANIMAL_TYPE
having ANIMAL_TYPE="cat" or ANIMAL_TYPE="dog"
order by ANIMAL_TYPE

 

동명 동물 수 찾기

SELECT NAME,count(*) as COUNT from ANIMAL_INS
group by NAME
having count(NAME)>1
order by NAME

 

입양 시각 구하기(1)

SELECT HOUR(DATETIME) as HOUR,COUNT(HOUR(DATETIME)) as COUNT from ANIMAL_OUTS
group by HOUR
having HOUR>=9 and HOUR<20
order by HOUR(DATETIME)

 

입양 시각 구하기(2)

SET @HOUR := -1;
SELECT (@HOUR := @HOUR +1) as HOUR ,
        (SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME)=@HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23

 

이름이 없는 동물의 아이디

SELECT ANIMAL_ID from ANIMAL_INS
where NAME is NULL

 

이름이 있는 동물의 아이디

SELECT ANIMAL_ID from ANIMAL_INS
where NAME is not NULL

 

NULL 처리하기

SELECT ANIMAL_TYPE, ifnull(name,'No name') as NAME, SEX_UPON_INTAKE 
FROM ANIMAL_INS
order by ANIMAL_ID

 

없어진 기록 찾기

SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME 
from ANIMAL_OUTS
left join ANIMAL_INS
on ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID 
where ANIMAL_INS.ANIMAL_ID is NULL

#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

 

 

있었는데요 없었습니다

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A left join ANIMAL_OUTS B
on A.ANIMAL_ID = B.ANIMAL_ID
where A.DATETIME > B.DATETIME
order by A.DATETIME

 

 

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

SELECT ANIMAL_INS.NAME, ANIMAL_INS.DATETIME
FROM ANIMAL_INS left join ANIMAL_OUTS
on ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
where ANIMAL_OUTS.ANIMAL_ID is NULL
order by ANIMAL_INS.DATETIME
LIMIT 3

 

 

보호소에서 중성화한 동물

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.ANIMAL_TYPE, ANIMAL_INS.NAME
FROM ANIMAL_INS
left join ANIMAL_OUTS
on ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.SEX_UPON_INTAKE like "%Intact%"
AND (ANIMAL_OUTS.SEX_UPON_OUTCOME like "%Neutered%" OR
     ANIMAL_OUTS.SEX_UPON_OUTCOME like "%Spayed%")

 

루시와 엘라 찾기

SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE
FROM ANIMAL_INS
where NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

 

이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS
where ANIMAL_TYPE='Dog' and lower(NAME) like '%el%'
order by NAME

 

중성화 여부 파악하기

SELECT ANIMAL_ID, NAME, 
IF(SEX_UPON_INTAKE LIKE '%NEUTERED%' OR SEX_UPON_INTAKE LIKE '%SPAYED%','O','X') AS '중성화'
FROM ANIMAL_INS

 

 

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

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS left join ANIMAL_OUTS
on ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
order by ANIMAL_OUTS.DATETIME - ANIMAL_INS.DATETIME desc
limit 2

 

 

DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d') as 날짜
from ANIMAL_INS

 

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

SELECT DISTINCT CART_ID 
FROM CART_PRODUCTS
where NAME='MILK' 
AND CART_ID IN (SELECT CART_ID
            FROM CART_PRODUCTS
            WHERE NAME = 'Yogurt')

 

헤비 유저가 소유한 장소

SELECT ID, NAME, HOST_ID
FROM PLACES
where HOST_ID IN(
    SELECT HOST_ID from PLACES
    group by HOST_ID
    having count(*)>=2)