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;





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



'스터디 > SQL' 카테고리의 다른 글

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

댓글(0)

Designed by JB FACTORY