SQL 2020-06-08 : rownum, subquery, ceil, view

rownum만 쓸 땐 hidden column으로 이미 만들어져 있으니 바로 사용 가능

rownum을 가공하여 재처리 후에 쓸 땐 만든 뒤에 inline으로 활용해야 사용 가능

 

 

CREATE 만 써도 되지만 이미 존재할 경우 에러발생.

없으면 만들고 있으면 대체하라 는 의미에서 CREATE OR REPLACE 라고 작성.

with read only를 문장 맨 뒤에 넣으면 view를 통해서는 수정 불가

with check option을 문장 맨 뒤에 넣으면 VIEW 를 통해서 접근 가능한 데이터만 수정 가능

 

subquery

SELECT ~ FROM table WHERE (SELECT ~)

inline

SELECT ~ FROM (SELECT ~)

 

ceil 올림

ceil(rownum/3) as page 를 통한 페이징 기법

3은 한 페이지에 해당하는 레코드 수

select * from(
 
select rownum as rn, ceil(rownum/3) as page, 사원명, 급여 
    from (SELECT last_name AS 사원명, nvl(salary,0) AS 급여 FROM employees ORDER BY 급여 DESC) 
 
    ) where page = 1;
/*ex1) 사원테이블에서 부서가 90인 사원들을 v_view1으로 뷰테이블을 만드시오
     (사원ID, 사원이름, 급여, 부서ID만 추가)*/
CREATE OR REPLACE VIEW v_view1 AS     
    SELECT employee_id, last_name, salary, department_id
        FROM employees WHERE department_id=90;     
 
 
 
 
/*[문제1] 사원테이블에서 급여가 5000 이상 10000 이하인 사원들만 v_view2으로 뷰를 만드시오. 
(사원ID, 사원이름, 급여, 부서ID)*/
CREATE OR REPLACE VIEW v_view2 (사원ID, 사원이름, 급여, 부서ID) AS
    SELECT employee_id, last_name, salary, department_id 
        FROM employees WHERE salary BETWEEN 5000 AND 10000 ORDER BY salary;
 
CREATE OR REPLACE VIEW v_view2 AS
    SELECT employee_id AS 사원ID, last_name AS 사원이름, salary AS 급여, department_id AS 부서ID 
        FROM employees WHERE salary BETWEEN 5000 AND 10000 ORDER BY salary;
 
SELECT * FROM v_view2;
--닉네임을 미리 붙이던 뷰 생성시 붙이던 상관은 없으나
--미리 붙이는게 SELECT 문의 유효성을 체크한 뒤에 뷰 생성문만 추가하면 되니까 더 편한 것 같다.
 
 
 
 
/*ex2) v_view2 테이블에서 103사원의 급여를 9000.00에서 12000.00으로 수정하시오*/
UPDATE v_view2 SET salary =12000 WHERE employee_id=103; 
--103번 사원은 범위를 벗어나서 사라진다. 자동으로 처리된다.
 
 
 
 
/*[문제2] 사원테이블과 부서테이블에서 사원번호, 사원명, 부서명을 v_view3로 뷰 테이블을 만드시오
조건1) 부서가 10, 90인 사원만 표시하시오
조건2) 타이틀은 사원번호, 사원명, 부서명으로 출력하시오
조건3) 사원번호로 오름차순 정렬하시오*/
CREATE OR REPLACE VIEW v_view3 AS
    SELECT employee_id AS 사원번호, last_name AS 사원명, department_name AS 부서명 
        FROM employees JOIN departments USING(department_id) 
            WHERE department_id in(10,90) ORDER BY employee_id;
SELECT * FROM v_view3;        
 
 
 
 
/*[문제3] 부서ID가 10, 90번 부서인 모든 사원들의 부서위치를 표시하시오
조건1) v_view4로 뷰 테이블을 만드시오
 조건2) 타이틀을 사원번호, 사원명, 급여, 입사일, 부서명, 부서위치(city)로 표시하시오
 조건3) 사원번호 순으로 오름차순 정렬하시오
 조건4) 급여는 백 단위 절삭하고, 3자리 마다 콤마와 '원'을 표시하시오
 조건5) 입사일은  '2004년 10월 02일' 형식으로 표시하시오        */
