6day

-- 6일차
-- JOIN & Sub-Query
-- 관계형 DB?

/*
회사사원
사원번호 사원명 직책 부서명 부서전화번호 고객이름 고객전화번호
1         완샘  강사 교육팀 011-111-1111  최정빈   1234-1234
1         완샘  강사 교육팀 011-111-1111  김도영   2234-2234
2         성일  인턴 교육팀 011-111-1111  이성치   3333-3333
2         성일  인턴 교육팀 011-111-1111  최정빈   1234-1234

회사사원
사원번호 사원명 직책 부서번호(FK : Foreign Key)
1        완샘   강사    10
2        성일   인턴    10

부서
부서번호(PK)  부서명  부서전화번호
10 (Primary Key)교육팀  011-111-1111
20
*/
-- 오라클 사전(dictionary, dict) : 테이블명(table_name), 설명(comments)
-- ALL_XXX : DB 전체에서 쓰이는 정보들
-- USER_XXX : 현재 스키마(유저)에 속해있는 객체들에 대한 정보
-- DBA_XXX : DB관리자가 조회할 수 있는 정보들
-- V$XXX : 성능 관리 위해 존재하는 View
SELECT *
FROM DICTIONARY
WHERE LOWER(COMMENTS) LIKE '%constraint%';
-- constraints : 제약조건
SELECT * FROM USER_CONSTRAINTS;
SELECT * FROM USER_CONS_COLUMNS;
-- JOIN : inner join, outer join
-- cartesian product(카테시안 프로덕트) : 안쓴다.
SELECT *
FROM EMP, DEPT; --14 * 4 = 56
-- inner join (내부조인)
-- SQL 1992 code (1992년에 표준으로 제정된 SQL 문법)
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- ANSI JOIN (미국에서 새로 제정된 SQL 문법)
SELECT *
FROM EMP E
  INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO; -- INNER 생략 가능
-- USING() : 컬럼명이 동일할 경우만 사용
SELECT *
FROM EMP E
  JOIN DEPT D USING(DEPTNO);
 
-- OUTER JOIN(외부 조인)
-- SQL 1992 CODE
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;
INSERT INTO EMP(EMPNO, ENAME, HIREDATE, SAL)
VALUES(9999, '최반', SYSDATE, 500);
COMMIT;
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);
SELECT * FROM EMP;
-- ANSI JOIN OUTER JOIN : OUTER 는 생략 가능
SELECT *
FROM EMP E
  RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
-- FULL OUTER JOIN : ANSI JOIN 방식에서만 가능
SELECT *
FROM EMP E
  FULL JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
-- SELF JOIN
SELECT 사원.ENAME, 관리자.ENAME
FROM EMP 사원
  LEFT JOIN EMP 관리자 ON 사원.MGR = 관리자.EMPNO;
-- 1. 사원번호, 사원명, 부서번호, 부서명 출력하는데
--    부서에 속해있지 않으면 부서명에 '신입사원'이라고 출력하세요 (익봉)
SELECT E.EMPNO,E.ENAME,E.DEPTNO,NVL(D.DNAME,'신입사원')
FROM EMP E
LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

SELECT * FROM DEPT;

-- 2. 'BLAKE'가 관리하는 사원들의 사원번호, 사원명, 부서명을 출력하세요.(최반)
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
JOIN EMP M ON E.MGR = M.EMPNO
WHERE M.ENAME = 'BLAKE';

-- 3. 사원번호, 사원명, 부서명, 관리자번호, 관리자명, 관리자부서명 출력
--    (4개의 테이블에 대한 조인이 걸려야 함) (도영)

SELECT E.EMPNO 사원번호, E.ENAME 사원명,  NVL(D.DNAME, '신입사원') 부서명, M.EMPNO 관리자번호, M.ENAME 관리자명, MD.DNAME
FROM EMP E
LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO
LEFT JOIN EMP M ON E.MGR = M.EMPNO
LEFT JOIN DEPT MD ON M.DEPTNO = MD.DEPTNO
WHERE E.ENAME != 'KING';
-- hr 계정 접속
SELECT * FROM TAB;
-- natural join (컬럼명이 똑같은게 하나 있을때 가능)
SELECT FIRST_NAME, JOB_TITLE
from employees natural join jobs;

SELECT first_name, job_title
FROM EMPLOYEES
  join jobs on employees.job_id = jobs.job_id;
SELECT * FROM JOBS;
SELECT * FROM DEPARTMENTS;
select * from locations;
SELECT * FROM EMPLOYEES;
SELECT * FROM COUNTRIES;
select * from regions;
-- 1. job_id로 jobs 테이블과 연결
-- 2. department_id로 departments 테이블과 연결
-- 3. departments.manager_id(부서장) = employees.employee_id 연결
-- 4. locations.location_id = departments.location_id 연결
-- 5. locations.country_id = countries.country_id 연결
-- 6. countries.region_id = regions.region_id 연결
-- 보너스 숙제
-- 사원명(이름+성), 직업명(job_title), 부서명(department_name),
-- 부서장이름(이름+성), 부서도시명(city), 부서국가명(country_name),
-- 부서지역명(region_name)을 출력하세요. 총 7개의 테이블 조인