190415

------------------------------------------------------------ 첫 번째 트랜잭션이 시작됨
 
-- 첫 번째 트랜잭션 내의 DML 작업 1
insert into store_users
values ('kim', '김유신', 'zxcv1234', '010-2222-2222', 'kimg@naver.com', 10, sysdate);
 
-- 첫 번째 트랜잭션 내의 DML 작업 2
update STORE_USERS
set USER_POINT = 500
where user_id = '1';
 
-- 첫 번째 트랜잭션 내의 DML 작업 3
update STORE_USERS
set USER_POINT = 0
where user_id = 'kim';
 
SELECT * FROM STORE_USERS;
 
-- 첫 번째 트랜잭션 내의 DML 작업 1, DML 작업 2, DML 작업 3을 영구적으로 DB에 반영한다.
commit;
--------------------------------------------------------- 첫 번째 트랜잭션 종료
--------------------------------------------------------- 두 번째 트랜잭션 시작
-- 두 번째 트랜잭션의 DML 작업 1
DELETE  FROM STORE_USERS WHERE USER_ID = '2';
 
UPDATE STORE_USERS SET USER_PHONE = '010-1234-5678';
 
INSERT into store_users VALUES('hong', '홍길동', 'zxcv1234', '010-3333-2222', 'hong@naver.com', 0, SYSDATE);
 
-- 두 번째 트랜잭션 내에서 실행한 DML 작업 1, DML 작업 2의 DB 반영을 취소시킨다.
ROLLBACK;
 
--------------------------------------------------------- 두 번째 트랜잭션 종료
--------------------------------------------------------- 세 번째 트랜잭션 시작
 
-- 뷰 생성하기
-- CREATE VIEW 뷰의 이름
-- AS SELECT ~
--     FROM ~
--     WHERE ~
 
CREATE OR REPLACE VIEW emp_info_view
AS
    SELECT A.EMPLOYEE_ID AS ID, 
    A.FIRST_NAME || ', ' || A.LAST_NAME AS FULL_NAME, 
    A.SALARY, 
    A.SALARY * 12 AS ANNUAL_SALARY, 
    A.DEPARTMENT_ID AS DEPT_ID, 
    B.DEPARTMENT_NAME AS DEPT_NAME, 
    C.GRA AS SALARY_GRADE
    FROM EMPLOYEES A, DEPARTMENTS B, JOB_GRADES C -- 복합 뷰
    WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
    AND SALARY >= C.LOWEST_SALARY
    AND SALARY <= C.HIGHEST_SALARY;
    WITH READ ONLY; -- SELECT만 가능하도록
    
SELECT * FROM EMP_INFO_VIEW;
 
SELECT *
FROM EMP_INFO_VIEW
WHERE DEPT_ID = 60;
 
-- 뷰의 삭제
-- drop view 뷰 이름;
-- 뷰는 데이터를 포함하고 있찌 않기 때문에 뷰를 삭제하더라도 데이터는 지워지지 않는다.
DROP VIEW EMP_INFO_VIEW;
 
SELECT * FROM EMP_INFO_VIEW;
 
SELECT * FROM EMPLOYEES;
 
-- 인라인 뷰 작성하기
-- 부서별 사원수를 조회하기
SELECT DEPARTMENT_ID, COUNT(*) cnt
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
SELECT a.dept_id, b.department_name, a.cnt
from (select DEPARTMENT_id as dept_id, count(*) cnt -- dept_id // 연산식은 꼭 별칭을 주는 습관을 들이도록
       from EMPLOYEES
       GROUP BY DEPARTMENT_id) A, DEPARTMENTS B
where a.dept_id = b.department_id; -- 밖에서도 별칭으로 활용해야
 
 
--rownum
select rownum, department_id, department_name
from departments;
 
-- Top-N 분석하기
-- 급여를 가장 많이 받는 직원 3명 조회하기
SELECT ROWNUM, ID, NAME, SALARY -- 별칭 그대로 써야
FROM (SELECT employee_id as id, first_name as name, salary
         FROM EMPLOYEES
         ORDER by salary desc)
WHERE ROWNUM <= 3;
 
