[수업 23일차] 21.09.06 SQL DataBase 3

-- 읽기 전용 view를 만들면 insert, update,delete 쿼리가 안 됨.

 

SQL 오류: ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
42399.0000 - "cannot perform a DML operation on a read-only view"

---

 

ORA-00001: 무결성 제약 조건(WEB.SYS_C007423)에 위배됩니다

 

중복이 있으면 안된다. 

 

---

--3. 숫자와 관련된 함수들

-- 1) 절대값을 구하는 함수 : abs (정수)
select abs(23) from dual;
select abs(-23) from dual;


-- 2)양수(1), 음수(-1), 0을 반환해 주는 함수 : sign(정수)
select sign(23) from dual;
select sign(23),sign(-23), sign(0) from dual;

--3) 반올림을 해주는 함수 : round(실수)
select round(1234.5678) from dual;


-- 반올림을 할 때 자릿수를 지정
-- 형식) round([숫자(필수)], [반올림 위치(선택)]
-- 음수 값을 지정하면 자연수(정수) 쪽으로 한자리씩 위로 반올림 한다.
select round(0.1234567,6) from dual;     -- 0.0.123457
select round(2.3423557,4) from dual;     -- 2.3434
select round(1234.5678,-1) from dual;    -- 1230
select round(1255.5678,-2) from dual;    -- 1300


-- 4) 소수점 이하 자릿수를 잘라내는 함수 : trunc()
-- 형식) trunc([숫자(필수), 버림 위치(선택)])
select trunc(1234.1234567,0) from dual;     -- 1234
select trunc(1234.1234567,4) from dual;     -- 1234.1234
select trunc(1234.1234567,-3) from dual;    -- 1000


-- 5) 무조건 올림을 해 주는 함수 : ceil()
-- 지정된 숫자보다 큰 정수 중에서 가장 작은 수를 선택하는 함수.
select ceil(22.8) from dual;    -- 23
select ceil(21.1) from dual;    -- 23

-- 6) 지정된 숫자보다 작은 정수 중에서 가장 큰 수를 선택하는 함수 : floor()
select floor(-3.14) from dual;    -- -4
select floor(-235.123) from dual;  -- -26

-- 7) 제곱을 구해 주는 함수 : power()
select power(4,3) from dual;      --64

-- 8) 나머지를 구해 주는 함수 : mod()
--    형식) mod([나눗셈 될 숫자(필수)], [나눌 숫자[필수]])
select mod(7,4) from dual;      --3

 

 

 

---

 view(중요함)

 

물리적인 테이블에 근거한 논리적인 가상의 테이블을 말함.

view는 실질적으로 데이터를 저장하고 있지 않음.

view를 만들면 데이터베이스에 질의 시 실제 테이블에 접근하여 데이터를 불러오게 됨.

view는 필요한 내용들만 추출해서 사용하는 테이블

view는 주로 데이터를 조회할 때 가장 많이 사용됨

view는 테이블과 유사하며, 테이블처럼 사용이 가능함.

view는 테이블에 저장하기 위한 물리적인 공간이 필요없음.

테이블과 마찬가지로 insert, update, delete, select 명령이 가능함.

view를 사용하는 이유

1) 보안 관리를 위해 사용함(아주 중요).

==> 보안 등급에 맞추어 컬럼의 범위를 정해서 조회가 가능하도록 할 수 있음.

2) 사용자의 편의성을 제공함.

형식) create view 뷰이름

     as
    
     쿼리문;

 

 

-- 읽기 전용 view를 만들면 insert, update,delete 쿼리가 안 됨.

-- 읽기 전용 view를 만드는 방법 :view 생성 시 맨 마지막에 with read only 문구 추가

 

--create or replace view : 같은 이름의 view가 있는 경우에는 삭제하고 다시 view를 만들라는 의미

 

---

-- view를 만들 때 컬럼이름만 만들고 싶은 경우 -- 실제 데이터는 없는 경우

--  조건을 주실 때 말이 안되는 조건을 작성하면 됨.

 

 

---

컬럼 속성(제약 조건)

 

테이블에 부적합한 자료가 입력되는 것을 방지하기 위해서

테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러가지 규칙을 정하는 것.

1) not null

2) unique

3) primary key : no null + unique 제약 조건

4) foreign key

5) check

 

 

-- 1) not null

