[SQL] 20190605

병행처리 (스케줄링)

VIEW
- SELECT, INSERT, UPDATE, DELETE 가능
- 가상의 테이블이기 때문에 물리적인 저장 공간을 필요로 하지 않음

CREATE VIEW STU_VIEW (STU_NO, STU_NAME, STU_GENDER)
AS SELECT STU_NO, STU_NAME, STU_GENDER
FROM STUDENT;

select * from stu_view;

create view emp_view (empno, ename, dname) as
select emp.empno, emp.ename, dept.dname
from emp natural join dept;

문제 1. EMP 테이블로부터 10, 20번 부서의 사원들로 이루어진 뷰를 생성하시오.
create view emp_view (empno, deptno) as
select empno, deptno
from emp
where deptno = 10
or deptno = 20;

create view empno_view as
select *
from emp
where deptno in (10, 20);

CREATE VIEW EMPNO_VIEW(EMPNO, ENAME, DEPTNO) AS
SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE ENAME = 
(SELECT ENAME FROM EMP WHERE DEPTNO =10 OR DEPTNO =20);

*** 문제 2. 사원번호, 사원이름, 부서이름, 상급자 이름으로 구성된 뷰를 생성하시오.
create view emp4(empno, ename, dname, mgr_name) as
select e1.empno, e1.ename, d.dname, e2.ename
from emp e1, emp e2, dept d
where e1.mgr = e2.empno 
and e1.deptno = d.deptno;

e1의 매니저번호가 e2의 사원번호와 같아야함
e1의 부서번호를 d의 부서번호에서 가져옴

문제 3. 학과별 평균 몸무게와 평균 키를 가지는 뷰를 생성하시오.
create view student_view (stu_dept, stu_weight, stu_height) as
select stu_dept, round(avg(stu_weight), 2), round(avg(stu_height), 2)
from student
group by stu_dept;


보안 관리를 위한 View
- 보안 등급에 맞추워 컬럼 및 범위를 정하고 Privilege 부여
- Table의 명칭이나 Column의 명칭을 숨기기 위함

CREATE [UNIQUE] INDEW i_stu_weight
ON student(stu_weight, ...)

INDEX 삭제
DROP INDEW i_stu_weight;

INDEX 검색
user_indexes, iser_id_columns


Sequence 생성
CREATE SEQUENCE sequence_name
 STEAT WITH n
 INCREMENT BY n
 MAXVALUE n | NOMAXVALUE
 MINVALUE n | NOMINVALUE
 CYCLE | NOCYCLE -> default 값은 nocycle
 CACHE | NOCACHE -> default 값은 nocache

create sequence seq1
INCREMENT by 1 -> 1씩 증가
start with 1 -> 1부터 시작
MAXVALUE 100; -> max값 : 100

select seq1.nextval from dual;

select seq1.nextval, ename from emp;

create table emp_vv as
select seq1.nextval, ename from emp;

BMI지수= 몸무게(kg) ÷ (신장(m) × 신장(m))
round(weight / (height*height), 2)

문제 4. 학생이름, 키, 몸무게, BMI 지수를 가지는 뷰를 생성하시오.
create view student_test_view(stu_name, stu_height, stu_weight, BMI) as
select stu_name, stu_height, stu_weight, round(stu_weight / ((stu_height/100)*(stu_height/100)), 2)
from student;

create view student_test_view(stu_name, stu_height, stu_weight, BMI) as
select stu_name, stu_height, stu_weight, round(stu_weight / ((stu_height/100)*(stu_height/100)), 2)
from student
where stu_height is not null
and stu_weight is not null;

문제 5. 사원 이름, 급여, 커미션, 전체수입(급여+커미션)을 가지는 뷰를 생성하시오.
create view emp_test_view(ename, sal, comm, total_sal) as
select ename, sal, nvl(comm, 0), sal+(nvl(comm, 0))
from emp;

문제 6. 초기값 1, 증감값 1, 최대값 1000인 시퀀스를 만들고 시퀀스를 이용해서
학생들에게 숫자를 지정하는 새로운 테이블을 만드시오.
student_seq(seq_no, stu_no, stu_name, stu_grade, stu_class, stu_gender)

create sequence seq2
increment by 1
start with 1
maxvalue 1000;

create table student_seq(seq_no, stu_no, stu_name, stu_grade, stu_class, stu_gender) as
select seq2.nextval, stu_no, stu_name, stu_grade, stu_class, stu_gender
from student;

ROWID, ROWNUM
-> 행ID, 행NUMBER

select ename
from emp;

select rowid, deptno
from emp;

select rowid, deptno
from emp
where rowid = AAAR9eAAHAAAACUAAC;

AAAR9eAAHAAAACUAAC : 16진수

select rownum, deptno
from emp;

select stu_height, rownum
from student;

select stu.stu_height, rownum
from (select stu_height
      from student
      order by stu_height) stu;

select stu.stu_height, rownum
from (select stu_height
      from student
      order by stu_height) stu
where rownum < 4;

문제 7. 수입(급여+커미션)이 높은 상위 5명의 사원번호, 사원이름 부서명을 출력하는 뷰를 만드시오.

create view total_sal_view(ename, empno, dname, total_sal) as
select ename, empno, dname, sal+(nvl(comm, 0))
from emp natural join dept;

?
select ename, empno, dname, total_sal, rownum
from (select ename, empno, dname, sal+(nvl(comm, 0))
      from emp
      order by total_sal) emp_total
where rownum < 5;

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

CREATE VIEW V_STU_INCOME AS
SELECT *
FROM (SELECT EMPNO, ENAME, DNAME, (SAL+NVL(COMM, 0)) INCOME
         FROM EMP NATURAL JOIN DEPT
         ORDER BY 4 DESC) STU_INCOME
WHERE ROWNUM < 6;

문제 8. 부서별 평균 급여가 작은 부서 2개의 부서이름을 뷰로 출력하시오.

CREATE VIEW V_AGV_DEPT AS
SELECT *
FROM (SELECT DEPTNO, ROUND(AVG(SAL)) AS AVG_SAL
         FROM EMP
         GROUP BY DEPTNO
         ORDER BY 2) AVG_DEPT
WHERE ROWNUM < 3;

 

7, 8번 미해결