'Oracle'에 해당되는 글 16건

--문제1) 도시명 저장용 전용 테이블 준비
CREATE TABLE city (
 cid NUMBER   --일련번호 (1, 2, ... )
 ,city  VARCHAR2(10)  --중복되지 않은 도시명
);

--문제2) 일련번호 확보를 위한 시퀀스 객체 준비 (citySeq)

CREATE SEQUENCE citySeq START WITH 1;

--문제3) insa 테이블의 city 컬럼 정보를 중복되지 않게 읽어와서 city 테이블의 city 컬럼에 입력한다. 단, cid 컬럼에는 일련번호가 자동 입력되도록 한다.

INSERT INTO city (cid, city)
  SELECT citySeq.nextval, city
  FROM(SELECT DISTINCT city
  FROM insa);
 
--문제4) 부서명 저장용 테이블 준비

CREATE TABLE buseo (
 bid NUMBER
 ,buseo  VARCHAR2(15) NOT NULL
);

--문제5) 부서 테이블 전용 시퀀스 객체 준비 (buseoSeq)

CREATE SEQUENCE buseoSeq START WITH 1;

--문제6) insa 테이블에서 buseo 컬럼 데이터를 중복되지 않게 읽어와서 buseo 테이블에 입력한다. 단, bid는 일련번호가 자동 입력되도록 한다.

INSERT INTO buseo (bid, buseo)
  SELECT buseoSeq.nextval, buseo
  FROM(SELECT DISTINCT buseo
  FROM insa);

--문제7) 직위명 저장용 테이블 준비

CREATE TABLE jikwi (
 jid NUMBER
 ,jikwi  VARCHAR2(15) NOT NULL
);

--문제8) 직위 테이블 전용 시퀀스 객체 준비 (jikwiSeq)

CREATE SEQUENCE jikwiSeq START WITH 1;

--문제9) insa 테이블에서 jikwi 컬럼 데이터를 중복되지 않게 읽어와서 jikwi 테이블에 입력한다. 단, jid는 일련번호가 자동 입력되도록 한다.

INSERT INTO jikwi (jid, jikwi)
  SELECT jikwiSeq.nextval, jikwi
  FROM(SELECT DISTINCT jikwi
  FROM insa);
 
 
--문제10) insa 테이블에 cid, bid, jid 컬럼 추가

ALTER TABLE insa
  ADD (
    cid NUMBER,
    bid NUMBER,
    jid NUMBER
  ); 
 
--문제11) insa 테이블에서 기존 직원의 city, buseo, jikwi 정보를 가지고
--신규 컬럼인 cid, bid, jid 컬럼의 값을 일괄 수정한다.
--힌트) insa 테이블의 city 컬럼 값과 city 테이블의 city 컬럼 값이 같은 경우를
--찾아서 cid 컬럼 값을 얻고, 그 결과를 insa 테이블의 cid 컬럼에 업데이트 적용.
--cid 수정 쿼리, bid 수정 쿼리, jid 수정 쿼리 별도 실행.

UPDATE insa i
  SET cid = (SELECT cid FROM city WHERE city = i.city);

UPDATE insa i
  SET bid = (SELECT bid FROM buseo WHERE buseo = i.buseo);

UPDATE insa i
  SET jid = (SELECT jid FROM jikwi WHERE jikwi = i.jikwi); 
 
--문제12) insa 테이블에서 city, buseo, jikwi 컬럼 삭제.

ALTER TABLE insa
  DROP (city, buseo, jikwi);

--문제13) insa, city, buseo, jikwi 테이블에 제약 조건 추가 (PK, FK)
--city의 cid, buseo의 bid, jikwi의 jid -> PK
--insa의 cid, bid, jid -> FK

ALTER TABLE city
 ADD CONSTRAINT CITY_CID_PK PRIMARY KEY(cid);

ALTER TABLE buseo
 ADD CONSTRAINT BUSEO_CID_PK PRIMARY KEY(bid);

ALTER TABLE jikwi
 ADD CONSTRAINT JIKWI_CID_PK PRIMARY KEY(jid);

ALTER TABLE insa
 ADD (CONSTRAINT CITY_CID_FK FOREIGN KEY(cid)
   REFERENCES city(cid),
      CONSTRAINT BUSEO_BID_FK FOREIGN KEY(bid)
   REFERENCES buseo(bid),
      CONSTRAINT JIKWI_JID_FK FOREIGN KEY(jid)
   REFERENCES jikwi(jid));

--문제14) jikwi 테이블에 min_basicPay 컬럼(최소 기본급) 추가 및 min_basicPay 정보 입력.
--min_basicPay 컬럼의 자료형은 NUMBER로 지정. 직위별 최소 기본급 액수는 임의로 지정.
--예를 들어, 사원의 최소 기본급은 1000000원.

ALTER TABLE jikwi
  ADD (
    min_basicPay NUMBER
  );
 
UPDATE jikwi
  set min_basicPay = (CASE
    WHEN jikwi='과장' THEN 3000000
    WHEN jikwi='대리' THEN 2000000
    WHEN jikwi='부장' THEN 3500000
    WHEN jikwi='사원' THEN 1000000
  END); 

--문제15) insa, city, buseo, jikwi 테이블을 가지고 JOIN 쿼리 작성.
--출력 컬럼은 num, name, ssn, ibsaDate, tel, cid, city, bid, buseo,
--jid, jikwi, basicPay, sudang, pay로 한다. pay 는 basicPay + sudang 연산 결과이다.

--SELECT num, name, ssn, ibsaDate, tel, c.cid, city, b.bid, buseo,
--      j.jid, jikwi, basicPay, sudang, (basicPay+sudang) AS pay
--    FROM insa i, city c, buseo b, jikwi j
--    WHERE i.cid = c.cid
--      AND i.bid = b.bid
--      AND i.jid = j.jid;

--문제16) insa, city, buseo, jikwi 테이블을 가지고 JOIN 쿼리 작성 -> 뷰 작성 (insaView)

CREATE OR REPLACE VIEW insaView
AS
SELECT num, name, ssn, ibsaDate, tel, c.cid, city, b.bid, buseo,
      j.jid, jikwi, basicPay, sudang, (basicPay+sudang) AS pay
    FROM insa i, city c, buseo b, jikwi j
    WHERE i.cid = c.cid
      AND i.bid = b.bid
      AND i.jid = j.jid;
          

COMMIT;

-- 1. 기본급 상위 10%만 출력. (이름, 기본급)
SELECT *
  FROM (SELECT name, basicpay,
    RANK() OVER(ORDER BY basicpay DESC) AS rank
    FROM insa)
    WHERE rank<= (SELECT COUNT(*) * 0.1 AS 총원
    FROM insa);
 
--2. 기본급(basicpay) 높은 순위로 5순위까지만 출력
SELECT *
 FROM (SELECT name, basicpay
  ,RANK() OVER(ORDER BY basicpay DESC) AS rank
  FROM insa)
 WHERE rank<=5;

--3. 입사일이 빠른 순서로 5순위까지만 출력.
SELECT *
 FROM (SELECT name, ibsadate
  ,RANK() OVER(ORDER BY ibsadate ASC) AS rank
  FROM insa)
 WHERE rank<=5;
--4. 부서별 기본급이 가장 높은 사람 출력. (이름, 부서, 기본급)
SELECT buseo, name, basicpay
  FROM (SELECT name, buseo, basicpay
    ,RANK() OVER(PARTITION BY BUSEO ORDER BY basicpay DESC) AS rank
    FROM insa)
  WHERE rank=1;
 
--5. 남, 여별 기본급 순위 출력. (이름, 성별, 순위)
SELECT *
  FROM (SELECT name, DECODE(SUBSTR(ssn, 8, 1),1,'남',2,'여',3,'남',4,'여') AS 성별 , buseo, basicpay
    ,RANK() OVER(PARTITION BY SUBSTR(ssn, 8, 1) ORDER BY basicpay DESC) AS rank
    FROM insa);
--6. 지역(city)별로 급여(기본급+수당) 1순위 직원만 출력. GROUP BY 구문 이용. PARTITION BY 이용.
SELECT name, city, 급여
 FROM (SELECT name, city, basicpay+sudang AS "급여"
  ,RANK() OVER(PARTITION BY city ORDER BY basicpay+sudang DESC) AS rank
  FROM insa)
 WHERE rank=1;
--7. 부서별 인원수가 가장 많은 부서 및 인원수 출력.
SELECT buseo, 인원수
  FROM
  (SELECT buseo, COUNT(*) AS 인원수, RANK() OVER(ORDER BY COUNT(*) DESC) AS rank
    FROM insa
    GROUP BY buseo)
  WHERE rank=1;
--8. 지역(city)별 인원수가 가장 많은 지역 및 인원수 출력.
SELECT city, 인원수
  FROM(
  SELECT city, COUNT(*) AS 인원수, RANK() OVER(ORDER BY COUNT(*) DESC) AS rank
  FROM insa
  GROUP BY city)
  WHERE rank = 1;
--9. 지역(city)별 평균 급여(basicpay+sudang)가
--   가장 높은 지역 및 평균급여 출력.
SELECT city, 급여
  FROM (SELECT city, ROUND(AVG(basicpay+sudang)) AS "급여"
  ,RANK() OVER(ORDER BY ROUND(AVG(basicpay+sudang)) DESC) AS rank
  FROM insa
  GROUP BY city)
  WHERE rank = 1;
--10. 여자 인원수가 가장 많은 부서 및 인원수 출력.
SELECT buseo, 여자인원수
  FROM(SELECT buseo, COUNT(*) AS 여자인원수, RANK() OVER(ORDER BY COUNT(*) DESC) AS rank
  FROM insa
  WHERE SUBSTR(ssn, 8, 1) IN (2, 4)
  GROUP BY buseo)
  WHERE rank = 1;
 
--11. 지역별 인원수 순위 출력.
SELECT city, COUNT(*) AS 인원수, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
  FROM insa
  GROUP BY city;
--12. 지역별 인원수 순위 출력하되 5순위까지만 출력.
SELECT *
  FROM(SELECT city, COUNT(*) AS 인원수, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
  FROM insa
  GROUP BY city)
  WHERE rank <= 5;
--13. hr 계정. employees, jobs 테이블.
--job_title이 'Finance Manager'인 직무의 최고 급여액(max_salary)보다
--많은 급여(salary)를 받는 직원 출력. 하위쿼리.
SELECT first_name, last_name, salary
  FROM(SELECT first_name, last_name, salary
  , (SELECT max_salary AS maxs FROM jobs WHERE job_title = 'Finance Manager') AS maxs
  FROM employees)
  WHERE salary > maxs;
 
SELECT first_name, last_name, salary
  FROM (SELECT first_name, last_name, salary, max_salary
  FROM employees, jobs
  WHERE job_title = 'Finance Manager')
  WHERE salary > max_salary;

--1. 평균 급여보다 많은 급여(basicpay+sudang)를 받는 직원 출력 
SELECT * FROM insa
 WHERE basicpay+sudang > (SELECT AVG(basicpay+sudang) FROM insa);
-->27명

--2. '이순애' 직원의 급여보다 더 많은 급여를 받는 직원 출력.
-->2명(포함해서 3명)
SELECT * FROM insa
 WHERE basicpay+sudang > (SELECT (basicpay+sudang) FROM insa
                            WHERE name = '이순애');

--3. 총무부의 평균 급여보다 많은 급여를 받는 직원들의 이름, 부서명 출력.
-->26명
SELECT name, buseo FROM insa
 WHERE basicpay+sudang > (SELECT AVG(basicpay+sudang) FROM insa
                            WHERE buseo = '총무부');

--4. 개발부 직원들보다 더 많은 급여를 받는 직원 정보.
-->
SELECT * FROM insa
 WHERE basicpay+sudang > (SELECT MAX(basicpay+sudang) FROM insa
                            WHERE buseo = '개발부');

--5. 직원 전체 평균 급여보다 많은 급여를 받는 직원의 수 출력.
--> 27명
SELECT COUNT(*) FROM insa
 WHERE basicpay+sudang > (SELECT AVG(basicpay+sudang) FROM insa
                            );

--6. '홍길동' 직원과 같은 부서의 직원 정보.
-->7명
SELECT * FROM insa
 WHERE buseo = (SELECT buseo FROM insa
                  WHERE name = '홍길동');

--7. '김신애' 직원과 같은 부서, 직위를 가진 직원 정보
--> 9명
SELECT * FROM insa
 WHERE (buseo,jikwi) IN (SELECT buseo,jikwi FROM insa
                  WHERE name = '김신애' );

--8. 이름, 부서, 출신도, 기본급, 수당, 기본급+수당(총급여), 세금, 실수령액 출력
--       세금: 총급여가 250만원 이상이면 2%, 200만원 이상이면 1%, 나머지 0.
--       실수령액: 총급여-세금
--       CASE~END 문, 하위 쿼리 사용.
SELECT name, buseo, city, TO_CHAR(basicpay,'L9,999,999') AS basicpay, TO_CHAR(sudang,'L9,999,999') AS sudang, TO_CHAR(pay,'L9,999,999') AS "별칭",TO_CHAR(tax,'L9,999,999') AS tax,TO_CHAR((pay-tax),'L9,999,999') AS 실수령액
  FROM (SELECT name, buseo,city,basicpay,sudang,(basicpay+sudang) AS pay,
        CASE
        WHEN (basicpay+sudang) >=250000 THEN (basicpay+sudang) *0.02
        WHEN (basicpay+sudang) >=200000 THEN (basicpay+sudang) *0.01
        ELSE (basicpay+sudang)*0
        END AS tax
        FROM insa);

--9. 기본급+수당이 가장 많은 사람의 이름, 기본급+수당 출력.
--MAX() 함수, 하위 쿼리 이용.
--> 1명
SELECT name,(basicpay+sudang)
  FROM insa
  WHERE (basicpay+sudang) >= (SELECT MAX(basicpay+sudang) FROM insa);

--10. 기본급(basicpay)이 평균(직원 전체의 기본급 평균) 이하인 사원 출력. (이름, 기본급). AVG() 함수. 하위쿼리.
--> 33명
SELECT name,basicpay
  FROM insa
  WHERE basicpay <= (SELECT AVG(basicpay) FROM insa);

SELECT * FROM insa; --인사테이블 전체출력

DESC insa;

SELECT * FROM tab; --테이블 목록확인
--1. INSA 테이블 전체자료 출력
SELECT * FROM insa;

--2. SCOTT 사용자 소유 테이블 목록 확인
SELECT * FROM tab;

--3. INSA 테이블 구조 확인
DESC insa;

--4. insa 테이블의 이름(name), 기본급(basicpay) 출력
SELECT name, basicpay FROM insa;

--5. insa 테이블의 이름(name), 기본급(basicpay), 수당(sudang), 기본급+수당 출력
SELECT name, basicpay, sudang, basicpay+sudang AS 급여
  FROM insa;
 
--6. 이름(name), 출신도(city), 부서명(buseo) 출력. 별명 사용.
SELECT name AS 이름, city AS 도시, buseo AS 부서
  FROM insa;
 
--7. 서울 사람의 이름(name), 출신도(city), 부서명(buseo), 직위(jikwi) 출력. WHERE 구문 추가.
SELECT name, city, buseo, jikwi
  FROM insa
  WHERE city = '서울';

--8.출신도가 서울 사람이면서 -->WHERE 구문
-- 기본급이 150만원 이상인 사람 -->WHERE 구문
-- 출력 (name, city, basicpay, ssn)
SELECT name, city, basicpay, ssn
  FROM insa
  WHERE city = '서울' AND basicpay >= 1500000;

