SQL 구문정리
6. 조건 연산자, with문, 트랜잭션
1. 조건연산자
case -- if/else문과 같은 로직을 구사할 수 있다. 다양한 조건문 처리 가능
## 실습
select
sum
(
case
when rental_rate = 0.99 then 1
else 0
end
) as "C",
sum
(
case
when rental_rate = 2.99 then 1
else 0
end
) as "B" ;
or
select
rental_rate, count(*) CNT
from film
group by rental_rate ;
or
select *
from
(
select
sum(case when rental_rate = 0.99 then CNT else o end) as C
, sum(case when rental_rate = 2.99 then CNT else o end) as B
, sum(case when rental_rate = 4.99 then CNT else o end) as A
from
(
select
rental_rate, count(*) CNT
from film
group by rental_rate
)A
)A
;
coalesce -- 입력한 인자값 중에서 널값이 아닌 첫번째 값을 리턴한다
##실습
create table tb_item_coalesce_Test () ;
insert into tb_item_coalesce_Test ()
values
() -- null 인자 기입
;
select product,
(price - discount) as net_price -- null값이 나온다
from
tb_item_ coalesce_test ;
##coalesce 함수 실습
select
product, price, discount, coalesce(discount, 0), -- discount의 널 유무
(price - coalesce(discount, 0)) as net_price
from
tb_item_coalesce_test ;
##coalesce, case 표현식으로 처리
select
product,
(price -
case
when discount is null then 0
else discount
end
) as net_price
from tb_item_coalesce_test ;
nullif -- 입력한 두개의 인자의 값이 동일하면 null을 리턴하고, 그렇지 않으면 첫번째 인자값을 리턴한다.
|id|first_name|last_name|gender
|--|----------|---------|------
| 1|John |Doe | 1
| 2|David |Dave | 1
| 3|Bush |Lily | 2
## 여자 대비 남자 비율 구하기
select
(sum(case
when genger = 1 then 1
else 0
end)
/ sum(case
when gender = 2 then 1
else 0
end)) * 100 as "male/femail ratio"
from TB_MEMBER_NULLIF_TEST ;
## 0일 경우 오류
update TB_MEMBER_NULLIF_TEST
set gender = 1
where gender = 2;
select
(sum(case
when gender = 1 then 1
else 0
end)
/ sum(case
when gender 2 then 1
else 0
end)) * 100 as "male/femail ratio"
from TB_MEMBER_NULLIF_TEST ; -- SQL ERROR : 0으로 나눌 수 없습니다
## nullif 함수 사용
select
(sum(case
when gender = 1 then 1
else 0
end)
/ nullif(sum(case
when gender = 2 then 1
else 0
end), 0) -- 여성의 합계가 0이면 null을 리턴하도록 함
) * 100 as "male/femail ratio"
from TB_MEMBER_NULLIF_TEST ; -- NUll 리턴됨
cast -- 특정 데이터 타입으로 형변환이 가능하도록 한다.
## 문자열 -> 정수형
select
cast ('100' as integer) ; -- '100'이라는 문자열을 정수형으로 형변환 하였다
or
select
'100' :: integer ;
## 문자열 -> 정수형 오류
select
cast('10c' as integer) -- SQL ERROR
or
select
'10c' :: integer ;
## 문자열 -> date 타입
select
cast('2015-01-01' as date) ;
or
select '2015-01-01' :: date ;
## 문자열 -> 실수형
select
cast('10.2' as double precision) ;
or
select '10.2' :: double precision ;
2. with 문
with문의 활용 -- select문의 결과를 임시 집합으로 저장해두고 SQL문에서 마치 테이블처럼 집합을 불러올 수 있다
## 실습
with tmp1 as (
select -
from -
)
select * from tmp1 ;
with tmp1 as (
select -
from -
)
select * from tmp1 where length = 'Long' ;
재귀 쿼리 -- 데이터 값 기준 부모 자식간의 관계를 표현하는 SQL
## 조직도 실습
with recursive tmp1 as (
select
employee_id,
manager_id,
full_name,
0 LVL
from TB_EMP_RECURSIVE_TEST
where manager_id is null -- 최상의 관리자부터 재귀 시작
union
select
e. employee_id,
e. manager_id,
e. full_name,
s. LVL + 1
from TB_EMP_RECURSIVE_TEST E, tmp1 S
where s. employee_id = e. manager_id -- 사원ID와 관리자ID 조인
)
select employee_id, manager_id, LPAD(' ', 4* (LVL)) || full_name as full_name
from tmp1;
3. 트랜잭션
## commit, begin, rollback
BEGIN; -- 생략 가능
INSERT INTO TB_ACCOUNT_TRANSACTION_TEST
(NAME,BALANCE)
VALUES('Alice',10000); -- 'Alice',10000
commit;
INSERT INTO TB_ACCOUNT_TRANSACTION_TEST
(NAME,BALANCE)
VALUES('Danny',20000);
ROLLBACK; -- 'Alice',10000
Quiz 1
아래 basket 테이블에서 fruit 컬럼이 중복된 행을 삭제하는 delete문을 작성하시오. 단, row_number() 함수를 사용하시오.
##문제
CREATE TABLE BASKET(
ID SERIAL PRIMARY KEY
, FRUIT VARCHAR(50) NOT NULL
);
INSERT INTO BASKET(FRUIT) VALUES('APPLE');
INSERT INTO BASKET(FRUIT) VALUES('APPLE');
INSERT INTO BASKET(FRUIT) VALUES('ORANGE');
INSERT INTO BASKET(FRUIT) VALUES('ORANGE');
INSERT INTO BASKET(FRUIT) VALUES('ORANGE');
INSERT INTO BASKET(FRUIT) VALUES('BANANA');
COMMIT;
## 1 fruit별로 순위를 ASC로 구한 후 순위가 1보다 큰 것을 구한다
select ID, fruit, row_num
from
(
select
id,
fruit,
row_number() over ( partition by fruit order by id asc) as row_num
from basket) T
where T. row_num > 1 ;
## 2 기본키인 ID값을 가지고 해당 row를 삭제한다
delete from basket
where id in
(#1) ;
Quiz 2
with문과 row_number()를 이용하여 film 테이블에서 rating컬럼별로 length 컬럼이 가장 긴 영화의 목록을 구하는 select문을 작성하시오
## 1 rating 컬럼별 length를 역순으로 정렬하여 순위를 구한다
select
film_id,
title,
rating,
length,
rank() over (partition by rating order by length DESC) length_rating
from film ;
## 2 1번 select문을 with문으로 감싼 후 아래의 select문에서 순위가 1위인 것만을 추출한다
with tmp1 as (
select
film_id,
title,
rating,
length,
rank() over (partition by rating order by length DESC) length_rating
from film
)
select *
from tmp1
where length_rank = 1 ;
