스파르타 코딩클럽(부트캠프) SQL 입문 3장

함수명 : replace

사용방법 : replace(바꿀 컬럼, 현재값, 바꿀 값)

select restaurant_name "원래 상점명",
           replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'

---

SELECT addr,
REPLACE(addr, '문곡리', '문가리') "바뀐주소"
FROM food_orders
WHERE addr LIKE '%문곡리%'

---

함수 : substring(substr)

사용방법 : substr(조회 할 컬럼, 시작 위치, 글자 수)

select addr "원래 주소",
           substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

---

함수명 : concat

사용방법 : concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은값3, ....)

붙일 수 있는 문자의 종류 : 컬럼, 한글, 영어, 숫자, 기타 특수문자

select restaurant_name "원래 이름",
           addr "원래 주소",
           concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

---

select restaurant_name "원래 이름",

addr "원래 주소",

concat(restaurant_name, '-', cuisine_type)"음식 타입별 음식점",

concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"

from food_orders

where addr like '%서울%'

---

서울 지역의 음식 타입별 평균 음식 주문 금액 구하기(출력:'서울', '타입', 평균금액)

SELECT SUBSTR(addr, 1, 2) "지역",

cuisine_type,

AVG(price) "평균 금액"

FROM food_orders

WHERE addr LIKE '서울%'

group by 1, 2

 

---

이메일 도메인별 고객 수와 평균 연령 구하기

SELECT SUBSTR(email, 10) "도메인",

count(1) "고객 수",

avg(age) "평균 연령"

from customers

group by 1

---

 

[지역(시도)] 음식점이름(음식종류) 컬럼을 만들고 총 주문건수 구하기

SELECT CONCAT('[', SUBSTR(addr, 1, 2), ']', restaurant_name, '(', cuisine_type, ')')"음식점",

COUNT(1) "주문 건수"

FROM food_orders fo

group by 1

---

조건문 (IF, CASE)

 

함수명 : if

사용 방법 : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

select restaurant_name,

cuisine_type "원래 음식 타입",

if(cuisine_type='Korean', '한식', '기타') "음식 타입"

from food_orders

 

---

select addr "원래 주소",

if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"

from food_orders

where addr like '%문곡리%'

---

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",

count(customer_id) "고객 수",

avg(age) "평균 연령"

from customers

group by 1

---

함수명 : case

사용방법 :
case when 조건1 then 값(수식)1
         when 조건2 then 값(수식)2
         else 값 (수식)3
end

SELECT CASE when cuisine_type='Korean' THEN '한식'

when cuisine_type IN ('Japanese', 'Chinese')then '아시아'

else '기타'

END "음식타입",

cuisine_type

FROM food_orders

 

---

select order_id,

           price,

           quantity,

            case when quantity=1 then price

                      when quantity>=2 then price/quantity end "음식 단가"

from food_orders

---

10세 이상, 30세 미만 고객들의 나이와 성별로 그룹 나누기(이름도 같이 출력)

select case when age between 10 and 19 AND gender='male' then '10대 남성'

when age BETWEEN 10 and 19 and gender='female' then '10대 여성'

when age between 20 and 29 and gender='male' then '20대 남성'

when age between 20 and 29 and gender='female'then '20대 여성'

END

name, age, gender

FROM customers

WHERE age BETWEEN 10 and 29

---

주문 시기와 음식 수를 기반으로 배달할증료 구하기

select restaurant_name,

           price/quantity "단가",

            cuisine_type,

            order_id,

case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'

         when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'

         when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'

         when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'

         when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'

          when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'

          when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'

          when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'

          when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"

from food_orders

---

지역과 배달시간을 기반으로 배달 수수료 구하기 (식당 이름, 주문 번호 함께 출력)

• 지역 : 서울 , 기타 - 서울일 때 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없다.
• 시간 : 25, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식가격의 10%

SELECT case when delivery_time >30 then price*0.1*if(addr like '%서울%', 1.1, 1)

                        when delivery_time >25 then price*0.05*if(addr like '%서울%', 1.1, 1)

                else 0 end "수수료",

                restaurant_name, order_id, price, delivery_time, addr

from food_orders

---

주문 시기와 음식 수를 기반으로 배달할증료 구하기

• 주문 시기 : 평일 기본료 = 3000/ 주말 기본료 =3500
• 음식 수 : 3개 이하이면 할증 없음/ 3개 초과이면 기본료*1.2

 

SELECT case when day_of_the_week='weekday' then 3000*if(quantity>3, 1.2, 1)

when day_of_the_week='weekend' then 3500*if(quantity>3, 1.2, 1)

end "배달할증료",

restaurant_name, order_id, day_of_the_week, quantity

from food_orders

---

출처 : 스파르타 코딩클럽

1. raing은 숫자가 포함되어 있으나 문자형으로 저장되어 있다.
2. 문자 숫자를 혼합하여 함수에 사용할 때 데이터 타입을 변경해주어야한다.

cast( if (rating = 'Not given', '1', rating) as decimal) //숫자로 변경
cast(restaurant_name, '-', cast(order_id as char)) // 문자로 변경