데이터베이스 20180820 (학원4)

20180820

@GROUPING

 -> ROLLUP이나 CUBE에 의한 집계산출물이

 -> 인자로 전달받은 컬럼집합 -> 내가 조회한녀석 -> 0을 반환

    ROLLUP이나 CUBE에 의해서 조회된 녀석 -> 1을 반환

JOIN

 -> 여러 테이블의 레코드를 조합해서 하나의 열로 표현한 것

 -> 두개 이상의 테이블에서 연관성을 가지고 있는 데이터들을 따로 분류하여 새로운 가상의

테이블을 이용하여 출력하는 것

 -> 서로 다른 테이블에서 각각의 공통의 값을 이용함으로써 필드를 조합함

DBMS -> 오라클 쓰고 있다.

오라클용 구문

ANSI 표준구문(DBMS종류와 상관없이 공통적으로 사용가능)

JOIN은 2가지경우로 나눌 수 있다.

1. 컬럼명이 다른 경우

 - 오라클 전용 구문

select EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE from employee, department where dept_code=dept_id;

 -> 오라클에서는 WHERE절을 이용하여 '='를 사용하면 됨

 - ANSI 표준구문

select EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE FROM EMPLOYEE JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

2. 컬럼명이 같은 경우 

====================================================================

JOIN의 종류

 - INNET JOIN(내부조인) : 교집합(일반적인 JOIN)

 - OUTER JOIN(외부조인) : 합집합

  -> LEFT JOIN(왼쪽 외부조인)

  -> RIGHT OUTER JOIN(오른쪽 외부조인)

  -> FULL OUTER JOIN(완전 외부조인)

INNER JOIN

 -> 테이블 A와 테이블 B모두 조건구문에 일치하는 데이터만 반환합니다.

ex) select EMP_NAME, DEPT_TITLE FROM EMPLOYEE INNER JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

LEFT OUTER JOIN

 -> 테이블 A는 모두반환, 테이블 B는 조건구문에 일치하는 데이터만 반환

 -> A->EMPLOYEE, B -> DEPARTMENT

ex) select EMP_NAME, DEPT_TITLE FROM EMPLOYEE LEFT JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

RIGHT OUTER JOIN

 -> 테이블 B는 모두반환, 테이블 A는 조건구문에 일치하는 데이터만 반환

FULL OUTER JOIN

 -> 테이블 A, 테이블 B 모두 반환

CROSS JOIN(상호조인)

 -> 카테이션곱(CARTENSIAN PRODUCT)라고도 함

 -> 조인되는 테이블의 각 행들이 모두 맵핑된 데이터가 검색되는 조인방법

 -> A와 B를 조인할때, JOIN조건을 생략, A테이블의 모든행이 B테이블의 모든행과 조인되는경우

 -> 한쪽테이블의 모든 행과 다른쪽 테이블의 모든행을 조인

 -> 결과는 두 테이블 컬럼수를 곱한 갯수

NOT_EQUI JOIN(비등가조인)

 -> 지정한 컬럼 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식

 -> '='가 아닌 [BETWEEN, >, >=, <,<=,<>] 등을 사용한다.

=====================================================================

INSERT INTO KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,

MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) VALUES ('999','고길똥','990101-1111111','GOGO@KH.OR.KR',

'01011111111','D8','J5','S1',800000,0.3,NULL,TO_DATE('90/02/06','RR/MM/DD'),NULL,'N');

====================================================================

이렇게 추가한다음에

select * from employee where emp_id=999;

이렇게 써서 값이 뜨면 잘 추가된 것이다.

---------------------------------------------------------------------

DELETE FROM EMPLOYEE where EMP_ID =999;

->다시지우기 -> 테스트용으로 만들었기 때문이다.

---------------------------------------------------------------------

SELF JOIN

 -> 같은 테이블을 조인하는 경우

다중조인

 -> 여러개의 조인문을 한번에 사용

 -> 주의사항 -> 조인순서를 신경써야한다.

1. EMPLOYEE 테이블이랑 DEPARTMENT 테이블을 조인해서 본다.

2. DEPARTMENT테이블이랑 LOCATION테이블을 조인 LOCAL_NAME을알고싶다.

===============================================================================

===============================================================================

---

--20180820

select DECODE(GROUPING(DEPT_CODE),0,NVL(DEPT_CODE,'인턴'),'합계') as 부서코드,

DECODE(GROUPING(JOB_CODE),0,NVL(JOB_CODE,'직급없음'),'합계') as 직급코드, count(*),

CASE WHEN GROUPING(DEPT_CODE)=0 AND GROUPING(JOB_CODE)=1 THEN '부서별 합계' 

WHEN GROUPING(DEPT_CODE)=1 AND GROUPING(JOB_CODE)=0 THEN '직급별 합계'

WHEN GROUPING(DEPT_CODE)=1 AND GROUPING(JOB_CODE)=1 THEN '직급별 합계'

ELSE '그룹별 합계' END as 구분

FROM EMPLOYEE GROUP BY CUBE(DEPT_CODE,JOB_CODE) ORDER BY 1,2;

