[MySQL] 다시 체크할 SQL 구문! (프로그래머스 MySQL level1, 2)

진짜진짜 완전 오랜만에 쓰는 티스토리 블로그

앞으로 개발블로그로 쓸것이다!!

지금은 내일 있는 프로그래머스 겨울인턴 코딩테스트를 위해 코테 연습을 해보려고한다!

(힘..힘내자)

---

[프로그래머스] 코딩테스트 연습문제::MySQL (level 1,2)

---

1. order by 사용법

SELECT * FROM Customers ORDER BY Country DESC;	--내림차순
SELECT * FROM Customers ORDER BY Country ASC;	--오름차순 (default)

where가 있는 경우 where 먼저

2. MIN, MAX 날짜에 적용 시

SELECT MAX(DATETIME) FROM ANIMAL_INS; --최신
SELECT MIN(DATETIME) FROM ANIMAL_INS; --오래된 것 (ex) 가장 먼저 들어온 동물)

 MAX가 최근꺼~! 그래서 나온 결과는?  2018-02-03 10:40:00

그렇다면 가장 최근의 데이터인 모든 row를 가져오기 위해서는 어떻게 해야할까?!

stack overflow가 그랬다. Use a subselect

SELECT *  FROM ANIMAL_INS  WHERE DATETIME=(
    SELECT max(DATETIME) FROM ANIMAL_INS
) -- 5번에서 설명한 limit를 사용해도 된다!

이렇게 가져온 max값을 where 문의 datetime의 값으로 주면 됨!!

구우우우욷!ㅎㅎ

3. 중복제거하기(distinct, group by)

문제) 동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.

중복을 제거하는 방법은 2가지가 있다.

(1)  distinct 키워드

 (temp tablespace에 임시로 저장하고 작업하는 방식이라 시스템에 부하를 줄 수 있음)

SELECT distinct name FROM ANIMAL_INS --결과: 중복안되게 이름들이 쫙 나옴

문제에서는 결과로 레코드의 개수를 구해야 하므로 집계함수 count로 distinct를 감싸준다.

SELECT count(distinct name) FROM ANIMAL_INS 

굳!!

(2)  group by 절

(select절의 컬럼을 group by절에도 동일하게 명시를 해야하는 부담감이 있지만 distinct에 비하여 조금 빠른감이 있다고 한다)

SELECT name FROM ANIMAL_INS group by name
-- 여기까지는 결과로 중복이 제거된 이름 목록이 쭉 나온다 (select distinct name... 했을 때와 같음)

하지만 count를 사용하면 group by로 묶은 각각의 이름 개수들이 나옴

이게 아닌데..

결론!! count 집계함수 사용시, group by는 각각의 겹치는 개수를, distinct는 겹치지 않는 값들 전체의 개수를 반환한다.

바로 다음문제가 바로 group by에 count를 사용한 경우였다

문제: 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요 

SELECT ANIMAL_TYPE, count(*) FROM ANIMAL_INS  group by ANIMAL_TYPE order by ANIMAL_TYPE

4. order by 복수 정렬조건

문제) 동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회하는 SQL문을 작성해주세요. 단, 이름이 같은 동물 중에서는 보호를 나중에 시작한 동물을 먼저 보여줘야 합니다.

1. 이름 순(오름차순) 2. 시간 순(내림차순) 으로 정렬을 해야한다.

SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS order by NAME asc, DATETIME desc

콤마(,)로 구분하여 정렬 조건을 두 개 이상 줄 수 있다.

5. 상위 n개 레코드만 보여주기: limit

문제) 가장 먼저 들어온 동물들의 이름 보여주기

가장먼저이니까 (한개만보여줄때) 두가지 방법으로 사용 가능하다

-- 1. limit
SELECT NAME FROM ANIMAL_INS order by DATETIME LIMIT 1;	--limit n으로 사용 -> 상위 n개 출력
-- 2. subselect
SELECT NAME FROM ANIMAL_INS  WHERE DATETIME=(
    SELECT min(DATETIME) FROM ANIMAL_INS
)

 

6. where절에서 이름이 null인 레코드 찾기

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME is NULL

반대로 null이 아닌 것들만 출력하려면

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME is not NULL

7. null 초기값 설정

... 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 No name으로 표시해 주세요.

SELECT ANIMAL_TYPE, IFNULL(NAME, "No name"), SEX_UPON_INTAKE FROM ANIMAL_INS order by ANIMAL_ID

IFNULL(속성필드, null일때 대체할 값) : null이 아니면 첫번째 인자 리턴, null이면 두번째 인자 리턴

8. having

문제) 동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외(where name is not null 추가)하며, 결과는 이름 순으로 조회해주세요.

SELECT name, count(*) from ANIMAL_INS group by name  having count(name) > 1 order by name

두번이상인데 having에 >2 이러고 있어서 틀렸다;;

9. 입양시각 구하기

문제) 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회, 결과는 시간대 순으로 정렬

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

having 절을 안써서 틀렸다..

SELECT 
    HOUR(@dt),
    MINUTE(@dt),
    SECOND(@dt),
    DAY(@dt),
    WEEK(@dt),
    MONTH(@dt),
    QUARTER(@dt),
    YEAR(@dt);

datetime은 이렇게 시, 분, 초, 일, 주, 달 등등 만 가져올 수있다. date()로 날짜만, time()으로 시간만 가져올 수도 있음

(입양시각구하기(2)는 어려워서 다음에 다시!)

10. WHERE IN 사용하기

IN은 리스트나 서브쿼리 안에 있는 값과 매칭하는 레코드를 반환한다.

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"
-- 중첩 OR를 WHERE IN 사용하면 훨씬 간단하게 표현할 수 있음
 SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty');

11. 부분문자열 포함 여부 알기

문제) 보호소에 돌아가신 할머니가 기르던 개를 찾는 사람이 찾아왔습니다. 이 사람이 말하길 할머니가 기르던 개는 이름에 'el'이 들어간다고 합니다. 동물 보호소에 들어온 동물 이름 중, 이름에 EL이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않습니다.

-- locate 사용
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS
WHERE LOCATE("el", NAME) > 0 and ANIMAL_TYPE = "Dog"
order by name
-- like 사용
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS 
WHERE NAME LIKE '%EL%' AND ANIMAL_TYPE LIKE 'Dog'
ORDER BY NAME

결과

개를 찾고 있는데 animal_type으로 dog를 필터링 안해줘서 틀렸다.

12. DATE_FORMAT

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") as 날짜
FROM ANIMAL_INS order by ANIMAL_ID

%y (소문자)로 하면 연도의 뒷 두자리만 나옴(ex) 2018 -> 18) (대문자 -> 2018)

13. case 사용

문제) 보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

case 사용법:

CASE
	WHEN 조건
	THEN '반환 값'
	WHEN 조건
	THEN '반환 값'
	ELSE 'WHEN 조건에 해당 안되는 경우 반환 값'
END

답)

-- 코드를 입력하세요
SELECT
    ANIMAL_ID,
    NAME,    
    case
        when (SEX_UPON_INTAKE Like'%Neutered%') OR (SEX_UPON_INTAKE Like'%Spayed%') then 'O'
        else 'X' --else를 지정해 주지 않은 경우 null 반환
    end as '중성화'
FROM ANIMAL_INS

---

후 진짜 오랜만에 푸니까 너무 어렵고,

sql에 내가 모르는 문법들이 정말 많은 것 같다

외주할 때 너무 비효율적으로 쿼리문을 구성한것 같다..

sql도 더 많이 공부해야겠다

level 1, level 2 다풀었다! 이제 3,4도 풀어야지