Subquery, any, all 연산자, exists 연산자, subquery를 이용한 테이블 생성 및 자료 추가

--Subquery : 질의 속에 질의가 있는 형태

                    (안쪽 질의의 결과를 바깥쪽 질의에서 사용하는 것이 일반적이다)
--select * from sawon;
--'김이화'와 직급이 같은 직원 출력
select sawon_jik from sawon where sawon_name='김이화';  --직급을 얻었다
select * from sawon where sawon_jik='사원';  --해당 직급 자료 출력
-- 아래처럼 두개를 합치는 이유는 서버 퍼포먼스를 향상시킬수 있기때문이다. 서프쿼리가 한번에 처리 할 수 있기때문에.
--1) 일반적인 Subquery 참조 방식 : 안쪽 질의의 결과를 바깥 질의에서 사용
select * from sawon where sawon_jik=(select sawon_jik from sawon where sawon_name='김이화');  --위에 두개를 합친것이다. 즉 김이화의 직급을 구해서 구한 값으로 sawon_jik에서 찾는다.

--직급이 대리중 가장 먼저 입사한 직원은?
select min(sawon_ibsail) from sawon where sawon_jik='대리';
-- select * from sawon where sawon_ibsail='2002-3-2'; --(x) 착각한것.  2002-3-2일에 입사한 사람 모두 나와라. 이뜻임.
select * from sawon where sawon_jik='대리' and sawon_ibsail='2002-3-2';  --(o) 이렇게 써줘야한다.
select * from sawon
where sawon_jik='대리' and    --옆에말을 꼭 써줘야한다.  아래 주석부터 읽어볼 것. sawon_jik='대리' and 없다고 생각하고 아래부터 읽어볼것.

sawon_ibsail=(select min(sawon_ibsail) from sawon where sawon_jik='대리'); --(x) *중요* 착각하면 안된다. 안쪽 질의가 준값은 '2002-3-2'일 이라는 값을 줬기때문에. 바깥쿼리에서 그 값으로 검색을하면 '2002-3-2'에 입사한 모든 사람이 출력되게 된다. 그러므로 바깥쪽쿼리에서 '대리'만 출력하는 조건을 또 걸어줘야한다.

 

--'김해자'고객과 담당 직원이 같은 고객자료 출력
select * from gogek where gogek_samsano=(select gogek_damsano from gogek where gogek_name='김해자');

--고객중 '차일호'와 나이가 같은 고객자료 모두 출력
select * from gogek where substr(gogek_jumin,1,2)=(select substr(gogek_jumin,1,2) from gogek where gogek.gogek_name='차일호');

--인천에서 근무하는 직원 출력
select * from sawon where buser_num=(select buser_no from buser where buser.buser_loc='인천');  --*중요*테이블이 두개가 같이 사용되었다.
select * from buser;

--인천에서 이외의 지역에서 근무하는 직원 출력
select * from sawon where buser_num in(select buser_no from buser where not buser.buser_loc='인천');  --not을 붙이면 인천을 제외한 나머지값들을 다 넘어오기때문에 =로 받을 수 없다. in을 걸어야한다.

--사번이 3번 직원과 직급이 같고, 직급이 사원인 직원의 평균 연봉보다 귭여가 많은 직원 출력
select * from sawon where sawon_jik=(select sawon_jik from sawon where sawon_no=3) and sawon_pay>=(select avg(sawon_pay) from sawon where sawon_jik='사원');

 

--sub쿼리 연습문제----------------
SAWON, BUSER, GOGEK 테이블을 사용한다.

문1) 2000년 이후에 입사한 남자 중 급여를 가장 많이 받는 직원은? 함정
select * from sawon where sawon_pay=(select max(sawon_pay) from sawon where sawon_gen='남' and sawon_ibsail>='2000-1-1')
and sawon_gen='남' and sawon_ibsail>='2000-1-1';
 

문2)  평균급여보다 급여를 많이 받는 직원은?
select * from sawon where sawon_pay>(select avg(sawon_pay) from sawon);
 

