--exists 연산자 : subquery의 결과유무를 검사
select buser_name, buser_loc from buser bu
where EXISTS (select 'imsi' from sawon where buser_num=bu.buser_no); -- true false를 반환
--직원이 없는 부서 자료 출력
select buser_name, buser_loc from buser bu
where not EXISTS (select 'imsi' from sawon where buser_num=bu.buser_no);
--상관 서브쿼리
-- 각 부서의 최대 급여자는?
select * from sawon a
where a.sawon_pay = (select max(sawon_pay) from sawon b
where a.buser_num = b.buser_num);
--급여순위 3위 이내의 자료 출력(내림차순)
select sawon_no, sawon_name, sawon_pay from sawon a
where 3 >(select count(*) from sawon b where b.sawon_pay > a.sawon_pay) and sawon_pay is not null order by sawon_pay desc;
--subquery 기타
create table ex1 as select * from sawon; --create table 제약조건은 복사되지 않음
select * from ex1
desc ex1;
create table ex2 as select * from sawon where 1=0; -- 구조만 복사(자료는 없음)
select * from ex2;
insert into ex2 select * from sawon where sawon_jik='과장'; -- insert
insert into ex2(sawon_no, sawon_name, buser_num) select sawon_no, sawon_name, buser_num from sawon where sawon_jik='대리';
select * from ex2;
create table ex3 as select sawon_no bunho, sawon_name irum, sawon_pay pay from sawon where 1=0; -- 별명으로 구조 생성
select * from ex3
insert into ex3 select sawon_no, sawon_name, sawon_pay from sawon where sawon_no <= 5; -- 사원번호 5이하애들만 테이블에 삽입(삽입할 테이블의 컬럼 순서대로 적어준다)
--복수 테이블에 복수 컬럼 추가하기
create table ex4 as select sawon_no, sawon_name, sawon_jik from sawon where 1=0;
create table ex5 as select sawon_no, sawon_name, sawon_pay, sawon_gen from sawon where 1=0;
select * from ex4;
select * from ex5;
insert all
into ex4 values(sawon_no, sawon_name, sawon_jik)
into ex5 values(sawon_no, sawon_name, sawon_pay, sawon_gen)
select sawon_no, sawon_name, sawon_jik, sawon_pay, sawon_gen from sawon where buser_num=10;
--조건에 따른...
insert all
when sawon_jik='사원' then into ex4 values(sawon_no, sawon_name, sawon_jik)
when sawon_gen='여' then into ex5 values(sawon_no, sawon_name, sawon_pay, sawon_gen)
select sawon_no, sawon_name, sawon_jik, sawon_pay, sawon_gen from sawon where buser_num in (20,30);
--update에서 subquery
update sawon2 set sawon_jik=(select sawon_jik from sawon where sawon_name='이순라') where sawon_no=1;
select * from sawon2;
--delete에서 subquery
delete from sawon2 where sawon_no in(select DISTINCT gogek_damsano from gogek);
select * from sawon2;
--rownum : Top-N 처리 - select시 원하는 개수만큼 자료 출력
-- 연산자는 <, <= 를 사용한다.
select sawon_no, sawon_name, sawon_pay from sawon where rownum<=5;
--급여 순위 3위 이내 출력
select rownum, sawon_name, sawon_pay from (select sawon_name, sawon_pay from sawon order by sawon_pay desc) where rownum <= 3;
--가장 최근에 입사한 직원 5위 이내 출력
select rownum, sawon_name, sawon_ibsail from (select sawon_name, sawon_ibsail from sawon order by sawon_ibsail desc) where rownum <= 5;