프로그래머스 SQL Kit 답지

SELECT

SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID

 

SELECT NAME, DATATIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;

 

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

 

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

 

 

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC

 

 

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1;

 

SUM MAX MIN

SELECT MAX(DATETIME) AS '시간'
FROM ANIMAL_INS

 

 

SELECT MIN(DATETIME) AS '시간' FROM ANIMAL_INS

 

 

SELECT COUNT(*) AS 'count'
FROM ANIMAL_INS

 

 

SELECT COUNT(DISTINCT NAME) AS count
FROM ANIMAL_INS

 

 

GROUP BY

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS 'count'
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

 

 

SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
GROPU BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME

 

 

SELECT HOUR(DATETIME) HOUR, COUNT(DATETIME)
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR

 

 

 

IS NULL

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL

 

 

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

 

 

 

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPOP_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

 

 

JOIN

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 A.ANIMAL_ID

 

 

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

 

 

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

 

 

 

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS AS A JOIN ANIMAL_OUTS AS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID AND A.SEX_UPON_INTAKE != B.SEX_UPON_OUTCOME
ORDER BY A.ANIMAL_ID

 

 

 

String, Date

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE name in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

 

 

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE "%EL%" AND ANIMAL_TYPE = "Dog"
ORDER BY NAME;

 

 

SELECT ANIMAL_ID, NAME,
CASE WHEN SEX_UPON_INTAKE LIKE "%Neutered%" OR SEX_UPON_INTAKE LIKE "%Spayed%"
THEN 'O'
ELSE
'X'
END AS '중성화'
FROM ANIMAL_INS

 

 

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A, ANIMAL_OUTS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY B.DATETIME - A.DATETIME DESC
LIMIT 2

 

 

 

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