문3) '한국남' 직원의 입사 이후에 입사한 직원은?
select * from sawon where sawon_ibsail>(select sawon_ibsail from sawon where sawon_name='한국남');
 

문4) 직급이 과장인 사람 중에서 급여를 가장 많이 받는 사람은? 함정.
select * from sawon where sawon_pay=(select max(sawon_pay) from sawon where sawon_jik='과장') and sawon_jik='과장';

 

문5) 2000 ~ 2005 사이에 입사한 총무부,영업부,전산부 직원 중 급여가 가장 적은 사람은?  함정
 (직급이 NULL인 자료는 작업에서 제외)

select * from sawon where sawon_pay=(select min(sawon_pay) from sawon
where sawon_ibsail>='2000-1-1' and sawon_ibsail<='2005-12-31' and buser_num in(select buser_no from buser where buser_name in('총무부','영업부','전산부'))
and sawon_jik is not null) and buser_num in(select buser_no from buser where buser_name in('총무부','영업부','전산부')) and sawon_ibsail>='2000-1-1' and sawon_ibsail<='2005-12-31' ;

문6) 이순라, 이순신과 직급이 같은 사람은 누구인가?
select * from sawon where sawon_jik in(select sawon_jik from sawon where sawon_name='이순라' or sawon_name='이순신');
 

문7) 과장 중에서 최대급여, 최소급여를 받는 사람은? 함정.
select * from sawon where sawon_pay in((select max(sawon_pay) from sawon where sawon_jik='과장'),
(select min(sawon_pay) from sawon where sawon_jik='과장') ) and sawon_jik='과장';
 

문8) 20번 부서의 최소급여보다 많은 사람은?
select * from sawon where sawon_pay>(select min(sawon_pay) from sawon where buser_num=20);
 

문9) 30번 부서의 평균급여보다 급여가 많은 '대리' 는 몇명인가?
select * from sawon where sawon_pay>(select avg(sawon.sawon_pay) from sawon where buser_num=30) and sawon_jik='대리';
 

문10) 고객을 확보하고 있는 직원들의 이름, 직급, 부서명을 입사일 별로 출력하라.

select sawon_name as 이름, sawon_jik as 직급, buser_name as 부서명 from sawon left outer join buser on buser_num= buser.buser_no
where sawon_no in(select gogek_damsano from gogek group by gogek_damsano) order by sawon.sawon_ibsail desc;

select * from gogek;

문11) 이순신과 같은 부서에 근무하는 직원과 해당 직원이 관리하는 고객 출력
(고객은 나이가 30 이하면 '청년', 40 이하면 '중년', 그 외는 '노년'으로 표시하고, 고객 연장자 부터 출력)

      출력 ==>  직원명    부서명     부서전화     직급      고객명    고객전화    고객구분

               한송이    총무부   123-1111     사원      백송이    333-3333       청년   

 

select sawon_name as 직원명, buser_name as 부서명, buser_tel as 부서전화, sawon_jik as 직급, gogek_name as 고객명, gogek_tel as 고객전화,
case when ((case when substr(gogek_jumin,8,1)<=2 then ((to_char(sysdate,'YYYY')) - ((substr(gogek_jumin,1,2))+1900))
when substr(gogek_jumin,1,2)<=4 then ((to_char(sysdate,'YYYY')) - ((substr(gogek_jumin,1,2))+2000)) end )<=30)
then '청년'
when ((case when substr(gogek_jumin,8,1)<=2 then ((to_char(sysdate,'YYYY')) - ((substr(gogek_jumin,1,2))+1900))
when substr(gogek_jumin,1,2)<=4 then ((to_char(sysdate,'YYYY')) - ((substr(gogek_jumin,1,2))+2000)) end )<=40)
then '중년' else '노년' end as 고객구분
from sawon
inner join buser on buser_num=buser_no
inner join gogek on sawon.sawon_no= gogek.gogek_damsano
where sawon.buser_num=(select buser_num from sawon where sawon.sawon_name='이순신') order by gogek_jumin asc;

select * from gogek;

 

 

