2일차 (24-06-25) TIL 학습기록

 

캠프 참여하기 전부터 일정이 잡혀있던 코딩테스트를 보러 가야 해서... 잠시 외출을 하게 될 것 같다.

코딩테스트도 증명서가 있나...

 

오늘 목표

• SQL 3주차 강의
• 7시에 SQL 온라인 강의
• TIL 관련 서식 만들어두기

---

 

3강

REPLACE, CONCAT 사용법

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


SELECT 		addr 
			,REPLACE(addr, '문곡리', '문가리')
FROM 		food_orders fo 
WHERE 		addr like '%문곡리%';


SELECT 	addr "원래주소"
		,SUBSTR(addr,1,2) "시도" 
FROM	food_orders fo 
WHERE 	addr like '서울%';

SELECT 	addr "원래주소"
		,CONCAT('[',SUBSTR(addr,1,2),'] ', restaurant_name)  "시도" 
FROM	food_orders fo 
WHERE 	addr like '서울%';

 

 

#CASE문 사용법
SELECT 		CASE WHEN cuisine_type = 'Korean' THEN '한식'
				 WHEN cuisine_type IN ('Japanese', 'Chinese') THEN '아시아'
				 ELSE '기타'
				 END '음식타입'
			,cuisine_type 
FROM 		food_orders fo


#if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
SELECT 		addr
			,IF(addr like '%평택군%', REPLACE(addr, '문곡리', '문가리'), addr)
FROM 		food_orders fo
WHERE addr LIKE '%문곡리%'


SELECT 		CASE WHEN quantity = 1 THEN price
				 WHEN quantity >=2 THEN price/quantity
				 END "음식단가"
FROM 		food_orders fo


SELECT 		addr
			,CASE WHEN addr LIKE '%경기도%' THEN '경기도'
				  WHEN addr LIKE '%특별시%' OR addr LIKE '%광역시%' THEN SUBSTR(addr,1,5)
				  ELSE SUBSTR(addr,1,2)
			END
FROM 		food_orders fo


select name,
       age,
       gender,
       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 "그룹" 
from customers
where age between 10 and 29


SELECT		CASE WHEN delivery_time >25 AND delivery_time <=30 THEN price*1.05*(if(addr like '%서울%', 1.1, 1))
				 WHEN delivery_time >30 THEN price*1.1*(if(addr like '%서울%', 1.1, 1))
				 ELSE 0 END "수수료"
FROM 		food_orders fo


SELECT 		day_of_the_week
			,quantity
			,IF(day_of_the_week = 'Weekday',3000,3500)*IF(quantity<=3, 1, 1.2) 
FROM 		food_orders fo

 

 

3강 숙제

SELECT 		order_id "ID"
			,day_of_the_week "주말?"
			,IF(day_of_the_week = 'Weekday',25,30) "지연기준"
			,delivery_time "배달시간"
			,IF(IF(day_of_the_week = 'Weekday',25,30)>delivery_time,'On-time','Late') "지연여부"
FROM 		food_orders fo