--join
select * from buser;
insert into buser values(50,'비서실', '서울', '02-100-4444');
select * from sawon;
alter table sawon modify buser_num number(4) null;
update sawon set buser_num=null where sawon_no=2;
commit;
--cross join
select sawon_name, buser_name from sawon, buser;
select sawon_name, buser_name from sawon cross join buser;
--self join
select a.sawon_name, b.sawon_jik from sawon a, sawon b where a.sawon_no=b.sawon_no;
--equi join
--inner join(값이 일치하는 자료만, null자료는 제외)
select sawon_name, buser_name from sawon, buser where buser_num=buser.buser_no; --oracle
select sawon.sawon_name, buser.buser_name from sawon inner join buser on sawon.buser_num= buser.buser_no; --ansi sql(표준)
select sawon_name, gogek_name, gogek_jumin from sawon inner join gogek on sawon.sawon_no=gogek.gogek_damsano;
--right outer join
select sawon_name, buser_name from sawon, buser where buser_num(+)=buser_no; --oracle
select sawon_name, buser_name from sawon right outer join buser on buser_num=buser_no; --ansi
--left outer join
select sawon_name, buser_name from sawon, buser where buser_num=buser_no(+); --oracle
select sawon_name, buser_name from sawon left outer join buser on buser_num=buser_no; --ansi
--non-equi join ==> 조건에 = 이외의 연산자 등장
-- non-equi join 연습 : "=" 이외의 연산자를 사용하여 join 조건 부여
create table paygrade(grade number(2) primary key, lowpay number, highpay number);
insert into paygrade values(1,0,1999);
insert into paygrade values(2,2000,2999);
insert into paygrade values(3,3000,3999);
insert into paygrade values(4,4000,4999);
insert into paygrade values(5,5000,9999);
select * from paygrade;
commit;
select sawon_name, sawon_pay, grade from sawon, paygrade where sawon_pay >= lowpay and sawon_pay <= highpay and sawon_pay is not null; --oracle
select sawon_name, sawon_pay, grade from sawon inner join paygrade on sawon_pay >= lowpay and sawon_pay <= highpay and sawon_pay is not null; --ansi
--부서 내 근무자 목록(부서없는 근무자 제외)
select buser_name, sawon_name, sawon_jik, buser_tel from sawon inner join buser on sawon.buser_num = buser.buser_no order by buser.buser_name asc;
--관리고객이 있는 직원만 고객자료와 함께 출력
select distinct gogek_damsano from gogek;
select sawon_name, gogek_name, gogek_tel from sawon inner join gogek on sawon.sawon_no = gogek.gogek_damsano order by sawon.sawon_name;
select sawon_name, gogek_name, gogek_tel from sawon inner join gogek on sawon.sawon_no = gogek.gogek_damsano order by sawon.sawon_name;
--부서별 급여함(부서가 없으면 계약직으로 출력)
select nvl(buser_name,'계약직') as 부서, sum(sawon_pay) as 급여합, avg(sawon_pay) as 평균, count(*) as 인원수 from sawon, buser where sawon.buser_num = buser.buser_no(+) group by buser_name --oracle
select nvl(buser_name,'계약직') as 부서, sum(sawon_pay) as 급여합, avg(sawon_pay) as 평균, count(*) as 인원수 from sawon left outer join buser on sawon.buser_num = buser.buser_no group by buser_name -- ansi
문1) 직급이 사원인 직원이 관리하는 고객 출력
출력 ==> 사번 사원명 직급 고객명 고객전화 고객성별
3 한국인 사원 우주인 123-4567 남
select sawon_no as 사번,sawon_name as 사원명, sawon.sawon_jik as 직급, gogek.gogek_name as 고객명, gogek.gogek_tel as 고객전화,
case
when substr(gogek.gogek_jumin,8,1) = 1 then '남'
when substr(gogek.gogek_jumin,8,1) = 2 then '여'
end as 고객성별
from sawon
join gogek on sawon.sawon_no = gogek.gogek_damsano
문2) 직원별 고객 확보 수 -- GROUP BY 사용
- 모든 직원 참여
select sawon_name, count(gogek_no) --주의: count를 고객이름으로 할경우 동명이인을 처리하지 못한다.
from sawon
left outer join gogek on sawon.sawon_no = gogek.gogek_damsano
group by sawon_name
order by sawon_name
- 고객이 없는 직원 제외
select sawon_name, count(gogek_no) --주의: count를 고객이름으로 할경우 동명이인을 처리하지 못한다.
from sawon
join gogek on sawon.sawon_no = gogek.gogek_damsano
group by sawon_name
order by sawon_name
문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면 담당직원 자료 출력
: ~ WHERE GOGEK_NAME='강나루'
출력 ==> 직원명 직급
한국인 사원
select sawon_name, sawon_jik
from sawon
join gogek on sawon.sawon_no = gogek.gogek_damsano
where gogek_name = '강나루'
문4) 직원명을 입력하면 관리고객 자료 출력
: ~ WHERE SAWON_NAME='한국인'
출력 ==> 고객명 고객전화 주민번호 나이
강나루 123-4567 700512-1234567 38
select gogek_name, gogek.gogek_tel, gogek.gogek_jumin, to_char(sysdate,'YYYY') - (substr(gogek.gogek_jumin,1,2)+1900) as 나이
from sawon
join gogek on sawon.sawon_no = gogek.gogek_damsano
where sawon_name='홍길동'
