SQL
정리한 이유
매번 TypeORM의 엄청난 도움을 받으며 포트폴리오를 위한 서버를 구현하다가
팔로우 관계와 유사한 기능이 있는 경우
M:N으로 조인된 컬럼의 데이터를 지워줘야하는 필요성이 생겼다.
그래서 queryBuilder를 통해서 지워주는 방법이 있다는 것을 알게되고 간단한 방법으로 구현하게는 되었으나,
언젠가 SQL의 깊지는 않아도 간단한 문법은 구현할 수 있어야한다는 생각으로
프로그래머스 문제-SQL 고득점 Kit를 풀어보았다.
아래는 간략하게 풀며 정리한 내용이며 추가적으로 신기한 구현방법이 있으면 수정하겠다.
1. SELECT
모든 레코드 조회하기
동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요.
1
SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC
ASC ,DEC 는 각 각 오름차순, 내림차순을 뜻한다.
역순 정렬하기
동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 ANIMAL_ID 역순으로 보여주세요.
1
2
3
SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;
아픈 동물 찾기
1
2
3
4
5
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION
LIKE 'Sick'
ORDER BY ANIMAL_ID ASC;
문자열을 찾을때는 LIKE를 사용하자
추가적으로 %, _가 있는데 앞뒤로 무시 or 무엇이든 을 뜻하게 된다
아래 간단한 두가지 예제를 보자
1
2
3
4
5
SELECT NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION
LIKE '_Sick'
ORDER BY ANIMAL_ID ASC;
앞에 어떤 sick가 나오는 것을 찾게된다 ex)asick
1
2
3
4
5
SELECT NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION
LIKE '&Sick'
ORDER BY ANIMAL_ID ASC;
앞에 어떤것이 나오든 sick가 들어있는 것을 찾게된다 ex)ㅁㄴㅇㄹㅁㅇㄴㄹㄴㅁㅇㄹㅁsick
조합해서도 사용 가능하다
어린 동물 찾기
동물 보호소에 들어온 동물 중 젊은 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.
1
2
3
4
5
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NOT INTAKE_CONDITION
LIKE 'Aged'
ORDER BY ANIMAL_ID ASC;
동물의 아이디와 이름
동물 보호소에 들어온 모든 동물의 아이디와 이름을 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요.
1
2
3
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC;
여러 기준으로 정렬하기
동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회하는 SQL문을 작성해주세요. 단, 이름이 같은 동물 중에서는 보호를 나중에 시작한 동물을 먼저 보여줘야 합니다.
1
2
3
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC;
상위 n개 레코드
동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.
1
2
3
SELECT NAME FROM ANIMAL_INS
WHERE DATETIME = (SELECT MIN(DATETIME)
FROM ANIMAL_INS);
WHERE문을 써서 조건을 줄 수 있으며 GROUP BY로 묶여진 경우 HAVING문을 써주면 된다.
2. SUM, MAX, MIN
최댓값 구하기
가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.
1
SELECT MAX(DATETIME) FROM ANIMAL_INS;
최솟값 구하기
동물 보호소에 가장 먼저 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.
1
SELECT MIN(DATETIME) FROM ANIMAL_INS;
동물 수 구하기
동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 SQL 문을 작성해주세요.
1
SELECT COUNT(*) FROM ANIMAL_INS;
중복 제거하기
동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요.
이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.
1
2
3
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL;
3. GROUP BY
고양이와 개는 몇 마리 있을까
동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.
1
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE
-> ORDER BY ANIMAL_TYPE 는 고양이를 먼저 조회하기 위해 사용
동명 동물 수 찾기
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
1.첫번째 시도
1
SELECT NAME, COUNT(NAME) FROM ANIMAL_INS WHERE COUNT(NAME)>=1 GROUP BY NAME ORDER BY NAME
그룹을 묶은 다음 조건을 쓰기 위해서는 HAVING을 써야한다고 한다..
2.두번째 시도
1
SELECT NAME, COUNT(NAME) FROM ANIMAL_INS GROUP BY NAME HAVING COUNT(NAME)>1 ORDER BY NAME
입양 시각 구하기(1)
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
1
SELECT HOUR(DATETIME), COUNT(HOUR(DATETIME)) FROM ANIMAL_OUTS GROUP BY HOUR(DATETIME) HAVING HOUR(DATETIME) >= 09 AND HOUR(DATETIME) < 20 ORDER BY HOUR
아래와 같이 변수선언 쌉가능 …
1
2
SELECT HOUR(DATETIME) HOUR, COUNT(DATETIME) FROM ANIMAL_OUTS GROUP BY HOUR(DATETIME) HAVING HOUR >= 9 AND HOUR < 20 ORDER BY HOUR
입양 시각 구하기(2)
이문제는 처음에 입양 시각 구하기(1)의 문제와 유사하게 작성하였는데 0~23이라는 범위에서 아는 지식을 넘어갔으므로 .. 검색을 열심히했다 :)
1
2
3
4
5
6
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;
4. IS NULL
이름이 없는 동물의 아이디
동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.
SELECT ANIMAL_ID FRoM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID ASC
이름이 있는 동물의 아이디
동물 보호소에 들어온 동물 중, 이름이 있는 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID ASC
NULL 처리하기
1차시도 -> 뭔가 이렇게 하면 될 것 같다는 막연한 생각으로 도전 ..
1
2
3
4
SELECT ANIMAL_TYPE, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IS NULL AS 'NO Name'
ORDER BY ANIMAL_ID ASC
당연히 안됬다
찾아보니 null처리 해주는 메서드? 문법? 이 따로 있다 :) ….. IFNULL(해당 컬럼, null이름을 뭘로바꿀지) 로 사용하면 된다
1
2
3
SELECT ANIMAL_TYPE, IFNULL(NAME,'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC;
5. JOIN
없어진 기록 찾기
1
천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.
left outer 조인은 명명한 테이블 기준으로 오른쪽에 붙이는 테이블이 빈값이면 null을 반환해주므로 아래와 같이 작성
아래에서는 ANIMAL_INS가 LEFT OUTER JOIN에 명명되었으므로 INS 기준으로 OUTS가 붙게된다.
보통 LEFT JOIN이라고 칭하는게 LEFT OUTER JOIN 이다.
조인할 테이블이면 보통 외래키가 있으므로 ON을 통해 해당 외래키를 연결하면 됨
1
2
3
4
5
6
7
SELECT OUT_A.ANIMAL_ID, OUT_A.NAME
FROM ANIMAL_OUTS AS OUT_A
LEFT OUTER JOIN ANIMAL_INS AS IN_A
ON OUT_A.ANIMAL_ID = IN_A.ANIMAL_ID
WHERE IN_A.ANIMAL_ID IS NULL
ORDER BY OUT_A.ANIMAL_ID ASC
있었는데요 없었습니다
1
관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.
1차시도
아래와 같이하면 문제가 없을 줄 알았음 -> 안됨 -> 중복케이스도 다 나오게 된다
1
2
3
SELECT IN_A.ANIMAL_ID, IN_A.NAME
FROM ANIMAL_INS AS IN_A, ANIMAL_OUTS AS OUT_A
WHERE IN_A.DATETIME > OUT_A.DATETIME
2차시도
1
2
3
4
5
6
SELECT OUT_A.ANIMAL_ID, OUT_A.NAME
FROM ANIMAL_OUTS AS OUT_A
LEFT OUTER JOIN ANIMAL_INS AS IN_A
ON OUT_A.ANIMAL_ID = IN_A.ANIMAL_ID
WHERE IN_A.DATETIME > OUT_A.DATETIME
ORDER BY IN_A.DATETIME ASC
오랜 기간 보호한 동물(1)
1
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
1차시도 정렬 후 limit만 걸면 될 줄 알았다.
1
2
3
4
5
6
SELECT IN_A.NAME, IN_A.DATETIME
FROM ANIMAL_INS AS IN_A
LEFT OUTER JOIN ANIMAL_OUTS AS OUT_A
ON IN_A.ANIMAL_ID = OUT_A.ANIMAL_ID
ORDER BY IN_A.DATETIME ASC
LIMIT 3
2차시도 ANIMAL_INS에 존재하지 않는 정보가 있다면 OUT_A.ANIMAL_ID 는 null값으로 되며 조인 되기때문에
이 부분을 처리해줘야한다
1
2
3
4
5
6
7
SELECT IN_A.NAME, IN_A.DATETIME
FROM ANIMAL_INS AS IN_A
LEFT OUTER JOIN ANIMAL_OUTS AS OUT_A
ON IN_A.ANIMAL_ID = OUT_A.ANIMAL_ID
WHERE OUT_A.ANIMAL_ID IS NULL
ORDER BY IN_A.DATETIME ASC
LIMIT 3
보호소에서 중성화한 동물
1
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
1차시도 뭐가 문제일까 한참 본 쿼리이다
1
2
3
4
5
6
SELECT OUT_A.ANIMAL_ID, OUT_A.ANIMAL_TYPE, OUT_A.NAME
FROM ANIMAL_OUTS AS OUT_A
LEFT OUTER JOIN ANIMAL_INS AS IN_A
ON IN_A.ANIMAL_ID = OUT_A.ANIMAL_ID
WHERE IN_A.SEX_UPON_INTAKE LIKE "%Intact%"
AND OUT_A.SEX_UPON_OUTCOME IN('%Neutered%', '%Spayed%');
2차시도 그냥 풀어서 적용하고 괄호치니까 잘됬다 그러면 IN조건이 조금 문제인듯 하다..
IN 안에서는 %, _ 이런게 안되나보다 ㅇㅁㅇ… (되게 해주지 편할텐데)
1
2
3
4
5
6
SELECT OUT_A.ANIMAL_ID, OUT_A.ANIMAL_TYPE, OUT_A.NAME
FROM ANIMAL_OUTS AS OUT_A
LEFT OUTER JOIN ANIMAL_INS AS IN_A
ON IN_A.ANIMAL_ID = OUT_A.ANIMAL_ID
WHERE IN_A.SEX_UPON_INTAKE LIKE "%Intact%"
AND (OUT_A.SEX_UPON_OUTCOME LIKE "%Neutered%" OR OUT_A.SEX_UPON_OUTCOME LIKE "%Spayed%");
번외로 어떤분의 풀이중에서는 그냥 이름 다른것만 찾아서 리턴시키는분도 계셨다 천재인가보다.
백엔드 개발자 분에게 물어보니 정규식도 좋은 방법이라 하셨다
역시 천재는 많다 :) …
1
2
3
4
5
6
SELECT OUT_A.ANIMAL_ID, OUT_A.ANIMAL_TYPE, OUT_A.NAME
FROM ANIMAL_OUTS AS OUT_A
LEFT OUTER JOIN ANIMAL_INS AS IN_A
ON IN_A.ANIMAL_ID = OUT_A.ANIMAL_ID
WHERE IN_A.SEX_UPON_INTAKE LIKE "%Intact%"
AND OUT_A.SEX_UPON_OUTCOME REGEXP ('neutered|spayed');
6. String, Date
루시와 엘라 찾기
동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요.
1차시도
이렇게 해야하나 싶을정도로 무지한 상태이기 때문에 도전을 해보았지만 .. IN 문법이 있다는 것을 알게되었다. 그리고 아래와 같이 시도하면 Lucy케이스 이외에는 적용되지 않는다.
1
2
3
4
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME
LIKE 'Lucy' OR 'Ella' OR 'Pickle' OR 'Rogan' OR 'Sabrina' OR 'Mitty';
2차시도
IN 문법 적용
1
2
3
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN('Lucy','Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty');
이름에 el이 들어가는 동물 찾기
보호소에 돌아가신 할머니가 기르던 개를 찾는 사람이 찾아왔습니다. 이 사람이 말하길 할머니가 기르던 개는 이름에 ‘el’이 들어간다고 합니다. 동물 보호소에 들어온 동물 이름 중, 이름에 “EL”이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않습니다.
개인프로젝트를 하면서 검색등을 위해 몇번씩 구현해본 것이라 쉽게 가능
1
2
3
4
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%el%' AND ANIMAL_TYPE='Dog'
ORDER BY NAME ASC
오랜 기간 보호한 동물(2)
입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.
이건 뭐 문제보고 한 5분만에 그냥 찾아봤다 지식이 거의 없어서 혼자 삽질할빠에 빨리 답을알고 잘 적용하는게 더 시간아끼는길이라 판단이 되어 …(변명인가)
여튼 외래키 사용까지는 알고 있었는데 LIMIT를 몰랐다 .. ㅎㅎ..ㅎ.ㅎ
- 중복케이스가 나와도 상위 2개만을 잡기때문에 해결에 문제는 없지만 더 정확한 것은 join 을 통해서 해결하면 좋다.
1
2
3
4
5
SELECT IN_A.ANIMAL_ID, IN_A.NAME
FROM ANIMAL_INS AS IN_A, ANIMAL_OUTS AS OUT_A
WHERE IN_A.ANIMAL_ID = OUT_A.ANIMAL_ID
ORDER BY OUT_A.DATETIME - IN_A.DATETIME
DESC LIMIT 2
중성화 여부 파악하기
보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 ‘Neutered’ 또는 ‘Spayed’라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 ‘O’, 아니라면 ‘X’라고 표시해주세요.
1차시도
분명 무슨 조건문 같은거 쓸 수 있을 것 같은데 그게 뭔지 몰라서 일단 …
1
2
3
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%'
2차시도
그래서 CASE WHEN THEN ELSE END 조합으로 사용하는 방법을 알게되었다. 그리고 LIKE IN(‘a’,’b’) 와 같은 행위가 되지않는것도 알게되었다 ㅇㅅㅇ..
1
2
3
4
5
6
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
DATETIME에서 DATE로 형 변환
ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜1를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.
1차시도 얼추 이렇게 폼 잡아주면 될 것 같았는데 안됨
1
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, 'yyyy-mm-dd') AS '날짜' FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC`
2차시도 정해진 형식이 있다.. %Y는 4자리 년도 %y는 2자리 연도라고 한다.
1
2
3
4
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC
`