--9.출신도가 '인천' 이면서, 기본급이 100만원~200만원인 경우만 출력.
SELECT * FROM insa
   WHERE city='인천' AND
      basicpay BETWEEN 1000000 AND 2000000;
 
--10.출신도가 서울 사람이거나 부서가 개발부인 자료 출력 (name, city, buseo)
SELECT name, city, buseo
  FROM insa
  WHERE city = '서울' OR buseo = '개발부';
 
--11.출신도가 서울, 경기인 사람만 출력 (name, city, buseo). IN 연산자 사용.
SELECT name, city, buseo
  FROM insa
  WHERE city IN ('서울','경기');

--12.부서가 '개발부', '영업부'인 사람만 출력. IN 연산자 사용.
SELECT * FROM insa
   WHERE buseo IN ('개발부', '영업부');

--13. 급여(basicpay + sudang)가 250만원 이상인 사람. --> WHERE 구문
--단 필드명은 한글로 출력. -->별칭 (name, basicpay, sudang, basicpay+sudang)
SELECT name, basicpay, sudang, basicpay+sudang AS 급여
  FROM insa
  WHERE basicpay+sudang >= 2500000;

--14. 주민번호를 기준으로 남자(성별란이 1, 3)만 출력. (이름, 주민번호(ssn)).
--SUBSTR() 함수 이용.
SELECT name, ssn
  FROM insa
  WHERE SUBSTR(ssn, 8, 1) IN ('1', '3');
 
--15. 주민번호를 기준으로 80년대 태어난 사람만 출력. (이름, 주민번호(ssn)).
--SUBSTR() 함수 이용.

   WHERE SUBSTR(ssn, 1, 1) = '8';

--16. 서울 사람 중에서 70년대 태어난 사람만 출력. SUBSTR() 함수 이용.
SELECT * FROM insa
   WHERE SUBSTR(ssn, 1, 1) = '7' AND city = '서울';

--17. 서울 사람 중에서 70년대 태어난 남자만 출력. SUBSTR() 함수 이용.
SELECT * FROM insa
   WHERE city = '서울'
      AND SUBSTR(ssn, 1, 1) = '7'
      AND SUBSTR(ssn, 8, 1) IN ('1', '3');
 
--18. 서울 사람이면서 김씨만 출력(성씨가 1자라는 가정). (이름, 출신도).
--SUBSTR() 함수 이용.
SELECT name, city FROM insa
   WHERE city = '서울'
      AND SUBSTR(name, 1, 1) = '김';

--19. 2000년도에 입사한 사람 출력. (이름, 출신도, 입사일).
--SUBSTR() 또는 TO_CHAR() 함수 이용.
SELECT name, city, ibsadate
  FROM insa
  WHERE SUBSTR(ibsadate, 1, 2) = '00';
  --WHERE TO_CHAR (ibsadate, 'YY') = '00';

--20. 2000년 10월에 입사한 사람 출력. (이름, 출신도, 입사일).
SELECT name, city, ibsadate
  FROM insa
  WHERE TO_CHAR (ibsadate, 'YY-MM') = '00-10';

--21. 주민번호를 기준으로 직원의 나이 구하기(단, 모든 직원이 1900년대에 태어났다는 가정). (이름, 주민번호, 나이)

--시간 간격 계산
--일 기준 -> 날짜 - 날짜
--월 기준 -> MONTHS_BETWEEN()
--년 기준 -> 일기준 계산 결과 / 365 또는 월기준 계산 결과 / 12

--기준년도 - 생년월일중에서 년도 + 1

SELECT name, ssn,
  ROUND(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(ssn, 1, 6)))/12) AS 나이
  FROM insa
  ORDER BY 나이 ASC;

--22. 주민번호 기준으로 현재 나이대가 30대인 사람만 출력.
SELECT name, ssn,
  ROUND(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(ssn, 1, 6)))/12) AS 나이
  FROM insa
  WHERE ROUND(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(ssn, 1, 6)))/12) >= 30
  AND ROUND(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(ssn, 1, 6)))/12) <40
  ORDER BY 나이 ASC;

--23. 주민번호 기준으로 5월달생만 출력. SUBSTR() 함수 이용.
SELECT name, ssn
  FROM insa
  WHERE SUBSTR(ssn, 3, 2) = '05';

--24. 주민번호 기준으로 5월달생만 출력.
--주민번호의 생년월일 부분만을 추출->날짜형 자료로 변환->월 부분만 추출.
SELECT * FROM insa
  WHERE TO_CHAR(TO_DATE(SUBSTR(ssn, 1, 6)), 'MM') = 5;
 
--25. 출신도 내림차순으로 정렬하고, 출신도가 같으면 기본급 내림차순
SELECT *
  FROM insa
  ORDER BY city DESC, basicpay DESC;
 
--26. 서울 사람 중에서 기본급+수당(->급여) 내림차순으로 정렬. (이름, 출신도, 기본급+수당)
SELECT name, city, basicpay+sudang AS 급여
  FROM insa
  WHERE city = '서울'
  ORDER BY 급여 DESC;
 
--27. 여자 중 부서 오름차순으로 정렬하고, 부서가 같으면 기본급 내림차순 정렬. (이름, 주민번호, 부서, 기본급)
SELECT name, ssn, buseo, basicpay FROM insa
   WHERE SUBSTR(ssn, 8, 1) IN ('2', '4')
   ORDER BY buseo ASC, basicpay DESC;

--28. 남자 중에서 나이를 기준으로 오름차순 정렬해서 출력.
SELECT * FROM insa
   WHERE SUBSTR(ssn, 8, 1) IN ('1', '3')
   ORDER BY TO_DATE(SUBSTR(ssn, 1, 6)) ASC;

--29. 서울 지역 사람들 중에서 입사일이 빠른 사람을 먼저 출력.
SELECT * FROM insa
   WHERE city = '서울'
   ORDER BY ibsadate ASC;

--30. 성씨가 김씨가 아닌 사람 출력. (이름, 출신도, 기본급).
--LIKE 연산자 또는 SUBSTR() 함수 이용.

SELECT * FROM insa
   WHERE name NOT LIKE '김%';

--31. 출신도가 서울, 부산, 대구 이면서
--전화번호에 5 또는 7이 포함된 자료 출력하되
--부서명의 마지막 부는 출력되지 않도록함. (이름, 출신도, 부서명, 전화번호)

SELECT name, city, RTRIM(buseo, '부') AS buseo, tel FROM insa
   WHERE city IN ('서울', '부산', '대구')
      AND (tel LIKE '%5%' OR tel LIKE '%7%');

 
--32. 전화번호가 있으면 '-'을 제거하고 출력하고, 없으면 '전화번호없음'을 출력
SELECT name, NVL2(tel, REPLACE(tel, '-', ''), '전화번호없음') AS tel FROM insa;

--33. HR계정, employees 테이블에서 커미션(commission_pct) 받는 사람의 수와 안받는 사람(null)의 수를 출력
--COUNT(), NVL2(), GROUP BY 구문

--안 받는 직원->72
--받는 직원->35

SELECT NVL2(commission_pct, '받는 직원', '안받는 직원'), COUNT(*) AS "직원수"
  FROM employees
  GROUP BY NVL2(commission_pct, '받는 직원', '안받는 직원');

--34. insa 테이블에서 basicpay+sudang가 100만원 미만
--, 100만원 이상~200만원 미만, 200만원 이상인 직원들의 수 출력.
--GROUP BY 구문

SELECT DECODE(TRUNC((basicpay+sudang)/1000000), 0, 'C그룹', 1, 'B그룹', 2, 'A그룹'), COUNT(*) AS 그룹당직원수
   FROM insa
   GROUP BY DECODE(TRUNC((basicpay+sudang)/1000000), 0, 'C그룹', 1, 'B그룹', 2, 'A그룹');

--35. 주민번호를 가지고 생년월일의 년도별 직원수 출력.

SELECT TO_CHAR(TO_DATE(SUBSTR(ssn, 1, 6)), 'YYYY'), COUNT(*)
  FROM insa
  GROUP BY TO_CHAR(TO_DATE(SUBSTR(ssn, 1, 6)), 'YYYY')
  ORDER BY TO_CHAR(TO_DATE(SUBSTR(ssn, 1, 6)), 'YYYY') ASC;

--36. 주민번호를 기준으로 월별 오름차순, 월이 같으면 년도 내림차순 출력. (이름, 주민번호) . SUBSTR() 함수 이용.

SELECT name, ssn FROM insa
   ORDER BY SUBSTR(ssn, 3, 2) ASC
      , TO_CHAR(TO_DATE(SUBSTR(ssn, 1, 6)), 'YYYY') DESC;

--37. 입사일을 기준으로  월별 오름차순, 월이 같으면 년도 내림차순 출력.
--주의. 입사일은 자료형이 DATE입니다.
--주의. 2000년도 이후 입사자는 내림차순 지정시 뒤로 갈수 있으므로 주의할 것.

--SUBSTR(원본문자열, 시작위치, 글자수)

SELECT * FROM insa
   ORDER BY TO_CHAR(ibsadate, 'MM') ASC
      , TO_CHAR(ibsadate, 'YYYY') DESC;

--38. 전체인원수, 남자인원수, 여자인원수를 동시 출력.
SELECT COUNT(*) AS 전체직원수
        ,COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 남자직원수
        ,COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 여자직원수
  FROM insa;

--39. 개발부, 영업부, 총무부 인원수 출력. COUNT(), DECODE() 함수 이용.
--개발부->14
--영업부->16
--총무부->7

SELECT COUNT(DECODE(buseo, '개발부', 1)) AS 개발부,
  COUNT(DECODE(buseo, '영업부', 1)) AS 영업부,
  COUNT(DECODE(buseo, '총무부', 1)) AS 총무부
  FROM insa;
 

--40. 서울 사람의 남자 인원수 출력.
SELECT COUNT(*) AS 서울_남자직원수
   FROM insa
   WHERE city='서울' AND SUBSTR(ssn, 8, 1)='1';

--41. 부서가 영업부이고, 남자인 경우, 여자인 경우 인원수 출력.  COUNT(), DECODE() 함수 이용.
SELECT COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 영업부_남자직원수
   ,COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 영업부_여자직원수
   FROM insa
   WHERE buseo='영업부';  

--42. 개발부, 영업부, 총무부 인원수 출력. 단, 지역은 '서울'로 한정.

SELECT COUNT(DECODE(buseo, '개발부', 1)) AS 개발부,
  COUNT(DECODE(buseo, '영업부', 1)) AS 영업부,
  COUNT(DECODE(buseo, '총무부', 1)) AS 총무부
  FROM insa
  WHERE city = '서울';

--43. 서울 사람의 남자와 여자의 기본급합 출력.
--서울, 남자, 기본급합 출력
--> SELECT SUM(basicpay) AS 서울_남자_기본급합
   FROM insa
   WHERE city='서울' AND SUBSTR(ssn, 8, 1)='1';
--> SELECT SUM(DECODE(SUBSTR(ssn, 8, 1), '1', basicpay))
   AS 서울_남자_기본급합
   FROM insa
   WHERE city='서울';

SELECT SUM(DECODE(SUBSTR(ssn, 8, 1), '1', basicpay))
      AS 서울_남자_기본급합
   ,SUM(DECODE(SUBSTR(ssn, 8, 1), '2', basicpay))
      AS 서울_여자_기본급합
   FROM insa
   WHERE city='서울';

--44. 남자와 여자의 기본급(basicpay) 평균값 출력. AVG(), DECODE() 함수 이용.
--남자 평균->1676168
--여자 평균->1428634
SELECT ROUND(AVG(DECODE(SUBSTR(ssn, 8, 1), '1', basicpay))) AS 서울_남자_기본급_평균
   ,ROUND(AVG(DECODE(SUBSTR(ssn, 8, 1), '2', basicpay))) AS 서울_여자_기본급_평균
   FROM insa;

--45. 개발부 남자와 개발부 여자의 기본급 평균값 출력. AVG(), DECODE() 함수 이용.
--개발부 남자 평균->1392500
--개발부 여자 평균->1384375

SELECT ROUND(AVG(DECODE(SUBSTR(ssn, 8, 1), '1', basicpay))) AS 서울_남자_기본급_평균
   ,ROUND(AVG(DECODE(SUBSTR(ssn, 8, 1), '2', basicpay))) AS 서울_여자_기본급_평균
   FROM insa
  WHERE buseo = '개발부';

--46. 부서별 남자와 여자 인원수 구하기
SELECT buseo, COUNT((DECODE(SUBSTR(ssn, 8, 1), '1', 1))) AS 남자, COUNT((DECODE(SUBSTR(ssn, 8, 1), '2', 1))) AS 여자
  FROM insa
  GROUP BY buseo;

--47. 지역별 남자와 여자 인원수 구하기
SELECT city, COUNT((DECODE(SUBSTR(ssn, 8, 1), '1', 1))) AS 남자, COUNT((DECODE(SUBSTR(ssn, 8, 1), '2', 1))) AS 여자
  FROM insa
  GROUP BY city;

--48. 입사년도별 남자와 여자 인원수 구하기
SELECT TO_CHAR(ibsadate, 'yyyy') AS 입사년도, COUNT((DECODE(SUBSTR(ssn, 8, 1), '1', 1))) AS 남자, COUNT((DECODE(SUBSTR(ssn, 8, 1), '2', 1))) AS 여자
  FROM insa
  GROUP BY TO_CHAR(ibsadate, 'yyyy')
  ORDER BY 입사년도 ASC;

--49. 영업부, 총무부 인원만을 가지고 입사년도별 남자와 여자 인원수 구하기

SELECT TO_CHAR(ibsadate, 'yyyy') AS 입사년도, COUNT((DECODE(SUBSTR(ssn, 8, 1), '1', 1))) AS 남자, COUNT((DECODE(SUBSTR(ssn, 8, 1), '2', 1))) AS 여자
  FROM insa
  WHERE buseo IN ('영업부', '총무부')
  GROUP BY TO_CHAR(ibsadate, 'yyyy')
  ORDER BY 입사년도 ASC;

--50. 서울 사람중 부서별 남자와 여자인원수, 남자와 여자 급여합 출력.
SELECT buseo
   , COUNT(*) AS 부서별직원수
   , COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 남자직원수
   , COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 여자직원수
   , SUM(DECODE(SUBSTR(ssn, 8, 1), '1', basicpay))
      AS 남자기본급합
   , SUM(DECODE(SUBSTR(ssn, 8, 1), '2', basicpay))
      AS 여자기본급합
   FROM insa
   WHERE city='서울'
   GROUP BY buseo;

--51. 부서별 인원수 출력. 인원수가 10 이상인 경우만.
SELECT buseo
   , COUNT(*) AS 부서별직원수
   FROM insa
   GROUP BY buseo
   HAVING COUNT(*) >= 10;

--52. 부서별 남,여 인원수 출력. 여자인원수가 5명 이상인 부서만 출력.
SELECT buseo
   , COUNT(*) AS 부서별직원수
   , COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 남자직원수
   , COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 여자직원수
   FROM insa
   GROUP BY buseo
   HAVING COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) >= 5;

--53. 부서별 평균 급여를 출력하되, A, B, C 등급으로 나눠서 출력.
--200만원 초과 - A등급
--150~200만원 - B등급
--100만원 미만 - C등급

SELECT buseo
  , ROUND(AVG(basicpay+sudang))
  , CASE
      WHEN ROUND(AVG(basicpay+sudang))>2000000 THEN 'A등급'
      WHEN ROUND(AVG(basicpay+sudang)) BETWEEN 1500000 AND 2000000 THEN 'B등급'
      WHEN ROUND(AVG(basicpay+sudang))<1500000 THEN 'C등급'
    END AS 등급
  FROM insa
  GROUP  BY buseo;

 
