오늘은 프로그래머스에서 SQL 1단계 문제들을 풀었다. 문제를 풀다보니 자주 등장하는 개념이 있어서 이것들을 모아서 정리해보고자 한다.
각 개념마다 Oracle (⭕), SQL Server (MSSQL) (🪟), MySQL (🐬)을 기준으로 각 DBMS에서의 SQL문을 적고, 보충 설명이 필요하면 약간의 보충 설명도 덧붙일 것이다. 또, 어떤 개념에서는 특정 문제의 풀이를 예시 SQL문으로 제시하기도 할 것이다.
미리 말하자면, 아래의 SQL문들은 해설지에서 보일 듯한 정석적인 답들은 아니다. 내가 문제를 풀면서 소화할 수 있는 영역 내에서 이것저것 기록하고 정리해 본 것이다. 그래서 이 코드들이 반드시 효율적이지 않을 수도 있다. 그리고 프로그래머스에서는 MySQL과 오라클만을 쓸 수 있다. 그래서 SQL Server의 부분은 직접 실습해보진 못하고 공식 문서와 들었던 강의 내용을 참고해서 이론적으로 정리한 것이다.
1. 🔗 문자열 연결
문자열 포매팅?
print(f'{boxes:3d} Box(es)')파이썬, 자바, C++ 등에서는 문자열을 포매팅해서 출력할 수 있지만, Oracle, SQL Server, MySQL에서 문자열 포매팅 함수라는 것은 존재하지 않는다. 그래서 만약 접두사나 접미사, 구분자 등을 넣고 싶다면 문자열 연결의 개념으로 접근해야 한다.
문자열 합치기
⭕ Oracle
SELECT 문자열1 || 문자열2- Oracle에서는
||연산자로 문자열을 합칠 수 있다.
SELECT CONCAT(문자열1, 문자열2)- Oracle에서는
CONCAT함수로 한 번에 2개의 문자열만 합칠 수 있다. 따라서, 3개 이상의 문자열을 합치고 싶다면 2개의 문자열에 대해 먼저CONCAT을 적용하고 그 결과를 다시 남아있는 문자열에 적용시켜야 한다.
SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job"
FROM employees
WHERE employee_id = 152;공식 문서의 예시이다.
- 문자열이
'으로 감싸져 있기 때문에, 문자열 안에'를 넣고 싶다면''으로 입력한다. - 첫 번째
CONCAT:last_name과's job category is를 합친다. ->last_name's job category is가 된다. - 두 번째
CONCAT:last_name's job category is와job_id를 합친다. ->last_name's job category is job_id가 된다.
🪟 SQL Server
SELECT 문자열1 + 문자열2- SQL Server에서는
||연산자 대신+연산자를 사용한다.
SELECT CONCAT(문자열1, 문자열2, 문자열3, ...)- Oracle과 달리, SQL Server와 후술할 MySQL의
CONCAT함수는 3개 이상의 문자열에 대해서도 사용할 수 있다.
🐬 MySQL
SELECT CONCAT(문자열1, 문자열2, 문자열3, ...)각 문자열 사이에 구분자 넣기
⭕ Oracle
SELECT 문자열1 || 구분자 || 문자열2 || 구분자 || 문자열3 || ... || 문자열n- Oracle에는
CONCAT_WS함수가 없다. 그래서 이렇게 쓰는 수밖에.
🪟 SQL Server
SELECT CONCAT_WS(구분자, 문자열1, 문자열2, 문자열3, ...)CONCAT_WS함수는 각 문자열 사이에 구분자를 넣어 연결한 문자열을 만들어 주는 함수이다.- 전화번호를 예로 들어보자. 전화번호의 각 부분을 나타내는 문자열이 3개 있을 때,
CONCAT_WS('-', '010', '0000', '0000')은010-0000-000을 반환하게 된다.
🐬 MySQL
SELECT CONCAT_WS(구분자, 문자열1, 문자열2, 문자열3, ...)- SQL Server의
CONCAT_WS와 같은 역할을 한다.
2. 📆 날짜 관련
오늘 날짜에서 네 자리 연도를 출력하기
⭕ Oracle
SELECT TO_CHAR(SYSDATE, 'YYYY')TO_CHAR의 두 번째 인수에 사용되는 형식 지정자는 공식 문서 참고
🪟 SQL Server
SELECT FORMAT(GETDATE(), 'yyyy')FORMAT의 두 번째 인수에 사용되는 형식 지정자는 공식 문서 참고
🐬 MySQL
SELECT DATE_FORMAT(CURDATE(), '%Y')DATE_FORMAT의 두 번째 인수에 사용되는 형식 지정자는 공식 문서 참고
특정 연도, 월의 데이터
예시 문제: 조건에 맞는 데이터 출력하기 | 프로그래머스 스쿨
⭕ Oracle
SELECT BOOK_ID, TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE TO_CHAR(PUBLISHED_DATE, 'YYYY') = '2021'
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;TO_CHAR로 반환되는 결과는VARCHAR2(문자열) 타입이다. 그래서=뒤에2021이 아닌'2021'이 있다.
SELECT BOOK_ID, TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE EXTRACT(YEAR FROM PUBLISHED_DATE) = 2021
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;EXTRACT는 datetime 데이터 타입에 사용하면FROM뒤의 값에서FROM앞에서 요구된 datetime 필드를 뽑아내어 반환한다.VARCHAR2를 반환하는TIMEZONE_REGION,TIMEZONE_ABBR을 제외하고 모두NUMBER형태의 값을 반환한다.
SELECT BOOK_ID, TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE PUBLISHED_DATE >= TO_DATE('2021-01-01', 'YYYY-MM-DD')
AND PUBLISHED_DATE < TO_DATE('2022-01-01', 'YYYY-MM-DD')
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;- 날짜, 시간의 대소 비교를 할 수 있다. 더 최근인 날짜, 시간이 큰 것으로 여겨진다. 이건 SQL Server나 MySQL에서도 마찬가지.
🪟 SQL Server
SELECT BOOK_ID, FORMAT(PUBLISHED_DATE, 'yyyy-MM-dd') AS PUBLISHED_DATE
FROM BOOK
WHERE DATEDIFF(year, '2021-01-01', PUBLISHED_DATE) = 0
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;SELECT BOOK_ID, FORMAT(PUBLISHED_DATE, 'yyyy-MM-dd') AS PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = 2021
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;🐬 MySQL
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = 2021
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE PUBLISHED_DATE >= DATE('2021-01-01')
AND PUBLISHED_DATE < DATE('2022-01-01')
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;두 날짜의 차 구하기
예시 문제: 자동차 대여 기록에서 장기/단기 대여 구분하기 | 프로그래머스 스쿨
이 문제를 풀 때는 한 가지 조심해야 하는 점이 있다. 대여를 시작한 당일에 반납하면 대여 기간은 0일인 것이 아니라, 1일이 된다! 그 점을 주의하고, CASE문을 이용하면 어렵지 않게 풀 수 있을 것이다.
⭕ Oracle
SELECT HISTORY_ID, CAR_ID, TO_CHAR(START_DATE, 'YYYY-mm-dd') AS START_DATE, TO_CHAR(END_DATE, 'YYYY-mm-dd') AS END_DATE,
CASE WHEN END_DATE - START_DATE >= 29 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE EXTRACT(YEAR FROM START_DATE) = 2022 AND EXTRACT(MONTH FROM START_DATE) = 9
ORDER BY HISTORY_ID DESC;- Oracle에서는 날짜(
DATE)끼리 뺄 수 있다. 이 경우에 두 날짜 간의 일수 차이 (NUMBER)를 얻게 된다.
📆 SQL Server
SELECT HISTORY_ID, CAR_ID, FORMAT(PUBLISHED_DATE, 'yyyy-MM-dd') AS START_DATE, DATE_FORMAT(END_DATE, 'yyyy-MM-dd') AS END_DATE,
CASE WHEN DATEDIFF(day, START_DATE, END_DATE) >= 29 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) = 9
ORDER BY HISTORY_ID DESC;DATEDIFF함수는 두 번째 인수에서 세 번째 인수까지 첫 번째 인수 기준으로 얼마나 지나야 하는지를 계산한다. 여기에서는 만약DATEDIFF의 결과가 30이었다면,day기준으로START_DATE부터 30이 지나야END_DATE가 됨을 의미한다.
🐬 MySQL
SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
CASE WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE) >= 29 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) = 9
ORDER BY HISTORY_ID DESC;TIMESTAMPDIFF는 후술할DATEDIFF와 인수 순서가 서로 반대이다.TIMESTAMPDIFF는 세 번째 인수에서 두 번째 인수를 빼서 그 결과를 첫 번째 인수 기준으로 반환한다.
SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
CASE WHEN DATEDIFF(END_DATE, START_DATE) >= 29 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) = 9
ORDER BY HISTORY_ID DESC;DATEDIFF는TIMESTAMPDIFF와 반대로, 첫 번째 인수에서 두 번째 인수를 뺀다.TIMESTAMPDIFF와 달리 기준을 정할 수 없고, 결과는DAY를 나타낸다. SQL Server의DATEDIFF와 혼동하지 않도록 주의해야 한다.
3. 🗑️ NULL일 경우 치환하기
⭕ Oracle
SELECT NVL(값, NULL일 시의 치환값);🪟 SQL Server
SELECT ISNULL(값, NULL일 시의 치환값);🐬 MySQL
SELECT IFNULL(값, NULL일 시의 치환값);4. 🥇 처음 n개 데이터
⭕ Oracle
SELECT *
FROM 테이블명
FETCH FIRST N ROWS ONLY;🪟 SQL Server
SELECT TOP N *
FROM 테이블명;🐬 MySQL
SELECT *
FROM 테이블명
LIMIT N;5. ⛓️ 테이블 조인
예시 문제: 조건에 부합하는 중고거래 댓글 조회하기 | 프로그래머스 스쿨
⭕ Oracle
SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, TO_CHAR(R.CREATED_DATE, 'YYYY-MM-DD') AS CREATED_DATE
FROM USED_GOODS_BOARD B, USED_GOODS_REPLY R
WHERE EXTRACT(YEAR FROM B.CREATED_DATE) = 2022
AND EXTRACT(MONTH FROM B.CREATED_DATE) = 10
AND B.BOARD_ID = R.BOARD_ID
ORDER BY R.CREATED_DATE, B.TITLE;- 오라클에서는 주의해야 하는 것이 있다. 테이블 별칭을 지정할 때
AS를 사용할 수 없다!
프로그래머스에서 MySQL을 많이 썼다보니 AS 붙이기에 너무 익숙해졌다. 그래서 오라클에서도 습관처럼 FROM절에 AS를 붙였더니, 에러가 났다. 도대체 왜 에러가 나는가? 하며 공부한 내용을 다시 살펴보고 나서야 깨달았다. 오라클은 FROM절에서 AS를 쓸 수 없다는 사실을...
🪟 SQL Server
SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, FORMAT(R.CREATED_DATE, 'yyyy-MM-dd') AS CREATED_DATE
FROM USED_GOODS_BOARD AS B, USED_GOODS_REPLY AS R
WHERE YEAR(B.CREATED_DATE) = 2022
AND MONTH(B.CREATED_DATE) = 10
AND B.BOARD_ID = R.BOARD_ID
ORDER BY R.CREATED_DATE, B.TITLE;🐬 MySQL
SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, DATE_FORMAT(R.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD AS B, USED_GOODS_REPLY AS R
WHERE YEAR(B.CREATED_DATE) = 2022
AND MONTH(B.CREATED_DATE) = 10
AND B.BOARD_ID = R.BOARD_ID
ORDER BY R.CREATED_DATE, B.TITLE;6. 🔚 결론
이렇게 해서 프로그래머스 1단계 문제를 풀면서 자주 나오는 풀이 개념들을 한 번 정리해 보았다. 뭔가 파이썬과는 다르게 절과 함수를 대문자로 적어야 하니까 좀 어색하다. 그렇지만 파이썬 코드를 짤 때와는 또다른 재미가 있는 것 같다.
점점 조회할 컬럼 수도 많아지고 SQL문의 줄도 길어지는데, 가독성을 위해서 SQL문의 Convention이나 가이드들을 한 번 찾아봐야 겠다.
SQLD 시험 전까지 프로그래머스의 고득점 키트까지 모두 풀어보고 싶다. 아직은 1단계 푸는 데에도 좀 고민을 하면서 풀고 있다. 그렇지만, 강의를 듣고, 공부하고, 실습하고, 정리하며 SQL 지식이 쌓이면 고득점 문제도 술술 풀어낼 수 있지 않을까?
오늘은 거의 새벽 4시가 다 되어 글을 마무리 해버렸다. 정리한 내용 자체는 간단하지만, 문제 푸는 시간에 좀 많은 시간을 썼다. 아마 내일(31일 TIL)은 자정 전에 마무리 할 수 있을 것이다.
