프로그래머스 SQL 문제 풀이

최댓값 구하기

SELECT datetime FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1

---

SELECT MAX(DATETIME) 
FROM ANIMAL_INS;

최솟값 구하기

SELECT DATETIME FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1;

---

SELECT MIN(DATETIME)
FROM ANIMAL_INS;

동물 수 구하기

SELECT count(*) FROM ANIMAL_INS;

---

SELECT COUNT(*) 
FROM ANIMAL_INS;

중복 제거하기

SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS;

---

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

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

SELECT ANIMAL_TYPE, COUNT(*)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

동명 동물 수 찾기

SELECT NAME, COUNT(*)
FROM ANIMAL_INS
WHERE NAME is not NULL
GROUP BY NAME
HAVING count(*) > 1
ORDER BY NAME

---

SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME;

입양 시각 구하기(1)

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

---

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

입양 시각 구하기(2)

SELECT HOUR(DATETIME) AS HOUR, COUNT(*)
FROM ANIMAL_OUTS
GROUP BY HOUR
ORDER BY HOUR

없어진 기록 찾기

SELECT ao.ANIMAL_ID, ao.NAME FROM animal_outs AS ao
LEFT JOIN animal_ins AS ai ON ao.animal_id = ai.animal_id
WHERE ai.intake_condition is NULL;

SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_INS RIGHT JOIN ANIMAL_OUTS 
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE INTAKE_CONDITION IS NULL;

---

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

있었는데요 없었습니다.

SELECT ai.ANIMAL_ID, ai.NAME FROM ANIMAL_INS AS ai
LEFT JOIN ANIMAL_OUTS AS ao ON ai.ANIMAL_ID = ao.ANIMAL_ID
WHERE ai.DATETIME > ao.DATETIME
ORDER BY ai.DATETIME;

---

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

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

SELECT ai.name, ai.datetime FROM animal_ins as ai
LEFT JOIN animal_outs as ao ON ai.animal_id = ao.animal_id
WHERE SEX_UPON_OUTCOME is NULL
ORDER BY ai.datetime LIMIT 3

---

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

보호소에서 중성화한 동물

SELECT ai.animal_id, ai.animal_type, ai.name 
FROM animal_ins AS ai
LEFT JOIN animal_outs AS ao ON ai.animal_id = ao.animal_id
WHERE sex_upon_intake != sex_upon_outcome

---

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS AS A
LEFT JOIN ANIMAL_OUTS AS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE 'Intact%' 
AND (B.SEX_UPON_OUTCOME LIKE 'Spayed%' OR B.SEX_UPON_OUTCOME LIKE 'Neutered%');

루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME LIKE 'Lucy' OR NAME LIKE 'Ella' OR NAME LIKE 'Pickle'
OR NAME LIKE 'Rogan' OR NAME LIKE 'Sabrina' OR NAME LIKE 'Mitty'
ORDER BY ANIMAL_ID

---

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

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

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE UPPER(NAME) LIKE '%EL%' AND UPPER(ANIMAL_TYPE) LIKE 'DOG'
ORDER BY NAME

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

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS AS A
RIGHT JOIN ANIMAL_OUTS AS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NOT NULL
ORDER BY B.DATETIME - A.DATETIME DESC
LIMIT 2