-- null 값이 입력되지 못하게 하는 제약 조건.

-- 특정 열에 데이터의 중복여부와는 관계 없이 null 값을 허용하지 않는 제약 조건.

 

-- 2) unique 제약 조건

-- 열에 저장할 데이터의 중복을 허용하지 않고자 할 때 사용하는 조건

-- null 값은 허용됨

 

--3) primary key : no null + unique 제약 조건

-- 테이블에 하나만 존재해야 하는 제약 조건.

-- 보통은 주민번호나 emp 테이블의 empno(사원번호) 등이 primary key의 조건이 됨.

-- 4) foreign key 제약 조건

-- 다른 테이블의 필드(컬럼)을 참조해서 무결성을 검사하는 조건.

-- 참조키 : 부모 테이블의 컬럼을 이야기 함.

-- 외래키 : 자식 테이블의 컬럼을 이야기 함. 

-- 자식 테이블의 컬럼의 값(데이터)이 부모테이블에 없는 경우에는 무결성의 규칙이 깨져 버림.

 

-- 외래키가 존재하기 위해서는 우선적으로 부모테이블이 먼저 만들어져야 함.

 

--5) check 제약 조건

-- 열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용함.

-- 주어진 값만 허용하는 조건.

 

---

시퀀스(sequence)

 

연속적인 번호를 만들어 주는 기능. 

형식)  

    create sequence 시퀀스이름
    
    start with n (시작 번호 설정 - 기본적으로 기본값은 1)
    
    increment by n(n : 증가 번호 설정 - 기본적으로 증가값은 1)
    
    maxvalue n ( n :시퀀스 최대 번호 설정)
    
    minvalue n (n : 시퀀스 최소 번호 설정)
    
    cache/ nocache (시퀀스의 값을 빠르게 설정하기 위해 캐쉬 메모리 사용 여부)

    1)cache : 시퀀스를 빨리 제공하기 위해서 미리 캐쉬 메모리에 시퀀스를 넣어 두고
    
              준비하고 있다가 시퀀스 작업이 필요할 때 사용을 함.
              
              디폴트로는 20개의 시퀀스를 캐쉬 메모리에 보관하게 됨.
              
              하지만 만약 시스템이 비정상적으로 종료를 하거나 전원이 차단되어
              
              시스템이 종료를 하게 되면 캐쉬 메모리에 남아 있던 남은 시퀀스
              
              번호는 사라지게 됨. 
              
    2) nocache : cache 기능을 사용하지 않겠다는 의미.

 

---

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
서브 쿼리

 

하나의 쿼리문 안에 포함되어 있는 또 하나의 쿼리문을 말함.

쿼리문 안에 또 다른 쿼리문이 존재하는 쿼리문을 말함.

서브쿼리는 메인쿼리가 서브쿼리를 포함하는 종속적인 관계임.

여러번 쿼리를 실행해서 얻을 수 있는 결과를 하나의 중첩된 

쿼리 문장으로 결과를 얻을 수 있게 해 줌.

주의 사항

1) 서브쿼리는 괄호로 묶어서 사용해야 함.

2) 서브 쿼리 안에서는 order by 절을 사용할 수 없음.

3) 연산자 오른쪽에 있어야 함.

사용방법 : 우선은 안쪽에 있는 쿼리문을 실행한 후, 

그 결과값을 가지고 바깥쪽 쿼리문을 실행함.

---

-- group by 절

 

-- 특정 컬럼이나 값을 기준으로 해당 레코드를 묶어서 자료를 관리할 때 사용.

-- 보통은 특정 컬럼을 기준으로 집계를 구하는데 많이 사용이 됨.

-- 보통은 그룹함수와 함께 사용하면 효과적으로 활용이 가능함.

 

 

 

---

-- having 절

-- group by 절 다음에 나오는 조건절로

-- group by 절의 결과에 조건을 주어서 제한할 때 사용함.

-- group by 절에는 where (조건절)이 올 수 없음.

-- products 테이블에서 카테고리 별로 상품의 갯수를 화면에 보여주세요.

 

---

트랜잭션(transaction)

데이터 처리의 한 단위를 말함.

오라클에서 발생하는 여러 개의 SQL 명령문들을 

하나의 논리적인 작업 단위로 처리하는 것을 말함.

All or Nothing 방식으로 처리함. 