--총무부에 근무하는 직원들이 관리하는 고객 출력
--1) subquery
SELECT * from gogek where gogek_damsano in(select sawon_no from sawon where buser_num=(select buser_no from buser where buser_name='전산부'));

--2) join : 조인일때는 select *   <--- 별하면 안된다. 정확하게 칼럼명을 적어 줄 것.
select gogek_no, gogek_name, gogek_tel, gogek_jumin, gogek_damsano
from gogek
inner join sawon on sawon_no=gogek_damsano
inner join buser on buser_num=buser_no
where buser_name='전산부';

 

--any, all 연산자 - subquery -
< any ; subquery의 반환값 중 최대값 보다 작은 ~
> any ; subquery의 반환값 중 최소값 보다 큰  ~
< all ; subquery의 반환값 중 최소값 보다 작은 ~
> all ; subquery의 반환값 중 최대값 보다 큰 ~

 

--대리의 연봉 중 최대값 보다 작은 자료 출력
select sawon_no, sawon_name, sawon_pay from sawon
where sawon_pay < any(select sawon_pay from sawon where sawon_jik='대리');

--10번 부서의 최고 급여자 보다 급여를 많이 받는 직원은?
select sawon_name, sawon_pay from sawon where sawon_pay > all (select sawon_pay from sawon where buser_num=10);

--10번 부서의 최저 급여자 보다 급여를 많이 받는 직원은?
select sawon_name, sawon_pay from sawon where sawon_pay > any (select sawon_pay from sawon where buser_num=10);

--상관 서브 쿼리(가급적 사용하지 말것. 이유: 가독성이 떨어진다.) : 안쪽 질의에서 바깥쪽 질의를 참조하고, 다시 안쪽에 결과를 바깥쪽 질의에서 참조하는 것
--각 부서의 최대 급여자는 ?
select * from sawon a
where a.sawon_pay=(select max(b.sawon_pay) from sawon b where a.buser_num=b.buser_num);

--from 절에 사용하는 subquery : inline view
//전체 평균 급여와 최대 급여 사이의 급여를 받는 직원 출력;
select sawon_no, sawon_name, sawon_pay
from sawon a, (select avg(sawon_pay) avgs,max(sawon_pay) maxs from sawon) b
where a.sawon_pay between b.avgs and b.maxs;

//각 부서별 최대 급여자를 출력;
select a.sawon_no, a.sawon_name, a.sawon_pay, a.buser_num from sawon a,(select buser_num, max(sawon_pay) maxpay from sawon
group by buser_num) b
where a.buser_num=b.buser_num and a.sawon_pay=b.maxpay;  --a.buser_num=b.buser_num 때문에 null값은 가진 것은 빠진다.

//부서별 평균급여 중 10번 부서의 평균 급여보다 큰 자료만 출력
select buser_num, avg(sawon_pay) from sawon group by buser_num
having avg(sawon_pay) > (select avg(sawon_pay) from sawon where buser_num='10');

--exists 연산자 : subquery의 결과 유무를 검사
--직원이 있는 부서 출력 : 즉 자료를 가지고 있는.
select buser_name, buser_loc from buser bu
where exists (select 'imsi' from sawon where buser_num= bu.buser_no);

--not exists 연산자
--직원이 없는 부서 출력 : 즉 자료를 가지고 있지않은.
select buser_name, buser_loc from buser bu
where not exists (select 'imsi' from sawon where buser_num= bu.buser_no);

--subquery를 이용한 table 생성 및 자료 추가 가능
create table sa1 as select * from sawon;   --테이블 복사 명령(단, 제약조건(pk,fk,Unq,check...)은 안넘어간다. 주의할것) 뒤에 쓴 select * from sawon 부분이 서브쿼리다.
select * from sa1;

create table sa2 as select * from sawon where 1=0;  --테이블의 칼럼값은 필요없을때, 서브쿼리에서 없는 조건값을 일부로 넘겨줘서 자료를 안넘김. 즉, 구조만 넘겼다.
select * from sa2;