--54. 이름, 성별, 나이 출력
--    성별: 주민번호 1,3->남자, 2,4->여자 (DECODE 사용)
--    나이: 주민번호 이용해서
      
SELECT  ROUND(MONTHS_BETWEEN(SYSDATE,TO_DATE(SUBSTR(ssn,1,6))) /12)
  FROM insa;
      
SELECT name 이름, DECODE(SUBSTR(ssn,8,1),'1','남자','2','여자','3','남자','4','여자') 성별,
        ROUND(MONTHS_BETWEEN(SYSDATE,TO_DATE(SUBSTR(ssn,1,6))) /12) 나이
  FROM insa;
 
SELECT MONTHS_BETWEEN(TO_DATE('2015'),TO_CHAR(TO_DATE(SUBSTR(ssn,1,6)),'YYYY-MM-DD')) 나이
  FROM insa;
--55. 서울 사람 중에서 기본급이 200만원 이상이 사람. (이름, 기본급)
SELECT name 이름, basicpay 기본급
  FROM insa
  WHERE city = '서울' AND basicpay >2000000;
 
--56. 입사월별 인원수 구하기. (월, 인원수)   COUNT, GROUP BY, TO_CHAR 사용
 --       출력형태 ----------
 --       월      인원수
 --       1월    10명
  --      2월    25명

SELECT TO_NUMBER(TO_CHAR(ibsadate, 'MM'))||'월' AS 입사월
      , COUNT(*) AS 그룹별직원수
  FROM insa
  GROUP BY TO_CHAR(ibsadate, 'MM')
  ORDER BY TO_CHAR(ibsadate, 'MM');

--57. 이름, 생년월일, 기본급, 수당을 출력.
       --생년월일은 주민번호 기준 (2000-10-10 형식으로 출력)
      -- 기본급은 \1,000,000 형식으로 출력
SELECT name AS "이름", TO_CHAR(TO_DATE(SUBSTR(ssn,1,6)),'YYYY-MM-DD') 생년월일, TO_CHAR(basicpay,'L999,999,999') 기본급, sudang 수당
  FROM insa;

SELECT * FROM insa;

--58. 이름, 출신도, 기본급을 출력하되 출신도 내림차순 출력(1차 정렬 기준).
--출신도가 같으면 기본급 오름차순 출력(2차 정렬 기준).
SELECT name 이름, city 출신, basicpay 기본급
  FROM insa
  ORDER BY city ASC, basicpay DESC;
 
 
--59. 전화번호가 NULL이 아닌것만 출력. (이름, 전화번호)
SELECT name 이름, tel 전화번호
  FROM insa
  WHERE tel IS NOT NULL;
 
 
--60. 근무년수가 10년 이상인 사람 출력. (이름, 입사일)
SELECT name, ibsadate, FLOOR(MONTHS_BETWEEN(SYSDATE, ibsadate)) AS 근무개월수
  FROM insa
  WHERE FLOOR(MONTHS_BETWEEN(SYSDATE, ibsadate))>=120;

 
--61. 주민번호를 기준으로 75~82년생 출력. (이름, 주민번호, 출신도).
SUBSTR() 함수, BEWTEEN AND 구문, TO_NUMBER() 함수 이용.
SELECT name, ssn, city
  FROM insa
  WHERE TO_NUMBER(SUBSTR(ssn, 1, 2)) BETWEEN 80 AND 82;

--62. 근무년수가 5~10년인 사람 출력. (이름, 입사일)
SELECT name 이름, TO_CHAR(ibsadate,'YYYY-MM-DD') 입사일
  FROM insa
  WHERE FLOOR(MONTHS_BETWEEN(SYSDATE,ibsadate)) BETWEEN 60 AND 120;
 
--63. 김씨, 이씨, 박씨만 출력 (이름, 부서). SUBSTR() 함수 이용.
SELECT name 이름,buseo 부서
  FROM insa
  WHERE SUBSTR(name,1,1) IN ('김','이','박');

--64. 입사일을 "년-월-일 요일" 형식으로 남자만 출력 (이름, 주민번호, 입사일)
SELECT  name 이름, ssn 주민번호,TO_CHAR(ibsadate,'YYYY-MM-DD DAY') 입사일
  FROM insa
  WHERE SUBSTR(ssn, 8, 1)= '1';

--65. 부서별 직위별 급여합 구하기. (부서, 직위, 급여합)
SELECT buseo, jikwi, COUNT(*), SUM(basicpay), ROUND(AVG(basicpay))
  FROM insa
  GROUP BY buseo, jikwi
  ORDER BY buseo;

 
--66. 부서별 직위별 인원수를 구하되 인원수가 5명 이상인 경우만 출력.
SELECT buseo, jikwi, COUNT(*)
  FROM insa
  GROUP BY buseo,jikwi
  HAVING COUNT(*)>=5;

--67. 2000년에 입사한 여사원. (이름, 주민번호, 입사일)
SELECT COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS "2000년입사한여자수"
  FROM insa
  WHERE TO_CHAR(ibsadate,'YY')=00;

--68. 성씨가 한 글자(김, 이, 박 등)라는 가정하에 성씨별 인원수 (성씨, 인원수)
SELECT SUBSTR(name,1,1) 성씨,COUNT(*) 인원수
  FROM insa
  GROUP BY SUBSTR(name,1,1);

--69. 출신도(CITY)별 성별 인원수.
SELECT city,
  COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 남자수,
  COUNT(DECODE(SUBSTR(ssn, 8, 1), '2', 1)) AS 여자수
  FROM insa
  GROUP BY city;

--70. 부서별 남자인원수가 5명 이상인 부서와 남자인원수.
SELECT buseo,COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) AS 남자직원수
  FROM insa
  GROUP BY buseo
  HAVING  COUNT(DECODE(SUBSTR(ssn, 8, 1), '1', 1)) >= 5;
 
--71. 입사년도별 인원수.
SELECT TO_CHAR(ibsadate,'YYYY') 입사년도,COUNT(*) AS "인원수"
  FROM insa
  GROUP BY TO_CHAR(ibsadate,'YYYY')
  ORDER BY 인원수 DESC;
 
--71. 전체인원수, 2000년, 1999년, 1998년도에 입사한 인원을 다음의 형식으로 출력.
        --전체 2000 1999 1998
         --60    x    x    x
SELECT COUNT(*) 전체,
       COUNT(DECODE(TO_CHAR(ibsadate,'YYYY'),'2000',1)) AS "2000년",
       COUNT(DECODE(TO_CHAR(ibsadate,'YYYY'),'1999',1)) AS "1999년",
       COUNT(DECODE(TO_CHAR(ibsadate,'YYYY'),'1998',1)) AS "1998년"
      FROM insa;
--72. 아래 형식으로 지역별 인원수 출력.
--      전체 서울  인천  경기
--       60    x     x     x

SELECT COUNT(*) 전체,
        COUNT(DECODE(city,'서울',1)) 서울
        ,COUNT(DECODE(city,'인천',1)) 인천
        ,COUNT(DECODE(city,'경기',1)) 경기
  FROM insa;

------------------------------------------------
UPDATE

1. 테이블에서 기존의 데이터를 변경한다.

2. UPDATE 테이블_명
 SET 컬럼_명= 변경할_값[, 컬럼_명= 변경할_값, ...]
 [WHERE 조건];

INSA 테이블에서 사번(NUM)이 1048인 직원(이기상)의 전화번호를 '010-111-2222'로 수정.
SELECT *
 FROM insa
 WHERE num=1048;

UPDATE insa
 SET tel='010-111-2222'
 WHERE num=1048;
COMMIT;
--> 실행후 COMMIT 또는 ROLLBACK를 반드시 선택할 것.

INSA 테이블에서 사번(NUM)이 1048인 직원(이기상)의 부서와 직위를 '영업부', '과장'로 수정.
UPDATE insa
 SET buseo='영업부', jikwi='과장'
 WHERE num=1048;
COMMIT;

문제) INSA 테이블에서 '과장' 직위(jikwi) 이상인(과장, 부장) 경우만 수당(sudang)을 10% 인상합니다.

SELECT name, jikwi, sudang, sudang*1.1 AS "10% 인상된 수당"
 FROM insa
 WHERE jikwi IN ('과장', '부장');

UPDATE insa
 SET sudang = sudang * 1.1
 WHERE jikwi IN ('과장', '부장');
COMMIT;
 

문제) INSA 테이블에서 전화번호가 011, 016, 017, 018, 019로 시작되는 전화번호인 경우 모두 010-XXXX-XXXX으로 변경합니다.

SELECT *
  FROM insa
  WHERE SUBSTR(tel, 1, 3) != '010';

SELECT SUBSTR(tel, 4, 10) AS tel
  FROM insa
  WHERE SUBSTR(tel, 1, 3) != '010';

UPDATE insa
  SET tel = ('010' || SUBSTR(tel, 4, 10))
  WHERE SUBSTR(tel, 1, 3) != '010';
COMMIT;

HR 계정에서 employees 테이블의 직원들의 salary를 10% 인상합니다. 단, 부서명(department_name)이 'IT'인 경우로 한정.

SELECT e.first_name, e.last_name, e.salary, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id
  AND d.department_name='IT';

SELECT first_name, last_name, salary
 FROM employees
 WHERE department_id IN (SELECT department_id
     FROM departments
     WHERE department_name='IT');

UPDATE employees
 SET salary = salary*1.1
 WHERE department_id IN (SELECT department_id
     FROM departments
     WHERE department_name='IT');

ROLLBACK;

문제) HR 계정 employees 테이블에서 job_title이 'Sales Manager'인 직원들의 salary를 해당 직무의 최고 급여(max_salary)로 수정. 단, 입사일(hire_date)가 2005년 이전(해당 년도 포함) 입사자에 한해서 적용함.

SELECT *
  FROM employees
   WHERE job_id = (SELECT job_id FROM jobs
    WHERE job_title='Sales Manager')
  AND TO_CHAR(hire_date, 'YYYY') <= 2005;

SELECT max_salary FROM jobs
   WHERE job_title='Sales Manager';

UPDATE employees
 SET salary = (SELECT max_salary FROM jobs
   WHERE job_title='Sales Manager')
 WHERE job_id = (SELECT job_id FROM jobs
    WHERE job_title='Sales Manager')
  AND TO_CHAR(hire_date, 'YYYY') <= 2005;
ROLLBACK;

문제) HR 계정 employees 테이블에서 모든 직원의 salary를 해당 직원의 직무(job_id) 최고 급여(max_salary)로 수정.
예를 들어, AD_PRES는 40000, AD_VP는 30000, IT_PROG는 10000으로 수정됨.
--> 상관 하위 쿼리 이용.

SELECT first_name, last_name, salary, job_id
  , (SELECT max_salary FROM jobs
   WHERE job_id = e.job_id) AS max_salary
  FROM employees e;

UPDATE employees e
 SET salary = (SELECT max_salary FROM jobs
   WHERE job_id = e.job_id);

SELECT first_name, last_name, salary, job_id
  , (SELECT max_salary FROM jobs
   WHERE job_id = e.job_id) AS max_salary
  FROM employees e;

ROLLBACK;

문제) INSA 테이블에서 '영업부'인 경우는 기본급(basicpay)를 2100000으로 수정하고, '개발부'인 경우는 기본급을 2200000으로 수정, '총무부'인 경우는 기본급을 2000000으로 수정합니다. 단, 대리, 사원 직급(jikwi)만 해당.

UPDATE insa
 SET basicpay = (CASE
   WHEN buseo='영업부' THEN 2100000
   WHEN buseo='개발부' THEN 2200000
   WHEN buseo='총무부' THEN 2000000
      END)
   WHERE buseo IN ('총무부', '개발부', '영업부')
  AND jikwi IN ('사원', '대리');
COMMIT;

문제) HR 계정 employees 테이블에서 salary를 각 부서이름별(department_name)로 다른 인상율 적용합니다.
Finance -> 10%
Executive -> 15%
Accounting -> 20%

UPDATE employees
 SET salary = CASE
   WHEN department_id = (하위쿼리) THEN salary*1.1
   WHEN department_id = (하위쿼리) THEN salary*1.15
   WHEN department_id = (하위쿼리) THEN salary*1.2
   ELSE salary
       END;
ROLLBACK;

위에서 하위쿼리는
SELECT department_id FROM departments WHERE department_name='Finance'
SELECT department_id FROM departments WHERE department_name='Executive'
SELECT department_id FROM departments WHERE department_name='Accounting'
  

UPDATE employees
 SET salary = CASE
   WHEN department_id = (SELECT department_id FROM departments WHERE department_name='Finance') THEN salary*1.1
   WHEN department_id = (SELECT department_id FROM departments WHERE department_name='Executive') THEN salary*1.15
   WHEN department_id = (SELECT department_id FROM departments WHERE department_name='Accounting') THEN salary*1.2
   ELSE salary
       END;

문제) INSA 테이블에서 직위별로 기본급(basicpay) 인상율을 다르게 적용하는 쿼리 작성.
사원 ->  20%
대리 ->  15%
과장 ->  10%
부장 ->  5%

문제) INSA 테이블에서 직위별로 기본급(basicpay) 인상율을 다르게 적용하는 쿼리 작성.
단, 개발부 직원들(13명)만 적용할 것.
사원, 대리 ->  10%
과장, 부장 ->  5%

------------------------------------------------
DELETE

1. 테이블에서 지정한 행을 삭제하는데 사용한다.

2.
DELETE [FROM] 테이블_명 [WHERE 조건];

INSA 테이블에서 '서울' 지역 직원들만 삭제.
DELETE
 FROM insa
 WHERE city = '서울';
COMMIT;

INSA 테이블에서 사번(NUM)이 1048인 직원(이기상)의 정보를 삭제.
DELETE
 FROM insa
 WHERE num=1048;
COMMIT;

HR 계정에서 employees 테이블의 직원들의 정보를 삭제합니다.
단, 부서명(department_name)이 'IT'인 경우로 한정.
--> 실제로는 employees 테이블의 정보가 다른 테이블에 의해서 참조 당하는 경우 삭제가 안될 수 있습니다.

DELETE
 FROM employees
 WHERE department_id = (SELECT department_id FROM departments
    WHERE department_name = 'IT');
COMMIT;

DELETE FROM employees
  WHERE employee_id=103; --X. 자신이 다른 직원의 매니저로 연결된 상태
DELETE FROM employees
  WHERE employee_id=104; --O
ROLLBACK; 

***참조 당하는 경우 삭제가 안될 수 있습니다. FK 제약 지정시 삭제 옵션(on delete cascade) 추가된 상태인 경우 삭제 가능.

------------------------------------------------
뷰(View)

1. 뷰란 이미 특정한 데이터베이스 내에 존재하는 하나 이상의 테이블에서 사용자가 얻기 원하는 데이터들만을 정확하고 편하게 가져오기 위하여 사전에 원하는 컬럼들 만을 모아서 만들어 놓은 가상의 테이블로 편리성 및 보안에 목적이 있다. 가상의 테이블이란 뷰가 실제로 존재하는 테이블이 아니라 하나 이상의 테이블에서 파생된 또 다른 정보를 볼 수 있는 방법 이며 그 정보들을 추출해내는 SQL 문장이라고 볼 수 있다.

2. CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름
 [(alias[, alias]...]
 AS subquery
 [WITH CHECK OPTION]
 [WITH READ ONLY];

HR 계정 employees 테이블의 정보를 얻는 뷰 작성.
예를 들어,
employees, departments, locations, countries, regions 테이블.
직원 정보(first_name, last_name, job_title, department_name
, city, COUNTRY_NAME, REGION_NAME) 출력하는 쿼리인 경우

SELECT e.first_name, e.last_name, d.department_name, l.city
 , c.country_name, r.region_name
 FROM employees e, departments d, locations l, countries c, regions r
 WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id;
와 같이 작성하면 복잡하고 어렵다.
이 쿼리에 대한 사용빈도가 높다면 이를 뷰로 만드는 것이 좋다.

CREATE OR REPLACE VIEW employees_detail_view
AS
SELECT e.first_name, e.last_name, d.department_name, l.city
 , c.country_name, r.region_name
 FROM employees e, departments d, locations l, countries c, regions r
 WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id;

SELECT * FROM employees_detail_view;

--뷰의 구조 확인
DESC employees_detail_view;

--뷰의 소스 확인
SELECT view_name, text FROM user_views
 WHERE view_name='EMP_DETAILS_VIEW'; --뷰이름은 대문자로 작성

employees 테이블에서 급여(salary) 많은 직원 중에서 두 번째 직원 출력. 뷰 생성.
SELECT rn, employee_id, first_name, last_name, salary
FROM (SELECT ROWNUM AS rn, employee_id, first_name, last_name, salary 
FROM (SELECT employee_id, first_name, last_name, salary
 FROM employees
 ORDER BY salary DESC))
WHERE rn=2;

--뷰를 생성할 때 조건절 부분은 넣지 않는 것이 좋다.
CREATE OR REPLACE VIEW empView2
AS
SELECT rn, employee_id, first_name, last_name, salary
FROM (SELECT ROWNUM AS rn, employee_id, first_name, last_name, salary 
FROM (SELECT employee_id, first_name, last_name, salary
 FROM employees
 ORDER BY salary DESC));

SELECT *
 FROM empView2
 WHERE rn=1;
 
SELECT *
 FROM empView2
 WHERE rn=2;

문제) INSA 테이블에서 아래 결과를 얻는 뷰 작성.
이름, 부서, 출신도, 기본급, 수당, 기본급+수당(총급여), 세금, 실수령액 출력
       세금: 총급여가 250만원 이상이면 2%, 200만원 이상이면 1%, 나머지 0.
       실수령액: 총급여-세금
       CASE~END 문 사용.

원본 SELECT 쿼리 준비
SELECT 이름, 부서, 출신도, 기본급, 수당, 총급여, 세금, 총급여-세금 AS 실수령액
 FROM (SELECT name AS 이름, buseo AS 부서, city AS 출신도
 , basicpay AS 기본급
 , sudang AS 수당, basicpay+sudang AS 총급여
 , CASE
  WHEN basicpay+sudang>=2500000 
   THEN ROUND((basicpay+sudang) * 0.02)
  WHEN basicpay+sudang>=2000000 
   THEN ROUND((basicpay+sudang) * 0.01)
  ELSE 0
   END AS 세금
 FROM insa);

뷰 생성 구문 작성
CREATE OR REPLACE VIEW  insa_details_view
AS
SELECT 이름, 부서, 출신도, 기본급, 수당, 총급여, 세금
 , 총급여-세금 AS 실수령액
 FROM (SELECT name AS 이름, buseo AS 부서, city AS 출신도
 , basicpay AS 기본급
 , sudang AS 수당, basicpay+sudang AS 총급여
 , CASE
  WHEN basicpay+sudang>=2500000 
   THEN ROUND((basicpay+sudang) * 0.02)
  WHEN basicpay+sudang>=2000000 
   THEN ROUND((basicpay+sudang) * 0.01)
  ELSE 0
   END AS 세금
 FROM insa);

뷰 실행 테스트
SELECT * FROM insa_details_view;

문제) HR 계정 employees 테이블에서 직원 정보 출력시 부서명(department_name), 직위명(job_title)을 같이 출력하는 뷰 작성. 부서명이 없어도 직원 정보는 출력할 것. 뷰이름은 employees_all_view로 지정.

SELECT EMPLOYEE_ID
      ,FIRST_NAME
      ,LAST_NAME
      ,EMAIL
      ,PHONE_NUMBER
      ,HIRE_DATE
      ,JOB_ID        --> JOB_TITLE
      ,SALARY
      ,COMMISSION_PCT
      ,MANAGER_ID
      ,DEPARTMENT_ID --> DEPARTMENT_NAME
      FROM employees;

CREATE OR REPLACE VIEW employees_all_view
AS
SELECT e.employee_id
      ,e.first_name
      ,e.last_name
      ,e.email
      ,e.phone_number
      ,e.hire_date
      ,j.job_title AS job_title
      ,e.salary
      ,e.commission_pct
      ,e.manager_id
      ,d.department_name AS department_name
      FROM employees e, departments d, jobs j
 WHERE e.department_id = d.department_id(+)
  AND e.job_id = j.job_id;

SELECT * FROM employees_all_view;

문제) HR 계정 employees 테이블에서 부서명(department_name)별 직원의 수 출력. employees_all_view 사용할 것.

SELECT department_name, COUNT(*) AS 부서별직원수
 FROM employees_all_view
 GROUP BY department_name;
 

3. 인라인 뷰
인라인 뷰는 FROM 절에서 서브 쿼리를 사용하여 생성한 임시 뷰이다. 인라인 뷰는 SQL 문이 실행되는 동안만 임시적으로 정의된다. 즉, 객체로서 저장되지 않는다.

employees 테이블에서 입사일(hire_date)이 빠른 순으로 5순위까지만 출력.
SELECT *
 FROM (SELECT first_name, last_name, hire_date
 , RANK() OVER(ORDER BY hire_date ASC) AS rank
 FROM employees)
 WHERE rank <= 5;

--인라인 뷰 기반 DELETE 쿼리 실행
employees 테이블에서 job_title이 'Shipping Clerk'인 직원 삭제.

하위 쿼리를 이용하는 경우
DELETE
 FROM employees
 WHERE job_id IN (SELECT job_id FROM jobs
    WHERE job_title='Shipping Clerk');
--> 실제로는 employees 테이블의 정보가 다른 테이블에 의해서 참조 당하는 경우 삭제가 안될 수 있습니다.
ROLLBACK;

인라인 뷰를 이용하는 경우
DELETE
 FROM (SELECT * FROM employees e, jobs j
   WHERE e.job_id=j.job_id) ej  --AS 구문 뺄 것.
 WHERE ej.job_title = 'Shipping Clerk';
ROLLBACK;

-- 인라인 뷰 기반 UPDATE 쿼리 실행
employees 테이블에서 job_title이 'Shipping Clerk'인 직원의 salary를 10% 인상.

하위 쿼리를 이용하는 경우
UPDATE employees
 SET salary = salary * 1.1
 WHERE job_id IN (SELECT job_id FROM jobs
    WHERE job_title='Shipping Clerk');

인라인 뷰를 이용하는 경우
UPDATE (SELECT * FROM employees e, jobs j
   WHERE e.job_id=j.job_id) ej
 SET ej.salary = ej.salary * 1.1
 WHERE ej.job_title = 'Shipping Clerk';
ROLLBACK;

문제) HR 계정에서 (departments 테이블) department_name은 'Sales'이고, (jobs 테이블) job_title은 'Sales Manager'인 경우만 (employees 테이블) salary를 10% 인상. 인라인 뷰 이용할 것.

UPDATE ( departments, jobs, employees 테이블에 대한 JOIN 쿼리 ) ej
 SET ej.salary = ej.salary * 1.1
 WHERE ej.department_name='Sales' AND ej.job_title='Sales Manager';
ROLLBACK;

문제) HR 계정 employees 테이블에서 salary를 각 부서이름별(department_name)로 다른 인상율 적용합니다. 인라인 뷰 이용할 것.
Finance -> 10%
Executive -> 15%
Accounting -> 20%

UPDATE (SELECT * FROM employees e, departments d
  WHERE e.department_id=d.department_id) ed
 SET salary = CASE
   WHEN department_name='Finance' THEN salary*1.1
   WHEN department_name='Executive' THEN salary*1.15
   WHEN department_name='Accounting' THEN salary*1.2
   ELSE salary
       END;
ROLLBACK;

4. WITH CHECK OPTION 지정 뷰
WITH CHECK OPTION 절을 사용하면 뷰를 통해 참조 무결성(reference integrity)을 검사할 수 있고 DB 레벨에서의 constraint 적용이 가능하다.

INSA 테이블에서 buseo가 '영업부'인 직원만 출력하는 뷰 생성.

CREATE OR REPLACE VIEW insa_op_view
AS
SELECT *
 FROM insa
 WHERE buseo='영업부';

CREATE OR REPLACE VIEW insa_dp_view
AS
SELECT *
 FROM insa
 WHERE buseo='영업부' -->이 조건식이 제약으로 지정될 예정임.
WITH CHECK OPTION CONSTRAINT insa_check_view;

INSERT INTO insa_op_view (num, name, ssn, ibsaDate
    , city, tel, buseo, jikwi, basicPay, sudang)
  VALUES (1102, '박길동', '801212-1022432', '1998-10-11'
  , '서울', '011-2356-5555', '기획부', '부장', 2610000, 200000);
COMMIT;
--> 뷰를 통해서도 INSERT가 가능합니다.

INSERT INTO insa_dp_view (num, name, ssn, ibsaDate
    , city, tel, buseo, jikwi, basicPay, sudang)
  VALUES (1103, '박길동', '801212-1022432', '1998-10-11'
  , '서울', '011-2356-5555', '기획부', '부장', 2610000, 200000);
--> ORA-01402
--> buseo가 '기획부'인 경우는 WITH CHECK OPTION에 위배됨.
--> buseo는 '영업부'만 가능함.

INSERT INTO insa_dp_view (num, name, ssn, ibsaDate
    , city, tel, buseo, jikwi, basicPay, sudang)
  VALUES (1103, '박길동', '801212-1022432', '1998-10-11'
  , '서울', '011-2356-5555', '영업부', '부장', 2610000, 200000);
COMMIT;

SELECT * FROM insa_dp_view;

---------------------------------------------------
시퀀스 (SEQUENCE)

1. 데이터베이스와 관련된 응용프로그램을 개발하다 보면, 연속적으로 숫자 값을 자동으로 증가시켜야 하는 경우가 있다. 이와 같이 특정 컬럼에 대해 유일한 값을 연속적으로 생성시키기 위해 사용하는 방법으로 오라클에서 제공하는 것이 sequence라는 객체이다.

2. CREATE SEQUENCE seq_name
 [INCREMENT BY n] /* n= 증가치 */
 [START WITH n] /* n= 시작값 */
 [[NO]MAXVALUE n] /* n= 최고값 */
 [[NO]MINVALUE n] /* n= 최소값 */
 [[NO]CYCLE] /* MAXVALUE에 도달하면 초기값 부터 다시 시작 여부 */
 [[NO]CACHE n] /* 미리 캐시 해 두는 개수 */

CREATE TABLE bbs (
 sid NUMBER PRIMARY KEY  --글번호(자동 번호 증가, 자동 입력)
 ,name VARCHAR2(10)      --글쓴이 이름
 ,content VARCHAR2(100)  --글내용
 ,writeday DATE  DEFAULT SYSDATE  --글쓴 날짜(현재 날짜 자동 입력)
);
--> sid 운영하려면 시퀀스 객체 필요함.
--> 자동 입력되는 컬럼은 입력 항목에서 제외해야 됩니다.

CREATE SEQUENCE bbs_seq;  -->시퀀스 객체 생성

INSERT INTO bbs (sid, name, content)
 VALUES (bbs_seq.nextval, 'hong', 'TEST');
INSERT INTO bbs (sid, name, content)
 VALUES (bbs_seq.nextval, 'kim', 'KOREA');
COMMIT;
SELECT * FROM bbs;
--> sid 컬럼의 값은 자동 증가, 자동 입력되어 있다.

--현재 증가된 시퀀스 번호 확인
SELECT bbs_seq.currval FROM dual;

--시퀀스 삭제
DROP SEQUENCE bbs_seq;

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

--------------------------------------
UNIQUE KEY(UK)

1. Table에서 지정한 컬럼의 데이터가 중복되지 않고 유일하다. PRIMARY KEY와 유사하나 NULL값을 (중복)허용한다. 내부적으로 UNIQUE INDEX를 만들어 처리한다. 테이블 내에서 UK는 여러번 지정 가능합니다.

2.
컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT constraint명] UNIQUE

테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 UNIQUE(컬럼1명, 컬럼2명, ...)

UK 지정 테스트 - 컬럼 레벨의 형식
CREATE TABLE test5 (
 col1 NUMBER(3) PRIMARY KEY
 ,col2 VARCHAR2(10) UNIQUE
);

INSERT INTO test5 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test5 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test5 (col1, col2)
 VALUES (3, NULL);
INSERT INTO test5 (col1, col2)
 VALUES (4, 'ABCD');  --ORA-00001
INSERT INTO test5 (col1, col2)
 VALUES (3, NULL);  --ORA-00001
COMMIT;

UK 지정 테스트 - 테이블 레벨의 형식
CREATE TABLE test6 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
 ,CONSTRAINT TEST6_COL1_PK PRIMARY KEY(col1)
 ,CONSTRAINT TEST6_COL2_UK UNIQUE(col2)
);

--UK 제약 확인
SELECT * FROM constcheck WHERE table_name='TEST6';

UK 지정 테스트 - 테이블 생성 후 제약 추가
CREATE TABLE test7 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
);

ALTER TABLE test7
 ADD (CONSTRAINT TEST7_COL1_PK PRIMARY KEY(col1)
  ,CONSTRAINT TEST7_COL2_UK UNIQUE(col2));

--UK 제약 확인
SELECT * FROM constcheck WHERE table_name='TEST7';

------------------------------------------------------
HR 스키마 ERD에서 UNIQUE 제약 확인

SELECT * FROM constcheck WHERE table_name='EMPLOYEES';
--> EMAIL 컬럼에 UNIQUE 제약 지정됨.

------------------------------------------------------
CHECK (CK)

1. 컬럼에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약조건이다. 컬럼에 입력되는 데이터를 검사해서 조건에 맞는 데이터만 입력되도록 한다.

2.
컬럼 레벨의 형식
컬럼명 데이터타입 CONSTRAINT constraint명 CHECK(컬럼명 조건)

테이블레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 CHECK(컬럼명 조건)

CK 제약 테스트 - 컬럼 레벨의 형식
CREATE TABLE test8 (
 col1 NUMBER PRIMARY KEY
 ,col2 VARCHAR2(10)
 ,col3 NUMBER(3) CHECK (col3 BETWEEN 0 AND 100)
);

INSERT INTO test8 (col1, col2, col3)
 VALUES (1, 'HONG', 100);
INSERT INTO test8 (col1, col2, col3)
 VALUES (2, 'PARK', 80);
INSERT INTO test8 (col1, col2, col3)
 VALUES (3, 'CHOI', 200); --ORA-02290
COMMIT;

CK 제약 테스트 - 테이블 레벨의 형식
CREATE TABLE test9 (
 col1 NUMBER
 ,col2 VARCHAR2(10)
 ,col3 NUMBER(3)
 ,CONSTRAINT TEST9_COL1_PK PRIMARY KEY(col1)
 ,CONSTRAINT TEST9_COL3_CK CHECK (col3 BETWEEN 0 AND 100)
);

--제약 확인
SELECT * FROM constcheck WHERE table_name='TEST9';