명령어 여러 개의 집합이 정상적으로 처리가 되면 종료를 하고, 

여러 개의 명령어 중에서 하나의 명령어라도 잘못이 되면

전체를 취소하는 방식을 말함.

트랜잭션 사용 이유 : 데이터의 일관성을 유지하면서

                  데이터의 안정성을 보장하기 위해 사용함. 
                
트랜잭션 사용 시 트랜잭션을 제어하기 위한 명령어.

1) commit : 모든 작업을 정상적으로 처리하겠다고 확정하는 명령어. 

             트랜잭션(insert, update,delete) 작업의 내용을
             
             실제 DB에 반영. 이전에 있던 데이터에 update 현상이 발생을 함.
             
             모든 사용자가 변경된 데이터의 결과를 확인할 수 있음.
             
2) rollback : 작업 중에 문제가 발생했을 때 트랜잭션 처리 과정에서 

              발생한 변경 사항을 취소하여 이전 상태로 되돌리는 명령어.

              트랜잭션(insert, update, delete) 작업 내용을 취소함.
              
              이전에 commit 한 곳까지만 복구가 됨.

 

 

 

--1. dept_01 테이블을 만들 때 dept 테이블을 복사하여 만들어 보자.
create table dept_01
as
select * from dept;

-- 2. dept_01 테이블에 있는 내용을 삭제해 보자.
delete from dept_01;

-- 3. 이때 만일 부서번호가 20번 부서에 대해서만 삭제를 하고 싶었는데 잘못해서 전체가 삭제가
--    되어 데이터를 찾을 수가 없음
rollback;

--4. 20 번 부서만 삭제를 하자.
delete from dept_01 where deptno = 20;

-- 5. 데이터베이스에 완전하게 적용을 시키자.
commit;
rollback;

 

-- savepoint : 트랜잭션을 작게 분할하는 것을 말함.

--             사용자가 트랜잭션 중간 단계에서 포인트를 지정하여

--             트랜잭션 내의 특정 savepoint 까지 rollback

--              할 수 있게 하는 것을 말함.

 

--   1. dept 테이블을 복사하여 dept_02 테이블을 만들어 보자. 
create table dept_02
as
select*
from dept;

-- 2. dept_02 테이블에서 40번 부서를 삭제한 후에 commit을 해 보자.
delete from dept_02
where deptno = 40;

commit;


-- 3. dept_02 테이블에서 30번 부서를 삭제해 보자.
delete from dept_02
where deptno =30;


-- 4. 이 때 savepoint c1 을 설정해 보자
savepoint c1;


-- 5. 부서번호가 20번인 부서를 삭제해 보자.
delete from dept_02
where deptno = 20;

-- 6. savepoint c2를 설정해 보자.
savepoint c2;

-- 7. 마지막으로 부서번호가 10번인 부서를 삭제해 보자.
delete from dept_02
where deptno =10;

--8.부서번호가 20번인 부서를 삭제하기 바로 전으로 되돌려 보려고 함.

--rollback을 이용하면 특정 지점으로 되돌아 갈 수 있게 됨.

rollback c1;

---

 

 

--3. 숫자와 관련된 함수들

-- 1) 절대값을 구하는 함수 : abs (정수)
select abs(23) from dual;
select abs(-23) from dual;


-- 2)양수(1), 음수(-1), 0을 반환해 주는 함수 : sign(정수)
select sign(23) from dual;
select sign(23),sign(-23), sign(0) from dual;

--3) 반올림을 해주는 함수 : round(실수)
select round(1234.5678) from dual;


-- 반올림을 할 때 자릿수를 지정
-- 형식) round([숫자(필수)], [반올림 위치(선택)]
-- 음수 값을 지정하면 자연수(정수) 쪽으로 한자리씩 위로 반올림 한다.
select round(0.1234567,6) from dual;     -- 0.0.123457
select round(2.3423557,4) from dual;     -- 2.3434
select round(1234.5678,-1) from dual;    -- 1230
select round(1255.5678,-2) from dual;    -- 1300


-- 4) 소수점 이하 자릿수를 잘라내는 함수 : trunc()
-- 형식) trunc([숫자(필수), 버림 위치(선택)])
select trunc(1234.1234567,0) from dual;     -- 1234
select trunc(1234.1234567,4) from dual;     -- 1234.1234
select trunc(1234.1234567,-3) from dual;    -- 1000