-- cf
 SELECT ROWNUM, employee_id, first_name, salary
 FROM EMPLOYEES
 where rownum <= 3
 ORDER BY salary DESC; -- 정렬이 마지막으로 되므로
 
 -- 가장 최근에 입사한 직원 5명 조회하기
 -- 사원 아이디, 이름, 부서명, 입사일 조회
 SELECT ROWNUM, X.EMPLOYEE_ID, X.FIRST_NAME, X.DEPARTMENT_NAME, X.HIRE_DATE
 FROM (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME, E.HIRE_DATE
          FROM EMPLOYEES E, DEPARTMENTS D
          WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
          ORDER BY E.HIRE_DATE DESC) X
 WHERE ROWNUM <= 5;
 
 -- 80번 부서에서 급여를 가장 많이 받는 직원
 SELECT EMPLOYEE_ID
 FROM (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 80)
 WHERE ROWNUM <= 1;
 
 -- Sundita와 같은 해에 입사한 사원 중에서 급여를 가장 많이 받는 직원 3명 구하기
SELECT *
FROM (SELECT * 
         FROM EMPLOYEES
         WHERE TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE FIRST_NAME = 'Sundita')
ORDER BY SALARY DESC)
WHERE ROWNUM <= 3;
 
-- Steven King에게 보고하는 사원 중에서 가장 최근에 입사한 사원 3명 구하기
SELECT
FROM
WHERE
 
SELECT * 
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID
AND MANAGER.EMPLOYEE_ID = 'Steven King'
 
MANAGER.FIRST_NAME || MANAGER.LAST_NAME = 
 
ORDER BY WORKER.HIRE_DATE DESC
 
WHERE ROWNUM <= 3;
 
-- 위의 쿼리는.. 조인으로 하려다 망한 코드
 
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM (SELECT * 
         FROM EMPLOYEES
         WHERE MANAGER_ID = (SELECT EMPLOYEE_ID
                                        FROM EMPLOYEES
                                        WHERE FIRST_NAME = 'Steven' AND LAST_NAME = 'King')
         ORDER BY HIRE_DATE DESC)
         WHERE ROWNUM <=3 ;
 
-- 분석함수 사용하기
-- ROW_NUMBER()는 행마다 순번을 부여한다.
-- 같은 순번을 가진 행이 존재하지 않는다. 표시되지 않는 순위가 있다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, RANK() OVER (ORDER BY SALARY DESC)
FROM EMPLOYEES; 
-- DENSE: 1, 2, 2, 3
-- DENSE 없을 때: 1, 2, 2, 4
-- DENSE_RANK()는 행마다 순위를 부여한다.
-- 같은 순위를 가진 행이 존재한다. 표시되지 않는 순위가 없다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, RANK() OVER (ORDER BY SALARY DESC)
FROM EMPLOYEES;
 
-- 급여순으로 정렬했을 때 5위~10위 사이에 속하는 직원 조회하기
SELECT RN, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROWNUM AS RN, EMPLOYEE_ID, FIRST_NAME, SALARY   -- 급여순으로 정렬된 결과에 순번 부여
         FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY                 -- 급여순으로 내림차순 정렬
                  FROM EMPLOYEES
                  ORDER BY SALARY DESC))
WHERE RN >= 5 AND RN <= 10;
 
-- 급여순으로 정렬했을 때 11위~15위 사이에 속하는 직원 조회하기 (위의 것보다 추천함)
SELECT RN, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) RN, EMPLOYEE_ID, FIRST_NAME, SALARY
         FROM EMPLOYEES)
         WHERE RN >= (3-1)*5+1 AND RN <= 3*5; -- !!중요!!
 
 
