직급이 조교수 (assistant professor) 인 사람의 보너스를 200만원 인상
단 null 은 추가가 안된다.
null 값도 0 으로 취급해서 200 씩 값이 더해졌다.
nvl2 로도 처리 가능
Subquery
Subquery (서브쿼리) : query 속의 query
단일행 Subquery
ex) select * from emp where 조건 (select sal from emp where ~)
SCOTT 의 sal 보다 적은 급여를 받는 사람의 정보 출력
Subquery 를 쓰지 않는다면, Subquery 안의 결과가 조건에 따라 바뀌게 되면 그때마다 새로운 query를 사용해야한다. 큰 조건은 같은데, 그 안의 subquery의 조건만 달라질 때 쓰면 유용하다.
ex) Scott 의 sal 보다 적은 급여를 받는 사람의 정보를 원함.
큰 조건 : Scott 의 sal 보다 적은 급여를 받는 사람
작은 조건 : Scott 의 sal 이 달라짐.
emp table 에서 WARD 보다 comm 을 적게 받는 사람의 이름과 comm 출력
마찬가지로 null 처리하기 위해 nvl 을 써준다
professor table 에서 Sharon Stone 교수의 직급과 동일한 교수 중 현재 급여가 250이 안되는 교수의 급여를 20% 인상
Subquery 주의점
- Subquery 부분은 where 절의 연산자 오른쪽에 위치, 반드시 () 로 묶기
- 특별한 경우를 제외하고 subquery 절에 order by 사용불가
- 단일행 subquery 와 다중행 subquery 를 구분해서 사용해야한다.
단일행 Subquery 연산자
= : 같다
<> : 같지 않다
> : 크다
>= : 크거나 같다
< : 작다
<= : 작거나 같다
student table 과 department 테이블을 사용하여 'Anthony Hopkins' 학생과 1전공 (deptno1) 이 동일한 학생들의 이름과 1전공 이름을 출력 (join 활용)
위가 ansi style
아래가 oracle style
SQL 미션
메일로 제출
메일 제목 : 관계설정_홍길동
-- 미션
-- table 생성
create table product(productno number,productname varchar2(300),modelname varchar2(300),origin varchar2(300),
price number,inventory number,maker varchar2(300));
create table orderlist(orderlistno number,modelname varchar2(300),quantity number,price number,
productno number,orderno number);
create table orders(orderno number,orderdate date,customerno number,employeeno number);
create table customer(customerno number, customername varchar2(300),city varchar2(300),
streetaddress varchar2(300), postalcode number,tel number,mileage number);
create table delivery(deliveryno number,deliveryaddress varchar2(300),deliverydate date,
deliverymodelname varchar2(300),deliveryquantity number,customerno number);
create table employee(employeeno number,employeename varchar2(300),tel number,position varchar2(300));
-- pk 추가
alter table product add constraint pk_product_productno primary key(productno);
alter table orderlist add constraint pk_orderlist_orderlist_no primary key(orderlistno);
alter table orders add constraint pk_orders_orderno primary key(orderno);
alter table customer add constraint pk_customer_customerno primary key(customerno);
alter table delivery add constraint pk_delivery_deliveryno primary key(deliveryno);
alter table employee add constraint pk_employee_employeeno primary key(employeeno);
-- fk 추가
alter table orderlist add constraint fk_orderlist_productno foreign key(productno) references product(productno);
alter table orderlist add constraint fk_orderlist_orderno foreign key(orderno) references orders(orderno);
alter table orders add constraint fk_orders_customerno foreign key(customerno) references customer(customerno);
alter table orders add constraint fk_orders_employeeno foreign key(employeeno) references employee(employeeno);
alter table delivery add constraint fk_delivery_customerno foreign key(customerno) references customer(customerno);
-- 제약조건 조회
select * from all_constraints where table_name='PRODUCT';
select * from all_constraints where table_name='ORDERLIST';
select * from all_constraints where table_name='ORDERS';
select * from all_constraints where table_name='CUSTOMER';
select * from all_constraints where table_name='DELIVERY';
select * from all_constraints where table_name='EMPLOYEE';
제약조건 조회