CK 제약 테스트 - 테이블 생성 후 제약 추가
CREATE TABLE test10 (
 col1 NUMBER
 ,col2 VARCHAR2(10)
 ,col3 NUMBER(3)
);

ALTER TABLE test10
 ADD (CONSTRAINT TEST10_COL1_PK PRIMARY KEY(col1)
 ,CONSTRAINT TEST10_COL3_CK CHECK (col3 BETWEEN 0 AND 100));

--제약 확인
SELECT * FROM constcheck WHERE table_name='TEST10';

문제) ssn 컬럼에서 주민등록번호 입력시 성별 확인용(1 또는 2) 체크 제약 추가할 것.
CREATE TABLE member (
 sid NUMBER
 ,name VARCHAR2(10)
 ,ssn VARCHAR2(14)
 ,tel VARCHAR2(20)
);

--제약 추가
ALTER TABLE member
 ADD (CONSTRAINT MEMBER_SID_PK PRIMARY KEY(sid)
 ,CONSTRAINT MEMBER_SSN_CK CHECK (SUBSTR(ssn, 8, 1) IN (1, 2)));

--제약 확인
SELECT * FROM constcheck WHERE table_name='MEMBER';

--입력 테스트
INSERT INTO member (sid, name, ssn, tel)
 VALUES (1, 'KIM', '801212-1234567', '010-111-2222'); --O
INSERT INTO member (sid, name, ssn, tel)
 VALUES (2, 'PARK', '850101-3234567', '010-222-3333'); --X
COMMIT;

문제) 전화번호가 항상 010~ 으로 시작하도록 제약 추가
ALTER TABLE member
 ADD CONSTRAINT ~   ;

------------------------------------------------------
HR 스키마 ERD에서 CHECK 제약 확인

SELECT * FROM constcheck;

JOB_HISTORY C END_DATE end_date > start_date
JOB_HISTORY C START_DATE end_date > start_date

EMPLOYEES C SALARY salary > 0

--------------------------------------------------
FOREIGN KEY (FK)

1. 참조 키 또는 외래 키(FK)는 두 테이블의 데이터 간 연결을 설정하고 강제 적용하는 데 사용되는 열이다. 한 테이블의 기본 키 값이 있는 열을 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다. 이 때 두 번째 테이블에 추가되는 열이 외래 키가 된다.

2. 부모 테이블이 먼저 생성된 후 자식 테이블(foreign key를 포함하는 테이블)이 생성되어야 한다.

3.
컬럼 레벨의 형식
컬럼명 데이터타입 CONSTRAINT constraint명
 REFERENCES 참조_테이블명 (참조_컬럼명)
 [ON DELETE CASCADE | ON DELETE SET NULL]

테이블레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 FOREIGN KEY(컬럼)
 REFERENCES 참조_테이블명 (참조_컬럼명)
 [ON DELETE CASCADE | ON DELETE SET NULL]

부모 테이블 생성 작업을 먼저 해야합니다. 부모 테이블에는 반드시 PK 또는 UK 제약이 있는 컬럼이 존재해야 합니다.
CREATE TABLE jobs (
 jikwi_id NUMBER
 ,jikwi_name VARCHAR2(10)
);

ALTER TABLE jobs
  ADD CONSTRAINT JOBS_JIKWI_ID_PK PRIMARY KEY(jikwi_id);
 

INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (1, '과장');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (2, '대리');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (3, '사원');
COMMIT;

FK 제약 지정 - 컬럼 레벨의 형식
CREATE TABLE employees (
 sid NUMBER PRIMARY KEY
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER REFERENCES jobs(jikwi_id)
);

INSERT INTO employees (sid, name, jikwi_id)
 VALUES (1, '홍길동', 1);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (2, '김길동', 2);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (3, '박길동', 4);  --ORA-02291
COMMIT;

SELECT sid, name, jikwi_name
  FROM employees e, jobs j
  WHERE e.jikwi_id = j.jikwi_id;

FK 제약 지정 - 테이블 레벨의 형식
CREATE TABLE employees (
 sid NUMBER
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER
 ,CONSTRAINT EMPLOYEES_SID_PK PRIMARY KEY(sid)
 ,CONSTRAINT EMPLOYEES_JIKWI_ID_FK
   FOREIGN KEY(jikwi_id)
   REFERENCES jobs(jikwi_id)
);

FK 제약 지정 - 테이블 생성 후 제약 추가
CREATE TABLE employees (
 sid NUMBER
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER
);

ALTER TABLE employees
 ADD (CONSTRAINT EMPLOYEES_SID_PK PRIMARY KEY(sid)
  ,CONSTRAINT EMPLOYEES_JIKWI_ID_FK
   FOREIGN KEY(jikwi_id)
   REFERENCES jobs(jikwi_id));

4. FOREIGN KEY 생성 시 주의사항
- 참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
- 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 있어야 한다.
- 테이블 사이에 PRIMARY KEY와 FOREIGN KEY가 정의 되어 있으면, primary key 삭제 시 foreign key 컬럼에 그 값이 입력되어 있으면 삭제가 안 된다. (단, FK 선언 때 ON DELETE CASCADE나 ON DELETE SET NULL옵션을 사용한 경우에는 삭제된다.)
- 부모 테이블을 삭제하기 위해서는 자식 테이블을 먼저 삭제해야 한다.
- 서로 제약에 의해 참조하는 경우는 서로 삭제되지 않는다. 제약조건을 먼저 삭제해야 한다.

ALTER TABLE 테이블명
 DROP CONSTRAINT (참조)제약명;

DROP TABLE jobs; --ORA-02449

DROP TABLE employees; --자식 테이블 먼저 삭제

DROP TABLE jobs; --부모 테이블 삭제

-------------------------------------
--(참조)제약 조건 확인용 뷰 생성
CREATE OR REPLACE VIEW constraint_check
AS
SELECT  uc.table_name AS table_name   --테이블명
 , ucc.column_name AS column_name --지정된 컬럼
 , uc.constraint_name AS constraint_name --제약명
 , uc.constraint_type AS constraint_type --제약조건 종류(P, R, U, C)
 , uc.search_condition AS search_condition --CHECK 제약 조건식
 , uc.delete_rule AS delete_rule --ON DELETE CASCADE 옵션
 , (SELECT table_name
  FROM user_cons_columns
  WHERE constraint_name=uc.r_constraint_name)
  AS r_table_name  --참조하는 테이블
 , (SELECT column_name
  FROM user_cons_columns
  WHERE constraint_name=uc.r_constraint_name)
  AS r_conlumn_name  --참조하는 컬럼
 FROM user_constraints uc, user_cons_columns ucc
 WHERE uc.constraint_name = ucc.constraint_name;

--제약 조건 확인 방법
SELECT * FROM constraint_check;

SELECT * FROM constraint_check
  WHERE table_name='테이블명';

-------------------------------------------------
HR 스키마 ERD에서 FK 제약 확인 (자식 테이블, 부모 테이블 확인)

SELECT * FROM constraint_check;

-------------------------------------------------
ON DELETE CASCADE 옵션 지정 테스트

ON DELETE CASCADE 옵션 지정 전 -----------------------
CREATE TABLE jobs (
 jikwi_id NUMBER
 ,jikwi_name VARCHAR2(10)
 ,CONSTRAINT JOBS_JIKWI_ID_PK PRIMARY KEY(jikwi_id)
);

INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (1, '과장');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (2, '대리');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (3, '사원');
COMMIT;

FK 제약 지정
CREATE TABLE employees (
 sid NUMBER PRIMARY KEY
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER REFERENCES jobs(jikwi_id)
);

INSERT INTO employees (sid, name, jikwi_id)
 VALUES (1, '홍길동', 1);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (2, '김길동', 2);
COMMIT;

employees 테이블에서 '김길동' 자료 삭제 시도. --O
DELETE FROM employees WHERE sid=2;

jobs 테이블에서 '과장' 자료 삭제 시도. --X
DELETE FROM jobs WHERE jikwi_id=1;

jobs 테이블에서 '사원' 자료 삭제 시도. --O
DELETE FROM jobs WHERE jikwi_id=3;

--> ON DELETE CASCADE 옵션 지정이 필요합니다.
--> employees 테이블에서 FK 제약 제거 후 다시 FK 제약 지정
SELECT * FROM constraint_check WHERE table_name='EMPLOYEES';
--> NO_ACTION

ALTER TABLE employees
 DROP CONSTRAINT 제약명;

ON DELETE CASCADE 옵션 지정 후 -----------------------
ALTER TABLE employees
 ADD CONSTRAINT EMPLOYEES_JIKWI_ID_FK
  FOREIGN KEY (jikwi_id)
  REFERENCES jobs(jikwi_id)
  ON DELETE CASCADE;

SELECT * FROM constraint_check WHERE table_name='EMPLOYEES';
--> CASCADE

--> 옵션 지정 후에는 PK 자료를 언제든지 삭제 가능.
    단. PK 지정 자료가 삭제되면 FK 자료도 같이 삭제됨.

jobs 테이블에서 '과장' 자료 삭제 시도. --O
DELETE FROM jobs WHERE jikwi_id=1;
--> employees 테이블의 '홍길동' 자료도 같이 삭제됨.

과정 개설 취소하는 경우 과정에 등록된 과목들을 같이 삭제해야 하는 경우가 있다.
이런 경우는 ON DELETE CASCADE 옵션이 필요하다.

-------------------------------------
HR 스키마 ERD에서 ON DELETE CASCADE 옵션 분석

SELECT * FROM constraint_check WHERE table_name='JOB_HISTORY';
--> NO_ACTION으로 지정됨.

SELECT * FROM constraint_check WHERE table_name='EMPLOYEES';
--> NO_ACTION으로 지정됨.

* HR 스키마에서 모든 테이블은 ON DELETE CASCADE 옵션이 적용 안된 상태임.

-------------------------------------
NOT NULL

1. 테이블에서 지정한 컬럼의 데이터가 NULL 값을 갖지 못한다.

2.
컬럼레벨의 형식
컬럼명 데이터타입 [CONSTRAINT constraint명] NOT NULL

테이블레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 CHECK(컬럼명 IS NOT NULL)

3. 기존 테이블에 NOT NULL 컬럼을 추가 시에는 ADD보다는 MODIFY절을 사용하면 더 간단하게 부여할 수 있다.

ALTER TABLE 테이블명
 ADD CONSTRAINT constraint명 CHECK(컬럼명 IS NOT NULL);

ALTER TABLE 테이블명
 MODIFY 컬럼명 자료형 NOT NULL;

4. 기존 데이터를 NULL로 수정하는 경우에는 오류가 발생한다.

NOT NULL 제약 테스트 - 컬럼 레벨의 형식
CREATE TABLE test10 (
 col1 NUMBER PRIMARY KEY
 ,col2 VARCHAR2(10) NOT NULL
);

INSERT INTO test10 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test10 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test10 (col1, col2)
 VALUES (3, NULL);  --X
COMMIT;

NOT NULL 제약 테스트 - 테이블 레벨의 형식
CREATE TABLE test10 (
 col1 NUMBER
 ,col2 VARCHAR2(10)
 ,CONSTRAINT 제약명 PRIMARY KEY(col1)
 ,CONSTRAINT 제약명 CHECK(col2 IS NOT NULL)
);

NOT NULL 제약 테스트 - 테이블 생성 후 제약 추가
CREATE TABLE test10 (
 col1 NUMBER
 ,col2 VARCHAR2(10)
);

ALTER TABLE test10
 ADD CONSTRAINT 제약명 PRIMARY KEY(col1);
ALTER TABLE test10
 ADD CONSTRAINT 제약명 CHECK(col2 IS NOT NULL);
--> NOT NULL 제약 추가하는 경우만 아래 방법도 가능
ALTER TABLE test10
 MODIFY col2 NOT NULL;

NOT NULL 제약 확인 -- ADD CONSTRAINT 방법 사용한 경우
SELECT * FROM constraint_check WHERE table_name='TEST10';

NOT NULL 제약 확인 -- MODIFY 방법 사용한 경우
DESC test10;

--NOT NULL 제약 확인
SELECT * FROM user_tab_columns
 WHERE table_name='테이블명';
--> nullable 컬럼 확인

-------------------------------------
HR 스키마 ERD에서 NOT NULL 옵션 분석

SELECT * FROM user_tab_columns;
--> nullable 컬럼 확인

------------------------------------
DEFAULT 표현식

1. insert와 update 문에서 특정 값이 아닌 디폴트값을 입력할 수 도 있다.

2.
컬럼명 데이터타입 DEFAULT 디폴트값

3. INSERT 명령 실행시 해당 컬럼에 값을 할당하지 않거나, DEFAULT 키워드에 의해서 디폴트값을 입력할 수 있다.

4. DEFAULT 키워드와 다른 제약 (NOT NULL 등) 표기가 같이 오는 DEFAULT 키워드 를 먼저 표기할 것.

DEFAULT 표현식 테스트
CREATE TABLE bbs (
 sid NUMBER PRIMARY KEY  --글번호(자동 번호 증가, 자동 입력)
 ,name VARCHAR2(10)      --글쓴이 이름
 ,content VARCHAR2(100)  --글내용
 ,writeday DATE  DEFAULT SYSDATE  --글쓴 날짜(현재 날짜 자동 입력)
);
--> sid 운영하려면 시퀀스 객체 필요함.
--> 자동 입력되는 컬럼은 입력 항목에서 제외해야 됩니다.

INSERT INTO bbs (sid, name, content) --writeday 컬럼 지정 제외
 VALUES (1, 'hong', 'TEST');
INSERT INTO bbs (sid, name, content) --writeday 컬럼 지정 제외
 VALUES (2, 'park', 'ABCD');
COMMIT;
SELECT * FROM bbs;
--> 출력해보면 writeday 컬럼에 날짜가 자동 입력된 것을 확인할 수 있다.

DEFAULT 표현식 확인 -------------------
SELECT * FROM user_tab_columns
 WHERE table_name='BBS';
--> data_default 컬럼의 값 확인

테이블 생성 후 DEFAULT 표현식 추가 --------------
ALTER TABLE 테이블명
 MODIFY 컬럼명 [자료형] DEFAULT 값;

DEFAULT 표현식 삭제 --------------------
ALTER TABLE 테이블명
 MODIFY 컬럼명 [자료형] DEFAULT NULL;

-------------------------------------
HR 스키마 ERD에서 DEFAULT 확인

SELECT * FROM user_tab_columns;
--> data_default 컬럼 확인

-------------------------------------
--(참조)제약 조건 및 NOT NULL, DEFAULT 확인용 뷰 생성
CREATE OR REPLACE VIEW constraint_check
AS
SELECT utc.table_name AS table_name
 , utc.column_name AS column_name
 , data_type, data_length, data_precision
 , nullable, data_default
 , constraint_name, constraint_type
 , search_condition, delete_rule
 , r_table_name, r_conlumn_name
 FROM
 (SELECT  uc.table_name AS table_name   --테이블명
 , ucc.column_name AS column_name --지정된 컬럼
 , uc.constraint_name AS constraint_name --제약명
 , uc.constraint_type AS constraint_type --제약조건 종류(P, R, U, C)
 , uc.search_condition AS search_condition --CHECK 제약 조건식
 , uc.delete_rule AS delete_rule --ON DELETE CASCADE 옵션
 , (SELECT table_name
  FROM user_cons_columns
  WHERE constraint_name=uc.r_constraint_name)
  AS r_table_name  --참조하는 테이블
 , (SELECT column_name
  FROM user_cons_columns
  WHERE constraint_name=uc.r_constraint_name)
  AS r_conlumn_name  --참조하는 컬럼
 FROM user_constraints uc, user_cons_columns ucc
 WHERE uc.constraint_name = ucc.constraint_name) ucc
  , user_tab_columns utc
 WHERE ucc.table_name(+)=utc.table_name
  AND ucc.column_name(+)=utc.column_name;

