7day

-- 7일차

--(9) 10번 부서중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 업무,
         이름, 부서명, 입사일, 지역을 출력하라.

 

SELECT EMPNO, JOB, ENAME, dname, hiredate, loc FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE E.DEPTNO = 10 AND JOB !=ALL (SELECT JOB FROM EMP WHERE DEPTNO=30); -- not in 도 가능

--(10) 10번 부서와 같은 일을 하는 사원의 사원번호, 업무, 이름, 부서번호, 부서명,
          지역, 급여를 급여가 많은 순으로 출력하라.

 

select empno, job, ename, e.deptno, dname, sal FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE JOB IN (SELECT JOB FROM EMP WHERE DEPTNO = 10) and e.deptno != 10 ORDER BY SAL DESC;

--(4) EMP Table에 있는 EMPNO와 MGR을 이용하여 서로의 관계를 다음과 같이 출력하라.
    --> SMITH의 매니져는 FORD이다.

 

SELECT e.ename || '의 매니져는 ' || m.ename || '이다.' FROM EMP E JOIN EMP M ON E.MGR = M.EMPNO;

-- having 절 서브쿼리
-- 부서별 월급합계의 평균보다 높은 월급 합계를 가지는 부서의 부서명과
-- 월급합계를 출력
/*
10 1000
10 2000
20 3000
20 4000
10000 / 4 = 2500 : 직원월급의 평균
(3000 + 7000) / 2 = 5000 : 부서월급합계의 평균*/

SELECT *
from (SELECT deptno, sum(sal) s
  FROM EMP
  WHERE DEPTNO IS NOT NULL
  GROUP BY DEPTNO)
where s > (
      SELECT avg(s)
      FROM (
        SELECT deptno, sum(sal) s
        FROM EMP
        WHERE DEPTNO IS NOT NULL
        GROUP BY DEPTNO));

-- 좀 더 쉽게 having 절 이용 (위에서는 from, where에서 서브쿼리 사용)

 

SELECT deptno, sum(sal) FROM EMP GROUP BY DEPTNO having sum(sal) >       (SELECT AVG(SUM(SAL))        FROM EMP        where deptno is not null -- 최반씨 월급이 너무 낮아서 평균이 낮아짐        GROUP BY DEPTNO);

-- 부서명도 같이 출력
-- 상호관련 서브쿼리 : 바깥쪽 쿼리와 서로 맞물려서 작동하는 서브쿼리

 

SELECT DEPTNO, (SELECT DNAME FROM DEPT WHERE DEPTNO = e.deptno), SUM(SAL) FROM EMP e  GROUP BY DEPTNO having sum(sal) >       (SELECT AVG(SUM(SAL))        FROM EMP        --WHERE DEPTNO IS NOT NULL -- 최반씨 월급이 너무 낮아서 평균이 낮아짐        GROUP BY DEPTNO);

-- DML (Data Manipulation Language) : update, insert, delete
-- 반드시 commit이나 rollback 필요(TCL : Transaction Control Language)
-- insert into 테이블명 (컬럼명1, 컬럼명2 ....) values (값1, 값2 ...);

-- 테이블 복사 : emp를 읽어서 emp2 테이블 작성

 -- 제약조건은 복사되지 않는다.(PK, FK 등등)
 

CREATE TABLE EMP2 AS SELECT * FROM EMP;

DESC EMP2; -- 테이블 구조 보기

 

--데이터 입력

 

INSERT INTO EMP2(EMPNO, ENAME) VALUES(1111, '이성치'); commit;

 

-- 이전 커밋(commit)했던 상태로 되돌린다. ( 전 단계 취소)

 

ROLLBACK;

 
-- delete (from) 테이블명 where절

DELETE EMP2;

-- 여러행 입력

INSERT INTO EMP2 as select * from emp where deptno = 30; COMMIT;

-- emp2 테이블에서 empno 가 7900인 데이터 삭제

 

DELETE FROM EMP2 WHERE EMPNO = 7900;

-- update 테이블명 set 컬럼명1 = 값1, 컬럼명2 = 값2 .... where절

 

UPDATE EMP2 SET ENAME = '앨런', hiredate = sysdate WHERE EMPNO = 7499; SELECT * FROM EMP2; update emp2 set comm = 100;

-- DDL (Data Definition Language) : create, alter, drop, truncate 등.
-- auto commit이 된다.

 

CREATE TABLE EMP3 AS SELECT * FROM EMP WHERE 1=2; SELECT * FROM EMP3;

 

-- TCL : commit, rollback, savepoint

 

-- emp2 테이블 조회

 

SELECT * FROM EMP2;

 

-- 수정해라 emp2 무엇을 수정해라? ename을 와드로 수정해라 조건 empno가 7521

 

UPDATE EMP2 SET ENAME = '와드' WHERE EMPNO = 7521;

SAVEPOINT a; -- 저장점 a 부분 : 이름이 와드로

UPDATE EMP2 SET JOB = '무직' WHERE EMPNO = 7521;

SAVEPOINT B; -- 저장점 b 부분 : 직업이 무직으로

ROLLBACK TO a; -- A 시점으로 복구

SELECT * FROM EMP2;

COMMIT; -- 커밋을 하는 순간 savepoint는 의미가 없어진다.

-- lock 현상
-- 세션1 : update emp2 set ename='WARD' where empno = 7521;
-- 세션2 : update emp2 set hiredate = sysdate where empno = 7521;
-- 세션2는 세션1이 commit, rollback을 할때까지 lock에 걸린다.
-- row-level lock이라 한다.(테이블에 insert 가능)
-- table-level lock (delete 테이블 했을때)