CREATE OR REPLACE VIEW v_view4 AS
    SELECT employee_id AS 사원번호, last_name AS 사원명, to_char(trunc(salary, -3),'9,999,999')||'원' AS 급여, 
    to_char(hire_date,'yyyy"년" mm"월" dd"일"') AS 입사일, department_name AS 부서명, city AS 부서위치
        FROM employees JOIN departments USING (department_id) JOIN locations USING (location_id) 
            WHERE department_id in(10,90) ORDER BY employee_id asc;
SELECT * FROM v_view4;
 
 
 
 
/*ex3) 뷰에 제약조건달기
사원테이블에서 업무ID  'IT_PROG'인 사원들의 사원번호, 이름, 업무ID만 v_view5 뷰 테이블을 작성하시오.
단 수정 불가의 제약조건을 추가 하시오*/
CREATE OR REPLACE VIEW v_view5 AS
    SELECT employee_id, last_name, job_id 
        FROM employees WHERE job_id='IT_PROG' with read only;
SELECT * FROM v_view5;
 
 
 
 
/*ex4) 뷰에 제약조건 달기
사원테이블에서 업무ID 'IT_PROG'인 사원들의 사원번호, 이름, 이메일, 입사일, 업무ID만 v_view6 뷰 테이블을 작성하시오, 
단 업무ID가 'IT_PROG'인 사원들만 추가, 수정할 수 있는 제약조건을 추가하시오*/
CREATE OR REPLACE VIEW v_view6 AS
SELECT employee_id, last_name, email, hire_date, job_id 
    FROM employees WHERE job_id='IT_PROG' with check option;
SELECT * FROM v_view6;
 
insert into v_view6(employee_id, last_name, email, hire_date, job_id)
values(500,'kim','candy','2004-01-01','Sales'); 
/*→ ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
IT_PROG 아니면 추가할 수 없음*/
 
UPDATE v_view6 SET job_id='Sales' WHERE employee_id=103;
/*→ ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
view자체의 수정을 야기하므로 불가*/
 
insert into v_view6(employee_id, last_name, email, hire_date, job_id)
values(500,'kim','candy','2004-01-01','IT_PROG');
--IT_PROG를 넣는것이므로 가능
 
SELECT * FROM v_view6;
delete FROM v_view6;
/*- error
ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found
무결성 제약조건(HR.DEPT_MGR_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다*/
 
 
 
 
/*[문제4]
테이블명 : bookshop
isbn      varchar2(10)  기본키(제약조건명:PISBN)
title       varchar2(50)  널값 허용X (제약조건명:CTIT) --책제목
author    varchar2(50)  -- 저자 
price      number      -- 금액
company  varchar2(30)  -- 출판사*/
 
CREATE TABLE bookshop
(
    isbn       VARCHAR2(10)    constraint PISBN primary key, 
    title      VARCHAR2(50)    constraint CTIT NOT NULL, 
    author     VARCHAR2(50)    NULL, 
    price      NUMBER          NULL, 
    company    VARCHAR2(30)    NULL 
);
 
/*데이터
is001   자바3일완성           김자바  25000   야메루출판사
pa002  JSP달인되기           이달인  28000   공갈닷컴
or003  오라클무작정따라하기   박따라  23500   야메루출판사*/
insert into bookshop values('is001', '자바3일완성', '김자바',25000,'야메루출판사');
insert into bookshop values('pa002', 'JSP달인되기', '이달인',28000,'공갈닷컴');
insert into bookshop values('or003', '오라클무작정따라하기', '박따라',23500,'야메루출판사');
SELECT * FROM bookshop;
 
/*테이블명 : bookorder
idx   number        primary key    -- 일련번호         
isbn  varchar2(10)   FKISBN    -- bookshop의 isbn의 자식키
qty   number        -- 수량*/
 