-- 5) 무조건 올림을 해 주는 함수 : ceil()
-- 지정된 숫자보다 큰 정수 중에서 가장 작은 수를 선택하는 함수.
select ceil(22.8) from dual;    -- 23
select ceil(21.1) from dual;    -- 23

-- 6) 지정된 숫자보다 작은 정수 중에서 가장 큰 수를 선택하는 함수 : floor()
select floor(-3.14) from dual;    -- -4
select floor(-235.123) from dual;  -- -26

-- 7) 제곱을 구해 주는 함수 : power()
select power(4,3) from dual;      --64

-- 8) 나머지를 구해 주는 함수 : mod()
--    형식) mod([나눗셈 될 숫자(필수)], [나눌 숫자[필수]])
select mod(7,4) from dual;      --3


/* view(중요함)

물리적인 테이블에 근거한 논리적인 가상의 테이블을 말함.

view는 실질적으로 데이터를 저장하고 있지 않음.

view를 만들면 데이터베이스에 질의 시 실제 테이블에 접근하여 데이터를 불러오게 됨.

view는 필요한 내용들만 추출해서 사용하는 테이블

view는 주로 데이터를 조회할 때 가장 많이 사용됨

view는 테이블과 유사하며, 테이블처럼 사용이 가능함.

view는 테이블에 저장하기 위한 물리적인 공간이 필요없음.

테이블과 마찬가지로 insert, update, delete, select 명령이 가능함.

view를 사용하는 이유

1) 보안 관리를 위해 사용함(아주 중요).

==> 보안 등급에 맞추어 컬럼의 범위를 정해서 조회가 가능하도록 할 수 있음.

2) 사용자의 편의성을 제공함.

형식) create view 뷰이름

     as
    
     쿼리문;

*/
--1) 보안 관리를 위해 사용함(아주 중요).

-- 인사부 view
-- 컬럼에 sal (급여), comm(보너스) 컬럼은 제외
create view emp_insa
as
select empno, ename, job, mgr, hiredate, deptno
from emp;

-- 양업부 view
-- 컬럼에 sal(급여) 컬럼은 제외
create view emp_sales
as
select empno, ename, job,mgr, hiredate, comm, deptno
from emp;



-- 회계부 view
-- 컬럼에 emp 테이블의 모든 컬럼이 반영.
create view emp_account
as
select*
from emp;

-- emp 테이블을 복사해서 emp_view라는 view를 만들어 보자
create view emp_view
as
select*
from emp;

--emp_view에 데이터를 추가해 보자.
insert into emp_view 
    values(9000,'ANGEL','SALESMAN',7698,sysdate,1500,200,30);
    
    select*
    from emp_view;
    
-- 읽기 전용 view를 만들면 insert, update,delete 쿼리가 안 됨.

-- 읽기 전용 view를 만드는 방법 :view 생성 시 맨 마지막에 with read only 문구 추가

create view emp_view1
as
select *
from emp
with read only;

insert into emp_view1
    values(9000,'ANGEL','SALESMAN', 7698, sysdate, 1500, 200, 30);
    
    
--create or replace view : 같은 이름의 view가 있는 경우에는 삭제하고 다시 view를 만들라는 의미

create or replace view emp_account 
as
select empno, ename, job, hiredate, sal,comm,deptno
from emp;

--2) 사용자의 편의성을 제공함.
create or replace view emp_annulSalary(empno,ename,annul_salary)
as
select empno, ename, (sal+nvl(comm,0))*12
from emp
with read only;

-- view를 만들 때 컬럼이름만 만들고 싶은 경우 -- 실제 데이터는 없는 경우
--  조건을 주실 때 말이 안되는 조건을 작성하면 됨.
creat or replace view emp_view2
as
select *
from emp where deptno = 1
from emp
where deptno = 1;

-- [문제1] emp 테이블을 이용하여 emp_dept20 이라는 view를 만들어 보세요.
-- 단 , 부서번호가 20번 부서에 속한 사원들의 사번, 이름, 담당업무, 
-- 관리자, 부서번호만으로 만들어진 view를 화면에 보여주세요

create replace view emp_dept20
as
select empno, ename, job, mgr, deptno
from emp
where deptno = 20
with read only;