/*

혹시 null값이 있으면 전부 인턴이야 그리고 GROUPING해서 나온 값이 아니면 ROLLUP이나 CUBE에 나온값이므로

합계라고 출력해 라는 구문이다.

DECODE가 조건식, 0이 나온경우 인턴이라는 값을 줘라 그것도 아니면 합계 라고 주기

밑에도 마찬가지로 JOB_CODE가 0이고 없으면 직급없음, 합계

그룹핑을 하고 and로 묶어줬는데 GROUPING가 0이라는 얘기는 잡코드가 1인경우 이거는 내가 조회한 녀석인데

JOB_CODE는 내가 조회한게 아니면 부서별 합계이다. 대표적인게 3번이다.

부서코드가 1이고 직급코드가 0인경우 내가 조회한 결과를 말한다.

*/

/*

JOIN

 -> 여러 테이블의 레코드를 조합해서 하나의 열로 표현한 것

 -> 두개 이상의 테이블에서 연관성을 가지고 있는 데이터들을 따로 분류하여 새로운 가상의

테이블을 이용하여 출력하는 것

 -> 서로 다른 테이블에서 각각의 공통의 값을 이용함으로써 필드를 조합함

*/

select EMP_NAME, DEPT_CODE FROM EMPLOYEE;

select * from department;

/*

DBMS -> 오라클 쓰고 있다.

오라클용 구문

ANSI 표준구문(DBMS종류와 상관없이 공통적으로 사용가능)

JOIN은 2가지경우로 나눌 수 있다.

1. 컬럼명이 다른 경우

 - 오라클 전용 구문

2. 컬럼명이 같은 경우 

*/

select EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE from employee, department where dept_code=dept_id;

--EMPLOYEE에 앞에 3개가 있고 뒤에껀 DEPARTMENT꺼다.

select EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE FROM EMPLOYEE JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

--아까는 where절을 통해서 줬지만 지금은 JOIN ON이라는 구문을 썼다. -> 이건 ANSI 표준구문이다.

select * from job;

select EMP_ID, EMP_NAME, EMPLOYEE.JOB_CODE, JOB_NAME FROM EMPLOYEE, JOB where EMPLOYEE.JOB_CODE=JOB.JOB_CODE;

--이것이 오라클 전용 구문이다. -> 변수로 지정할수도 있지만 생략가능하다는것이다.

select EMP_ID, EMP_NAME, E.JOB_CODE, JOB_NAME FROM EMPLOYEE E, JOB J where E.JOB_CODE=J.JOB_CODE;

select EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME FROM EMPLOYEE JOIN JOB USING(JOB_CODE);

--위에 오라클 구문과의 차이이다. 이건 표준구문이다.

/*

INNER JOIN

*/

select EMP_NAME,DEPT_CODE FROM EMPLOYEE;--null값이 있다.

select EMP_NAME, DEPT_TITLE FROM EMPLOYEE INNER JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

--> INNER JOIN과 JOIN은 같은것이다.null이 출력안된다

select e.emp_name, d.dept_title from employee e, department d where e.DEPT_CODE=d.dept_id;

--> 오라클용이다.--> INNER JOIN과 JOIN은 같은것이다.null이 출력안된다

/*

LEFT JOIN

 -> 테이블 A는 모두반환, 테이블 B는 조건구문에 일치하는 데이터만 반환

*/

select EMP_NAME, DEPT_TITLE FROM EMPLOYEE LEFT JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

-->23개행이나오고 null도 나온다.

/*

RIGHT JOIN

 -> 테이블 B는 모두반환, 테이블 A는 조건구문에 일치하는 데이터만 반환

*/

select EMP_NAME, DEPT_TITLE FROM EMPLOYEE RIGHT JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

--기준을 다르게 하는 것이다.DEPARTMENT를 기준으로 한다.

/*

FULL OUTER JOIN

 -> 테이블 A, 테이블 B 모두 반환

*/

select EMP_NAME, DEPT_TITLE FROM EMPLOYEE full JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

--기준을 두개다 설정해서 나오는 것이다.

/*

CROSS JOIN(상호조인)

 -> 카테이션곱(CARTENSIAN PRODUCT)라고도 함

 -> 조인되는 테이블의 각 행들이 모두 맵핑된 데이터가 검색되는 조인방법

 -> A와 B를 조인할때, JOIN조건을 생략, A테이블의 모든행이 B테이블의 모든행과 조인되는경우

 -> 한쪽테이블의 모든 행과 다른쪽 테이블의 모든행을 조인

 -> 결과는 두 테이블 컬럼수를 곱한 갯수

*/

select EMP_NAME, DEPT_TITLE FROM EMPLOYEE CROSS JOIN DEPARTMENT;

--일일이 다 매핑을 해주고 그걸 출력한다.

/*

NOT_EQUI JOIN(비등가조인)

 -> 지정한 컬럼 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식

 -> '='가 아닌 [BETWEEN, >, >=, <,<=,<>] 등을 사용한다.

*/

select EMP_ID, EMP_NAME, EMPLOYEE.SALARY, EMPLOYEE.SAL_LEVEL, SAL_GRADE.SAL_LEVEL

