SQL ) LV2. 중성화 여부 파악하기
관련 개념
- 정규식 REGEXP
- LIKE보다 더 다양한 검색이 가능
- 예시) TITLE칼럼 값이 ‘A’ 또는 ‘B’ 또는 ‘C’를 포함한 데이터 출력
- SELECT * FROM TABLE_NAME WHERE TITLE REGEXP ‘A|B|C’;
- 예시) TITLE칼럼 값에 한글이 포함된 모든 데이터 출력
- SELECT * FROM TABLE_NAME WHERE TITLE REGEXP ‘가-힇’
- 관련 기호
- . : 문자 하나를 나타냄
- 예시) ‘…’ : 문자열의 길이가 세 글자 이상인 것을 찾음
- * : 앞에 나온 문자의 0개 이상 반복을 의미
- 예시) ‘A*’ : ‘A’가 0번 이상 등장하는 문자열을 찾음 → ‘B’, ‘A’, ‘BAA’
- + : 앞에 나온 문자의 1개 이상 반복을 의미
- 예시) ‘A+’ : ‘A’가 1번 이상 등장하는 문자열을 찾음 → ‘A’, ‘BA’
- ^ : 문자열의 처음을 나타냄
- 예시) ‘^김’ : ‘김’으로 시작하는 문자열을 찾음 → ‘김땡떙’
- $ : 문자열의 끝을 나타냄
- 예시) ‘요$’ : ‘요’로 끝나는 문자열을 찾음 → ‘안녕하세요’
- [] : []안에 나열된 패턴에 해당하는 문자열 찾기
- 예시) ‘[123]A’ : ‘1A’, 또는 ‘2A’ 또는 ‘3A’인 문자열 찾기
- {m, n} : m회 이상 n회 이하 반복되는 문자
- 예시 ) ‘A{1,2}’ : ‘A’가 1회 이상 2회 이하 반복되는 문자열 찾기 → ‘AB’, ‘ABAB’
- ? : 0~1회 나타나는 문자
- 예시) ‘[ABC]?’ : ‘A’ 또는 ‘B’ 또는 ‘C’가 0~1회 등장하는 문자열 찾기 → ‘D’, ‘AB’, ‘CD’
- | : OR를 나타냄
- [A-z] : 알파벳 대문자 OR 소문자인 문자열 찾기
- [0-9] : 숫자인 문자열 찾기
- [^문자] : 괄호 안의 문자를 포함하지 않는 문자열 찾기
- 예시 ) [^ABC] : ‘A’ 또는 ‘B’ 또는 ‘C’를 포함하지 않는 문자열 찾기
접근법
- SEX_UPON_INTAKE 값에 따라 중성화여부를 나타내는 칼럼을 생성해야함
- CASE문을 활용해서 조건을 충족한 경우 ‘O’, 그렇지 않은 겨우 ‘X’를 보이도록 했다.
- 풀이1 ⇒ LIKE 연산자
- 또한, 조건이 SEX_UPON_INTAKE에 특정 문자열(Neutered, Spayed)가 포함됐는지 판단해야하므로 ㅣLIKE연산자를 사용했다.
- 풀이2 ⇒ 정규식
- REGEXP ‘Neutered | Spayed’
코드
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 1;
SELECT ANIMAL_ID, NAME,
CASE
WHEN SEX_UPON_INTAKE REGEXP 'Neutered|Spayed' THEN 'O'
ELSE 'X'
END AS 중성화
FROM ANIMAL_INS ORDER BY 1;
SQL ) LV2. NULL 처리하기
접근법
- NAME칼럼 값이 NULL인 경우 No name 출력해야하므로 IFNULL 연산자 사용
코드
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS ORDER BY ANIMAL_ID;
SQL ) LV2. 입양 시각 구하기(1)
접근법
- DATETIME 칼럼값에서 시간을 추출한 HOUR 칼럼 생성후, 해당 칼럼 기준으로 GROUP화 했다.
- 이때, 그룹화된 결과 값에서 시간이 9~19인 범위로 좁혀야 하므로 HAVING 절에서 BETWEEN 연산자를 사용했다.
- 또한, 각 시간대별 레코드 수도 조회해야하므로 집계함수를 적용한 COUNT 칼럼도 추가했다.
코드
SELECT HOUR(DATETIME) HOUR, COUNT(*) COUNT FROM ANIMAL_OUTS
GROUP BY HOUR HAVING HOUR BETWEEN 9 AND 19 ORDER BY HOUR;
SQL ) LV2. DATETIME에서 DATE로 형 변환
코드
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS ORDER BY 1;
SQL ) LV2. 카테고리 별 상품 개수 구하기
접근법
- 카테고리 칼럼값에서 앞의 2글자를 추출해야하므로 SUBSTR 또는 SUBSTRING 함수 사용해서 CATEGORY 칼럼 생성
- 이후, CATEGORY 칼럼 기분 그룹화 이후 COUNT연산자를 사용해 각 CATEGORY별 레코드 수를 함께 반환했다.
코드
SELECT SUBSTR(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT GROUP BY CATEGORY ORDER BY CATEGORY;
SELECT SUBSTRING(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT GROUP BY CATEGORY ORDER BY CATEGORY;
SQL ) LV2. 가격대 별 상품 개수 구하기
관련 개념
- DIV : 몫을 구하는 함수
- % : 나머지를 구하는 함수
접근법
- 각격대를 만원단위로 구분해야하므로, PRICE칼럼값을 1만으로 나눈 뒤, 소숫점 뒷자리는 FLOOR함수로 버리고, 이후 1만을 곱해서 만원 단위로 보여주도록 했다.
- 또한, 가격대별 레코드수를 조회해야하므로 위에서 생성한 PRICE_GROUP 칼럼 기준 그룹화 한 뒤 집계함수 COUNT를 사용해서 각 그룹별 레코드 수도 함께 조회했다.
코드
SELECT FLOOR(PRICE/10000) * 10000 AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT GROUP BY PRICE_GROUP ORDER BY PRICE_GROUP;
SQL ) LV2. 상품 별 오프라인 매출 구하기
접근법
- 우선 조회하려는 칼럼이 두 테이블에 흩어져 있으므로 PRODUCT_ID기준으로 JOIN연산을 수행했다.
- 이후, PRODUCT_CODE기준으로 그룹화하면 각 CODE별 매출액(PRICE * SALES_AMOUNT) 합계 칼럼 SALES를 생성했다.
코드
SELECT PRODUCT_CODE, SUM(PRICE * SALES_AMOUNT) AS SALES
FROM PRODUCT P JOIN OFFLINE_SALE O ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY 2 DESC, 1 ASC;
SQL ) LV2. 진료과별 총 예약 횟수 출력하기
접근법
- LEFT함수를 사용해 진료예약일시 APNT_YMD 칼럼 값을 왼쪽에서부터 길이 7만큼 조회 후 ‘2022-05’인 레코드만 조회하도록 했다.
- 또한, 진료과코드MCDP_CD를 기준으로 그룹화후 COUNT 집계함수를 사용해 진료과코드별 5월예약건수를 함께 조회했다.
코드
SELECT MCDP_CD 진료과코드, COUNT(*) 5월예약건수 FROM APPOINTMENT
WHERE LEFT(APNT_YMD, 7) = '2022-05' GROUP BY 진료과코드 ORDER BY 2, 1;
SQL ) LV2. 성분으로 구분한 아이스크림 총 주문량
접근법
- 각 테이블의 칼럼을 활용해야 하므로, 두 테이블을 FLAVOR기준으로 JOIN했다.
- 이후, INGREDIENT_TYPE기준 그룹화 후, 각 그룹별 TOTAL_ORDER를 SUM함수를 활용해 총주문량 합계를 구했다.
코드
SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) TOTAL_ORDER
FROM FIRST_HALF F JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;
SQL ) LV2. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
접근법
- 정규식을 활용해 OPTIONS에 통풍시트 OR 열선시트 OR 가죽시트가 포함된 레코드만 조회하도록 했다.
- 또한 CAR_TYPE별 레코드 수를 조회해야하므로 CAR_TYPE기준 GROUP화 후에 집계함수 COUNT를 사용해 자동차종류별 몇 대인지 함께 조회했다.
코드
SELECT CAR_TYPE, COUNT(*) CARS FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY CAR_TYPE ORDER BY CAR_TYPE;
SQL ) LV2. 자동차 평균 대여 기간 구하기
접근법
- 날짜차이(대여기간)을 구하기 위해 DATEDIFF함수를 사용했다.
- 여기서 주의할 점은 대여 기간을 구하는 것이므로, DATEDIFF한 값에 + 1을 했다.
- 이후, CAR_ID 별로 대여기간의 평균을 구해야하므로 CAR_ID기분 그룹화 후, 대여기간의 평균AVG을 구하고, ROUND함수를 사용해 평균을 소수 첫째자리까지 반올림했다.
- 또한, 대여기간의 평균 AVERAGE_DURATION이 7이상인 레코드만 조회해야하므로 HAVING절에 해당 조건을 추가했다.
코드
SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;
SQL ) LV2. 조건에 부합하는 중고거래 상태 조회하기
접근법
- 작성일이 2022년 10월 05일인 게시글만 조회해야하므로, WHERE절에서 해당 조건을 추가했다.
- 또한, STATUS칼럼 값이 SALE, RESERVED, DONE에 따라 ‘판매중’, ‘예약중’, ‘거래완료’를 보여줘여 하므로 CASE문을 사용했다.
코드
SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE
WHEN STATUS = 'SALE' THEN '판매중'
WHEN STATUS = 'RESERVED' THEN '예약중'
ELSE '거래완료'
END AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = '2022-10-05'
ORDER BY BOARD_ID DESC;