-- [문제2] 담당업무가 'SALESMAN'인 사원의 사번, 이름, 담당업무,
--        입사일, 부서번호를 컬럼으로 하는 view를 만들되 emp_sale 이라는 
-- view를 만들어 화면에 보여주세요.
create replace view emp_sale 
as
select empno, ename, job, hiredate, deptno
from emp
where job ='SALESMAN'
with read only;


/* 컬럼 속성(제약 조건)

테이블에 부적합한 자료가 입력되는 것을 방지하기 위해서

테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러가지 규칙을 정하는 것.

1) not null

2) unique

3) primary key : no null + unique 제약 조건

4) foreign key

5) check


*/


-- 1) not null

-- null 값이 입력되지 못하게 하는 제약 조건.

-- 특정 열에 데이터의 중복여부와는 관계 없이 null 값을 허용하지 않는 제약 조건.

create table null_test(
    col1 varchar2(10) not null,
    col2 varchar2(10) not null,
    col3 varchar2(10)
);

insert into null_test values('aa','aa1','aa2');
insert into null_test(col1,col2) values('bb','bb1');
insert into null_test(col1,col2) values('cc','');       -- error 발생


-- 2) unique 제약 조건

-- 열에 저장할 데이터의 중복을 허용하지 않고자 할 때 사용하는 조건

-- null 값은 허용됨

create table unique_test(
    col1 varchar2(10) unique,
    col2 varchar2(10) unique,
    col3 varchar2(10) not null,
    col4 varchar2(10) not null
);

insert into unique_test values('aa','bb','cc','dd');

insert into unique_test values('aa1','bb1','cc1','dd1');

update unique_test set col2 = 'bb' where col1 ='aa1';    --error 발생(중복된 데이터가 있으면 안된다)

--3) primary key : no null + unique 제약 조건

-- 테이블에 하나만 존재해야 하는 제약 조건.

-- 보통은 주민번호나 emp 테이블의 empno(사원번호) 등이 primary key의 조건이 됨.

-- 4) foreign key 제약 조건

-- 다른 테이블의 필드(컬럼)을 참조해서 무결성을 검사하는 조건.

-- 참조키 : 부모 테이블의 컬럼을 이야기 함.

-- 외래키 : 자식 테이블의 컬럼을 이야기 함. 

-- 자식 테이블의 컬럼의 값(데이터)이 부모테이블에 없는 경우에는 무결성의 규칙이 깨져 버림.

-- 외래키가 존재하기 위해서는 우선적으로 부모테이블이 먼저 만들어져야 함.

create table foreign_test(
    empno number(4) primary key,
    ename varchar2(20) not null,
    job varchar2(50) not null,
    deptno number(2) references dept(deptno)
);

insert into foreign_test values(1111, '홍길동', '영업부',30);

insert into foreign_test values(2222, '유관순', '회계부',10);

insert into foreign_test values(3333, '김유신','IT',50);   --error 발생.


--5) check 제약 조건

-- 열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용함.

-- 주어진 값만 허용하는 조건.

create table check_test(
    gender varchar2(10), 
    constraint gender_ch check(gender in ('남자','여자'))
);

insert into check_test values('남자');

insert into check_test values('여자');

insert into check_test values('여성');     --error  발생


/*시퀀스(sequence)

연속적인 번호를 만들어 주는 기능. 

형식)  

    create sequence 시퀀스이름
    
    start with n (시작 번호 설정 - 기본적으로 기본값은 1)
    
    increment by n(n : 증가 번호 설정 - 기본적으로 증가값은 1)
    
    maxvalue n ( n :시퀀스 최대 번호 설정)
    
    minvalue n (n : 시퀀스 최소 번호 설정)
    
    cache/ nocache (시퀀스의 값을 빠르게 설정하기 위해 캐쉬 메모리 사용 여부)

    1)cache : 시퀀스를 빨리 제공하기 위해서 미리 캐쉬 메모리에 시퀀스를 넣어 두고
    
              준비하고 있다가 시퀀스 작업이 필요할 때 사용을 함.
              
              디폴트로는 20개의 시퀀스를 캐쉬 메모리에 보관하게 됨.
              
              하지만 만약 시스템이 비정상적으로 종료를 하거나 전원이 차단되어
              
              시스템이 종료를 하게 되면 캐쉬 메모리에 남아 있던 남은 시퀀스
              
              번호는 사라지게 됨. 
              
    2) nocache : cache 기능을 사용하지 않겠다는 의미.
*/


