[프로그래머스][SQL] 고득점 kit

프로그래머스 SQL 고득점 Kit 

• Select : https://programmers.co.kr/learn/courses/30/parts/17042

프로그래머스

-- 1. 모든 레코드 조회하기
SELECT * from animal_ins order by animal_id asc

-- 2. 역순 정렬하기
SELECT Name, datetime from animal_ins order by animal_id desc

-- 3. 아픈 동물 찾기
SELECT animal_id, name from animal_ins where intake_condition = "Sick" order by animal_id asc ;

-- 4. 어린 동물 찾기
SELECT animal_id, name from animal_ins where not intake_condition = "aged" order by animal_id ASC

-- 5. 동물의 아이디와 이름
SELECT animal_id, name from animal_ins order by animal_id asc

-- 6. 여러 기준으로 정렬하기
SELECT animal_id, name, datetime from animal_ins order by name asc, datetime desc

-- 7. 상위 n개 레코드
SELECT name from animal_ins order by datetime asc limit 1

 

---

• sum, max, min : https://programmers.co.kr/learn/courses/30/parts/17043

프로그래머스

-- 1. 최댓값 구하기
SELECT datetime from animal_ins order by datetime desc limit 1

-- 2. 최솟값 구하기
SELECT min(datetime) from animal_ins

-- 3. 동물 수 구하기
SELECT count(animal_id) from animal_ins;

-- 4. 중복 제거하기
SELECT distinct(name) from animal_ins

 

---

• Group by : https://programmers.co.kr/learn/courses/30/parts/17044

프로그래머스

-- 1. 고양이와 개는 몇마리 있을까
SELECT animal_type, count(animal_id) as count from animal_ins group by animal_type order by animal_type asc

-- 2. 동명 동물 수 찾기
SELECT name, count(name) as count from animal_ins group by name having count(name) >= 2 order by name asc

-- 3. 입양 시작 구하기 (1)
select HOUR(datetime) as hour, count(datetime) as count 
from animal_outs 
group by hour having hour >= 9 and hour < 20
order by hour asc

-- 4. 입양 시각 구하기 (2)
SET @h = -1;
SELECT (@h := @h + 1) as hour,
(select count(hour(datetime)) from animal_outs where hour(datetime) = @h) as count
from animal_outs where @h < 23

 

---

• IS NULL : https://programmers.co.kr/learn/courses/30/parts/17045

프로그래머스

-- 1. 이름이 없는 동물의 아이디
SELECT animal_id from animal_ins where name is NULL

-- 2. 이름이 있는 동물의 아이디
SELECT animal_id from animal_ins where name is not null order by animal_id asc

-- 3. NULL 처리하기
SELECT animal_type, ifnull(name, "No name") as name, sex_upon_intake from animal_ins;

 

---

• JOIN : https://programmers.co.kr/learn/courses/30/parts/17046

프로그래머스

-- 1. 없어진 기록 찾기
SELECT outs.animal_id, outs.name from animal_outs outs
left outer join animal_ins ins
on outs.animal_id = ins.animal_id
where ins.animal_id is null
order by outs.animal_id asc

-- 2. 있었는데요 없었습니다
SELECT ins.animal_id, ins.name from animal_ins ins
join animal_outs outs on ins.animal_id = outs.animal_id
where ins.datetime > outs.datetime
order by ins.datetime asc

-- 3. 오랜 기간 보호한 동물 (1)
select ins.name, ins.datetime from animal_ins ins
left join animal_outs outs 
on ins.animal_id = outs.animal_id
where outs.animal_id is null
order by ins.datetime asc
limit 3

-- 4. 보호소에서 중성화한 동물
SELECT ins.animal_id, ins.animal_type, ins.name from animal_ins ins
left join animal_outs outs
on ins.animal_id = outs.animal_id
where (outs.sex_upon_outcome like '%Spayed%' or outs.sex_upon_outcome like '%Neutered%')
and ins.sex_upon_intake like '%Intact%'
order by ins.animal_id asc

 

---

• String, Date : https://programmers.co.kr/learn/courses/30/parts/17047

프로그래머스

-- 1. 루시와 엘라 찾기 
SELECT animal_id, name, sex_upon_intake from animal_ins
where name="Lucy" or name="Ella" or name="Pickle" or name="Rogan" or name="Sabrina" or name="Mitty"
order by animal_id asc

-- 2. 이름에 el이 들어가는 동물 찾기
SELECT animal_id, name from animal_ins
where lower(name) like "%el%" and animal_type = "Dog"
order by name asc

-- 3. 중성화 여부 파악하기
SELECT animal_id, name,
case when (SEX_UPON_INTAKE LIKE '%NEUTERED%' OR SEX_UPON_INTAKE LIKE '%SPAYED%')then 'O' else 'X' end
from animal_ins
order by animal_id asc

-- 4. 오랜 기간 보호한 동물 (2)
SELECT ins.animal_id, ins.name from animal_ins ins, animal_outs outs
where ins.animal_id = outs.animal_id
order by datediff(outs.datetime, ins.datetime) desc
limit 2

-- 5. DATETIME에서 DATE로 형 변환
SELECT animal_id, name, DATE_FORMAT(datetime, '%Y-%m-%d') as 날짜 from animal_ins
order by animal_id asc