SELECT * FROM constraint_check
 WHERE table_name='BBS';

-------------------------------------
과제) HR 스키마 ERD를 이용한 테이블 재구성

팀별로 HR 스키마에 있는 모든 테이블(7개)을 재구성하는 쿼리 작성

1. 기존 테이블의 정보 수집
- 테이블구조 (컬럼이름(column_name 컬럼), 자료형(data_type 컬럼), DEFAULT 표현식(data_default 컬럼), NOT NULL(nullable 컬럼)) 확인
SELECT * FROM user_tab_columns 
    WHERE table_name='테이블명';

- 제약 조건 확인
SELECT * FROM constraint_check
    WHERE table_name='테이블명';

2. 테이블 생성 (컬럼이름, 자료형, DEFAULT 표현식, NOT NULL)
CREATE TABLE regions (
 ....
);

3. PRIMARY KEY, CHECK, UNIQUE 제약 추가
ALTER TABLE regions
 ADD CONSTRAINT 제약명(테이블명_컬럼명_제약종류)  ....;

4. FOREIGN KEY 제약 추가 (ON DELETE CASCADE 옵션 확인)
ALTER TABLE regions
 ADD CONSTRAINT ....;

5. 각 테이블별로 샘플 데이터 5개 이상 입력 쿼리 추가
INSERT INTO regions (...) VALUES (...);
...
COMMIT;

6. 작성 후 .SQL 파일로 저장
team1.sql

7. 연습용 계정(team1~5, 1234) 추가. 뷰 생성 권한 추가.

8. .SQL 파일 실행 테스트. 연습용 계정 로그인 후 테스트.
@d:\team1.sql

9. 기존 테이블 정보 확인용 쿼리 작성 추가.  연습용 계정 로그인 후 테스트.
- 테이블 데이터 확인
SELECT * FROM regions;

- 테이블구조 (컬럼이름, 자료형, DEFAULT 표현식, NOT NULL) 확인
SELECT * FROM user_tab_columns 
    WHERE table_name='테이블명';

- 제약 조건 확인
SELECT * FROM constraint_check
    WHERE table_name='테이블명';

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

--------------------------------------------
무결성

1. 무결성에는 개체 무결성(Entity Integrity), 참조 무결성(Relational Integrity), 도메인 무결성(Domain Integrity)가 있다.

2. 개체 무결성
개체 무결성은 릴레이션에 저장되는 튜플(tuple)의 유일성을 보장하기 위한 제약조건이다.

3.참조 무결성
참조 무결성은 릴레이션 간의 데이터의 일관성을 보장하기 위한 제약조건이다.

4. 도메인 무결성
도메인 무결성은 속성에서 허용 가능한 값의 범위를 지정하기 위한 제약조건이다.

예를 들어, 학생 정보 저장용 테이블을 만든다면
--테이블 생성
CREATE TABLE member (     --테이블 이름 member
 sid NUMBER        --고유번호 저장용 컬럼
 ,name VARCHAR2(10) --이름, 10글자만 허용
 ,kor NUMBER(3)     --국어, 숫자 3자리만 허용
 ,eng NUMBER(3)     --영어, 숫자 3자리만 허용
 ,mat NUMBER(3)     --수학, 숫자 3자리만 허용
);

5. 제약조건 종류
- PRIMARY KEY(PK) : 해당 컬럼 값은 반드시 존재해야 하며, 유일해야 함(NOT NULL과 UNIQUE 제약조건을 결합한 형태)
- FOREIGN KEY(FK) : 해당 컬럼 값은 참조되는 테이블의 컬럼 값 중의 하나와 일치하거나 NULL을 가짐
- UNIQUE KEY(UK) : 테이블내에서 해당 컬럼 값은 항상 유일해야 함
- NOT NULL : 컬럼은 NULL 값을 포함할 수 없다.
- CHECK(CK) : 해당 컬럼에 저장 가능한 데이터 값의 범위나 조건 지정

------------------------------------------------
***PRIMARY KEY (PK)

1. 테이블에 대한 기본 키를 생성한다.

2. 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합이다. 기본 키는 테이블 당 하나만 존재한다. 그러나, 반드시 하나의 컬럼으로 만 구성되는 것은 아니다. NULL 값이 입력될 수 없고, 이미 테이블에 존재하고 있는 데이터를 다시 입력할 수 없다. UNIQUE INDEX가 자동으로 만들어 진다.

3.
컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT constraint명] PRIMARY KEY(컬럼명,...)

테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
[CONSTRAINT constraint명] PRIMARY KEY(컬럼1명, 컬럼2명,...)

4. constraint를 추가 시에 constraint 명을 생략하면 오라클 서버가 자동적으로 constraint 명을 부여한다. 일반적으로 constraint명은 테이블명_컬럼명_constraint약자'처럼 기술한다.

PK 지정 테스트 - 컬럼 레벨의 형식
CREATE TABLE test1 (
 col1 NUMBER(3) PRIMARY KEY
 ,col2 VARCHAR2(10)
);

INSERT INTO test1 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test1 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test1 (col1, col2)
 VALUES (3, NULL);
INSERT INTO test1 (col1, col2)
 VALUES (2, 'ABCD');  --ORA-00001 (중복된 번호 입력 불가)
INSERT INTO test1 (col1, col2)
 VALUES (NULL, NULL); --ORA-01400 (NULL 입력 불가)
COMMIT;

DESC test1;
이름   널        유형          
---- -------- ------------
COL1 NOT NULL NUMBER(3)      -->PK 제약 확인 불가
COL2          VARCHAR2(10)

--제약 조건 확인
SELECT * FROM user_constraints;

SELECT * FROM user_constraints
 WHERE table_name='TEST1'; --테이블 이름 대문자

--제약이 지정된 컬럼 확인
SELECT * FROM user_cons_columns
  WHERE table_name='TEST1'; --테이블 이름 대문자

--제약이 걸린 소유주, 제약명, 테이블, 제약종류, 컬럼 정보 확인
SELECT uc.owner, uc.constraint_name, uc.table_name, uc.constraint_type, ucc.column_name
  FROM user_constraints uc, user_cons_columns ucc
  WHERE uc.constraint_name = ucc.constraint_name
    AND uc.table_name='TEST1';  --테이블 이름 대문자

PK 지정 테스트 - 테이블 레벨의 형식
--> 제약명을 사용자가 결정한다
--> 제약명은 '테이블명_컬럼명_PK' 형식으로 작성한다.

CREATE TABLE test2 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
 ,CONSTRAINT TEST2_COL1_PK PRIMARY KEY(col1)
);

INSERT INTO test2 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test2 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test2 (col1, col2)
 VALUES (3, NULL);
INSERT INTO test2 (col1, col2)
 VALUES (2, 'ABCD');  --ORA-00001
INSERT INTO test2 (col1, col2)
 VALUES (NULL, NULL); --ORA-01400
COMMIT;

--제약이 걸린 소유주, 제약명, 테이블, 제약종류, 컬럼 정보 확인
SELECT uc.owner, uc.constraint_name, uc.table_name, uc.constraint_type, ucc.column_name
  FROM user_constraints uc, user_cons_columns ucc
  WHERE uc.constraint_name = ucc.constraint_name
    AND uc.table_name='TEST2';  --테이블 이름 대문자

PK 지정 테스트 - 다중 컬럼 PK 지정 (복합키)
CREATE TABLE test3 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
 ,CONSTRAINT TEST3_COL1_COL2_PK PRIMARY KEY(col1, col2)
);

INSERT INTO test3 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test3 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test3 (col1, col2)
 VALUES (3, NULL); --ORA-01400
INSERT INTO test3 (col1, col2)
 VALUES (3, 'ABCD'); --col1은 다른 데이터, col2는 같은 데이터
INSERT INTO test3 (col1, col2)
 VALUES (2, 'KOREA'); --col1은 같은 데이터, col2는 다른 데이터
INSERT INTO test3 (col1, col2)
 VALUES (NULL, NULL); --ORA-01400
INSERT INTO test3 (col1, col2)
 VALUES (2, 'ABCD'); --col1, col2 모두 같은 데이터. ORA-00001

COMMIT;

***PK 지정 테스트 - 테이블 생성 후 제약 추가
CREATE TABLE test4 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
);

ALTER TABLE test4
 ADD CONSTRAINT TEST4_COL1_PK PRIMARY KEY(col1);

INSERT INTO test4 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test4 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test4 (col1, col2)
 VALUES (3, NULL);
INSERT INTO test4 (col1, col2)
 VALUES (2, 'ABCD');  --ORA-00001
INSERT INTO test4 (col1, col2)
 VALUES (NULL, NULL); --ORA-01400
COMMIT;

-----------------------------------------
HR 스키마 ERD에서 PK 분석

DESC employees; -->PK 분석 불가

SELECT uc.owner, uc.constraint_name
 , uc.table_name, uc.constraint_type, ucc.column_name
  FROM user_constraints uc, user_cons_columns ucc
  WHERE uc.constraint_name = ucc.constraint_name
    AND uc.table_name='EMPLOYEES';  --테이블 이름 대문자
-->EMPLOYEE_ID 컬럼에 PK 지정됨.

SELECT uc.owner, uc.constraint_name
 , uc.table_name, uc.constraint_type, ucc.column_name
  FROM user_constraints uc, user_cons_columns ucc
  WHERE uc.constraint_name = ucc.constraint_name
    AND uc.table_name='DEPARTMENTS';  --테이블 이름 대문자
-->DEPARTMENT_ID 컬럼에 PK 지정됨.

SELECT uc.owner, uc.constraint_name
 , uc.table_name, uc.constraint_type, ucc.column_name
  FROM user_constraints uc, user_cons_columns ucc
  WHERE uc.constraint_name = ucc.constraint_name
    AND uc.table_name='JOB_HISTORY';  --테이블 이름 대문자
-->EMPLOYEE_ID, START_DATE 컬럼에 PK 지정됨.

--------------------------------------
제약조건 확인용 뷰 생성

CREATE VIEW 뷰이름
AS
서브쿼리;

SELECT * FROM 뷰이름;
SELECT * FROM 뷰이름 WHERE 조건식;

CREATE VIEW constcheck
AS
SELECT uc.owner AS owner, uc.constraint_name AS constraint_name
 , uc.table_name AS table_name
 , uc.constraint_type AS constraint_type
 , ucc.column_name AS column_name
 , uc.search_condition AS search_condition
 , uc.delete_rule AS delete_rule
  FROM user_constraints uc, user_cons_columns ucc
  WHERE uc.constraint_name = ucc.constraint_name;

SELECT * FROM constcheck;
SELECT * FROM constcheck WHERE table_name='TEST1';

* hr 계정은 CREATE VIEW 권한이 있지만, scott 계정은 CREATE VIEW 권한이 없습니다. 추가로 권한 부여가 필요함. 관리자만 가능.
GRANT CREATE VIEW TO scott;

--------------------------------------------------
***FOREIGN KEY (FK)

1. 참조 키 또는 외래 키(FK)는 두 테이블의 데이터 간 연결을 설정하고 강제 적용하는 데 사용되는 열이다. 한 테이블의 기본 키 값이 있는 열을 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다. 이 때 두 번째 테이블에 추가되는 열이 외래 키가 된다.

외래 키가 적용된 컬럼에는 데이터 입력시 기본 키 값 범위 내에서만 입력 가능.

2. 부모 테이블이 먼저 생성된 후 자식 테이블(foreign key를 포함하는 테이블)이 생성되어야 한다.

3.
컬럼 레벨의 형식
컬럼명 데이터타입 CONSTRAINT constraint명
 REFERENCES 참조_테이블명 (참조_컬럼명)
 [ON DELETE CASCADE | ON DELETE SET NULL]

테이블레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 FOREIGN KEY(컬럼)
 REFERENCES 참조_테이블명 (참조_컬럼명)
 [ON DELETE CASCADE | ON DELETE SET NULL]

부모 테이블 생성 작업을 먼저 해야합니다. 부모 테이블에는 반드시 PK 또는 UK 제약이 있는 컬럼이 존재해야 합니다.
CREATE TABLE jobs (
 jikwi_id NUMBER
 ,jikwi_name VARCHAR2(10)
);

ALTER TABLE jobs
  ADD CONSTRAINT JOBS_JIKWI_ID_PK PRIMARY KEY(jikwi_id);
 

INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (1, '과장');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (2, '대리');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (3, '사원');
COMMIT;

FK 제약 지정 - 컬럼 레벨의 형식
CREATE TABLE employees (
 sid NUMBER PRIMARY KEY
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER REFERENCES jobs(jikwi_id)
);

INSERT INTO employees (sid, name, jikwi_id)
 VALUES (1, '홍길동', 1);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (2, '김길동', 2);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (3, '박길동', 4);  --ORA-02291
COMMIT;

SELECT sid, name, jikwi_name
  FROM employees e, jobs j
  WHERE e.jikwi_id = j.jikwi_id;

FK 제약 지정 - 테이블 레벨의 형식
CREATE TABLE employees (
 sid NUMBER
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER
 ,CONSTRAINT EMPLOYEES_SID_PK PRIMARY KEY(sid)
 ,CONSTRAINT EMPLOYEES_JIKWI_ID_FK
   FOREIGN KEY(jikwi_id)
   REFERENCES jobs(jikwi_id)
);

FK 제약 지정 - 테이블 생성 후 제약 추가
CREATE TABLE employees (
 sid NUMBER
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER
);

ALTER TABLE employees
 ADD (CONSTRAINT EMPLOYEES_SID_PK PRIMARY KEY(sid)
  ,CONSTRAINT EMPLOYEES_JIKWI_ID_FK
   FOREIGN KEY(jikwi_id)
   REFERENCES jobs(jikwi_id));

4. FOREIGN KEY 생성 시 주의사항
- 참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
- 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 있어야 한다.
- 테이블 사이에 PRIMARY KEY와 FOREIGN KEY가 정의 되어 있으면, primary key 삭제 시 foreign key 컬럼에 그 값이 입력되어 있으면 삭제가 안 된다. (단, FK 선언 때 ON DELETE CASCADE나 ON DELETE SET NULL옵션을 사용한 경우에는 삭제된다.)
- 부모 테이블을 삭제하기 위해서는 자식 테이블을 먼저 삭제해야 한다.
- 서로 제약에 의해 참조하는 경우는 서로 삭제되지 않는다. 제약조건을 먼저 삭제해야 한다.

ALTER TABLE 테이블명
 DROP CONSTRAINT (참조)제약명;

DROP TABLE jobs; --ORA-02449

DROP TABLE employees; --자식 테이블 먼저 삭제

DROP TABLE jobs; --부모 테이블 삭제

-----------------------------------------------------
과제)  HR 스키마의 7개 테이블에서 PK, FK 제약을 확인하고 쿼리로 작성.

예를 들어,
ALTER TABLE 테이블명
 ADD (CONSTRAINT 제약명 PRIMARY KEY(컬럼명)
  ,CONSTRAINT 제약명
   FOREIGN KEY(컬럼명)
   REFERENCES 상대방테이블명(컬럼명)
  ,CONSTRAINT 제약명
   FOREIGN KEY(컬럼명)
   REFERENCES 상대방테이블명(컬럼명));

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

-----------------------------------------------------------
데이터베이스 객체