-- DDL
-- truncate : 늘어난 테이블 공간(실제 하드디스크의 용량)과
--               들어있는 데이터를 삭제하는 명령

 

TRUNCATE TABLE EMP2;

select * from emp2;    

rollback;

 

-- create, alter, drop
-- datatype : varchar2, char, clob, number, blob, date

 

CREATE TABLE TYPETBL (CCC CHAR(4) -- 바이트단위 : 한글 2자 ,NCC NCHAR(4) -- 유니코드 글자 4개 ,VVV VARCHAR2(4000) -- 4000 byte 가변형 문자타입 ,NVV NVARCHAR2(2000) -- 2000자가 최고 ,N1 NUMBER ,N2 NUMBER(3) ,N3 NUMBER(3,2) ,CL CLOB ,ddd date );

-- 행 삽입

 

INSERT INTO TYPETBL(CCC,NCC) VALUES('3', '졸려'; INSERT INTO TYPETBL(VVV, NVV) VALUES('글씨 들어가욧', '엔바투!');

SELECT LENGTHB(VVV), LENGTHB(NVV) FROM TYPETBL;

INSERT INTO TYPETBL(N1, N2, N3) VALUES(3.14, 314, 3.14);

SELECT N1,N2,N3 FROM TYPETBL;

-- 소수점에서는 반올림된 값이 입력된다 ※주의할것.

 

INSERT INTO TYPETBL(N1, N2, N3) VALUES(33.14, 141, 3.14711); INSERT INTO TYPETBL(CL) VALUES ('글씨를 넣어요~~~~~~'); SELECT * FROM TYPETBL; INSERT INTO TYPETBL(DDD) VALUES (TO_DATE('2001/3/4', 'yyyy/mm/dd')); SELECT * FROM TYPETBL;

-- number(3, 5) : 0.0012 두자리의 소수점이 0으로 채워져야 한다는 뜻.
-- alter(add 컬럼추가, modify 컬럼변경, rename column 컬럼이름변경,
--       drop column 컬럼삭제, drop constaint 제약조건삭제,
--       add constraint 제약조건추가 ... 등등등)

 

-- TEST 테이블 생성

 

CREATE TABLE TEST (NO NUMBER(10) ,text clob );

--  TEST 테이블 BIN 컬럼 추가

 

ALTER TABLE TEST ADD BIN CHAR(3);

-- 행 삽입

 

INSERT INTO TEST VALUES (1, '와우 좋아~', '123');

-- TEST 테이블에 BIN 컬럼 타입 길이 수정

 

ALTER TABLE TEST MODIFY BIN CHAR(6);

 

-- TEST 테이블에 BIN 컬럼 삭제

 

ALTER TABLE TEST DROP COLUMN BIN;

-- text 컬럼에 표시된 모든열을 제거 한다.

 

ALTER TABLE TEST set unused column text;

 

 

SELECT * FROM TEST;

desc test;

 

SELECT * FROM DICT
WHERE LOWER(COMMENTS) LIKE '%table%' and table_name like 'USER%';

SELECT * FROM USER_TAB_COLUMNS;
SELECT * FROM USER_TAB_COLS; -- hidden_column이 YES로 되어있음

 

-- 쓰지 않는 컬럼 삭제

 

ALTER TABLE TEST DROP UNUSED COLUMN;

-- 테이블의 데이터량이 많을 경우 바로 삭제를 하게되면 과부하가 많이 걸리므로
-- 사용자가 없는 새벽시간에 삭제를 하기위해서 위와 같이 한다.

 -- 테이블 삭제

 

DROP TABLE TEST;

 

-- Oracle 쓰레기통 내역 확인 하는 명령

 

SELECT * FROM RECYCLEBIN;

 

-- 그전에 있던 테이블을 복구해라~

 

FLASHBACK TABLE TEST TO BEFORE DROP;

 

-- 쓰레기통에 넣지 않고 바로 삭제

 

drop table TEST PURGE;

 

-- 현재 테이블 조회

 

SELECT * FROM TAB;

--emp2 테이블을 삭제한다.

 

DROP TABLE EMP2;

 

-- 쓰레기통 비우기

 

PURGE RECYCLEBIN;

-- 제약조건 (constraint)
-- Primary Key (PK), Foreign Key(FK),  Unique Key (UK)
-- Not null (NN), Check (조건을 통해서 데이터 제한)

 

CREATE TABLE CHKTBL (NO NUMBER(10) PRIMARY KEY ,SSN VARCHAR2(13) CONSTRAINT UK_CHKTBL_SSN UNIQUE ,NAME VARCHAR2(100) CONSTRAINT NN_CHKTBL_NAME NOT NULL ,JOB VARCHAR2(50) CONSTRAINT CK_CHKTBL_JOB CHECK                       (JOB IN ('인턴', '사원', '과장')) ,SAL NUMBER(10,2) ,HIREDATE DATE DEFAULT SYSDATE ,constraint CK_CHKTBL_SAL check(sal between 0 and 5000) );

 

-- 행삽입

 

INSERT INTO CHKTBL(NO, SSN, NAME, JOB, SAL) VALUES(1000, '8409161234567', '최정빈', '인턴', 5000); SELECT * FROM CHKTBL;

 

INSERT INTO CHKTBL(NO, SSN, NAME, JOB, SAL) VALUES(2000, '8409141234568', '김도영', '사원', 300);