-- 뉴스정보를 저장하는 테이블을 작성하기
-- 뉴스번호, 뉴스제목, 뉴스카테고리, 작성자, 내용, 사진파일명, 언론사이름, 작성일자, 최종수정일자
-- 뉴스번호는 행을 대표하는 컬럼이다.
-- 뉴스제목, 카테고리, 작성자, 내용, 언론사이름은 null을 허용하지 않는다.
-- 작성일자와 최종수정일자는 기본값으로 현재날짜가 저장된다.
CREATE TABLE sample_news(
    NEWS_NO             NUMBER(8,0) PRIMARY KEY,
    NEWS_TITLE           VARCHAR2(100) NOT NULL,
    NEWS_CATEGORY    VARCHAR2(200) NOT NULL,
    NEWS_WRITER        VARCHAR2(100) NOT NULL,
    NEWS_CONTENTS    VARCHAR2(1000) NOT NULL,
    NEWS_PICNAME      VARCHAR2(200),
    NEWS_PRESS          VARCHAR2(200) NOT NULL,
    NEWS_CREATE_DATE DATE DEFAULT SYSDATE,
    NEWS_EDIT_DATE DATE DEFAULT SYSDATE
);
 
-- 뉴스정보에 대한 댓글을 저장하는 테이블을 작성하기
-- 댓글번호, 작성자, 내용, 작성일자, 추천수, 뉴스번호
-- 댓글번호는 행을 대표하는 컬럼이다.
-- 작성자, 내용, 뉴스번호는 null을 허용하지 않는다.
-- 작성일자는 기본값으로 현재날짜가 저장된다.
-- 추천수는 기본값으로 0이 저장된다.
-- 뉴스번호는 위에서 생성한 뉴스정보 테이블의 뉴스번호를 참조한다.
CREATE TABLE sample_replies (
    replies_NO                        NUMBER(8,0) PRIMARY KEY,
    replies_WRITER                   VARCHAR2(300) NOT NULL,
    replies_CONTENTS               VARCHAR2(4000) NOT NULL,
    replies_CREATE_DATE DATE    DEFAULT SYSDATE,
    replies_RECOMMAND           NUMBER(8,0) DEFAULT 0,
    NEWS_NO                         NUMBER(8,0) REFERENCES sample_news(NEWS_NO)
);
 
-- 뉴스정보테이블의 뉴스번호에 사용할 일련번호용 시퀀스를 생성하시오
-- 시작값을 100000으로 지정하고, 1씩 증가시키는 시퀀스
CREATE SEQUENCE sample_news_seq START WITH 100000 INCREMENT by 1;
 
-- 뉴스댓글테이블의 댓글번호에 사용할 일련번호용 시퀀스를 생성하시오.
CREATE SEQUENCE sample_replies_seq ;
 
-- 뉴스정보 테이블에 임의의 뉴스 정보를 데이터를 추가하는 쿼리를 작성하고, 뉴스 정보를 저장하시오.
INSERT INTO sample_news 
VALUES(0, '제목', '카테고리', '작자', '내용', '사진명', '언론사', SYSDATE, SYSDATE);
 
INSERT INTO sample_news (news_no, news_title, news_category, news_writer, news_contents, news_press)
VALUES(sample_news_seq.nextval, '제목제목', '카테고리고리', '작자작자', '내용내용', '언론사언론');
 
COMMIT;
 
SELECT * from sample_news;
 
-- 위에서 저장한 뉴스 정보에 대한 댓글 정보를 추가하는 쿼리를 작성하고, 댓글 정보를 저장하시오.
INSERT into SAMPLE_replies
VALUES (1, '작자', '내용', SYSDATE, 1, 0);
 
INSERT into SAMPLE_replies (replies_no, replies_writer, replies_contents, news_no)
VALUES (sample_replies_seq.nextval, '작자예용', '내용내용!', 100000);
 
COMMIT;
 
SELECT * FROM sample_replies;
 
-- 1800번 위치에 근무하는 사원의 아이디, 이름, 직종아이디, 부서아이디, 부서이름을 조회하기
-- join
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM LOCATIONS L, EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID
AND L.LOCATION_ID = 1800;
 
-- 이름이 'A'나 'a'로  시작하는 사원의 수를 조회하기
-- like
SELECT COUNT(*)
FROM EMPLOYEES
WHERE FIRST_NAME LIKE UPPER('A%');
 
-- 모든 부서의 아이디, 부서이름, 부서별 사원수를 조회하기
-- group by, inline view
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, COUNT(*)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME;
 