insert into sa2 select * from sawon where sawon_jik='과장'; -- 서브쿼리를 이용한 레코드 추가.(서브쿼리의 내용은 다른 테이블이여도 괜찮다. 구조만 같으면 괜찮다!)
select * from sa2;
insert into sa2(sawon_no, sawon_name, buser_num) select sawon_no, sawon_name, buser_num from sawon; -- 레코드 추가시 (일부칼럼만) 가지고 레코드를 추가 할 수 있다. 즉, 값을 안준 칼럼은 null값이 된다.
select * from sa2;

create table sa3 as select sawon_no bungo, sawon_name irum, sawon_pay pay from sawon where 1=0; --구조만 가지고 갔다. 그리고 칼럼명을 별명을 줘서 바꿔버렸다. 즉, 만들때 별명을 주면 그 별명으로 새로운 칼럼이 생성된다.
select * from sa3;

//복수 테이블에 복수 칼럼 추가하기
create table sa4 as select sawon_no, sawon_name, sawon_jik from sawon where 1=0; --구조만 가지고 갔다. 그리고 칼럼명을 별명을 줘서 바꿔버렸다. 즉, 만들때 별명을 주면 그 별명으로 새로운 칼럼이 생성된다.
create table sa5 as select sawon_no, sawon_name, sawon_pay, sawon_gen from sawon where 1=0;
select * from sa4;
select * from sa5;

insert all into sa4 values(sawon_no, sawon_name, sawon_jik)   --두개의 테이블을 동시에 insert into할 수 있다.
into sa5 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';  --자료가 너무 많이 들어가기 때문에, 일부로 buser_num을 10으로 제한(큰 의미없음)한 것이다.
select * from sa4;
select * from sa5;

--조건에 따른 복수 테이블에 복수 칼럼 추가
insert all
when sawon_jik='사원' then  --'사원'일때문 sa4애 넣고
into sa4 values(sawon_no, sawon_name, sawon_jik)
when sawon_gen='남' then--  '남'일때만 sa5에 넣는다. 즉 조건에 의해서 insert에 참여를 시킨다.
into sa5 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);
select * from sa4;
select * from sa5;

--update 에서 subquery 사용
select * from sa1;
update sa1 set sawon_jik=(select sawon_jik from sawon where sawon_name='한국남')
where sawon_no=1;
select * from sa1;
--delete 에서 subquery 사용
delete from sawon where sawon_no=17;
select * from sa1;

--rownum : top-N을 구할 수 있다. (rownum은 오라클에만 있고, my-sql은 limit)
select * from sawon where sawon_pay rownum <= 6 ;

급여 순위 3위 이내 출력
select * from (select * from sawon order by sawon_pay desc)
where sawon_pay is not null and rownum <=3 ;

가장 최근에 입사한 직원 5위 이내 출력
select sawon_name sawon_ibsail from (select sawon_name, sawon_ibsail from sawon order by sawon_ibsail) where rownum <= 5;

-- Database Transaction : *중요* 한 사용자에 의해 한 개 이상의 SQL구문을 포함하는 가장 작은 논리적인 작업을 의미(단위별 자료 처리, 자료의 일관성 보장)
--                        commit 이나 rollback이 나오면 작업이 끝난다.
ex) commit; = 클라이언트에서 변경된 내용을 원본데이터에 반영시킨다.
select * from sa1;
delete from sa1 where sawon_no >=10;
commit;

ex) rollback; = 클라이언트에서 변경된 내용을 commit이 안되어 있으면 다시 되돌린다.(트랜잭션 로그파일에 저장되어 있던 내용을 되돌리는 개념이다)
delete from sa1 where sawon_no >=6;
select * from sa1;
rollback;

ex)  savepoint ?; = 세이브 포인트를 한 지점까지 작업을 인정해주고. 그 이하작업부터 rollback 시킨다.(사용시 : rollback to savepoint A;)
select * from sa1;
update sa1 set sawon_pay = 10000 where sawon_no=7;
savepoint A;
update sa1 set sawon_pay = 11000 where sawon_no=8;
rollback to savepoint A;
select * from sa1 where sawon_no in(7,8);
rollback;
update sa1 set sawon_pay = 12000 where sawon_no=10;
commit;
select * from sa1;