-- 시퀀스의 다음 시퀀스 번호를 알고 싶은 경우
select memo_seq.nextval from dual;


/*
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
서브 쿼리

하나의 쿼리문 안에 포함되어 있는 또 하나의 쿼리문을 말함.

쿼리문 안에 또 다른 쿼리문이 존재하는 쿼리문을 말함.

서브쿼리는 메인쿼리가 서브쿼리를 포함하는 종속적인 관계임.

여러번 쿼리를 실행해서 얻을 수 있는 결과를 하나의 중첩된 

쿼리 문장으로 결과를 얻을 수 있게 해 줌.

주의 사항

1) 서브쿼리는 괄호로 묶어서 사용해야 함.

2) 서브 쿼리 안에서는 order by 절을 사용할 수 없음.

3) 연산자 오른쪽에 있어야 함.

사용방법 : 우선은 안쪽에 있는 쿼리문을 실행한 후, 

그 결과값을 가지고 바깥쪽 쿼리문을 실행함.


*/

--emp 테이블에서 이름이 'SCOTT'인 사원의 급여보다 더 많은 급여를 받는
-- 사원의 사번, 이름, 담당업무, 급여를 화면에 보여주세요.
select empno, ename, job,sal
from emp
where sal > (select sal
from emp
where ename = 'SCOTT');

--[문제1] emp 테이블에서 평균급여보다 더 적게 받는 사원의
--       사번, 이름, 담당업무, 급여, 부서번호를 화면에 보여주세요.
select empno, ename, job, sal, deptno
from emp
where sal <(select avg(sal)
from emp);   --약 2073의 평균 급여




--[문제2] emp 테이블에서 사번이 7521인 사원과 담당업무가 같고,
--       급여가 7934인 사원보다 더 많이 받는 사원의 사번, 이름,
--       담당업무, 급여를 화면에 보여주세요.


select empno, ename, job,sal
from emp
where job = (select job
from emp
where empno = 7521) -- SALESMAN
and 
sal > (select sal
from emp
where empno = 7934);--1300



--[문제3] emp 테이블에서 담당업무가 'MANAGER'인 사원의 최소급여보다
-- 적으면서, 담당업무가 'CLERK'은 아닌 사원의 사번, 이름, 담당업무, 
-- 급여를 화면에 보여주세요.
select empno, ename,job,sal
from emp
where sal < (select min(sal)
from emp 
where job = 'MANAGER')-- 담당업무가 MANAGER인 사원의 최소급여 -2450
and 
job <>'CLERK';



--[문제4] 부서 위치가 'DALLAS'인 사원의 사번, 이름, 부서번호, 담당업무를 
-- 화면에 보여주세요.
select empno ,ename,deptno,job
from emp
where deptno =(select deptno
from dept
where loc = 'DALLAS'); --20번 부서





--[문제5] member10 테이블에 있는 고객의 정보 중 마일리지가 가장 높은 
-- 회원의 모든 정보를 화면에 보여주세요.


select*
from member10
where mileage = (select max(mileage)
from member10);    --10000 마일리지


--[문제6] emp 테이블에서 'SMITH'인 사원보다 더 많은 급여를 받는
-- 사원의 이름과, 급여를 화면에 보여주세요.

select ename, sal
from emp
where sal > (select sal 
from emp
where ename = 'SMITH'); --급여 800


--[문제7] emp 테이블에서 10번 부서 급여의 평균 급여보다 더 적은 급여를 
-- 받는 사원들의 이름, 급여, 부서번호를 화면에 보여주세요.

select ename, sal, deptno
from emp
where sal < (select avg(sal)
from emp
where deptno = 10);     --약 2916 평균 급여






--[문제8] emp 테이블에서 'BLAKE' 와 같은 부서에 있는 사원들의 이름과
-- 입사일자, 부서번호를 화면에 보여주되, 'BLAKE'는 제외하고 
-- 화면에 보여주세요.

select ename, hiredate, deptno
from emp
where deptno = (select deptno
from emp
where ename = 'BLAKE')    --30번 부서
and ename != 'BLAKE';


