코딩 테스트 대비 MySQL 문법 총정리

올해 하반기 신입공채를 준비하면서, 코딩테스트를 굉장히 많이 봤던것같다.

대기업, 중견기업, 은행권, IT회사 등 가리지 않고 응시하게 되었는데, 특히 대기업과 은행권 쪽에서는 SQL이 꼭 필수적으로 출제되었다. 보통은 한문제에서 최대 두문제까지 나왔고, 난이도는 천차만별이였다...

 

분명 옛날에는 코테수준의 SQL은 어렵지 않다고, 특히 은행권은 어렵지 않다는 의견이 지배적이었던거 같은데^^;; 최근 코테 경향을 보면 그렇지만도 않은것같다. 쉬운건 정말 쉽지만, 어려운건 정말 어렵다는 느낌...

왜 그렇게 생각했냐면, 지엽적(?)인 함수를 써야되는 문제들이 다수 출제되었다.

 

나는 프로그래머스 SQL 고득점 Kit를 통해 대비했는데, 취준 초반 시절엔 '음.. 어려워봤자 Join문이나 서브쿼리 정도 아닐까?'라고 생각했고, 레벨 3정도까지는 곧잘 풀리기에 자신감을 가지게 되었다. 그러나 인생은 실전인 법, 일단 Join은 당연하고, 문자 데이터에 대한 처리나, 날짜비교를 자유자재로 다루는 등의, 꾸준히 문법을 복습하지 않으면 어려움이 있는 문제들에서 번번히 패배를 선언했다.

 

아무튼 이러한 패배의 쓴맛을 본 뒤, 코테 전 복습용도로 함수에 대한 정리와 어려운 문제 풀이법, 각종 연산자 사용시 주의할 점 등을 기록해보려고 한다.

 

그럼 바로 시작~

 

---

 

각종 메소드와 사용법

소수점 자르기

• ROUND 사용해서 소수점아래 N만큼! 즉 ROUND(값,N)
• 반올림은 ROUND고 올림은 CEILING(N)

-- 자동차 종류가 SUV인 자동차의 평균 일일대여요금을 출력하라
SELECT round(avg(DAILY_FEE),0) as AVERAGE_FEE
from car_rental_company_car
where car_type = 'SUV'

 

 

정렬기준 두개, DATE FORMAT 설정

-- MCDP_CD가 CS거나 GS인 의사이름, 의사ID, 진료과, 고용일자를 조회
-- 고용일자를 내림차순 정렬, 같다면 이름기준 오름차순정렬
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD,'%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC

 

 

날짜 다루기

• MONTH(), YEAR(), HOUR()

-- 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL 작성
-- 전화번호가 NULL인경우는 출력대상에서 제외
-- 결과는 회원ID 기준 오름차순
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE GENDER = 'W' 
**AND MONTH(DATE_OF_BIRTH) = 3** 
AND TLNO IS NOT NULL    
ORDER BY MEMBER_ID;

 

 

GROUNP BY 사용, LIKE연산

• 서브테이블이 있을경우 그룹바이해서 집계함수 써주자//
• LIKE연산은 괄호 안씀. VALUE LINE ‘%냠%’ 이런형식임

