- 세개의 테이블 join 형식
ORACLE 형식
SELECT jikwon_name AS 직원명, buser_name AS 부서명, gogek_name AS 고객명
FROM jikwon,buser,gogek
WHERE buser.buser_no = jikwon.buser_num AND jikwon.jikwon_no = gogek.gogek_damsano; -- oracle join
ANSI 형식
SELECT jikwon_name AS 직원명, buser_name AS 부서명, gogek_name AS 고객명
FROM jikwon INNER JOIN buser ON buser.buser_no = jikwon.buser_num
inner join gogek ON jikwon.jikwon_no = gogek.gogek_damsano; -- ANSI JOIN (표준)
- 활용문제
문1) 총무부에서 관리하는 고객수 출력 (고객 30살 이상만 작업에 참여)
- 방법1
SELECT buser_name as 부서명, COUNT(gogek_no) AS 고객수 FROM jikwon INNER JOIN gogek ON jikwon_no = gogek_damsano
INNER JOIN buser ON buser_no = buser_num WHERE substr(gogek_jumin,1,2) <= 93 AND buser_name = '총무부' GROUP BY buser_name;
-- count(*) 안쓰는 것이 좋음, 무엇을 세야할지 파악해 그것 입력하기
- 방법2
SELECT buser_name as 부서명, COUNT(gogek_no) AS 고객수
FROM jikwon INNER JOIN gogek ON jikwon_no = gogek_damsano
INNER JOIN buser ON buser_no = buser_num
WHERE DATE_FORMAT(NOW(),'%Y') - (1900+SUBSTR(gogek_jumin,1,2)) >= 30 AND buser_name = '총무부' GROUP BY buser_name ;
문2) 부서명별 고객 인원수 (부서가 없으면 "무소속")
SELECT nvl(buser_name,'무소속') AS 부서명, COUNT(gogek_no) AS 고객수
FROM jikwon
LEFT OUTER JOIN buser ON buser_no = buser_num
INNER JOIN gogek ON gogek_damsano = jikwon_no
GROUP BY 부서명;
문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면 담당직원 자료 출력 : ~ WHERE GOGEK_NAME='강나루'
출력 ==> 직원명 직급 부서명 부서전화 성별
SELECT jikwon_name AS 직원명, jikwon_jik AS 직급, buser_name AS 부서명, buser_tel AS 부서전화,jikwon_gen AS 성별
FROM jikwon
INNER JOIN buser ON buser_no = buser_num
INNER JOIN gogek ON gogek_damsano = jikwon_no
WHERE gogek_name = '강나루';
문4) 부서와 직원명을 입력하면 관리고객 자료 출력 : ~ WHERE BUSER_NAME='영업부' AND JIKWON_NAME='이순신'
출력 ==> 고객명 고객전화 성별
- 방법1
SELECT gogek_name AS 직원명, gogek_tel AS 고객전화,
case SUBSTR(gogek_jumin,8,1) when 1 then '남'
when 3 then '남' when 2 then '여' ELSE ' 여' END AS 성별
FROM jikwon INNER JOIN buser ON buser_no = buser_num
INNER JOIN gogek ON gogek_damsano = jikwon_no
WHERE buser_name = '영업부' AND jikwon_name = '이순신';
- 방법2
SELECT gogek_name AS 직원명, gogek_tel AS 고객전화,
case WHEN gogek_jumin LIKE '%-1%' then '남'
WHEN gogek_jumin LIKE '%-2%' then '여'
WHEN gogek_jumin LIKE '%-3%' then '남'
ELSE ' 여' END AS 성별
FROM jikwon INNER JOIN buser ON buser_no = buser_num
INNER JOIN gogek ON gogek_damsano = jikwon_no
WHERE buser_name = '영업부' AND jikwon_name = '이순신';
