240927 TIL / SQL 실습

오늘 진행한 학습 요약

1. 엑셀보다 쉽고 빠른 SQL 2주차 강의 수강

• SQL을 이용하여 숫자 계산 값을 도출
• Query 결과를 원하는 순으로 조회

2. 엑셀보다 쉽고 빠른 SQL 3주차 강의 수강

• Query 결과 수정
• 수치 계산 및 문자를 조건별로 다르게 적용
• 수치 계산 및 문자 연산이 되지 않는 경우를 확인
• Data Type 에러 수정 방법

3. Spring 사전캠프 퀘스트 진행

• 4) 이제 놀만큼 놀았으니 다시 공부해봅시다!
• 5) 공부하다보니 팀 프로젝트 시간이 왔어요!
• 6) 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!

 

---

학습 정리

1. 엑셀보다 쉽고 빠른 SQL 2주차 강의 수강

• SQL로  계산하기(연산자, SUM, AVG, MIN, MAX, COUNT, DISTINCT)
  • 연산기호를 사용해 계산하기
    

    +	더하기
    -	빼기
    *	곱하기
    /	나누기

  • 계산에 사용되는 함수 종류
    • 합계 : SUM(컬럼)
    • 평균 : AVG(컬럼)
    • 최솟값 : MIN(컬럼)
    • 최댓값 : MAX(컬럼)
    • 데이터 갯수 : COUNT(컬럼) * 컬럼명 대신 1 혹은 * 사용 가능
    • 몇개의 값을 가지고 있는지 구할 때 : DISTINCT

/* 합계와 평균 구하기 */
select sum(food_preparation_time) total_food_preparation_time,
       avg(delivery_time) avg_food_preparation_time
from food_orders

/* 갯수 구하기 */
select count(1) count_of_orders,
       count(distinct customer_id) count_of_customers
from food_orders

/* 최소값 최대값 구하기 */
select min(price) min_price,
       max(price) max_price
from food_orders

•  GROUP BY 명령어로 범주별 연산하기
  • 컬럼의 종류별로 (카테고리 별로) 구분할 수 있는 명령어

select restaurant_name,
       max(price) "최대 주문금액"
from food_orders
group by restaurant_name

• ORDER BY 명령어로 Query 결과 정렬하기
  • SQL로 조회한 결과를 원하는 순서대로 정렬할 수 있는 명령어
  •  

    종류	구문	예시
    오름차순	생략	order by sum(price)
    내림차순	desc	order by sum(price) desc

select restaurant_name,
       max(price) "최대 주문금액"
from food_orders
group by restaurant_name
order by max(price) desc

---

 

2. 엑셀보다 쉽고 빠른 SQL 3주차 강의 수강

• 원하는 문자 포맷으로 가공하기(REPLACE, SUBSTRING, CONCAT)
  • REPLACE
    • replace(바꿀 컬럼, 현재 값, 바꿀 값)
  • SUBSTRING
    • substr(조회 할 컬럼, 시작 위치, 글자 수)
  • CONCAT
    • concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
    • 붙일 수 있는 문자의 종류
      •  컬럼
      •  한글
      •  영어
      •  숫자
      • 기타 특수문자

/* replace 구문으로 문곡리를 문가리로 바꾸기 */
select addr "원래 주소",
       replace(addr, '문곡리', '문가리') "바뀐 주소"
from food_orders
where addr like '%문곡리%'

/* substr 구문으로 음식점들의 주소를 앞에 단어 1번째에서 2번째까지 나오도록 바꾸기 */
select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

/* concat 구문으로 여러 컬럼의 문자를 합치기 */
select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

• 조건에 따라 포맷을 변경하기
  • IF 문을 사용해 조건에 따라 다른 방법을 적용
    • if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  • CASE 문을 활용해 조건별로 적용할 값을 지정하기
    • case(조건1, 값1, case(조건2, 값2, 값3))

/* IF문을 활용하여 '문곡리'가 평택에만 해당할때 '문가리'로 수정*/
select addr "원래 주소",
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

/* CASE문을 활용하여 korean일때는 한식 나머지는 기타로 표시*/
select restaurant_name,
       cuisine_type AS "원래 음식 타입",
       case when (cuisine_type='Korean') then '한식'
       else '기타'
       end as " 음식 타입"
from food_orders

 

• Data Type 오류가 날때
  • 컬럼의 유형을 확인하고 데이터 타입 변경해주기

--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 

--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

 

---

3. Spring 사전캠프 퀘스트 진행

• 4) 이제 놀만큼 놀았으니 다시 공부해봅시다!
  • '아닌' 값을 고르는 문은?
    • NOT
  • 두개를 모두 충족하게하는 문은?
    • AND
• 5) 공부하다보니 팀 프로젝트 시간이 왔어요!
  • 2022년에 시작된 프로젝트를 선택하는 쿼리에서 start_date < '2023-01-01' 이라는 조건을 선택하지 않기
    1. LIKE문을 사용하기
    2. YEAR문을 사용하기
  • 현재 진행중인 프로젝트를 선택하는 쿼리에서  지금 시점의 날짜를 하드코딩하지 않기
    1. CURRENT_DATE문 사용하기
    2. 다른 방법 더 찾아보기
  • 지속기간을 구하는 문은?
    • DATEDIFF(종료일, 시작일)
       
• 6) 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!
  • 순위를 정하는 문은?
    • RANK()
  • 데이터를 제한해서 출력하는 문은?
    • LIMIT