--[문제9] emp 테이블에서 평균급여보다 더 많은 급여를 받는 사원들의 사번, 
-- 이름, 급여를 화면에 보여주되, 급여가 높은데서 낮은 순으로 화면에 
-- 보여주세요.

select empno, ename, sal
from emp
where sal  > (select avg(sal)
from emp)
order by sal desc;    --약 2073의 평균 급여



--[문제10] emp 테이블에서 이름에 'T'를 포함하고 있는 사원들과 같은 부서에 
-- 근무하고 있는 사원의 사번과 이름, 부서번호를 화면에 보여주세요.

select empno, ename, deptno
from emp
where deptno in (select deptno
from emp
where ename like '%T%');     --20,30번 부서


--[문제11] 'SALES' 부서에서 근무하고 있는 사원들의 부서번호,
--         이름, 담당업무를 화면에 보여주세요.


select deptno, ename, job
from emp
where deptno = (select deptno
from dept
where dname ='SALES');    --30번 부서


--[문제12] emp 테이블에서 'KING'에게 보고하는 모든 사원의
--        이름과 급여, 관리자를 화면에 보여주세요.


select ename , sal, mgr
from emp
where mgr = (
select empno
from emp
where ename = 'KING');    --7839 사번


--[문제13] emp 테이블에서 자신의 급여가 평균급여보다 많고, 이름에
--       'S'자가 들어가는 사원과 동일한 부서에서 근무하는 모든 사원의 
--        사번, 이름, 급여, 부서번호를 화면에 보여주세요.

select empno, ename, sal, deptno
from emp
where sal > (select avg(sal)
from emp)    -- 평균급여 : 2073
and
deptno in(select deptno
from emp
where ename like '%S%');

--[문제14] emp 테이블에서 보너스를 받는 사원과 부서번호, 급여가 같은
--        사원들의 이름, 급여, 부서번호를 화면에 보여주세요.

select ename, sal, deptno
from emp
where deptno in (select deptno
from emp
where comm >0 )   --30번 부서
and
sal in (select sal
from emp
where comm > 0); --1250, 1600 급여



--[문제15] products 테이블에서 상품의 판매가격이 판매가격의
--        평균보다 큰 상품의 전체 내용을 화면에 보여주세요.

select *
from products
where output_price > (select avg(output_price)
from products);    --상품의 판매 평균가격 : 1,143,800원 



--[문제16] products 테이블에 있는 판매가격에서 평균 가격 이상의 
--        상품 목록을 구하되, 평균을 훅할 때 가격이 가장 큰 금액인
--        상품을 제외하고 평균을 구하여 화면에 보여주세요.


select *
from products
where output_price >= (select avg(output_price)
from products
where output_price != 
(select max(output_price)
from products));    --상품의 판매 평균가격 382,000원(최고 판매가 제외) 




--[문제17] products 테이블에서 카테고리의 이름에 '에어컨'이라는 
-- 단어가 포함된 카테고리에 속하는 상품목록의 전체 정보를 
-- 화면에 보여주세요.
select *
from products
where category_fk = (select category_code
from category
where category_name like '%에어컨%');     --에어컨 카테고리 코드 : 00010004



--[문제18] member10 테이블에 있는 고객 정보 중 마일리지가 가장 높은
-- 금액을 가지는 고객에게 보너스 마일리지 5000점을 더 주어
-- 고객명, 마일리지, 마일리지 +5000점을 화면에 보여주세요.

select memname, mileage, mileage + 5000 "추가 마일리지"
from member10
where mileage = (select max(mileage)
from memeber10);    --10000마일리지


-- group by 절

-- 특정 컬럼이나 값을 기준으로 해당 레코드를 묶어서 자료를 관리할 때 사용.

-- 보통은 특정 컬럼을 기준으로 집계를 구하는데 많이 사용이 됨.

-- 보통은 그룹함수와 함께 사용하면 효과적으로 활용이 가능함.

select deptno
from emp
order by deptno;

select deptno
from emp
group by deptno;