1. 데이터베이스에 저장되는 것들은 테이블 외에 기타 여러 가지 것들이 저장되는데 이것을 데이터베이스 객체(Database Objects)라고 부른다.

2. TABLE
하나 또는 여러 컬럼(Column)들이 모여 하나의 레코드를 이루며, 이러한 레코드들이 모여 테이블이된다. 예를 들어 "사원" 테이블은 사번, 이름, 부서 등 여러 컬럼을 갖게 되고 사원수만큼의 레코드를 갖게 된다.

3.
CREATE 문
데이터베이스 내의 모든 객체를 생성할 때 사용하는 문장
ALTER 문
이미 생성된 객체의 구조를 변경
DROP 문
생성된 객체를 삭제

4.데이터 딕셔너리(Data Dictionary)란 TABLE과 VIEW들의 집합으로 DATABASE에 대한 정보를 제공하는 중요한 부분으로 DATABASE 생성시 SYS schema 안의 내부 TABLE로 구성된다.

Data dictionary는 다음과 같이 시작되는 대표적인 4종류가 있다.
DBA_ : DB 전체에 포함되는 모든 객체에 대한 자세한 정보
ALL_ : 자신이 생성한 객체와 다른 사용자가 만든 객체 중에서 자신이 볼 수 있는 정보를 제공한다.
USER_ : 자신이 생성한 모든 객체에 대한 정보
V$_ : DB의 성능분석/통계 정보를 제공하며 X$테이블에 대한 뷰이다. dynamic performance
view는 V$fixed_table를 조회하여, 리스트를 확인할 수 있음
X$_ : DB의 성능 분석/통계 정보를 제공하는 테이블

5. USER_TABLES : 테이블에 저장된 테이블스페이스 이름, 데이터가 저장된 물리적 공간과 블록 파라미터 정보

SELECT * FROM user_tables;
SELECT * FROM tab;

----------------------------------------------
테이블 작성

1. 테이블은 관계 데이터베이스에 데이터 저장을 위해 이용되는 객체이며, 행과 열 을 통해 spread sheet와 비슷한 방식으로 데이터를 표시한다.

예를 들어, 성적 정보 저장용 테이블이 있다면
column  column  column column column
번호(PK) 이름     국어   영어   수학
1        홍길동   100    100    100    -> row
2        박길동    90     80     90    -> row
3        최길동   100     70     80    -> row

2.
CREATE [GLOBAL TEMPORARY] TABLE [스키마.]테이블_이름 (
 열_이름 데이터타입 [DEFAULT 표현식] [제약조건]
 [, 열_이름 데이터타입 [DEFAULT 표현식] [제약조건] ]
 [ ,...]
);

테이블 생성시 PK 제약을 지정할 수 있는 컬럼을 반드시 추가할 것.

예를 들어, 성적 정보 저장용 테이블을 만든다면
--테이블 생성
CREATE TABLE sungjuk (     --테이블 이름 sungjuk
 sid NUMBER        --고유번호 저장용 컬럼(PK)
 ,name VARCHAR2(10) --이름, 10글자만 허용, 한글 사용 여부 확인 NVARCHAR2(10)
 ,kor NUMBER(3)     --국어, 숫자 3자리만 허용
 ,eng NUMBER(3)     --영어, 숫자 3자리만 허용
 ,mat NUMBER(3)     --수학, 숫자 3자리만 허용
);

--테이블 존재 확인
SELECT * FROM user_tables;

--특정 테이블에 구조(컬럼) 확인
SELECT *
  FROM user_tab_columns
  WHERE table_name='SUNGJUK'; --테이블 이름 대문자료 표기할 것.

--특정 테이블의 자료 확인
SELECT * FROM sungjuk;

-------------------------------------------------
INSERT

1. INSERT 문은 테이블에 새 행(row)을 추가하는데 이용하며, single table insert이나 multi table insert를 수행할 수 있다.

Single table insert : 오직 하나의 테이블이나 뷰에 오직 하나의 행(row)의 값들을 삽입할 수 있다.

Multi table insert : 하나 이상의 테이블로부터 서브 쿼리로 얻은 여러 행(row)을 삽입하는 경우이다.

2.
INSERT INTO 테이블_명 [(컬럼_명1, 컬럼_명2, ...)] VALUES (값1, 값2, ...);

예를 들어, 학생 정보 저장용 테이블에 자료를 입력한다면

--입력시 컬럼과 값은 순서, 갯수, 자료형이 일치해야 한다.
--주의) 한글은 3byte씩 저장되므로 저장 공간의 크기를 확인할 것
INSERT INTO sungjuk (sid, name, kor, eng, mat)
 VALUES (1, '홍길동', 100, 100, 100);
INSERT INTO sungjuk (sid, name, kor, eng, mat)
 VALUES (2, '김길동', 90, 80, 90);
INSERT INTO sungjuk (sid, name, kor, eng, mat)
 VALUES (3, '박길동', 100, 70, 80);
COMMIT;
-->주의) INSERT(입력) 명령 실행후 반드시 commit(제출) 또는 rollback(취소) 선택할 것.

--특정 테이블의 자료 확인
SELECT * FROM sungjuk;

--VARCHAR2(10) 는 한글 3글자만 입력 가능
INSERT INTO sungjuk (sid, name, kor, eng, mat)
 VALUES (4, '대한민국', 100, 70, 80);   
--SQL 오류: ORA-12899: value too large for column "SCOTT"."SUNGJUK"."NAME" (actual: 12, maximum: 10)

--컬럼명과 값은 서로 일치해야 한다
INSERT INTO sungjuk (sid, name, kor, eng)
 VALUES (4, '최길동', 100, 70, 80);   
--SQL 오류: ORA-00913: too many values

--컬럼명과 값은 서로 일치해야 한다
INSERT INTO sungjuk (sid, name, kor, eng, mat)
 VALUES (4, '최길동', 100, 70);   
--SQL 오류: ORA-00947: not enough values

-------------------------------------------
데이터 타입(자료형)

1. 오라클이 제공하는 데이터 타입은 단일 값을 저장하는 스칼라 데이터 타입, 여러 개의 데이터를 저장할 수 있는 컬렉션 데이터 타입 그리고, 컬럼이 다른 테이블 객체를 참조하는 관계 데이터 타입이있다.

2. VARCHAR2
형식 : VARCHAR2(n)
가변 길이 문자 데이터를 저장하며 최대 길이는 4000자이고, 반드시 길이를 명시해야 한다.
NLS(국가별 언어 집합)는 한글과 영문만 가능
VARCHAR 는 최대 2000개 문자를 저장하며 VARCHAR2와는 다르게 VARCHAR(10)로 선언하면 null을 채워 실제로는 10개의 공간을 사용한다. 하지만 VARCHAR2(10)는 필요한 문자까지만 저장하는 variable length이며 최대 4000개 문자까지 저장할 수 있다.

한글 저장용 NVARCHAR2(n) 자료형

3. NUMBER
형식 : NUMBER(P, S)
P(1~38)는 정밀도로 전체 자리수를 나타내며 기본 값이 38이고 S(-84~127)는 소수점 이하의 자릿수이다.
정수나 실수 저장하기 위한 가변길이의 표준 내부 형식이다.

4.DATE
『년/월/일 시:분:초』까지 저장하며, 기본적으로 년/월/일 정보를 출력한다.

5. BLOB
입력되는 데이터가 이미지 유형 등의 이진 데이터를 저장할 수 있는 타입으로 4G 까지 저장 가능하다.

6. ROWNUM : 쿼리의 결과로 나오는 각각의 row들에 대한 순서 값을 나타내는 의사 컬럼이다.

NUMBER 자료형 테스트 ---------------
CREATE TABLE test1 (
 col1 NUMBER       --38자리
 ,col2 NUMBER(3)   --3자리
 ,col3 NUMBER(5,2) --5자리, 소수 이하 2자리
);

INSERT INTO test1 (col1, col2, col3)
 VALUES (123, 123, 123);  --123, 123, 123
SELECT * FROM test1;
INSERT INTO test1 (col1, col2, col3)
 VALUES (123, 123.45, 123.45); --123, 123, 123.45
SELECT * FROM test1;
INSERT INTO test1 (col1, col2, col3)
 VALUES (123, 123.456, 123.456); --123, 123, 123.46
INSERT INTO test1 (col1, col2, col3)
 VALUES (1234, 1234, 1234); --ORA-01438
SELECT * FROM test1;
COMMIT;

--DROP TABLE test1;

VARCHAR2 자료형 테스트 --------------------------
CREATE TABLE test2 (
 --col1 VARCHAR2     --ORA-00906
 col1 VARCHAR2(1)     --1글자(영문, 숫자 기준)
 ,col2 VARCHAR2(10)   --10글자
 ,col3 VARCHAR2(4000) --
);

INSERT INTO test2 (col1, col2, col3)
 VALUES ('T', 'TEST', 'TEST');
INSERT INTO test2 (col1, col2, col3)
 VALUES ('TEST', 'TEST', 'TEST');  --ORA-12899
INSERT INTO test2 (col1, col2, col3)
 VALUES ('T', '홍길동', '홍길동');
INSERT INTO test2 (col1, col2, col3)
 VALUES ('T', '대한민국', '대한민국');  --ORA-12899
COMMIT;

DATE 자료형 테스트 ------------------------------
CREATE TABLE test3 (
 col1 DATE     --년/월/일 시:분:초 저장
 ,col2 DATE
);

--날짜와 시간 정보 모두 저장되므로
--SYSDATE인 경우는 현재 시간까지 저장된다.
INSERT INTO test3 (col1, col2)
 VALUES (SYSDATE, TO_DATE('20120118')); --문자를 날짜형으로 변환
INSERT INTO test3 (col1, col2)
 VALUES (SYSDATE, '20120118'); --문자를 자동 형변환
INSERT INTO test3 (col1, col2)
 VALUES (SYSDATE, SYSDATE);
COMMIT;

SELECT * FROM test3; --년/월/일만 출력됨.
SELECT TO_CHAR(col1, 'YYYY-MM-DD HH24:MI:SS') AS col1
 ,TO_CHAR(col2, 'YYYY-MM-DD HH24:MI:SS') AS col2
 FROM test3; --년-월-일 시:분:초로 출력됨

문제) 오늘 날짜인 경우는 시:분 만 출력하고,
      오늘 날짜가 아닌 경우는 년-월-일만 출력합니다.

DECODE(기준, 값1, 결과1, 값2, 결과2)

if (기준 == 값1)
 결과1
else if (기준 == 값2)
 결과2

DECODE(기준, 값1, 결과1, 결과2)

if (기준 == 값1)
 결과1
else
 결과2

SELECT TO_CHAR(col2, 'YYYY-MM-DD')
        AS col2_1
      , TO_CHAR(col2, 'HH24:MI:SS')
        AS col2_2
      , DECODE( TO_CHAR(col2, 'YYYY-MM-DD')
              , TO_CHAR(SYSDATE, 'YYYY-MM-DD')  
              , TO_CHAR(col2, 'HH24:MI:SS')
                , TO_CHAR(col2, 'YYYY-MM-DD') ) AS col2_3
  FROM test3;

----------------------------------------------
INSA 테이블 분석

CREATE TABLE insa(
        num NUMBER(5) NOT NULL CONSTRAINT insa_pk PRIMARY KEY
       ,name VARCHAR2(20) NOT NULL
       ,ssn  VARCHAR2(14) NOT NULL
       ,ibsaDate DATE     NOT NULL
       ,city  VARCHAR2(10)
       ,tel   VARCHAR2(15)
       ,buseo VARCHAR2(15) NOT NULL
       ,jikwi VARCHAR2(15) NOT NULL
       ,basicPay NUMBER(10) NOT NULL
       ,sudang NUMBER(10) NOT NULL
);

INSERT INTO insa (num, name, ssn, ibsaDate, city, tel, buseo, jikwi, basicPay, sudang)
 VALUES
  (1001, '홍길동', '771212-1022432', '1998-10-11', '서울', '011-2356-4528', '기획부',
   '부장', 2610000, 200000);

COMMIT;

-----------------------------------------------
테이블 생성 및 자료 입력 쿼리 일괄 실행

SQL>@경로명/파일이름.SQL 

-----------------------------------------------
테이블 관리

***1. 새 테이블 생성
CREATE TABLE 테이블이름 (
 컬럼 자료형 [기타제약]
 ,...
);

예를 들어, 회원 정보 저장용 테이블 생성시 
CREATE TABLE members (
  mid NUMBER --고유번호 저장용. 필수.
  ,name NVARCHAR2(10) --한글 10글자까지 가능
  ,tel VARCHAR2(20) --영숫자 20글자까지 가능
  ,email VARCHAR2(30)
);

2. 기존 테이블 구조(컬럼명, 자료형) 확인
SELECT *
  FROM user_tab_columns
  WHERE table_name='테이블명'; --테이블 이름 대문자료 표기할 것.

DESC 테이블이름;

3. 기존 테이블 구조 복사해서 새 테이블 생성 (제약조건은 복사되지 않는다)
CREATE TABLE 새테이블명
AS
SELECT 컬럼리스트 FROM 기존테이블명 WHERE 1=0;

DESC 기존테이블명;
DESC 새테이블명;

**4. 기존 테이블 구조 및 기존 자료 복사해서 새 테이블 생성 및 자료 입력
CREATE TABLE 새테이블명
AS
SELECT 컬럼리스트 FROM 기존테이블명;

SELECT * FROM 기존테이블명;
SELECT * FROM 새테이블명;

insa 테이블을 원본으로 해서 '개발부' 직원들의 정보만 별도의 테이블에 복사
CREATE TABLE development
AS
SELECT num, name, ssn, ibsadate, city, tel, jikwi, basicpay, sudang 
    FROM insa
    WHERE buseo='개발부';
   
SELECT * FROM development;

insa 테이블에서 city 정보만을 별도로 city 테이블에 복사
CREATE TABLE city
AS
SELECT DISTINCT city
  FROM insa;

SELECT * FROM city;

5. 기존 테이블에 새 열 추가

ALTER TABLE 기존테이블명
 ADD (열이름 자료형, ...);
--> 기존 테이블에 데이터가 있는 경우는 새로 만들어진 컬럼의 데이터는 NULL만 채워진다.
--> NULL이 채워진 컬럼에 자료를 채우려면 UPDATE 명령을 이용한다.

6. 기존 테이블에서 기존 열 자료형 변경
ALTER TABLE 기존테이블명
 MODIFY 기존열이름 새로운자료형;
--> 기존 테이블에 데이터가 있는 경우는 새로운자료형이 기존 자료에 적합해야 한다.

7. 기존 테이블에서 기존 열 이름 변경
ALTER TABLE 기존테이블명
 RENAME COLUMN 기존열이름 TO 새열이름;
--> 기존 테이블에 데이터가 있어도 가능하다.

8. 기존 테이블에서 기존 열 삭제
ALTER TABLE 기존테이블명
 DROP (열이름, ...);
--> 기존 테이블에 기존 데이터가 같이 삭제된다. 복구 불가.

9. 기존 테이블 이름 변경
RENAME 기존테이블명 TO 새로운테이블명;

10. 기존 테이블 삭제 (휴지통 기능 있음)
DROP TABLE 테이블이름;
--> 테이블 삭제시 관련 객체(제약조건, 인덱스, 트리거 등)들이 같이 삭제된다.
--> 제약조건에 따라서 삭제 안되는 경우가 있다.

--휴지통에 있는 객체 확인
SELECT *
 FROM recyclebin;

--휴지통에 있는 객체 복원 (테이블명은 휴지통 내에서 부여된 임시 객체명)
FLASHBACK TABLE 테이블명 TO BEFORE DROP;

