MySql : 프로그래머스 SQL 고득점 Kit 답

(출처 :https://programmers.co.kr/design/logo)

---

SELECT

모든 레코드 조회하기

SELECT * FROM ANIMAL_INS

역순 정렬하기

SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC

아픈 동물 찾기

SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESCSELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE INTAKE_CONDITION='Sick'
ORDER BY ANIMAL_ID

어린 동물 찾기

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, DATETIME DESC

상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

---

SUM, MAX, MIN

 

최댓값 구하기

SELECT DATETIME
FROM ANIMAL_INS 
ORDER BY DATETIME DESC
LIMIT 1

최솟값 구하기

SELECT DATETIME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

동물 수 구하기

SELECT COUNT(ANIMAL_ID)
FROM ANIMAL_INS

중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

---

GROUP BY

 

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

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP by ANIMAL_TYPE
order by ANIMAL_TYPE

동명 동물 수 찾기

SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP by NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME

입양 시각 구하기(1)

SELECT HOUR(DATETIME)AS HOUR, COUNT(DATETIME)AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR
ORDER BY HOUR

입양 시각 구하기(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

---

IS NULL

 

이름이 없는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL

이름이 있는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

NULL 처리하기

SELECT ANIMAL_TYPE, IFNULL(NAME,'No name')as NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

---

JOIN

 

없어진 기록 찾기

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

있었는데요 없었습니다

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A
LEFT OUTER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME > B.DATETIME
ORDER BY A.DATETIME

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

SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS A
LEFT OUTER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.DATETIME IS NULL
ORDER BY DATETIME LIMIT 3

보호소에서 중성화한 동물

SELECT A.ANIMAL_ID,A.ANIMAL_TYPE,A.NAME
FROM ANIMAL_INS A
RIGHT OUTER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE '%Intact%' AND B.SEX_UPON_OUTCOME NOT LIKE '%Intact%'
ORDER BY B.ANIMAL_ID

---

String, Date

 

루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN('Lucy','Ella','Pickle','Rlgan','Sabrina','Mitty')
ORDER BY ANIMAL_ID

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

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%el%' AND ANIMAL_TYPE = 'dog'
ORDER BY NAME

 

중성화 여부 파악하기

SELECT ANIMAL_ID, NAME,
CASE SEX_UPON_INTAKE
 WHEN 'Intact Male' THEN 'X'
 WHEN 'Intact Female' THEN 'X'
 ELSE 'O'
END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

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

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY DATEDIFF(B.DATETIME, A.DATETIME) DESC LIMIT 2

DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

---