SQL 기초 문법 3
수업 자료와 함께 SQL KATA(프로그래머스 SQL문제)를 풀어보면서 알아두면 좋을 SQL기초 문법들
날짜 포맷
date_format(date(컬럼명), ' % 바꿔주고 싶은 날짜 포맷 ' )
년 : Y (4자리), y(2자리)
월 : M, m
일 : d, e
요일 : w
오늘 날짜를 예로 들면,
2024.08.06.(화)
%Y → 2024
%y → 24
%M → August
%m → 08
%d → 06
%e → 6
%w → 2
요일은
- 0 일요일
- 1 월요일
- 2 화요일
- 3 수요일
- 4 목요일
- 5 금요일
- 6 토요일
예를 들어, date_format(date(HIRE_YMD), '%Y-%m-%d') 이렇게 SQL문을 작성해서 실행하면
select DR_NAME, DR_ID, MCDP_CD,
date_format(date(HIRE_YMD), '%Y-%m-%d') HIRE_YMD
from doctor
where MCDP_CD in ('cs', 'gs')
order by HIRE_YMD desc, DR_NAME
데이터는 아래와 같은 추출된다.
DR_NAME DR_ID MCDP_CD HIRE_YMD
니모 DR20200012 CS 2020-03-01
윈더 DR20190029 CS 2019-03-01
뽀로로 DR20170123 GS 2017-03-01
벨 DR20100039 GS 2010-07-01
자스민 DR20100032 GS 2010-03-01
문제
FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.
예시
FOOD_PRODUCT 테이블이 다음과 같을 때
| PRODUCT_ID | PRODUCT_NAME | PRODUCT_CD | CATEGORY | PRICE |
| P0018 | 맛있는고추기름 | CD_OL00008 | 식용유 | 6100 |
| P0019 | 맛있는카놀라유 | CD_OL00009 | 식용유 | 5100 |
| P0020 | 맛있는산초유 | CD_OL00010 | 식용유 | 6500 |
| P0021 | 맛있는케첩 | CD_OL00001 | 소스 | 4500 |
| P0022 | 맛있는마요네즈 | CD_OL00002 | 소스 | 4700 |
SQL을 실행하면 다음과 같이 출력되어야 합니다.
| PRODUCT_ID | PRODUCT_NAME | PRODUCT_CD | CATEGORY | PRICE |
| P0020 | 맛있는산초유 | CD_OL00010 | 식용유 | 6500 |
테이블에서 특정 컬럼의 최댓값을 구하고 그 행의 데이터들을 출력
하는 문제다.
처음에는 max(price) 를 써서 간단하게 구하고 싶었는데.. 오류가 났다. 가격만 최댓값이 구해지고 나머지는 컬럼들의 가장 위의 값이 추출되었기 때문이다. 가격에만 최대값을 줬으니까...ㅎ 생각해보면 당연..🤭
select PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, max(PRICE) PRICE
from food_product
그래서 그냥 price 컬럼을 order by desc 로 내림차순 해준 후 → limit 1 을 줬더니 정답처리가 됐다.
select PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
from food_product
order by price desc
limit 1
근데 이런 경우, max나 min 같은 최대값 최소값을 써서 구하는 방법이 있지 않을까 궁급했고, 찾아보니 방법이 있었다. 아마 이 방법이 이 문제를 푸는 정석이 아닐까 싶다.
근데 생각보다 간단하진 않네... 아직 서브쿼리문이 익숙하지 않아서 그런가..
whrere 절에 서브쿼리문을 넣어주면 된다.
select PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
from food_product
where price = (
select max(price)
from food_product
)
빈 값 (NULL 값) 찾을 때
in null 과 in not null
IS NULL: 컬럼의 값이 빈 값 (NULL) 인 경우를 찾을 때 where 절에 넣어서 찾아주면 손쉽게 찾을 수 있다.
SELECT *
FROM my_table
WHERE column is null
IS NOT NULL: 컬럼의 값이 빈 값 (NULL)이 아닌 경우를 찾을 때
SELECT *
FROM my_table
WHERE column is not null
필요한 데이터가 서로 다른 테이블에 있을 때 join
- LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회된다.
예를 들어 테이블 1에 1,2,3 이 있고 테이블 2에 2,3,4가 있다고 했을 때 → 둘을 합치면 1, 2, 3 이 나온다.
-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명
- INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회된다.
테이블 1에 1,2,3 이 있고 테이블 2에 2,3,4가 있다고 했을 때 → 둘을 합치면 2, 3 이 나온다.
-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명
JOIN 으로 두 테이블의 데이터 조회하기
아래는 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구한 예
select cuisine_type,
sum(price) "원래 가격",
sum(price)-sum(discount_price) "할인 적용 가격",
sum(discount_price) "할인 가격"
from
(
select a.cuisine_type,
price,
price*((b.age-50)*0.005) discount_price
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age>=50
) t
group by 1
order by 4 desc