--휴지통 비우기
PURGE recyclebin;

DROP TABLE 테이블이름 PURGE;

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

----------------------------------------------
데이터베이스 관계

1. 두 테이블 간의 서로 연관된 데이터를 가지고 있는 상태. 두 테이블을 합쳐서 하나의 가상테이블로 검색하는 과정.
 
2. 제약조건 Foreign Key 제약을 지정하면 두 테이블 간에 관계를 맺을 수 있다.

3. 두 테이블 간에 관계가 있는지 확인하는 방법은 테이블에 FK 제약이 지정된 컬럼이 있는지 확인하면 된다.

4. 테이블에는 기본적인 Primary key 제약을 지정하게 된다. 유일한 키 값 지정을 위해서 사용한다.

5. 참조 테이블(PK)의 자료를 참조해서 현재 값을 지정하는 경우 Foreign key 제약을 지정한다. -> 참조값의 범위를 벗어나지 않도록 제약을 건 상태.
예를 들어, 부서 종류를 입력하는 경우 부서명 테이블의 자료를 참조해서 데이터를 입력해야 한다.

6. 제약 조건 확인
USER_CONSTRAINTS 딕셔너리 확인

--현재 소유자가 지정한 제약 조건 전체
SELECT *
 FROM user_constraints;

--CONSTRAINT_TYPE
C : CHECK
P : PRIMARY KEY
R : FOREIGN KEY (관계 설정)
U : UNIQUE

--특정 테이블에 지정한 제약 조건 확인 (테이블 이름은 대문자로 표기)
SELECT *
 FROM user_constraints
 WHERE TABLE_NAME='EMPLOYEES';

--특정 테이블에 지정한 특정 외래키 제약에 대한 참조 테이블 확인
SELECT TABLE_NAME
  FROM user_cons_columns
    WHERE constraint_name=(SELECT R_CONSTRAINT_NAME
 FROM user_constraints
 WHERE TABLE_NAME='EMPLOYEES'
    AND CONSTRAINT_NAME='EMP_JOB_FK');

--특정 테이블에 지정한 모든 외래키 제약에 대한 참조 테이블 확인
SELECT TABLE_NAME
  FROM user_cons_columns
    WHERE constraint_name IN (   
SELECT R_CONSTRAINT_NAME
 FROM user_constraints
 WHERE TABLE_NAME='EMPLOYEES'
    AND CONSTRAINT_TYPE='R');   

--------------------------------------------
JOIN

1. 조인을 사용하면 테이블 간의 논리적 관계를 기준으로 둘 이상의 테이블에서 데이터를 검색할 수 있다.

INNER JOIN - 공통 값을 가지는 조건을 만족하는 ROW만 출력
OUTER JOIN - 공통 값을 가지는 조건을 만족하는 경우 외에도 출력

EQUI JOIN - =(equal) 연산자를 이용한 조건을 사용한 JOIN
NON-EQUI JOIN - =(equal) 연산자 외의 연산자를 이용한 조건을 사용한 JOIN

SELF JOIN - 자기 자신을 두 개의 가상 테이블로 만들어서 JOIN하는 방법. 별칭 사용.

ANSI JOIN - JOIN 구문을 오라클 방법이 아니라 표준적인 방법으로 작성한 것.

2. employees, departments, job_history, jobs, locations, countries, regions 테이블에서 관계 분석

3.EQUI JOIN
두 개 이상의 테이블에 관계되는 컬럼들의 값들이 일치하는 경우에 사용하는 가장 일반적인 join 형태로 WHERE 절에 '='(등호)를 사용한다. 흔히 PRIMARY KEY, FOREIGN KEY 관계를 이용하며 오라클에서는 NATURAL JOIN이 EQUI JOIN과 동일하다. 또는 USING 절을 사용하여 EQUI JOIN과 동일한 결과를 출력 한다.

형식-1
SELECT 테이블_명1.컬럼_명, 테이블_명2.컬럼_명
 FROM 테이블_명1, 테이블_명2
 WHERE 테이블_명1.컬럼_명1=테이블_명2.컬럼_명1;

형식-ANSI SQL
SELECT 테이블_명1.컬럼_명, 테이블_명2.컬럼_명
 FROM 테이블_명1 JOIN 테이블_명2
 ON 테이블_명1.컬럼_명1=테이블_명2.컬럼_명1;

SELECT * FROM departments;
-->27개 부서아이디(부서명) 존재, PK

SELECT DISTINCT department_id FROM employees;
-->11개 부서아이디 존재, FK, departments 테이블에 존재하는 27개 부서아이디 중에서 11개 참조하는 중이다.
-->department_id 컬럼이 공통컬럼이다. 조인 조건에서 사용 가능.

SELECT e.first_name, e.last_name, e.phone_number
      , d.department_name
  FROM departments d, employees e
  WHERE d.department_id=e.department_id;
--> 106명
--> 1명은 department_id가 NULL인 상태이므로 조인조건에 맞지 않는다.

employees, departments 테이블. first_name이 'Nancy', last_name이 'Greenberg'인 직원의 department_name을 같이 출력.

형식-1
SELECT employees.first_name, employees.last_name, departments.department_name
 FROM employees, departments
 WHERE employees.department_id = departments.department_id
  AND 조건 추가;

SELECT e.first_name, e.last_name, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id
  AND e.first_name='Nancy'
  AND e.last_name='Greenberg';

형식-ANSI SQL
SELECT employees.first_name, employees.last_name, departments.department_name
 FROM employees JOIN departments
 ON employees.department_id = departments.department_id;

SELECT e.first_name, e.last_name, d.department_name
 FROM employees e JOIN departments d
 ON e.department_id = d.department_id
 WHERE e.first_name='Nancy'
  AND e.last_name='Greenberg';

employees, jobs 테이블. job_title이 'Finance Manager'인 직원 정보 출력.
형식-1
SELECT e.first_name, e.last_name, j.job_title
 FROM employees e, jobs j
 WHERE e.job_id = j.job_id
  AND j.job_title='Finance Manager';

형식-ANSI SQL
SELECT e.first_name, e.last_name, j.job_title
 FROM employees e JOIN jobs j
 ON e.job_id = j.job_id
 WHERE j.job_title='Finance Manager';

4. Non-Equi Join
- Join 조건에서 = 연산이 아닌 모든 경우를 말한다.
- 비교 대상인 컬럼 간에 연관성이 없어도 가능하다.

SELECT 컬럼리스트(별칭 사용 필요)
 FROM 테이블1 별칭1, 테이블2 별칭2
 WHERE 별칭1.컬럼 <= 별칭2.컬럼;

SELECT 컬럼리스트(별칭 사용 필요)
 FROM 테이블1 별칭1, 테이블2 별칭2
 WHERE 별칭1.컬럼 >= 별칭2.컬럼;

HR 스키마에서 employees.salary의 금액이 10000~20000 범위에 들어오는 직원의 정보 출력. jobs.job_title, departments.department_name 이 같이 출력되도록 한다.

SELECT e.first_name, e.last_name, e.salary, j.job_title, d.department_name
 FROM employees e, jobs j, departments d
 WHERE e.job_id=j.job_id
  AND e.department_id=d.department_id
  AND e.salary BETWEEN 10000 AND 20000;

HR 스키마에서 employees.salary의 금액이 특정 직무(jobs.job_title이 'Finance Manager')의 jobs.max_salary의 금액(16000)보다 큰 경우만 출력.
-->3명

--서브 쿼리
SELECT e.first_name, e.last_name, e.salary
 FROM employees e
 WHERE e.salary>=(SELECT max_salary FROM jobs WHERE job_title='Finance Manager');

--조인 쿼리
SELECT e.first_name, e.last_name, e.salary --, j.job_title, j.max_salary
 FROM employees e, jobs j
 WHERE e.salary>=j.max_salary
  AND j.job_title='Finance Manager';

HR 스키마에서 employees.salary의 금액이 특정 직무(jobs.job_title이 'Finance Manager')의 jobs.min_salary의 금액(8200)보다 작은 경우만 출력.

SELECT e.first_name, e.last_name, e.salary --, j.job_title, j.min_salary
 FROM employees e, jobs j
 WHERE e.salary<=j.min_salary
  AND j.job_title='Finance Manager';

5. Outer Join
- 양쪽 테이블에 동일한 값이 존재하는 경우만 출력되는 경우는 Equi Join(Inner Join)이고,
동일한 값이 없어도 한 쪽 테이블의 자료는 모두 출력되는 경우는 Outer Join입니다.

--LEFT OUTER JOIN(테이블2가 NULL이 출력되는 상태)
SELECT 컬럼리스트
 FROM 테이블1 별칭1, 테이블2 별칭2
 WHERE 별칭1.컬럼 = 별칭2.컬럼(+);

--RIGHT OUTER JOIN(테이블1이 NULL이 출력되는 상태)
SELECT 컬럼리스트
 FROM 테이블1 별칭1, 테이블2 별칭2
 WHERE 별칭1.컬럼(+) = 별칭2.컬럼;

--ANSI LEFT OUTER JOIN(테이블1의 자료는 모두 출력)
SELECT 컬럼리스트
 FROM 테이블1 별칭1 LEFT OUTER JOIN 테이블2 별칭2
 WHERE 별칭1.컬럼 = 별칭2.컬럼;

HR 스키마에서 employees 테이블에 first_name이 'Kimberely'인 직원은 department_id가 NULL인 상태이다.
-> INNER JOIN을 하게 되면 이 직원은 명단에 출력되지 않는다.
SELECT * FROM employees;
--> 107명

SELECT e.first_name, e.last_name, e.department_id, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id;
-->106명

SELECT e.first_name, e.last_name, e.department_id, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id(+);
-->107명

--서브쿼리인 경우
SELECT first_name, last_name
    , e.department_id
    , (SELECT department_name
  FROM departments
  WHERE department_id=e.department_id) AS department_name
  FROM employees e;

SELECT e.first_name, e.last_name, e.department_id, d.department_name
 FROM employees e FULL OUTER JOIN departments d
 ON e.department_id = d.department_id;

6. Self Join
- 일반적인 Join은 두 개의 서로 다른 테이블을 대상으로 Join을 하지만,
Self Join은 자기 자신을 가상의 테이블(테이블1, 테이블2가 모두 자기 자신인 상태)로 생각하고 Join을 진행하는 것.
- 별칭 사용 필수

SELECT 컬럼리스트
 FROM 테이블 별칭1, 테이블 별칭2
 WHERE 별칭1.컬럼 = 별칭2.컬럼;

SELECT 컬럼리스트
 FROM 테이블 별칭1, 테이블 별칭2
 WHERE 별칭1.컬럼 <= 별칭2.컬럼;

SELECT 컬럼리스트
 FROM 테이블 별칭1, 테이블 별칭2
 WHERE 별칭1.컬럼 >= 별칭2.컬럼;

HR 스키마에서 특정 직원(employees.first_name이 'Jonathon', employees.last_name이 'Taylor' 인 직원)의
employees.department_id와 같은 department_id를 가진 직원 정보 출력.
SELECT e2.first_name, e2.last_name, e2.department_id
 FROM employees e1, employees e2
 WHERE e1.department_id = e2.department_id
 AND e1.first_name='Jonathon'
 AND e1.last_name='Taylor';

HR 스키마에서 특정 직원(employees.first_name이 'Janette', employees.last_name이 'King' 인 직원)의
employees.salary와 같은 salary를 가진 직원 정보 출력.
SELECT e2.first_name, e2.last_name, e2.salary
 FROM employees e1, employees e2
 WHERE e1.salary = e2.salary
 AND e1.first_name='Janette'
 AND e1.last_name='King';

HR 스키마 employees 테이블에서 상급자, 부하 직원 정보 출력.
SELECT e1.employee_id, e1.first_name, e1.last_name
 , e2.employee_id, e2.first_name, e2.last_name, e2.manager_id
 FROM employees e1, employees e2
 WHERE e1.employee_id = e2.manager_id
 ORDER BY e1.employee_id;

HR 스키마 employees 테이블에서 특정 직원(first_name이 'Gerald', last_name이 'Cambrault'인 직원)의 부하 직원 정보 출력.
SELECT e1.employee_id, e1.first_name, e1.last_name
 , e2.employee_id, e2.first_name, e2.last_name, e2.manager_id
 FROM employees e1, employees e2
 WHERE e1.employee_id = e2.manager_id
 AND e1.first_name='Gerald'
 AND e1.last_name='Cambrault';

--------------------------------------------
JOIN 문제 풀이

1. departments, employees 테이블. 부서명(department_name), 부서장의 이름(first_name, last_name) 출력. 조인 쿼리 이용.
--> departments 테이블의 manager_id를 employees 테이블의 employee_id와 비교

--서브 쿼리의 경우
SELECT d.department_name
 --, 부서장이름 -->employees 테이블 검색 -> manager_id와 employee_id를 비교
 FROM departments d;

SELECT d.department_name
 , ( SELECT first_name FROM employees WHERE employee_id=d.manager_id  ) AS first_name
 , ( SELECT last_name FROM employees WHERE employee_id=d.manager_id ) AS last_name
 FROM departments d;

--JOIN 쿼리의 경우
SELECT d.department_name, e.first_name, e.last_name
 FROM departments d, employees e
 WHERE d.manager_id = e.employee_id;

--위 결과에서 특정부서('IT')만 출력.
SELECT d.department_name, e.first_name, e.last_name
 FROM departments d, employees e
 WHERE d.manager_id = e.employee_id
 AND department_name='IT';

2. employees, jobs 테이블. 'Neena', 'Kochhar' 직원의 직무명(job_title)까지 출력. 조인 쿼리 이용.

--서브 쿼리 이용한 경우

--JOIN 쿼리 이용한 경우

3. employees, jobs 테이블. 직위명(job_title)별 최소급여(min_salary)를 받는 직원 출력. 조인 쿼리 이용.
-->3명

SELECT e.first_name, e.last_name, j.job_title, j.min_salary
 FROM employees e, jobs j
 WHERE e.job_id = j.job_id
  AND e.salary = j.min_salary;

SELECT e.first_name, e.last_name, j.job_title, j.min_salary
 FROM employees e JOIN jobs j
  ON e.job_id = j.job_id
 WHERE e.salary = j.min_salary;

4. jobs, employees 테이블. 직위명(job_title)별 직원의 수 출력. 조인 쿼리 이용.

--서브 쿼리 이용한 경우

--JOIN 쿼리 이용한 경우

5. employees, departments 테이블에서 'David', 'Austin' 직원이 속한
부서명(department_name), 부서장(manager)이름(first_name, last_name) 출력. 조인 쿼리 이용.

--서브 쿼리 이용한 경우

--JOIN 쿼리 이용한 경우

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

6. employees 테이블에서 'Steven', 'King' 직원의 부하 직원 출력. 자체 조인.

SELECT e2.first_name, e2.last_name
 FROM employees e1, employees e2
 WHERE e1.employee_id = e2.manager_id
  AND e1.first_name='Steven'
  AND e1.last_name='King';

7. employees, jobs, job_history 테이블. employee_id가 101인 직원의 잡히스토리(first_name, last_name, start_date, end_date, job_title) 출력.

8. employees 테이블에서 부하 직원이 없는 직원 출력.
SELECT e1.employee_id, e1.first_name, e1.last_name
  FROM employees e1, employees e2
  WHERE e1.employee_id=e2.manager_id(+)
  AND e2.manager_id IS NULL;

9. employees 테이블에서 특정 직원(first_name이 'Gerald', last_name이 'Cambrault'인 직원)의 salary보다 급여를 더 많이 받는 직원 정보 출력.

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