상세 컨텐츠

본문 제목

SQL join 명령어 요약

Machine Learning

by 에스프리터 2018. 1. 16. 16:02

본문


< join : 하나 이상의 테이블(여러개)에서 원하는 데이터 추출 >
- cross join // 거의 안씀
select sawon_name, buser_name from sawon, buser;
select sawon_name,buser_name from sawon cross join buser; -- ANSI SQL 표준

<  equi join >
- inner join 양쪽 테이블 모두에 자료가 있는 경우에만 출력
select * from buser;
insert into buser values(50,'축구부', '파주','031-234-5678'); - 축구부 삽입
select * from sawon;
alter table sawon modify buser_num number(4) null; -- 부서 번호 4를 null로 변경
update sawon set buser_num=null where sawon_no=5;
commit;

select sawon_name, buser_name from sawon, buser where buser_num=buser_no; -- 오라클 표준
select sawon_name, buser_name from sawon inner join buser on buser_num=buser_no; -- ANSI 표준
select sawon_name, buser_name from sawon inner join buser on sawon.buser_num=buser.buser_no;

< non equi join // 잘 안씀 >
create table paygrade(grade number(1) 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;

select sawon_no, sawon_name, sawon_pay, grade from sawon, paygrade where sawon_pay >= lowpay and sawon_pay <= highpay; -- 오라클 표준
select sawon_no, sawon_name, sawon_pay, grade from sawon inner join paygrade on sawon_pay >= lowpay and sawon_pay <= highpay; -- ansi 표준

< SELF Join >
select a.sawon_name,b.sawon_jik from sawon a, sawon b where a.sawon_no=b.sawon_no;

- sawon + gogek
select sawon_no, sawon_name, sawon_jik, gogek_name, gogek_jumin from sawon inner join gogek on sawon_no=gogek_damsano;

- 부서내에 근무자 출력(부서없는 근무자)
select buser_name, sawon_name,sawon_jik,buser_tel from sawon inner join buser on sawon.buser_num = buser.buser_no;

- 부서별 급여합, 급여평균
select buser_num as 부서, sum(sawon_pay) as 급여합 from sawon group by buser_num;

- self join // 잘 안씀
- 보통 inner join을 많이 씀 - 그냥 순서대로 정렬
- Left outer join : 왼쪽 칼럼은 전부 나옴 / 오른쪽은 대응될땐 표시, 안되면 null 표기
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 표준

< full join : 양쪽 테이블 자료 모두 출력 >
select sawon_name,buser_name from sawon full join buser on buser_num=buser_no;

- right outer join : 오른쪽 칼럼은 전부 나옴 / 왼쪽쪽은 대응될땐 표시, 안되면 null 표기


< 문1) 직급이 사원인 직원이 관리하는 고객 출력 >
--출력 ==>  사번   사원명   직급    고객명    고객전화   고객성별
--           3    한국인   사원    우주인    123-4567     남
select sa.sawon_no, sa.sawon_name, sa.sawon_jik,
go.gogek_name, go.gogek_tel,
case substr(gogek_jumin,8,1)
when '1' then '남' else '여' end as 고객성별
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano
where sa.sawon_jik = '사원';

< 문2) 직원별 고객 확보 수  -- GROUP BY 사용 >
--   - 모든 직원 참여
select sawon_name 직원이름,count(gogek_name) 고객확보수
from sawon left outer join gogek
on sawon_no = gogek_damsano
group by sawon_name;  --동명이인 처리X

select sawon_no 직원이름,count(gogek_name) 고객확보수
from sawon left outer join gogek
on sawon_no = gogek_damsano
group by sawon_no;  --동명이인 처리

<  문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면,  >
--     담당직원 자료 출력  
--       :    ~ WHERE GOGEK_NAME='강나루'
--출력 ==>  직원명       직급
--          한국인       사원
select sawon_name, sawon_jik
from sawon inner join gogek
on sawon_no = gogek_damsano where gogek_name='이나라';

< 문4) 직원명을 입력하면 관리고객 자료 출력 >
--      : ~ WHERE SAWON_NAME='한국인'
--출력 ==>고객명   고객전화         주민번호       나이
--        강나루   123-4567    700512-1234567     38

-- 2000년생 이전 고객
select go.gogek_name 고객명, go.gogek_tel 고객전화,
go.gogek_jumin 주민번호,
to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin,1,2)+1900) 나이
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano where sa.sawon_name='한국남';

--2000년생 이후 고객 존재 시
select go.gogek_name 고객명, go.gogek_tel 고객전화,
go.gogek_jumin 주민번호 ,
case
when substr(go.gogek_jumin, 8,1) > 3
then to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin, 1,2)+2000)
else to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin, 1,2)+1900) end as 나이
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano where sa.sawon_name='한국남';

< 문1-1) 총무부에서 관리하는 고객수 출력 (고객 30살 이상만 작업에 참여) >

select buser_name 부서이름, count(buser_name) 고객수
from sawon inner join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no
where (to_char(sysdate, 'YYYY')- (substr(gogek_jumin, 1,2)+1900)) >=30 and  buser_name = '총무부'
group by buser_name;


< 문2-1) 부서명별 고객 인원수 (부서가 없으면 "무소속") >
select nvl(buser_name,'무소속') 부서명, count(nvl(buser_name,'무소속')) 고객인원수
from sawon inner join gogek on gogek_damsano = sawon_no
left outer join buser on  buser_no = buser_num
group by  nvl(buser_name,'무소속');


< 문3-1) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면     담당직원 자료 출력  >
--        :    ~ WHERE GOGEK_NAME='강나루'
--출력 ==>  직원명    직급   부서명  부서전화    성별

select sawon_name 직원명, nvl(sawon_jik,'프리랜서') 직급, nvl(buser_name,'프리랜서') 부서명, nvl(buser_tel,'프리랜서') 부서전화, sawon_gen 성별
from  sawon left outer join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no where gogek_name = '강나루';

< 문4-1) 부서와 직원명을 입력하면 관리고객 자료 출력 >
--       ~ WHERE BUSER_NAME='영업부' AND SAWON_NAME='이순신'
--출력 ==>  고객명    고객전화      성별
--            강나루   123-4567       남
select gogek_name 고객명, gogek_tel 고객전화, case substr(gogek_jumin,8,1) when '1' then '남' else '여' end as 성별
from  sawon inner join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no where buser_name='영업부'  and sawon_name = '이순신';




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



태그

관련글 더보기

댓글 영역