-- 서울에 위치한 식당중
-- 리뷰 평균점수는 소수점 세번째에서 반올림
-- 결과는 평균점수를 기준으로 내림차순 정렬, 같다면 즐찾수 기준으로 내림차순
-- SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, ROUND(AVG(B.REVIEW_SCORE),3)
SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, ROUND(AVG(B.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO A INNER JOIN REST_REVIEW B 
ON A.REST_ID = B.REST_ID
**WHERE A.ADDRESS LIKE '서울%'**
GROUP BY B.REST_ID
ORDER BY SCORE DESC, A.FAVORITES DESC;

 

 

출력 갯수 정하기

• LIMIT

-- 제일 비싼 식품의 식품ID, 식품이름, 식품코드, 식품분류, 식품가격을 조회
SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1;

 

 

집계

DISTINCT
SUM
COUNT
AVG

 

 

문자열 합치기

SELECT **CONCAT(LENGTH,'cm')** AS 'MAX_LENGTH'
FROM FISH_INFO
ORDER BY LENGTH DESC
LIMIT 1;

 

 

with문

• 서브쿼리 작성시 너무 길면 가독성이 떨어지므로, 미리 다른 임시 테이블명으로 서브쿼리 결과를 선언해두고, 이 결과 뒤에서 활용하는 방식이다.

WITH DEPT_LOC ( DEPTNO, LOC ) AS (
    SELECT A.DEPTNO
         , A.LOC
      FROM SCOTT.DEPT A
)
SELECT T1.*
  FROM DEPT_LOC
 WHERE 1=1
;

 

CONV

• 진법을 바꿔주는 메소드
• conv(숫자, 원래진법, 바꿀진법)으로 사용한다.

-- 숫자를 10진법에서 2진법으로 변경
select conv(400,10,2)

 

 

비트연산자

• 단순 변환할 경우에 conv를 쓰고, 비트 비교를 하고싶다면 바로 비트연산자 써주면 된다

& : 대응되는 비트가 모두 1이면 1을 반환함. (AND 연산)
| : 대응되는 비트 중에서 하나라도 1이면 1을 반환함. (OR 연산)
^ : 대응되는 비트가 서로 다르면 1을 반환함. (XOR 연산)
~ : 비트를 1이면 0으로, 0이면 1로 반전시킴. (NOT 연산)
<<: 지정한 수만큼 비트를 전부 왼쪽으로 이동시킴. (left shift 연산)
>>: 부호를 유지하면서 지정한 수만큼 비트를 전부 오른쪽으로 이동시킴. (right shift 연산)

 

 

연산자 사용시 주의할점

• Group By에 사용한 그룹화 기준 컬럼은 SELECT절에도 선언해줘야 정상적으로 실행된다.
• Null체크시 '=' 연산자가 아닌 IS NULL을 활용하도록 하자. '='연산자는 정상적인 체크가 불가능하다.

 

어려운 예제

[프로그래머스 레벨4] Front End 개발자 찾기

-- 개발자 테이블에서 FE 스킬을 가진 개발자 정보(ID, 이메일, 이름, 성), ID기준 오름차순 정렬
-- conv(숫자,원래진부,바꿀진법)으로 이진수로 바꾼 후 비트연산.. 앤드연산으로 1이 나오느냐?
-- 다르게 생각해보면, 개발자가 가진게 10010일때, 10스킬의 경우를 가지는지 확인하려면 A&B=B면 된다!!
-- A&B=A는 안됨ㅇㅇ 10을 기준으로하면 true지만 10010을 기준으로하면 false니까

select distinct a.id, a.email, a.first_name, a.last_name
from developers a
left outer join skillcodes b 
on a.skill_code & b.code = b.code
where b.category = "Front End"
order by a.id

외래키 설정이 굉장히 특이해서 어려웠던 문제다. 단순히 특정 외래키를 join하는게 아니라, 만약 개발자 테이블의 스킬이 400(110010000(2))일 경우, 스킬코드 테이블에서 256(100000000(2)), 128(10000000(2)), 16(10000(2))이라는 스킬을 가지고 있는 개발자인것이다. 따라서 이를 해결하기 위해선 비트연산이 필요하다. 처음엔 conv()를 통해 이진수로 변환하고, 앤드연산을 해서 한개의 1을 가지느냐.. 와같이 접근을 시도했는데, 생각을 조금 바꾸면 쉬워진다.

10010을 가졌을때 10이라는 스킬과 &연산을 해보면, 결과적으로 10이 나와야만 해당 스킬을 가진다고 말할 수 있는것이다.

즉, A&B=B를 on 조건으로 주면 정상적으로 일대다 연관관계가 설정된다. 주의할것은 문제에서 요구하는것은 '개발자 정보'이므로, 한 개발자가 여러개의 스킬을 가졌다고 해서 여러번 띄워주면 안되는것이다. Distinct를 꼭 써주도록 하자.