-- emp 테이블에서 부서별로 각 부서의 인원을 확인하고 싶은 경우  (//ex, 부서별 평균급여...)
select deptno, count(*)    --(부서로 묶었을때 카운트)
from emp
group by deptno
order by deptno;


--emp 테이블에서 부서별로 급여의 합계액을 화면에 보여주세요.
select deptno, sum(sal) "급여 합계"
from emp 
group by deptno
order by deptno;


-- [문제] emp 테이블에서 부서별로 그룹을 지어서 부서의 급여 합계와
-- 부서별 인원수, 부서별 평균 급여, 부서별 최대 급여, 
-- 부서별 최소 급여를 구하여 화면에 보여주세요.
-- 단, 급여 합계를 기준으로 내림차순으로 정렬하여 화면에 보여주세요.

select deptno, sum(sal), count(*),avg(sal), max(sal), min(sal)
from emp 
group by deptno
order by sum(sal)desc;



select *
from emp;


-- having 절

-- group by 절 다음에 나오는 조건절로

-- group by 절의 결과에 조건을 주어서 제한할 때 사용함.

-- group by 절에는 where (조건절)이 올 수 없음.

-- products 테이블에서 카테고리 별로 상품의 갯수를 화면에 보여주세요.
select category_fk, count(*)
from products
group by category_fk
having count(*) >= 2
order by category_fk;

/*

트랜잭션(transaction)
 
데이터 처리의 한 단위를 말함.

오라클에서 발생하는 여러 개의 SQL 명령문들을 

하나의 논리적인 작업 단위로 처리하는 것을 말함.

All or Nothing 방식으로 처리함. 

명령어 여러 개의 집합이 정상적으로 처리가 되면 종료를 하고, 

여러 개의 명령어 중에서 하나의 명령어라도 잘못이 되면

전체를 취소하는 방식을 말함.

트랜잭션 사용 이유 : 데이터의 일관성을 유지하면서

                  데이터의 안정성을 보장하기 위해 사용함. 
                
트랜잭션 사용 시 트랜잭션을 제어하기 위한 명령어.

1) commit : 모든 작업을 정상적으로 처리하겠다고 확정하는 명령어. 

             트랜잭션(insert, update,delete) 작업의 내용을
             
             실제 DB에 반영. 이전에 있던 데이터에 update 현상이 발생을 함.
             
             모든 사용자가 변경된 데이터의 결과를 확인할 수 있음.
             
2) rollback : 작업 중에 문제가 발생했을 때 트랜잭션 처리 과정에서 

              발생한 변경 사항을 취소하여 이전 상태로 되돌리는 명령어.

              트랜잭션(insert, update, delete) 작업 내용을 취소함.
              
              이전에 commit 한 곳까지만 복구가 됨.


*/

--1. dept_01 테이블을 만들 때 dept 테이블을 복사하여 만들어 보자.
create table dept_01
as
select * from dept;

-- 2. dept_01 테이블에 있는 내용을 삭제해 보자.
delete from dept_01;

-- 3. 이때 만일 부서번호가 20번 부서에 대해서만 삭제를 하고 싶었는데 잘못해서 전체가 삭제가
--    되어 데이터를 찾을 수가 없음
rollback;

4. 20 번 부서만 삭제를 하자.
delete from dept_01 where deptno = 20;

-- 5. 데이터베이스에 완전하게 적용을 시키자.
commit;
rollback;



-- savepoint : 트랜잭션을 작게 분할하는 것을 말함.

--             사용자가 트랜잭션 중간 단계에서 포인트를 지정하여

--             트랜잭션 내의 특정 savepoint 까지 rollback

--              할 수 있게 하는 것을 말함.

--   1. dept 테이블을 복사하여 dept_02 테이블을 만들어 보자. 
create table dept_02
as
select*
from dept;

-- 2. dept_02 테이블에서 40번 부서를 삭제한 후에 commit을 해 보자.
delete from dept_02
where deptno = 40;

commit;


-- 3. dept_02 테이블에서 30번 부서를 삭제해 보자.
delete from dept_02
where deptno =30;


-- 4. 이 때 savepoint c1 을 설정해 보자
savepoint c1;


-- 5. 부서번호가 20번인 부서를 삭제해 보자.
delete from dept_02
where deptno = 20;

-- 6. savepoint c2를 설정해 보자.
savepoint c2;

-- 7. 마지막으로 부서번호가 10번인 부서를 삭제해 보자.
delete from dept_02
where deptno =10;

--8.부서번호가 20번인 부서를 삭제하기 바로 전으로 되돌려 보려고 함.

--rollback을 이용하면 특정 지점으로 되돌아 갈 수 있게 됨.

rollback c1;


select *
from emp;