[4] DBeaver - DBeaver 활용한 SQL 3주차 강의

※  8월에 들었던 SQL강의 관련 포스팅

ㄴ 3주차 전체 쿼리 ( 길어서 일부분만 )

 

1.  REPLACE, SUBSTRING, CONCAT 사용하기

ㄴ REPLACE 함수 사용 : REPLACE(컬럼, 바꿀 대상, 바뀐 대상)
ㄴ SUBSTR 함수 사용 : SUBSTR(컬럼, 시작지점, 출력글자개수)
ㄴ CONCAT 함수 사용 : CONCAT(붙이고싶은값1, 붙이고싶은값2, 붙이고싶은값3, 등등)

 

# 실습1 REPLACE
# Blue Ribbon이 포함된 음식점이름중에서 Blue를 Pink로 변경해주세요.
SELECT 
	restaurant_name "원래 상점명",
	replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명" # replace(컬럼, 바꿀 대상, 바뀐 대상)
FROM food_orders 
WHERE restaurant_name LIKE '%Blue Ribbon%';


# 실습2 SUBSTR
# 주소에서 서울특별시 부분을 '서울'로 변경해주세요.
SELECT
	addr "원래 주소",
	SUBSTR(addr, 1, 2) "시도" # addr 컬럼의 첫번째 글자부터 2글자 출력해주세요.
FROM food_orders
WHERE addr LIKE '%서울특별시%';

# 실습3 CONCAT
# 음식점이름과 음식의 타입을 결합하여 보여주고, 주소가 서울인 식당을 - [서울] 음식점 이름으로 변경해주세요.
select restaurant_name "원래 이름",   
       addr "원래 주소",
       CONCAT(restaurant_name, '-', cuisine_type) "음식타입별 음식점",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%';

 

2.  IF문  CASE문 활용하기

[IF문]

ㄴ 실습1) 음식타입을 'Korean'일 때는 '한식', 'Korean'이 아닌 경우에는 '기타'라고 지정
ㄴ 실습2) 문곡리가 평택에만 해당될때 평택 문곡리만 문가리로 수정
ㄴ 실습3) 잘못된 이메일 주소만 수정해서 사용

# [IF문]
# 실습1 음식타입을 'Korean'일 때는 '한식', 'Korean'이 아닌 경우에는 '기타'라고 지정
select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders;
# IF문 조건에서 cuisine_type 컬럼으로 Korean일때 / 아닐때로 조건문 완성


# 실습2 문곡리가 평택에만 해당될때 평택 문곡리만 문가리로 수정
select 
       addr "원래 주소",
       if(addr LIKE '%평택군%', REPLACE(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
WHERE addr LIKE '%문곡리%';
# IF문 조건에서 주소에 평택군이 포함되는 데이터들만 불러오기위해 LIKE문을 사용해준다.


# 실습3) 잘못된 이메일 주소만 수정해서 사용
select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1;
# 데이터가 gmail만 @가 빠져있었어서 substring, if, replace를 활용하여 이메일 주소 수정 후 데이터 추출예정
# 이메일컬럼 10번째 자리에서 gmail 포함되는 데이터만 replace로 @를 넣어주고 @기준으로 뒤에 이메일 도메인들만 각각 추출

 

[CASE문]
ㄴ 실습1) 음식타입을 'Korean'일 때는 '한식', 'Japanese' 혹은 'Chinese'일 때는 '아시아' 그외에는 '기타'라고 지정
ㄴ 실습2) 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량으로 지정

# [CASE문]
# 실습1 음식타입을 'Korean'일 때는 '한식', 'Japanese' 혹은 'Chinese'일 때는 '아시아' 그외에는 '기타'라고 지정
SELECT 
	CASE WHEN cuisine_type = 'Korean' THEN '한식'
		 WHEN cuisine_type IN('Japanese','Chinese') THEN '아시아'
		 ELSE '기타' END "음식타입",
		 cuisine_type
FROM food_orders;
# cuisine_type 컬럼에 따라 한식,아시아로 나뉘고 나머지 조건은 기타로 데이터 지정
# 한식,아시아를 나눈 후 그 외의 조건에 대한 값을 지정해줄 필요없을 경우 else를 쓰지 않고 바로 END로 마무리 하여도 문제없음.



# 실습2) 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량으로 지정

# if문
SELECT 
	quantity,
	price,
	IF(quantity=1, price, (price/quantity)) as '음식가격'
FROM food_orders;
# 수량이 1일때는 음식주문금액 그대로, 그외 일때는 음식가격/주문수량으로 계산
# 데이터 자체가 0이 존재할 수 없는 데이터라 IF문에서 1과 1이 아닌경우로 나뉘어도 되지만 
# 명확하게는 2개이상이라는 조건이 있기 때문에 다른 테이블에서 데이터를 추출하는 경우는 주의해야하며, CASE문 사용이 더 바람직함.



# case문
SELECT 
	quantity,
	price,
	CASE WHEN quantity = 1 THEN price
		 WHEN quantity >= 2 THEN (price/quantity)
	END	as '음식가격' 
FROM food_orders;
# 위 IF문으로 사용한 조건과 같은 조건이며
# 1개, 2개이상이라는 조건을 명확하게 명시하면서 정확한 데이터를 추출할 수 있음.
# 위에서 설명한 것처럼 두개의 조건이 있고 그 외의 조건은 딱히 존재하지 않는다면 바로 END를 사용하여 CASE문을 끝내면됨.

 

 

3.  최종 실습

1. 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.
- 주중 : 25분 이상
- 주말 : 30분 이상
- SQL 문의 기본 구조로 시작
- 조건을 여러번 적용할 때 if, case 문 중 어떤 것을 이용할지 결정
- 조건에 ‘주중, 주말’ 조건과 ‘배달시간’ 조건을 동시에 줄 때 사용 할 논리연산자 결정

 

# 숙제
SELECT 
 order_id,
 restaurant_name,
 day_of_the_week,
 delivery_time,
 CASE WHEN day_of_the_week = 'Weekend' AND delivery_time >= 30 THEN 'Late'
 	  WHEN day_of_the_week = 'Weekday' AND delivery_time >= 25 THEN 'Late'
 ELSE 'On-time'
 END '지연여부'
FROM 
food_orders;
# day_of_the_week - 주중,주말 확인하는 컬럼
# delivery_time - 배달시간컬럼
# case when으로 day_of_the_week의 주중,주말 조건을 잡고,
# 주말은 30분이상일때 Late / 주중은 25분이상일때 Late
# 나머지에 부합하는 조건은 On-time으로 마무리