CREATE table bookorder (
idx number primary key,
isbn varchar2(10), 
qty number,
constraint FKISBN foreign key(isbn) references bookshop ON delete SET null
);
 
/*
데이터
1          is001     2
2          or003     3
3          pa002    5
4          is001     3
5          or003    10*/
insert into bookorder values(idx_seq.nextval, 'is001', 2);
insert into bookorder values(idx_seq.nextval, 'or003', 3);
insert into bookorder values(idx_seq.nextval, 'pa002', 5);
insert into bookorder values(idx_seq.nextval, 'is001', 3);
insert into bookorder values(idx_seq.nextval, 'or003', 10);
 
--시퀀스명 : idx_seq  증가값: 1  시작값 1  NOCACHE  NOCYCLE
CREATE sequence idx_seq start with 1 increment by 1 nomaxvalue nocache nocycle;
 
/*뷰 명 : bs_view
책제목        저자      총판매금액
-----------------------------------------
조건1) 총판매금액은 qty * price로 하시오
조건2) 수정불가의 제약조건을 추가하시오*/
 
CREATE OR REPLACE VIEW bs_view AS
    SELECT title AS 책제목, author AS 저자, sum(price*qty) AS 총판매금액 
        FROM bookorder JOIN bookshop USING (isbn) GROUP BY title, author
WITH READ ONLY;
SELECT * FROM bs_view;
 
 
 
 
/*ex5) 뷰 - 인라인
사원테이블을 가지고 부서별 평균급여를 뷰(v_view7)로 작성하시오
조건1) 반올림해서 100단위까지 구하시오
조건2) 타이틀은  부서ID, 부서평균
조건3) 부서별로 오름차순 정렬 하시오
조건4) 부서ID가 없는 경우 5000으로 표시하시오*/
CREATE OR REPLACE VIEW v_view7 (부서ID, 부서평균) AS
    SELECT nvl(department_id, 5000), round(avg(salary),-3) 
        FROM employees GROUP BY department_id ORDER BY department_id asc;
SELECT * FROM v_view7;
 
 
 
 
/*[문제5] 1. 부서별 최대급여를 받는 사원의 부서명, 최대급여를 출력하시오
       2. 1번 문제에 최대급여를 받는 사원의 이름도 구하시오*/
--내버전
CREATE OR REPLACE VIEW 부서별최대급여 (부서명, 최대급여) AS
    SELECT department_name, max(salary) 
        FROM employees 
        JOIN departments USING(department_id) 
            GROUP BY department_name ORDER BY 2 desc;
SELECT * FROM 부서별최대급여;
 
 
SELECT last_name AS 최대급여사원명, department_name AS 부서명, salary AS 급여 
    FROM employees 
    JOIN departments USING(department_id) 
    JOIN 부서별최대급여 on(department_name=부서명)
        WHERE salary = 최대급여 ORDER BY 3 desc;
 
--inline 적용된 모범답안
SELECT last_name 이름, department_name AS 부서명, salary AS 최대급여
    FROM employees 
    JOIN departments USING(department_id) 
        WHERE(department_id, salary) IN (SELECT department_id, MAX(salary) AS salary 
                                            FROM employees 
                                            GROUP BY department_id)
        ORDER BY 3 DESC;
 
 
 
 
/*ex6) Top N분석
급여를 가장 많이 받는 사원3명의 이름, 급여를 표시 하시오*/
SELECT ROWNUM, last_name, salary
    FROM (
        SELECT last_name, nvl(salary,0) AS salary 
        FROM employees ORDER BY 2 DESC    
        )
        WHERE ROWNUM<=3;
 
 
 
 
--ex7) 최고급여를 받는 사원1명을 구하시오
--내버전
SELECT last_name AS 사원명, salary AS 급여 
    FROM employees 
        WHERE salary=(SELECT MAX(salary) FROM employees);
        
