SQL subquery 연습문제 풀이

728x90
< 문제 모음 >
-- 고객 중에서 '차일호'와 나이가 같은 고객자료 출력
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;





본 포스팅은 IT 교육기관인 KIC 캠퍼스의 지원을 받은 리포트입니다.
본문은 '남궁성, JAVA의 정석 3rd Edition' 교재를 공부하면서 주요 내용을 요약하고 있습니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.



728x90

'DATA' 카테고리의 다른 글

PL/SQL 기초 연습  (0) 2018.01.22
SQL 계정(사용자) 생성 및 보안 요약  (0) 2018.01.19
SQL transaction, view 요약  (0) 2018.01.18
SQL union, merge, subquery 명령어 요약  (0) 2018.01.16
SQL join 명령어 요약  (0) 2018.01.16
SQL select 명령어 및 내장함수 예제  (0) 2018.01.15