JPQL 을 공부하면서, 또 개발을 하면서 SQL 문법들을 좀 익숙하게 해야겠다는 느낌이 들었다. 결국 개발을 하려면 데이터 베이스를 사용해야 하고 여러가지 복잡한 쿼리 또한 만들 줄 알아야하는데 당장 나는 알고리즘에만 능숙하지 데이터베이스 관련해서는 많이 부족하다고 생각했다. 대학생떄 수업도 듣고 좋은 점수도 받은 기억이 있지만 초심으로 돌아가서 프로그래머스 문제들을 다 풀어볼려고 한다.
SELECT
SELECT
SQL 에서 가장 기본적인 문법이다.
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != "AGED"WHERE 을 사용해서 AGED 가 아닌 목록들만 뽑았다.
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC,
DATETIME DESCORDER BY 는 순서를 정할 수 있게 해주는데 ASC 로 오름차순 DESC 로 내림차순해서 요구사항에 맞춰서 출력했다.
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME ASC
LIMIT 1가장 먼저 들어온 동물 한명만 출력하면 됐고 ASC 오더로 맞춘후에 LIMIT 을 사용해서 상위 N 개의 목록을 출력했다.
SUM, MAX, MIN
SUM, MAX, MIN
SELECT DATETIME
FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1가장 먼저 생각했던 솔루션, 하지만,
SELECT MAX(DATETIME)
FROM ANIMAL_INS 위와 같이 MAX 문법을 쓰게되면 해당 컬럼에서 가장 큰 값을 불러와준다.
SELECT COUNT(ANIMAL_ID)
FROM ANIMAL_INS COUNT 문법은 테이블의 컬럼의 데이터 개수를 가지고 온다한다. 그러나 NULL 값은 제외하고 계산하기에 조심해야한다.
전체 행 갯수를 가지고 오고 싶다면
SELECT COUNT(*) FROM TABLE 이런식으로 할 수고 있고
컬럼 데이터 갯수를 가지고 오고 싶다면
SELECT COUNT(컬럼) FROM TABLE이런식으로 해야한다.
만약 위에 문제에서 COUNT(NAME) 을 했다면은 틀리다고 나올거기 때문에 꼭 COUNT(ANIMAL_ID) 해줘야 한다.
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS중복을 제거 해야됐고 NAME 을 기준으로 제거하는 것이기 때문에 DISTINCT 를 NAME 옆에다가 써줘야 했다.
GROUP BY
GROUP BY
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC 유형별로 개수를 가지고 오게 하기 위해서기 떄문에 그냥 COUNT 만을 쓰는게 아니라 같이 묶을 컬럼을 GROUP BY 문법으로 정했다. 후에는 ANIMAL_TYPE 이 이름 순으로 나와야 했기 때문에 ORDER BY 를 넣어주었다.
SELECT NAME, COUNT(NAME) as cnt
FROM ANIMAL_INS
GROUP BY NAME
HAVING cnt > 1
ORDER BY NAME ASC조금씩 쿼리가 재밌어져간다. 이름 횟수를 출력하면 되는 문제였고 NAME 과 COUNT(NAME) 을 GROUP BY 해주면 됐었다. 그러나 GROUP BY 를 끝낸 후에 만들어진 컬럼에서 조건문을 실행 해야 했기때문에 HAVING 을 붙혀줬다.
GROUP BY 참고 블로그 1
GROUP BY 참고 블로그 2
SELECT HOUR(DATETIME) as HOUR, COUNT(DATETIME) as CNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 and HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME) ASC 조금씩 조합이 늘고 있는 기분이다. 먼저 새로운 문법은 HOUR 이라는 문구이다. DATETIME 타입에 HOUR을 적게 되면 시간만 출력을 해준다. 전 문제와 마찬가지로 COUNT 를 GROUP BY 로 묶어 주었고 WHERE 문법을 섞어줬다. 한가지 배운점이 있다면 HOUR 문법을 쓸때 그냥 HOUR 로만 적으면 에러가 나오고 꼭 HOUR(DATETIME) 을 같이 써줘야 한다는 점이다.
다른 COUNT 같은 문법은 AS 를 쓰면은 그거 그대로 쓸수 있었는데 신기한거같다.
아까랑 비슷한 느낌의 문제인거 같아서 쉬운줄 알았지만 레벨이 4인걸 보고 좀 깜짝 놀랐다. 내가 분명 모르는 SQL 문법이 나올거같아서 고민 했는데 아무리 생각해도 전 문제랑 비슷한거 같아서 똑같이 써봤는데,
SELECT HOUR(DATETIME) as HOUR, COUNT(DATETIME) as CNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 0 and HOUR(DATETIME) <= 23
GROUP BY HOUR(DATETIME) 
이런식으로 나왔다. 그렇다, 문제는 0시부터 23시까지의 모든 카운트를 출력 해야 했는데 그냥 HOUR 로만 적게 되면은 존재 하는 시간 만 출력 하기 때문에 답이 될수가 없었다.
SET @hour := - 1; -- 변수 선언
SELECT (@hour := @hour + 1) as HOUR,
(SELECT COUNT(DATETIME) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as cnt
FROM ANIMAL_OUTS
WHERE @hour < 23 굉장히 신기한 문법이고 처음 써봤다. JPQL 에서도 저런 문법을 보긴 봤는데 뭔지 몰라서 헷갈렸는데 SQL 문법을 풀게 되면서 이해가 되는거 같다.
SQL 에서는 = 연산자로 비교를 할 수 있지만 변수를 지정 해주는 문법은 다르게 쓰여야 한다. C++ 에서는 == 과 = 이 다르게 쓰이듯이 SQL 에서는 비교 연산자는 = 이고 변수에 값을 입력 할때는 := 을 쓰는 방식이다.
변수를 선언하고 싶을때는 꼭 SET 이라는 키워드를 쓰고 키워드로 @변수이름 쓰는게 인상적이었다.
옆에 카운트를 sub-query 로 쓰는것도 볼 수 있는데 HOUR(DATETIME) = @hour 이라고 쓰면서 비교 해주고 @hour := @hour +1 을 쓴것은 WHERE 문과 연결이 되는데 마치 while 룹을 쓰는것과 비슷해보였다.
int hour = -1;
while(hour < 23){
hour += 1
}로 해석할 수 있지 않을까 싶다. 역시 레벨4 쿼리인만큼 상당히 복잡하고 어려웠다.
IS NULL
IS NULL
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL 간단한 문법이었고 딱히 설명 할건 없어보인다. IS NULL 은 NULL 값을 체크해준다.
NULL 값이 있다면 이름을 변경해서 출력해야 하는 문제이다. 문제를 읽자마자 이런거 IF 가 과연 있을까? 했는데 있었다.
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASCIFNULL 은 만약 해당 값이 NULL 이면 교체하고 아니면 그대로 쓰는 문법이다.
JOIN
JOIN
내가 SQL 에 대해서 좀 더 깊은 공부를 하고 싶다는 생각을 들게 했던 JOIN 쿼리다. 상당히 중요한 쿼리이고 실제로 예시 문제들도 3~4 레벨로 꽤 높은 수준을 요구하고 있다. 일단 JOIN 에 대한 이해가 많이 필요하다고 생각하는데 이 문제에서는 ANIMAL_OUTS 에는 존재하지만 ANIMAL_INS 에는 존재하지 않는 ID 와 NAME 을 출력해야 한다.
그러기 위해서는 기본적으로 ANIMAL_OUTS 테이블에 있는 모든 컬럼들은 유지한채로 ANIMAL_INS 에는 없는 컬럼을 출력해야 하는데 이때 사용되는게 LEFT OUTER JOIN 이라고 한다.
LEFT OUTER JOIN 같은 경우
- 기준으로 잡는 테이블의 컬럼들은 모두 유지한다
- 테이블을 ON 으로 컬럼을 선택해서 조인을 하게 되면, 조인이 되는 테이블에서 같은 컬럼들 끼리 합쳐진다
- 만약 기준점으로 잡은 테이블에 컬럼에 맞는 컬럼이 오른쪽에 없다면 NULL 값으로 표시된다
이런 이론들을 살려서 문제를 풀어야 했고 답은 아래와 같다.
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS as OUTS
LEFT OUTER JOIN ANIMAL_INS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID ASC OUTS 에 있는 ANIMAL ID 와 INS 에 있는 ANIMAL ID 를 조인해주었고 OUTS 테이블은 그대로 모두 유지했다.
반대로, OUTS 이름에 존재하지 않는 컬럼은 모두 NULL 로 표시되어 있기 때문에 WHERE 문을 날려서 INS 테이블에 NULL 이라고
표시된 컬럼을 OUTS.ANIMAL_ID, 그리고 OUTS.NAME 으로 SELECT 후에 표시하면 풀 수 있는 문제다.
역시 3레벨이라서 그런지 어려워졌고 그래도 과거에 수업을 듣고 자격증 공부를 해서 그런지 잘 이해가 되서 다행이다.
보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회 했어야 했다. 그러기 위해서는 INS 테이블과 OUTS 테이블을 JOIN 해주는 쿼리가 필요했는데 이 전에 배웠던 LEFT OUTER JOIN 을 써도 되고 INNER JOIN 으로 공통된 부분을 조인해도 답이 나왔을것이다.
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS as OUTS
LEFT JOIN ANIMAL_INS as INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE OUTS.DATETIME < INS.DATETIME
ORDER BY INS.DATETIME ASC 이제 조인 쿼리를 수행하거나 SELECT 쿼리를 두 테이블을 나눠서 선택할때는 ALIAS, 즉 AS 를 적극적으로 써줘야 한다는것을 배웠다. (물론 AS 는 생략해도 된다)
나는 OUTS 테이블에 LEFT OUTER JOIN (혹은 LEFT JOIN) 해줘서 INS 와 합쳐주었다.
그리고 좀 배워야 할점은 OUTS.ANIMAL_ID = INS.ANIMAL_ID 가 아닌 NAME 같은 포인트로 조인을 할 경우 중복되는 부분이 있어서 답이 아니게 나왔다. 조인을 할 경우에는 PK 위주로 조인을 해주자.
후에는 WHERE 문을 살려서 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 SELECT 문으로 컬럼을 뽑아 주었다.
SQL JOIN 쿼리의 요약본이다 참고해도 좋을거같다.
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL 문을 작성해야 했다. 이 문제에서는 INS 테이블에 더 집중을 해줘야 했고 LEFT JOIN 을 INS 에 그리고 조인하는 테이블은 OUTS 를 해줬다.
SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS as INS
LEFT JOIN ANIMAL_OUTS as OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME ASC
LIMIT 3배운점은 IS NULL 을 써줄때 OUTS.NAME 으로 해주니깐 틀렸었다. 왜냐면은 NAME 컬럼에서 아무것도 안적힌 부분이 있어서 그런거 같다. 전 문제에서도 작성했지만 PK 를 위주로 써주는게 나을거같다.
들어올때는 중성화가 되지 않았지만 보호소를 나갈때는 중성화된 동물의 아이디와 생물 종, 그리고 이름을 아이디 순으로 출력하는 SQL 문제였다.
레벨 4라서 이전처럼 SET 같은걸 사용해서 변수를 지정해주는 문제일까 하고 긴장했지만 생각보다 간단했고 이 전 문제와 꽤 유사한 점도 많았다.
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 INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME
ORDER BY INS.ANIMAL_ID ASC 간단하게 중성화 여부가 INS 와 OUTS 가 다르면은 출력 해주었다.
STRING, DATE
STRING, DATE
새로운 토픽이 나왔고, 이번에는 꼭 탐색해야 하는 이름 목록을 주고 저 이름들만 출력해야 하는 SQL 쿼리를 작성해야 한다.
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty') 새로운 문법인 IN 을 배웠다. WHERE 이랑 같이 써줘야 하며 파이썬의 IN 문법이 떠올랐다.
동물 타입은 강아지, 그리고 이름에 'EL' 이라는 단어가 들어간 후보들을 SQL로 출력하면 되는 문제이다. 솔직히 SQL 문법이 대부분 익숙하지 않기 때문에 바로 답을 참고 했다.
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' AND NAME LIKE '%EL%'
ORDER BY NAME ASC 새로운 문법인 LIKE 를 배웠다.
위 내용과 같이, _ 혹은 %를 써줘도 되는데
-
_ 를 쓸경우 앞에 몇개를 쓰는 경우에 따라서 특정 데이터를 출력할수 있다.
-
% 를 쓸경우 글자수와 상관없이 찾게되고 앞에 붙히면 끝자리가 EL 로 끝나느것을 찾게 되고, 뒤에 붙히면 처음이 EL 붙은 데이터를 찾게되고, 양쪽에 붙히면은 어느 곳이든 EL 이 들어가면 된다고 한다.
참 신기한 내용이었고 SQL이 신기하다고 느꼈다. 분명 전에 배웠던 내용인데도 다시 배우게 된다.
꽤 새로운 형태의 문제였다. 중성화 여부에 따라서 'O' 혹은 'X' 를 출력해야 했고 중성화 컬럼을 만들어야 하는 SQL 쿼리다.
문제를 읽자마자 C++ 개념으로 생각했던거는 IF 혹은 CASE 문이 있으면 좋을거같다 라고 생각했고 찾아보니 역시 SQL 에도 CASE 와 IF 문이 존재 했었다. 꽤 동적인 쿼리로 이렇게 적을 수 있다는 점이 마음에 들었고 바로 실행 했다.
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 ASC되게 흥미롭다고 생각했고 SQL 에서 처음 보는 조건문 이었다. 물론 IF 문도 존재하긴 했지만 검색해본 결과 CASE 문이 좀 더 다채롭게 쓸수 있다고 한다
Neutered 혹은 Spayed 가 적힌 중성화 여부에 따라서 O 나 X 를 적었다.
CASE 를 먼저 써주고,
WHEN 으로 조건문을 써준 후에,
THEN 으로 WHEN 조건문이 TRUE 일 경우에 'O' 를 써주었고
ELSE 로 다른 경우인 'X'를 적어주었다.
END 로 닫아주는것도 잊지 말아야 겠다.
END 로 닫아준 후에는 AS 중성화를 적어줌으로 컬럼 이름을 변경 해주었다.
보호기간이 가장 길었떤 동물 두마리의 아이디와 이름을 조회하는 SQL 문제이다. 레벨3의 문제로 내가 모르는 문법이 한번 더 나왔다. 이 문제는 전에 풀었던 문제와는 다르게 직접적으로 INS 의 데이트타임과 OUTS 의 데이트 타임을 비교 해줘야 했고 그 차이가 가장 큰 순서로 정렬 해주면 됐었다.
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS as OUTS
JOIN ANIMAL_INS as INS ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
ORDER BY DATEDIFF(OUTS.DATETIME,INS.DATETIME) DESC
LIMIT 2 DATEDIFF 라는 함수를 새로 배워서 되게 좋았던거 같다. 이번에 JOIN 을 써봤는데 이번 JOIN 은 INNER JOIN 으로서 공통된 컬럼만 조인했고 원하는 답을 얻을 수 있었다.
DATETIME 형식을 DATE 로 변환해야 했다. 순간 이 문제를 보고 TYPE CASTING 이 생각났고 답을 참고해서 어떻게 써야하는지 확인했다.
SELECT ANIMAL_ID, NAME,
DATE_FORMAT(DATETIME,'%Y-%m-%d') as 날짜
FROM ANIMAL_INS
이런식으로 %Y-%m-%d 형식으로 년도, 월, 일 로 타입을 바꿀수 있는게 신기했다. %Y 같은 경우 4자리 년도, 1995 같은 년도고 %y 같은 경우는 95 로 차이가 있다고 한다.
결론
이로서 프로그래머스에서 기본적을 나온 모든 SQL 문제를 풀어보았다. 좀 즉석으로 빠르게 빠르게 푼거라서 나중에 어느정도의 리뷰가 필요할거같지만 개인적으로 에전에 배웠던 내용이라 그런지 잠깐 봤는데도 전부 이해가 되었다.
앞으로 종종 프로그래머스에서도 실제 기업 SQL 문제도 풀어보고 JPQL 문법 연습하면서 같이 배워야겠다. 종종 코테를 보면서 SQL 문제를 내줬는데 항상 알고리즘만 다 푼다는 마인드로 넘기고 그랬는데 앞으로는 조금 더 자신 있어질거같다.
공부를 앞으로도 열심히 해야겠다!
