SQL subquery 연습문제

< 문제 모음 >

-- 고객 중에서 '차일호'와 나이가 같은 고객자료 출력

select * from gogek where substr(gogek_jumin,1,2) = (select substr(gogek_jumin,1,2) from gogek where gogek_name='차일호');

-- 인천에서 근무하는 직원 출력

select * from sawon where buser_num = (select buser_no from buser where buser_loc='인천');

-- where 조건이 복수

-- 2번 직원과 직급이 같고, 직급이 사원인 직원의 평균 연봉보다 급여가 많은 직원 출력

select sawon_no, sawon_name, sawon_pay from sawon where sawon_jik=(select sawon_jik from sawon where sawon_no=7) and sawon_pay >= (Select avg(sawon_pay)

from sawon where sawon_jik='사원');

--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' );

--문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) 2000 ~ 2005 사이에 입사한 총무부,영업부,전산부 직원 중 급여가 가장 적은 사람은?

--       (직급이 NULL인 자료는 작업에서 제외)

select * from sawon where sawon_pay = (select min(sawon_pay) from sawon

where sawon_ibsail >='2000-1-1' and

sawon_ibsail <'2006-1-1' and

buser_num  in(select buser_no from buser where buser_name in('총무부','영업부','전산부')) and sawon_jik is not null);

--문5) 이순라, 이순신과 직급이 같은 사람은 누구인가?

select * from sawon

where sawon_jik  in(select sawon_jik from sawon where sawon_name = '이순신' or sawon_name = '이순라');

--문6) 과장 중에서 최대급여, 최소급여를 받는 사람은?

select * from sawon

where sawon_jik = '과장' and

sawon_pay in((select min(sawon_pay) from sawon where sawon_jik = '과장'),(select max(sawon_pay) from sawon where sawon_jik = '과장'));

--문7) 20번 부서의 최소급여보다 많은 사람은?

select * from sawon

where sawon_pay > (select min(sawon_pay) from sawon where buser_num = 20);

--문8) 30번 부서의 평균급여보다 급여가 많은 '대리' 는 몇명인가?

select count(sawon_jik) 인원수 from sawon

where sawon_jik = '대리' and

sawon_pay > (select avg(sawon_pay) from sawon where buser_num = 30) group by sawon_jik;

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

select sawon_name, sawon_jik,buser_name from sawon

left outer join buser on buser_num=buser_no

where sawon_no in(select distinct gogek_damsano from gogek)

order by sawon_ibsail;

--문10) 이순신과 같은 부서에 근무하는 직원과 해당 직원이 관리하는 고객 출력

--(고객은 나이가 30 이하면 '청년', 40 이하면 '중년', 그 외는 '노년'으로 표시하고, 고객 연장자 부터 출력)

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

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

select sawon_name 직원명, buser_name 부서명, buser_tel 부서전화, sawon_jik 직급, gogek_name 고객명, gogek_tel 고객전화,

case when (100 + to_char(sysdate,'YY') - substr(gogek_jumin,1,2)) <= 30 then '청년'

when (100 + to_char(sysdate,'YY') - substr(gogek_jumin,1,2)) <=40 then '중년' else '노년' end  고객구분 from sawon

inner join gogek on sawon_no = gogek_damsano

inner join buser on buser_num = buser_no

where buser_num = (select buser_num from sawon where sawon_name = '이순신')

order by 고객구분;

< subquery 추가 연습 >

-- subquery 추가 연습 : any와 all 연산자

-- < any : subquery의 반환값 중 최대값보다 작은 ~

-- > any : subquery의 반환값 중 최대값보다 큰 ~

-- < all : subquery의 반환값 중 최소값보다 작은 ~

-- < all : subquery의 반환값 중 최대값보다 작은 ~

-- 대리의 연봉이 최대값보다 작은 자료 출력

select * from sawon

where sawon_pay < any (select sawon_pay from sawon

where sawon_jik='대리');

-- 30번 부서의 최대 급여자 보다 급여를 많이 받는 사람은?

select * from sawon

where sawon_pay > all (select sawon_pay from sawon

where buser_num=30);

-- 총무부에 근무하는 직원들이 관리하는 고객 출력

-- 방법1 : subquery

select gogek_no, gogek_name, gogek_jumin 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 gogek_no, gogek_name, gogek_jumin from gogek

inner join sawon on sawon_no=gogek_damsano

inner join buser on buser_num=buser_no

where buser_name='총무부';

-- exists 연산자 사용 - subquery의 결과

-- 직원이 있는 부서 출력

select buser_name, buser_loc from buser bu

where not exists (select 'imsi' from sawon

where buser_num=bu.buser_no);

-- 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;

-- group by의 having 절 안에 subquery 사용

-- 부서별 평균급여중 20번 부서의 평균 급여보다 큰 자료만 출력

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=20);

< 상관 서브쿼리 : 안쪽 질의의 결과를 바깥쪽에서 참조하고, 다시 안쪽에 결과를 바깥쪽 질의에서 참조하는 형태 >

-- 각 부서의 최대 급여치는?

select * from sawon a

where a.sawon_pay = (select max(sawon_pay) from sawon b

where a.buser_num=b.buser_num);

-- 급여순위 3위 이내의 자료 출력(desc)

select a.sawon_name, a.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를 이용한 table 생성 및 insert >

create table sa1 as select * from sawon; -- 제약조건(pk)는 복사가 안됨

select * from sa1;

desc sa1;

create table sa1 as select * from sawon where 1=0;

select * from sa2;

desc sa2;

create table sa3 as select sawon_no bunho,

sawon_name irum, sawon_pay pay from sawon where 1=0;

select * from sa3;

-- insert

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)

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; -- 여러개의 데이터를 한번에 insert

insert all

into sa4 values(sawon_no, sawon_name, sawon_jik)

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;

select * from sa4;

< 조건에 따른 복수 테이블에 insert 하기 >

insert all

when sawon_jik='사원' then

into sa4 values(sawon_no, sawon_name, sawon_jik)

when sawon_gen='남' then

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;

< subquery로 업데이트, 삭제 >

create table sa6 as select * from sawon;

select * from sa6;

update sa6 set sawon_jik=(select sawon_jik from sawon

where sawon_name='한국남') where sawon_no=1;

select * from sa6;

delete from sa6; where sawon_no in(select distinct gogek_damsano from gogek); -- 고객을 가진 직원 자료 모두 삭제

select * from sa6;