SELECT a.department_id, b.department_name, a.cnt
FROM (SELECT DEPARTMENT_id, count(*) cnt
         FROM EMPLOYEES
         group by DEPARTMENt_id) A, DEPARTMENTS B
where a.DEPartment_id=b.department_id
order by a.department_id asc;
 
-- 10번부서와 20번 부서에서 근무하는 사원들의 업무를 조회하기
-- distinct
SELECT DISTINCT(JS.JOB_TITLE)
FROM JOBS JS, EMPLOYEES E
WHERE JS.JOB_ID = E.JOB_ID
AND DEPARTMENT_ID IN (10, 20);
 
-- Administration  및 Executive 부서에 근무하는 직종 아이디별 사원수를 조회하기
-- subquery, group by
SELECT job_id, count(*)
FROM (SELECT js.job_id
         FROM DEPARTMENTS D, JOBS JS, EMPLOYEES E
         WHERE D.DEPARTMENT_NAME IN ('Administration', 'Executive')
         AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
         AND E.JOB_ID = JS.JOB_ID)
GROUP BY JOB_Id;
 
SELECT job_id, count(*)
FROM EMPLOYEES
where DEPARTMENT_ID in (SELECT DEPARTMENT_ID
                                  from DEPARTMENTS
                                  where DEPARTMENT_NAME in ('Administration', 'Executive'))
group by job_id;
 
-- 입사연도별 사원수를 조회했을 때 입사한 사원수가 가장 많은 년도 3개와 입사한 사원수 조회하기
-- group by, Top-N 분석
SELECT ROWNUM, hire_year, cnt
from (SELECT TO_CHAR(HIRE_DATE, 'yyyy') hire_year, count(*) cnt
FROM EMPLOYEES
group by to_char(hire_date, 'yyyy')
ORDER by cnt desc)
where rownum <= 3;
 
-- 관리자별 사원수를 조회했을 때 관리자의 아이디, 관리자의 이름, 사원수를 조회하기
-- group by, self join, inline view
SELECT worker.manager_id, manager.first_name, count(*)
FROM EMPLOYEES worker, EMPLOYEES manager
WHERE worker.MANAGER_ID = MANAGER.employee_id
GROUP BY worker.MANAGER_ID, manager.first_name
order by worker.manager_id;
 
-- 급여등급별 사원수를 조회해서 사원수가 많은 순으로 조회하기
-- group by, order by
SELECT b.gra, count(*) cnt
from EMPLOYEES a, JOB_GRADES B
where a.SALARY >= B.LOWEST_SALARY and A.SALARY <= B.HIGHEST_SALARY
group by b.gra
order by cnt desc;
 
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여를 조회하기
-- subquery
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
where department_id = (SELECT department_id
                              from EMPLOYEES
                              where FIRST_NAME = 'Allan');
                              
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여, 급여등급을 조회하기
-- subquery, 비등가 조인
 
SELECT * from JOB_GRADES;
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여, 부서 아이디, 부서이름을 조회하기
-- subquery, join
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, b.gra
FROM EMPLOYEES A, JOB_GRADES B
where DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
and A.salary >= b.lowest_salary and a.salary <= b.highest_salary;
                                  
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여를 조회하고, 급여를 가장 많이 받는 사원
-- 3명을 조회하기
-- subquery, inline view, top-n 분석
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, SALARY
from EMPLOYEES
where DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
and ROWNUM <= 3;
 
-- 'Allan'과 같은 부서에 근무하는 사원들을 급여등급별로 사원수를 조회하기
-- subquery, 비등가조인, group by
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
where E.SALARY >= JG.LOWEST_SALARY AND E.SALARY <= JG.HIGHEST_SALARY
AND DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
GROUP BY JG.GRA;
                                  
 
-- 'Allan'과 같은 부서에 근무하는 사원들의 급여를 10%인상시키기
-- subquery, update
UPDATE (SELECT *
            FROM EMPLOYEES
            WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                               FROM EMPLOYEES
                                               WHERE FIRST_NAME = 'Allan'))
SET SALARY = SALARY * 1.1;