FROM EMPLOYEE JOIN SAL_GRADE ON(EMPLOYEE.SALARY BETWEEN 3000000 AND 4000000)

WHERE EMPLOYEE.SAL_LEVEL = SAL_GRADE.SAL_LEVEL ORDER by 3 desc;

--직원이 1명 더 있다고 가정 -> 급여 35000000 -> sal_GRADE :s1

INSERT INTO KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,

MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) VALUES ('999','고길똥','990101-1111111','GOGO@KH.OR.KR',

'01011111111','D8','J5','S1',800000,0.3,NULL,TO_DATE('90/02/06','RR/MM/DD'),NULL,'N');

select * from employee where emp_id=999;

select * from sal_grade;

--s1이라는 녀석은 최소가 6백만이고 최대가 천만원이다.

select EMP_ID, EMP_NAME,EMPLOYEE.salary, employee.sal_level, sal_grade.sal_level from employee

JOIN SAL_GRADE ON(EMPLOYEE.salary between sal_grade.MIN_SAL AND SAL_GRADE.MAX_SAL)

where employee.sal_level = sal_grade.sal_level order by 3 desc;

--최소에서 최대까지, 6백만과 천만원 사이였는데 고길동은 80만이여서 이사이에 못들어와 출력이 안된다.

DELETE FROM EMPLOYEE where EMP_ID =999;

select * FROM EMPLOYEE where EMP_ID =999;

/*

SELF JOIN

 -> 같은 테이블을 조인하는 경우

*/

select EMP_ID, EMP_NAME, manager_id from employee; --관리자들이 나온다.

--선동일은 매니저 ID가없으니 대표이다, 송종기는 관리자가 200이니 선동일이 관리한다.

select e1.emp_id as 사원ID, e1.emp_name 사원이름, e2.emp_name as 관리자이름 

from employee e1, employee e2 where e1.manager_id = e2.EMP_id;

--사원에 대한건 e1에서 갖고오고 관리자는 e2에서 갖고온다.

--사원에있는 매니저 ID랑 관리자에있는 EMP_ID가같으면된다.

-- 자신이 관리하고 있는 사원의 이름, 급여, 직급을 조회

-- E1 -> 매니저 테이블, E2 -> 관리직원정보 테이블

select * from employee;

select e1.emp_name 관리자이름, e2.emp_name 사원이름,e2.salary 급여, e2.job_code 직급코드 from employee e1, employee e2 

where e1.emp_id = e2.manager_id order by 1;

-------------------------------------------------------------------------------

/*

다중조인

 -> 여러개의 조인문을 한번에 사용

 -> 주의사항 -> 조인순서를 신경써야한다.

*/

select * from employee;s

select * from department;

--1. EMPLOYEE 테이블이랑 DEPARTMENT 테이블을 조인해서 본다.

select EMPLOYEE.EMP_ID, EMPLOYEE.EMP_NAME, EMPLOYEE.DEPT_CODE, DEPARTMENT.DEPT_TITLE

FROM EMPLOYEE JOIN DEPARTMENT ON(EMPLOYEE.DEPT_CODE = DEPARTMENT.DEPT_ID);

--2. DEPARTMENT테이블이랑 LOCATION테이블을 조인 LOCAL_NAME을알고싶다.

select LOCAL_NAME FROM DEPARTMENT JOIN LOCATION ON  (LOCATION.local_code = department.location_id);

select EMPLOYEE.EMP_ID, EMPLOYEE.EMP_NAME, EMPLOYEE.DEPT_CODE, DEPARTMENT.DEPT_TITLE, LOCATION.LOCAL_NAME 

FROM EMPLOYEE 

JOIN DEPARTMENT ON(EMPLOYEE.DEPT_CODE = DEPARTMENT.DEPT_ID)

JOIN LOCATION ON(DEPARTMENT.LOCATION_ID=LOCATION.LOCAL_CODE);

select e.emp_id, e.emp_name, e.dept_code, d.dept_title, l.local_name from employee e, department d,

location l where (e.dept_code=d.dept_id) and (d.location_id=l.local_code);

/*

-- 각 사원들의 이름, 나이, 부서명, 직책명을 출력하여라

--(부서코드, 직책코드가 아닌 부서명과 직책명을 출력하여야 함)

EMPLOYEE, DEPARTMENT, JOB

*/

select * from employee;

select * from department;

select * from job;

select e.emp_name 사원명,2018-((1900+(substr(e.emp_no,1,2)))) ,d.dept_title 부서명, j.job_name 직책명 from employee e, department d, job j

where e.dept_code=d.dept_id and e.job_code=j.job_code order by 2 desc;

--선생님이 쓰신 것

select emp_name 사원명, EXTRACT(YEAR FROM SYSDATE) - (SUBSTR(EMP_NO,1,2)+1900) 나이,

NVL(dept_title,'부서없음') 부서명, job_name 직책명

from employee

LEFT JOIN DEPARTMENT ON(dept_code=dept_id)

JOIN JOB USING(JOB_CODE);