📌 새로 알게된 것
regexp
- 정규 표현식
- sql 에서는 LIKE와 IN을 함께 사용할 수 없어 병렬 연결해야 한다.
- 문자열을 처리하는 방법 중의 하나로, 특정한 조건의 문자를 ‘검색’하거나 ‘치환’하는 과정을 매우 간편하게 처리할 수 있도록 해주는 수단
- ex)
where r.options regexp ('통풍시트|열선시트|가죽시트')
- ex)
IF(SEX_UPON_INTAKE REGEXP 'Neutered|Spayed', 'O', 'X') as '중성화'
- ex)
WHERE NAME regexp '^(Lucy|Ella|Pickle|Rogan|Sabrina|Mitty)$'
TRUNCATE(숫자,버릴 자릿수)
- 내림함수 0 이면, 소수점없는 정수를 돌려주고, 음수이면 정수기준 오른쪽부터 0 으로 채울 수 있다.
- MAX는 O(n) 이고 ORDER BY는 정렬이니 O(nlogn)이라 평균적으로 MAX가 더 빠르다.
1. 조건에 부합하는 중고거래 상태 조회하기 (String, Date)
Link
SELECT b.BOARD_ID,
b.WRITER_ID,
b.TITLE,
b.PRICE,
case
when b.STATUS = 'DONE' then '거래완료'
when b.STATUS = 'SALE' then '판매중'
else '예약중' end as STATUS
from USED_GOODS_BOARD b
where DATE_FORMAT(b.CREATED_DATE , '%Y-%m-%d') = '2022-10-05'
order by BOARD_ID desc
2. 자동차 평균 대여 기간 구하기 (String, Date)
Link
SELECT h.car_id, round(avg(DATEDIFF(h.END_DATE, h.START_DATE)+1),1) as AVERAGE_DURATION
from CAR_RENTAL_COMPANY_RENTAL_HISTORY h
group by h.car_id
having AVERAGE_DURATION >= 7
order by AVERAGE_DURATION desc, h.car_id desc
3. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기 (Group by)
Link
SELECT r.car_type, count(*) as CARS from CAR_RENTAL_COMPANY_CAR r
where r.options regexp ('통풍시트|열선시트|가죽시트')
group by r.car_type
order by car_type asc
4. 조건에 맞는 도서와 저자 리스트 출력하기 (Join)
Link
SELECT b.BOOK_ID,
a.AUTHOR_NAME,
DATE_FORMAT(b.PUBLISHED_DATE , '%Y-%m-%d') as PUBLISHED_DATE
from book b left join author a on b.author_id = a.author_id
where b.category = '경제'
order by b.PUBLISHED_DATE
5. 성분으로 구분한 아이스크림 총 주문량 (Group by)
Link
SELECT i.INGREDIENT_TYPE, sum(f.TOTAL_ORDER) as TOTAL_ORDER
from FIRST_HALF f join ICECREAM_INFO i on f.flavor = i.flavor
group by i.INGREDIENT_TYPE
order by f.TOTAL_ORDER
6. 진료과별 총 예약 횟수 출력하기 (Group by) ⁉️
Link
SELECT a.MCDP_CD as '진료과 코드',
count(*) as '5월예약건수'
from APPOINTMENT a
where date_format(a.APNT_YMD,'%Y-%m') = '2022-05'
group by a.MCDP_CD
order by count(*), a.MCDP_CD
- ⁉️ order by count(*) 대신
order by ‘5월예약건수’는 왜 안되는걸까? → ‘’을 빼야함
7. 재구매가 일어난 상품과 회원 리스트 구하기 (Select)
Link
SELECT user_id, product_id from ONLINE_SALE
group by user_id, product_id
having count(user_id) >= 2
order by user_id, product_id desc
8. 상품 별 오프라인 매출 구하기 (Join)
Link
SELECT p.product_code,
sum(p.price * s.sales_amount) as SALES
from PRODUCT p join OFFLINE_SALE s on p.PRODUCT_ID = s.PRODUCT_ID
group by p.PRODUCT_CODE
order by SALES desc, p.PRODUCT_CODE
9. 가격대 별 상품 개수 구하기 (Group by) 🔺
Link
SELECT TRUNCATE(price,-4) as price_group, COUNT(product_id) AS products
FROM product
GROUP BY price_group
ORDER BY price_group;
10. 카테고리 별 상품 개수 구하기 (String, Date)
Link
SELECT substring(product_code,1,2) as CATEGORY, count(*) as PRODUCTS
from PRODUCT
group by substring(product_code,1,2)
11. 3월에 태어난 여성 회원 목록 출력하기 (Select)
Link
SELECT MEMBER_ID,
MEMBER_NAME,
GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d') as DATE_OF_BIRTH
from MEMBER_PROFILE
where date_format(DATE_OF_BIRTH, '%m') = '03'
and gender = 'W'
and TLNO is not null
order by MEMBER_ID
12. 가격이 제일 비싼 식품의 정보 출력하기 (Sum, Max, Min) 🔺
Link
SELECT * from FOOD_PRODUCT
order by price desc
limit 1
SELECT * FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT)
- MAX는 O(n) 이고 ORDER BY는 정렬이니 O(nlogn)이라 평균적으로 MAX가 더 빠르다.
- 만약 max 값이 같은 제품이 여러개인 경우 max()는 해당하는 모든 제품들을 다 보여주는데 limit을 이용하면 가장 큰 하나 밖에 안보여준다.
13. DATETIME에서 DATE로 형 변환 (String, Date)
Link
SELECT ANIMAL_ID, NAME, date_format(DATETIME, '%Y-%m-%d') as '날짜'
from ANIMAL_INS
order by ANIMAL_ID
14. 입양 시각 구하기 1 (Group by)
Link
SELECT date_format(datetime,'%H') as HOUR, count(*) as COUNT from ANIMAL_OUTS
group by HOUR
having HOUR between 09 and 20
order by HOUR
15. NULL 처리하기 (Is null)
Link
SELECT ANIMAL_TYPE,
(case when NAME is null then 'No name' else NAME end) as NAME,
SEX_UPON_INTAKE
from ANIMAL_INS
16. 중성화 여부 판단하기 (String, Date) 🔺
Link
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
SELECT ANIMAL_ID, NAME,
IF(SEX_UPON_INTAKE REGEXP 'Neutered|Spayed', 'O', 'X') as '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;
17. 중복 제거하기 (Select, Distinct)
Link
SELECT count(distinct(NAME)) as count from ANIMAL_INS
- count는 * 아니면 null 값을 세지 않기 때문에 where 절 생략
18. 동물 수 구하기 (Select, Distinct)
Link
SELECT count(distinct(ANIMAL_ID)) as count from ANIMAL_INS
19. 이름에 el이 들어가는 동물 찾기 (String, Date)
Link
SELECT ANIMAL_ID, NAME from ANIMAL_INS
where name like '%el%' and ANIMAL_TYPE = 'Dog'
order by name
20. 루시와 엘라 찾기 (String, Date) 🔺
Link
SELECT ANIMAL_ID, name, SEX_UPON_INTAKE from ANIMAL_INS
where name regexp ('Lucy|Ella|Pickle|Rogan|Sabrina|Mitty')
order by animal_id
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME regexp '^(Lucy|Ella|Pickle|Rogan|Sabrina|Mitty)$'
ORDER BY ANIMAL_ID
- REGEXP는 문자열 내부에 어떤 부분이든 해당 정규식이 존재한다면 True 이다.
- 따라서
i'm not Lucy
라는 이름을 가진 동물이 있다면 위의 식에서는 통과를 해 버린다.
- 만약 정규식으로 통과하고 싶다면
‘^(Lucy|Ella|Pickle|Rogan|Sabrina|Mitty)$'
시작과 끝 역시도 표시해 줘야 한다.
21. 동명 동물 수 찾기 (Group by, Having) 🔺
Link
SELECT name, count(name) as count from ANIMAL_INS
group by name
having count(name) >= 2
order by name
- group by 후 수식이 필요할 때 having을 생각하자.
22. 고양이와 개는 몇 마리 있을까 (Group by)
Link
SELECT ANIMAL_TYPE, count(*) from ANIMAL_INS
where ANIMAL_TYPE in ('cat', 'dog')
group by ANIMAL_TYPE
order by ANIMAL_TYPE
23. 최솟값 구하기 (Sum, Max, Min)
Link
SELECT min(DATETIME) from ANIMAL_INS