Programmers - MySQL Solutions

프로그래머스 - 코딩테스트 연습에 기재된 MySQL 문제 풀이입니다.
아래와 동일한 코드 파일은 https://github.com/EUN316/python-code/blob/main/Programmers/MySQL.sql 에 업로드되어 있습니다.

프로그래머스

코딩테스트 연습 - MySQL Lv.1~5

SELECT

-- 모든 레코드 조회하기
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'
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 ASC, 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(*)
FROM ANIMAL_INS

-- 중복 제거하기
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS

IS NULL

-- 이름이 없는 동물의 아이디
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

-- 이름이 있는 동물의 아이디
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

-- NULL 처리하기
SELECT ANIMAL_TYPE, 
CASE WHEN NAME IS NULL THEN 'No name' ELSE NAME END AS NAME, 
SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

GROUP BY

-- 고양이와 개는 몇 마리 있을까
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Cat' OR ANIMAL_TYPE = 'Dog'
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

-- 동명 동물 수 찾기
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(HOUR(DATETIME)) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
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;

JOIN

-- 없어진 기록 찾기
SELECT T1.ANIMAL_ID, T1.NAME
FROM ANIMAL_OUTS AS T1
LEFT JOIN ANIMAL_INS AS T2 ON T1.ANIMAL_ID = T2.ANIMAL_ID
WHERE T2.DATETIME IS NULL
ORDER BY T1.ANIMAL_ID

-- 있었는데요 없었습니다
SELECT T1.ANIMAL_ID, T1.NAME
FROM ANIMAL_INS AS T1
INNER JOIN ANIMAL_OUTS AS T2 ON T1.ANIMAL_ID = T2.ANIMAL_ID
WHERE T1.DATETIME > T2.DATETIME
ORDER BY T1.DATETIME

-- 오랜 기간 보호한 동물(1)
SELECT T1.NAME, T1.DATETIME
FROM ANIMAL_INS AS T1
LEFT JOIN ANIMAL_OUTS AS T2 ON T1.ANIMAL_ID = T2.ANIMAL_ID
WHERE T2.DATETIME IS NULL
ORDER BY T1.DATETIME
LIMIT 3

-- 보호소에서 중성화한 동물
SELECT T1.ANIMAL_ID, T1.ANIMAL_TYPE, T1.NAME
FROM ANIMAL_INS AS T1
INNER JOIN ANIMAL_OUTS AS T2 ON T1.ANIMAL_ID = T2.ANIMAL_ID
WHERE T1.SEX_UPON_INTAKE LIKE 'Intact%' 
AND (T2.SEX_UPON_OUTCOME LIKE 'Spayed%' OR T2.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY T1.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

-- 이름에 el이 들어가는 동물 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' AND UPPER(NAME) LIKE '%EL%'
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
ORDER BY ANIMAL_ID

-- 오랜 기간 보호한 동물(2)
SELECT T1.ANIMAL_ID, T1.NAME
FROM ANIMAL_INS AS T1
INNER JOIN ANIMAL_OUTS AS T2 ON T1.ANIMAL_ID = T2.ANIMAL_ID
ORDER BY T2.DATETIME - T1.DATETIME DESC
LIMIT 2

-- DATETIME에서 DATE로 형 변환
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d')
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

Etc

-- 우유와 요거트가 담긴 장바구니
/*
SELECT T1.CART_ID
FROM CART_PRODUCTS AS T1
INNER JOIN CART_PRODUCTS AS T2 ON T1.CART_ID = T2.CART_ID
WHERE T1.NAME = 'Milk' AND T2.NAME = 'Yogurt'
ORDER BY T1.CART_ID
*/
SELECT CART_ID
FROM CART_PRODUCTS
WHERE CART_ID IN (SELECT CART_ID
                 FROM CART_PRODUCTS
                 WHERE NAME = 'Milk')
AND NAME = 'Yogurt'
ORDER BY CART_ID

-- 헤비 유저가 소유한 장소
SELECT *
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID 
                  FROM PLACES
                  GROUP BY HOST_ID
                  HAVING COUNT(HOST_ID)>=2)
ORDER BY ID