SQL 정리 1

(엑셀보다 쉽고 빠른 SQL 1~3주차 정리)

(DBeaver라는 프로그램 사용하여 실습 진행)

 

SQL : 데이터베이스와 대화를 하기 위한 언어. SQL이란 언어를 이용해 데이터베이스에 요청을 하는 질의를 Query라고 한다.

 

데이터베이스는 쉽게 말해 '데이터가 저장되어있는 큰 폴더'로, 그 안에 '테이블'이라는 파일이 있고 테이블은 '컬럼' 혹은 '필드' 라는 열로 이루어져 있다. 

 

데이터 조회

• select : 데이터를 가져오는 기본 명령어. 데이터를 조회하는 모든 query에 사용됨.
• from : 데이터를 가져올 테이블을 특정.
• as : 별명(alias) 주기. 컬럼 옆쪽에 영문일 경우 별명만, 특수문자나 한글일 경우 큰 따옴표 안에 적어줌. (as 생략 가능)

 

필터링

• where : 특정 조건 필터링 가능. ex) 나이가 21살인 사람.
• 같음, 큼, 작음 등의 조건 지정 가능 (=, <>, >, <, >=, <=)
• between : A와 B 사이
  • between A and B
  • ex) where age between 10 and 20
• in : '포함'하는 조건 주기
  • in (A, B, C)
  • ex) age in (15, 21, 31)
• like : 완전히 똑같지는 않지만, 비슷한 값을 조건으로 주기
  • ex1) name like '김%'
  • ex2) restaurant_name like '%Next%'
  • ex3) name like '%임'
• 여러 개의 조건으로 필터링 (논리연산)
  • and : 그리고
    • ex) age>20 and gender='female'
  • or : 또는
    • ex) age>20 or gender='female'
  • not : 아닌
    • ex) not gender='female'

 

계산식 수행

• +, -, *, / 사용 가능

select food_preparation_time,
       delivery_time,
       food_preparation_time + delivery_time as total_time
from food_orders

• sum(컬럼) : 합계
• avg(컬럼) : 평균
• count(컬럼) : 데이터 개수. 컬럼명 대신 1 혹은 * 사용 가능.
  • distinct : 중복 X, 몇 개의 값을 가지고 있는지 구할 때
• min(컬럼) :최솟값
• max(컬럼) : 최댓값

 

범주별 연산

• group by : 카테고리를 지정하여 수식 함수로 연산

select cuisine_type,
       sum(price) sum_of_price
from food_orders
group by cuisine_type

 

결과 정렬

• order by
• 내림차순 : desc

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

 

문자 포맷 가공

• replace(바꿀 컬럼, 현재 값, 바꿀 값) : 특정 문자를 다른 문자로 바꾸기

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

 

• substr(조회 할 컬럼, 시작 위치, 글자 수) : 특정 문자만 골라서 조회

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

 

• concat(붙이고 싶은 값1, 붙이고 싶은 값2,  붙이고 싶은 값3, ......) : 여러 컬럼의 값을 하나로 합치기

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

 

조건에 따른 연산

• if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  • ex) ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

select addr "원래 주소",
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

 

• case when 조건1 then 값(수식)1 when 조건2 then 값(수식)2 else 값(수식)3 end
  • 조건 두 개 이상 지정할 때. 여러번의 if문 적용 효과.
  • ex) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

select restaurant_name,
       cuisine_type AS "원래 음식 타입",
       case when (cuisine_type='Korean') then '한식'
       else '기타'
       end as " 음식 타입"
from food_orders

 

Data Type 오류 해결

data type이 다를 때 연산이 되지 않을 수 있는데, 이렇게 문자, 숫자를 혼합하여 함수에 사용할 때에는 데이터 타입을 변경해 주어야 한다.

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

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

 

 

연습 문제

1번

• 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
• (지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

select restaurant_name,
       order_id,
       delivery_time,
       price,
       addr,
       case when delivery_time>25 and delivery_time<=30 then price*0.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

 

2번

• 다음의 조건으로 배달시간이 늦었는지 판단하는 값 만들기.
• 주중 : 25분 이상
• 주말 : 30분 이상

select order_id,  
       restaurant_name,
       day_of_the_week,
       delivery_time,
       case when day_of_the_week='Weekday' and delivery_time>=25 then 'Late'
            when day_of_the_week='Weekend' and delivery_time>=30 then 'Late'
            else 'On-time' end "지연여부"
from food_orders