--강사님 답
SELECT ROWNUM, last_name, salary
    FROM (
            SELECT last_name, nvl(salary,0) AS salary 
            FROM employees ORDER BY 2 desc
        )
        WHERE ROWNUM=1; --← rownum=2는 error 특정 행은 사용할 수 없음)
 
 
 
 
/*ex8) 급여의 순위를 내림차순 정렬 했을 때, 3개씩 묶어서 2번째 그룹을 출력하시오
     (4,5,6 순위의 사원 출력 : 페이징 처리 기법)*/
--강사님 답안 참고해서 다시 작성해본 문장
select * from(
 
select rownum as rn, ceil(rownum/3) as page, 사원명, 급여 
    from (SELECT last_name AS 사원명, nvl(salary,0) AS 급여 FROM employees ORDER BY 급여 DESC) 
    
    ) where page = 1;
 
--from과 where 조건을 먼저 참조하기 때문에 
--먼저 page 컬럼을 만든 뒤 그것을 inline으로 삼아 다시 where 조건 걸어줘야 에러 안 남    
    
 
--내버전
CREATE OR REPLACE VIEW 급여순정렬준비 AS
    SELECT last_name AS 사원명, nvl(salary,0) AS 급여 
        FROM employees ORDER BY 급여 DESC;
SELECT * FROM 급여순정렬준비;
 
CREATE OR REPLACE VIEW 급여순정렬 AS
    SELECT rownum AS rn, 급여순정렬준비.* 
        FROM 급여순정렬준비;
SELECT * FROM 급여순정렬;
 
SELECT * FROM 급여순정렬 WHERE rn BETWEEN 4 AND 6;
 
--한번에 할 수가 없고 자꾸 예비를 만들어서 해야하네...
 
--강사님답안 2중 inline
SELECT  * FROM 
(SELECT rownum , ceil(rownum/3) AS page, tt.* FROM
    (SELECT last_name, nvl(salary,0)as salary FROM employees ORDER BY salary desc)tt
) WHERE page=2;
--ceil은 올림이다. 페이징처리 하는 단위 갯수로 나누고 올림 하면 각 레코드가 페이지 값을 갖게 된다
 
 
SELECT * FROM
(SELECT rownum rn, tt.* FROM 
    (SELECT last_name, nvl(salary,0) AS salary FROM employees ORDER BY 2 desc)tt
) WHERE rn>=4 AND rn<=6;
 
 
 
/*[문제6] 사원들의 연봉을 구한 후 최하위 연봉자 5명을 추출하시오
조건1) 연봉 = 급여*12+(급여*12*커미션)
조건2) 타이틀은 사원이름, 부서명, 연봉
조건3) 연봉은 ￦25,000 형식으로 하시오*/
SELECT * FROM 
        (
          SELECT last_name AS 사원이름, department_name AS 부서명, 
            to_char(salary*12+(salary*12*nvl(commission_pct,1)),'L999,999,999') AS 연봉 
            FROM employees JOIN departments 
            USING(department_id) ORDER BY 연봉 asc
        ) 
         where ROWNUM <=5;        
         
         
         
--rownum만 쓸 땐 hidden column으로 이미 만들어져 있으니 바로 사용 가능
--rownum을 가공하여 재처리 후에 쓸 땐 만든 뒤에 inline으로 활용해야 사용 가능
 
/*
CREATE 만 써도 되지만 이미 존재할 경우 에러발생.
없으면 만들고 있으면 대체하라 는 의미에서 CREATE OR REPLACE 라고 작성.
with read only를 문장 맨 뒤에 넣으면 view를 통해서는 수정 불가
with check option을 문장 맨 뒤에 넣으면 VIEW 를 통해서 접근 가능한 데이터만 수정 가능
 
subquery
SELECT ~ FROM table WHERE (SELECT ~)
inline
SELECT ~ FROM (SELECT ~)
 
ceil 올림
ceil(rownum/3) as page 를 통한 페이징 기법
3은 한 페이지에 해당하는 레코드 수
*/