업무에 바로 쓰는 SQL(삼성멀티캠퍼스) 5일차

-- 5일차 교육
-- 문제 풀어보기
/* 1.player_t테이블 선수중 1985년11월27일 오전10시 15분 23초이후에
태어난 선수의 이름, 생일조회
*/
-- 내가 작성한 SQL
SELECT player_name, birth
FROM PLAYER_T
WHERE birth >= TO_DATE('19851127:10:15:23', 'yyyymmdd:hh24:mi:ss');

-- 문제풀이
-- 날짜정보가 저장된 컬럼 데이터유형부터 확인
-- 1) 등록일자 - char(8) where 등록일자 = '20151209'
-- 2) 주문일자 - date where 주문일자 = '20151209' -> 간혹 이렇게 실수를 한다.
-- 날짜 문자
-- 3) 주문일자 - date where 주문일자 = to_date('2015', 'yyyy')
-- 정답)
SELECT player_name, birth
FROM player_t
WHERE birth >= TO_DATE('1985-11-27:10:15:23', 'yyyy-mm-dd:hh24:mi:ss');
/* 결과
정성호 1986-04-07
안상현 1986-03-05
한동원 1986-04-06
이강진 1986-04-25
김준 1986-12-09
/
/
2.player_t 테이블 선수 중
이름에 '수'글자가 들어 있는 선수들만
이름, 키, 소속팀명(team_name), 연고지(region_name)조회.
*/
-- 내가 작성한 SQL
SELECT * FROM TEAM_T;

SELECT player_name, NVL(height, 0), P.team_id, T.region_name
FROM PLAYER_T P, TEAM_T T
WHERE player_name LIKE '%수%' AND P.TEAM_ID = T.TEAM_ID;

-- 정답
SELECT P.PLAYER_NAME, P.HEIGHT, T.TEAM_NAME, T.REGION_NAME
FROM PLAYER_T P, TEAM_T T
WHERE player_name LIKE '%수%' AND P.TEAM_ID = T.TEAM_ID;
-- 결과 : 22개 행이 선택되었습니다.

/*
3. player_t 선수들의
이름, 키, 팀id, 본인팀최대키와 키차이 조회.
결과는 키 차이 많이 나는 선수부터 나오도록 정렬.
*/
-- 내가 작성한 SQL
SELECT player_name, height, team_id,
(SELECT MAX(height) FROM PLAYER_T WHERE team_id = P.team_id) AS "키차이"
FROM PLAYER_T P;

-- 분석함수를 사용한 예
SELECT player_name, height, team_id, MAX(height) OVER(PARTITION BY team_id) FROM PLAYER_T;
SELECT ename, sal, deptno, max(sal) OVER(PARTITION BY deptno) FROM emp;

-- 정답
select player_name, height, team_id,
MAX(height) OVER (partition by team_id)-height 키차이
from player_t
order by 키차이 desc ;

SELECT ename, sal, deptno,
MAX(sal) OVER(PARTITION BY deptno) 부서최대급여,
MAX(sal) OVER(PARTITION BY deptno)-sal 급여차액
FROM emp;
/*
4. player_t 선수중에서
키가 큰 10명의 선수이름, 키 만 나오도록 조회.
/
-- select player name, height from PLAYER_T where rownum < 11 order by height desc; <- 잘못된 SQL
-- 위 문장은 1000명 선수 중 11명만 가지고 와서 11명을 가지고 정렬하여 보여줄 뿐이다.
-- 원하고자 하는 구문은 1000명 선수를 키순으로 정렬후 제일 키큰 선수부터 11명 가져올것
-- 방법1> 키순으로 정렬한 다음 10개만 추출
SELECT *
FROM (SELECT player_name, height
FROM player_t
ORDER BY height DESC nulls last)
WHERE ROWNUM < 11;
/ 결과
서동명 196
권정혁 195
이석 194
김경두 194
최현 192
이대희 192
황연석 192
신의손 192
미트로 192
우성용 191
*/

SELECT player_name, height
FROM PLAYER_T
ORDER BY height DESC ;

-- 방법 2> 키큰 순서기준 등수 부여후 10등까지 추출
SELECT player_name, height,
RANK() OVER(ORDER BY height DESC nulls last) 등수
FROM PLAYER_T
WHERE 등수< 11;
-- where 절에서 별칭 호출 불가, 분석함수 rank() 가 제일 나중에 실행됨

SELECT *
FROM ( SELECT player_name, height,
RANK() OVER(ORDER BY height DESC nulls last) 등수
FROM PLAYER_T)
WHERE 등수< 11; -- 동차생이 있어서 의도치 않게 11명을 가져오게 되었음

SELECT *
FROM (SELECT player_name, height,
RANK() OVER(ORDER BY height DESC nulls last) 등수,
ROW_NUMBER() OVER (ORDER BY height DESC nulls last) 번호
FROM PLAYER_T)
WHERE 번호< 11;
--

-- 칠판 판서 - 연산속도 느림. 1000개 데이터에 모두에 부서명 붙인후 where 조건 걸림
SELECT ename, deptno, sal
FROM emp
WHERE sal > 1000;

SELECT ename, deptno, sal, (SELECT dname FROM dept WHERE deptno=30) 부서명
FROM emp
WHERE sal > 1000;

SELECT ename, deptno, sal, (SELECT dname FROM dept WHERE deptno=E.DEPTNO) 부서명
FROM emp E
WHERE sal > 1000;

-- 칠판 판서 - 연산속도가 빠름. 그룹핑해서 처리하면 3개 데이터에 부서명을 조회
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

SELECT deptno, SUM(sal),
(SELECT dname FROM dept WHERE deptno=30) 부서명
FROM emp
GROUP BY deptno;

SELECT deptno, SUM(sal),
(SELECT dname FROM dept WHERE deptno=E.DEPTNO) 부서명
FROM emp E
GROUP BY deptno;

-- 5. 소속선수들 평균키가 큰 상위 3개의 팀id, 평균키 조회하기.
-- 방법1> 평균키 기준 정렬후 3개만 추출
SELECT *
FROM ( SELECT team_id, AVG(height)
FROM PLAYER_T
GROUP BY team_id
ORDER BY AVG(height) DESC )
WHERE ROWNUM < 4;

-- 방법 2. 평균키 기준 등수부여 후 3등까지 추출
SELECT *
FROM (
SELECT team_id, AVG(height) 평균키,
RANK() OVER(ORDER BY AVG(height) DESC nulls last) "등수(키)",
RANK() OVER(ORDER BY COUNT(*) DESC nulls last) "등수(인원)"
FROM PLAYER_T
GROUP BY team_id)
WHERE ROWNUM < 4;

/* 판서

1) select ename, sal, sum(sal) from emp; --> 에러
2) select job, sum(sal) from emp group by job;
3) select name, sal from emp order by sum(sal); --> 에러
4) select job from emp group by job order by sum(sal);
*/

/* 6. 각 팀선수들 평균키 큰 상위 3개팀의
팀명, 평균키, 소속인원수조회
팀명(team_name) 평균키 나오도록.
방법1> 문제5번 결과를 테이블처럼 사용해서TEAM_T테이블과 조인
방법2> 문제5번문장 SELECT절에 팀명조회하는 SELECT문 사용. */

-- 내가 작성한 SQL
SELECT (SELECT team_name FROM TEAM_T WHERE team_id = A.team_id) 팀명, A.평균키
FROM (SELECT AVG(height) 평균키, team_id
FROM PLAYER_T
GROUP BY team_id
ORDER BY AVG(height) DESC ) A
WHERE ROWNUM < 4;

-- 정답 1안) JOIN을 응용한 SQL
SELECT A.*, T.team_name
FROM (
SELECT *
FROM ( SELECT team_id, AVG(height) 평균키,
RANK() OVER(ORDER BY AVG(height) desc) 등수
FROM PLAYER_T GROUP BY team_id )
WHERE 등수 < 4) A, team_t T
WHERE A.team_id = T.team_id;

-- 정답 2안) SELECT절에 팀명조회
SELECT (SELECT team_name FROM TEAM_T WHERE team_id = A.team_id) 팀명, A.평균키
FROM (SELECT AVG(height) 평균키, team_id
FROM PLAYER_T
GROUP BY team_id
ORDER BY AVG(height) DESC ) A
WHERE ROWNUM < 4;

-- 추가 comments
SELECT team_id, (SELECT team_name FROM team_t WHERE team_id = P.team_id), AVG(height)
FROM PLAYER_T P
GROUP BY team_id;

-- 연산 순서 : FROM절->GROUP BY절->SELECT절 team_id->slect절 서브쿼리->select절 AVG()

/*
7. emp 테이블 30번 부서원들을 입사일이 빠른 사원부터 나오도록
정렬후 본인 후임자가 몇일후에 입사했는지 입사간격
조회해 보기.
이름 입사일자 후임입사간격
ALLEN 1981-02-20 2
WARD 1981-02-22 68
BLAKE 1981-05-01 130
TURNER 1981-09-08 :
*/

SELECT ename, hiredate
, LEAD(hiredate, 1) OVER ( ORDER BY hiredate) 후임입사일자
FROM emp
WHERE deptno=30 ORDER BY hiredate;

-- LAG() 상위에 위치한 로우
-- Lead() 하위에 위치한 로우
-- order by hiredate 에 따르면 입사일이 빠른 순으로 정렬이 되어
-- Lead() 함수로 하위에 위치한 로우를 가져오면 선임, 후임 데이터를 형성하게 됨
-- 사용법 LAG(가져올 컬럼, 가져올 상위 개수, 상위 로우가 없을 경우 default값)
-- 사용법 LEAD(가져올 컬럼, 가져올 하위 개수, 하위 로우가 없을 경우 default값)
SELECT ename, hiredate
,LEAD(hiredate,1) OVER ( ORDER BY hiredate) "후임입사일자"
,LEAD(hiredate,1) OVER ( ORDER BY hiredate) - hiredate "간격"
FROM emp
WHERE deptno=30
ORDER BY hiredate;

-- 교재 P.173 ANSI Join
/*
오라클 조인문장 >>
from절: 테이블이름 콤마로 구분해서 나열
where절 : 조인조건 지정 (N개테이블조인시 N-1개 지정)
AND 기타처리조건

ANSI 조인문장>>
FROM절 : 콤마대신 ??? JOIN 으로 테이블구분해서 나열
USING/ ON절 사용함.
WHERE절: 기타처리조건 지정함.
*/

-- 1.emp, dept테이블 조인조건없이 모두 연결해서 조인처리.
select e.ename, d.dname from emp e, dept d ;
select e.ename, d.dname from emp e CROSS JOIN dept d;

-- 2. 테이블 조인시 컬럼이름 동일하고, = 조인처리시
/* 방법 1 /
SELECT ename, D.NAME
FROM emp e, dept d
WHERE e.deptno = d.deptno;
/ 방법 2 /
SELECT e.ename, d.dname
FROM emp e NATURAL JOIN dept d;
/ 방법 3 */
SELECT e.ename, d.dname
FROM emp e JOIN dept d USING(deptno); -- 가장 많이 사용

/*
NATURAL JOIN 과 JOIN USING()의 차이?
NATURAL JOIN
두 테이블의 일치하는 모든 열에서 같은 값을 가진 행을 선택한다.
두 테이블에서 필드명이 같으나 의미하는바가 다를 경우에도
NATURAL JOIN 에서는 두 테이블 간 같은 필드로 간주하여 JOIN을 해버림
가급적 사용을 자제할 것

JOIN USING(조인할 필드명)
JOIN 대상을명시해서 사용할 경우 쓴다. */

/* 방법 1 /
SELECT E.ename, D.NAME, e.DEPTNO
FROM emp e, dept d
WHERE e.deptno = d.deptno;
/ 방법 2 /
SELECT e.ename, d.dname, d.deptno -- 오류, JOIN 조건필드는 테이블명.필드명 로 작성 안함
FROM emp e NATURAL JOIN dept d;
/ 방법 3 */
SELECT e.ename, d.dname, d.deptno -- 오류, JOIN 조건필드는 필드명 으로 호출
FROM emp e JOIN dept d USING(deptno); -- 가장 많이 사용
-- NATURAL JOIN, JOIN USING 사용할 경우 select 절에서 필드값 가져올 경우
-- 테이블 별칭.필드명으로 사용하면 안됨. 그냥 필드명 으로 호출할 것

/* 3. 컬럼이름 다른경우, = 조건이 아닌 모든 경우 사용가능한 JOIN문 */
SELECT e.ename, d.dname
FROM emp e JOIN dept d ON(e.DEPTNO = d.deptno);

/* 1> emp, dept, salgrade, locations 테이블 조인해서
급여 1000, 3000 아닌 사원들만
사원이름, 급여,부서명,grade, city정보 조회하기. */
-- 오라클조인문장>
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
SELECT * FROM locations;

SELECT e.ename, e.sal, d.dname, s.grade, l.city
FROM emp e, dept d, salgrade s, locations l
WHERE e.DEPTNO = d.DEPTNO AND
e.SAL BETWEEN s.LOSAL AND s.HISAL AND
d.LOC_CODE = l.LOC_CODE AND
e.sal NOT IN (1000, 3000)
;
-- JOIN-ON문장>
SELECT e.ename, e.sal, d.dname, s.grade, l.city
FROM emp e JOIN dept d ON(e.deptno = d.deptno)
JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal)
JOIN locations l ON(d.loc_code = l.loc_code)
WHERE e.sal NOT IN (1000, 3000);

/* *****

1) SELECT * FROM DEPT ;
2) SELECT * FROM LOCATIONS;
dept, locations 사용해서 각 부서(dname), 근무도시(city)조회
결과는 모든 부서명이 다 표시되도록 ....
*/
SELECT d.dname, l.city
FROM dept d, locations l
WHERE d.loc_code = l.LOC_CODE(+);

-- JOIN-ON 문장(LEFT JOIN)
SELECT d.dname, l.city
FROM dept d JOIN locations l ON (d.loc_code = l.loc_code);

SELECT d.dname, l.city
FROM dept d LEFT JOIN locations l ON (d.loc_code = l.loc_code);

SELECT d.dname, l.city
FROM dept d LEFT OUTER JOIN locations l ON (d.loc_code = l.loc_code); -- OUTER 키워드는 옵션

-- JOIN-ON 문장(RIGHT JOIN)
SELECT d.dname, l.city
FROM location l RIGHT JOIN dept d ON (d.loc_code = l.loc_code);

SELECT d.dname, l.city
FROM location l RIGHT OUTER JOIN dept d ON (d.loc_code = l.loc_code); -- OUTER 키워드는 옵션

SELECT d.dname, l.city
FROM dept d, locations l
WHERE d.LOC_CODE(+) = l.loc_code(+); ----> 에러 발생,
-- (+) 연산자는 오라클에서만 사용 가능
-- (+) 연산자는 양쪽 사용 불가능, 단 ANSI JOIN으로 FULL JOIN 으로 처리 가능

SELECT d.dname, l.city
FROM locations l FULL OUTER JOIN dept d ON (d.loc_code= l.loc_code);

-- 교재 p.253 CHAPTER 9. DML 명령어
-- select 결과를 곧바로 TABLE 로 저장하는 방법(★★★)
-- select 문장 앞에 CREATE TABLE 테이블명 AS 를 붙이면 된다.
-- 칠판 판서
CREATE TABLE t1 as
SELECT empno, ename
FROM emp
WHERE deptno = 20;

SELECT * FROM t1;

-- select 결과를 테이블 구조화 저장할 때
-- 필드명을 바꿔서 저장할 경우 별칭으로 필드명 지정
CREATE TABLE t2 as
SELECT empno 번호, ename 이름, 3300 급여
FROM emp
WHERE deptno = 30;

SELECT * FROM t2;

SELECT * FROM emp WHERE 1=2;
-- 데이터를 하나도 뽑고 싶지 않을 때, 의도적! where 절은 무조건 false 를 반환
-- 위 SQL 문을 TABLE로 형성하면 어떤 결과가 나오지 ?

CREATE TABLE t3 as
SELECT * FROM emp WHERE 1=2;
-- emp 테이블과 동일한 테이블 t3를 형성하되, 데이터는 가져오지 말 것.

SELECT * FROM t3; -- 표시할 데이터가 없습니다.

DESC t3; -- 테이블 틀이 형성되어 있음.

/* 1. */
CREATE TABLE t7 AS
SELECT empno id, ename name, 7700 sal
FROM emp WHERE deptno=20;

/* 2. t7 테이블에 신규자료 입력 */
SELECT * FROM t7; -- 1
INSERT INTO t7 VALUES (100, '김구', 9900); -- 2
SELECT * FROM t7;
INSERT INTO t7(NAME, id) VALUES ('김길동',200); -- 3
SELECT * FROM t7;

INSERT INTO t7 -- 4
SELECT empno, ename, sal*2
FROM emp WHERE deptno = 10;

SELECT * FROM t7; -- 5

/* 3. t7테이블 기존 컬럼값 변경시 */
UPDATE t7
SET name='홍길동', SAL=8800
WHERE id=200;

SELECT * FROM t7;

-- 문제. t7 테이블 김구 사원의 급여를 emp 테이블 전체급여합계값으로 변경하기
UPDATE t7
SET sal = (SELECT SUM(sal) FROM emp)
WHERE name='김구';

SELECT * FROM t7;

/* 4. 테이블 ROW 삭제시 */
DELETE t7 WHERE id = 100; -- 1
SELECT * FROM t7; -- 2

-- t7 테이블 자료 중 emp 테이블 KING 사원보다 급여 적게 받는 사원 삭제하기
-- DELETE t7 WHERE sal < (emp테이블KING 급여);
DELETE t7 WHERE sal < (SELECT sal FROM emp WHERE ename='KING');
SELECT * FROM t7;

-- 교재 p.273 MERGE 문 - 집계 데이터 반영할 때 쓰는 함수
-- 일일판매실적(판매일자, 사번, 수량)과 실적집계(사번, 총수량) 테이블이 존재할 때,
-- 일일판매실적의 수량을 실적집계의 총수량에 반영하고자 할때
/*
MERGE INTO tatal_t
USING day_t
ON (total_t.사번 = day_t.사번)
WHEN matched THEN -- 실접집계 테이블에 사원 사번이 있는 경우 처리
UPDATE SET total_t.총수량 = total_t.총수량 + day_t.총수량
WHEN NOT matched THEN -- 옵션인데, 신규사원(사번 신규등록)이 판매실적이 있을 경우 처리!
INSERT VALUES (day_t.사번, day_t.수량);
*/

-- 특정일자에 대한 데이터만 집계 데이터에 반영하고자 할 경우
/*
MERGE INTO tatal_t
USING (SELECT * FROM day_t WHERE 판매일자='20151209')
ON (total_t.사번 = day_t.사번)
WHEN matched THEN
UPDATE SET total_t.총수량 = total_t.총수량 + day_t.총수량
WHEN NOT matched THEN
INSERT VALUES (day_t.사번, day_t.수량);
*/

-- 문제 1. MERGE() 실습하기
CREATE TABLE total_t AS
SELECT empno id, ename NAME, 3000 total_sal
FROM emp WHERE deptno = 30;

SELECT * FROM total_t;
/*
total_t 테이블 자료를 emp 테이블과 비교해서 동일한 사번을 가진 사원이 존재하면
total_sal값을 기존값 + emp 테이블 sal 값으로 변경,
해당 사원이 없으면 emp 테이블 사번, 이름, 급여값으로 등록
*/
-- 내가 작성한 SQL 문
MERGE INTO total_t t
USING emp e
ON (t.id = e.empno)
WHEN matched THEN
UPDATE SET t.total_sal = t.total_sal + e.sal
WHEN NOT matched THEN
INSERT VALUES (e.empno, e.ename, e.sal);

SELECT * FROM total_t;

-- 강사 SQL 문
MERGE INTO total_t t
USING emp e
ON ( t.id = e.empno )
WHEN MATCHED THEN
UPDATE SET t.total_sal = t.total_sal + e.sal
WHEN NOT MATCHED THEN
INSERT VALUES ( e.empno, e.ename, e.sal ) ;

SELECT * FROM TOTAL_T;

/* 정리 문제 /
/ 1. 실습 테이블 형성 */
CREATE TABLE 사원(사번 number(4), 이름 VARCHAR2(6), 별명 CHAR(6));

/* 2. varchar2 와 char 타입 비교 */
INSERT INTO 사원 VALUES(1, 'jane', 'babo');
SELECT * FROM 사원;

SELECT length(이름), LENGTH(별명) FROM 사원;

SELECT RPAD(이름, 7, ''), RPAD(별명, 7, '') FROM 사원;

/* 3. 사원테이블 컬럼데이터 타입/길이 변경 */
INSERT INTO 사원 VALUES (2, '세종대왕', 'king');
-- 결과 : ORA-01401: inserted value too large for column
SELECT * FROM 사원;

-- 테이블 구조 변경
ALTER TABLE 사원 MODIFY(이름 VARCHAR2(12));
INSERT INTO 사원 VALUES(2, '세종대왕', 'king');
SELECT * FROM 사원;

/* 4. 사원테이블 전화번호, 주소정보도관리하도록 컬럼 추가 */
ALTER TABLE 사원 ADD(전화번호 VARCHAR2(13), 주소 VARCHAR2(10));

SELECT * FROM 사원;

/* 5. 불필요한 컬럼제거 -- 회사에서는 하지 말것. 주의 !! */
ALTER TABLE 사원 DROP (별명);
SELECT * FROM 사원;
ALTER TABLE 사원 DROP COLUMN 전화번호;
SELECT * FROM 사원;

--****
-- 테이블 용량 확인 하는 방법
SELECT bytes
FROM user_segments
WHERE segment_name='EMP'; -- segment_name 의 value 는 꼭 대문자로 입력

SELECT bytes/1024 AS "KB"
FROM user_segments
WHERE segment_name='EMP';

/* 테이블 생성하면 크기는? */
CREATE TABLE t100(id number, NAME VARCHAR2(20));
SELECT bytes
FROM user_segments
WHERE segment_name='T100'; -- DB의 기본 SIZE로 생성, 65536 Bytes

/* 테이블의 SIZE를 부여하여 생성하는 법 - STORAGE() */
CREATE TABLE t101 (id NUMBER, NAME VARCHAR2(20)) STORAGE(INITIAL 3m);
SELECT bytes, bytes/1024 AS "KB", bytes/1024/1024 AS "M"
FROM user_segments
WHERE segment_name='T101'; -- DB SIZE : 3145728 Bytes, 3072 KB, 3M

/* 문제 */
-- t1 자료중 일부자료만 삭제시 DELETE t1 WHERE 조건;

-- t1 모든 ROW 삭제시
DELETE t1;
-- DELETE t1; 의 처리연산 순서
---- 1) A. 삭제할 자료를 다른 DISK 공간에 복사 (이 옵션 생략 불가)
---- 2) B. 해당 Row 삭제
---- ※ 그래서 시간이 걸린다(오버헤드 발생).
---- 그럼 왜 이 SQL문을 쓰지 ? 롤백(ROLL BACK) 대비용

TRUNCATE TABLE t1; -- 테이블 남아있음
-- TRUNCATE TABLE t1;의 연산 순서
---- 1) A. 복사 X
---- 2) B. Row 삭제만
---- 3) C. table 크기를 처음 생성크기로 줄임

DROP TABLE t1; -- 테이블 남아있지 않음

/* 제약조건 설정할 때 contraint 이름 사용하는 이유 ? */
CREATE TABLE c1 (
id NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(10));

INSERT INTO c1 VALUES(1, 'a'); -- 1) 성공
INSERT INTO c1 VALUES(1, 'b'); -- 2) 오류
-- ORA-00001: unique constraint (SQL00.SYS_C0014255) violated

-- 오류 메시지를 이해하기 힘들 때 constraint 로 commnet 를 달수가 있다.
CREATE TABLE c2 (
id NUMBER(4) CONSTRAINT C2_IDPK PRIMARY KEY,
NAME VARCHAR2(10));
INSERT INTO c2 VALUES(1, 'tom'); -- 1) 성공
INSERT INTO c2 VALUES(1, 'jane'); -- 2) 에러 메시지 확인
-- ORA-00001: unique constraint (SQL00.C2_IDPK) violated

--*****
/* 2. player_t 테이블 이름 대신 pt로 줄여서 사용하도록 SYNONYM 키워드 */
CREATE SYNONYM pt FOR player_t;
SELECT * FROM pt;
SELECT * FROM PLAYER_T;

CREATE TABLE t1 AS
SELECT empno, ename FROM emp; -- CREATE TABLE : DISK 에서 생성, 실제데이터

CREATE VIEW v1 AS
SELECT empno, ename FROM emp; -- CREATE VIEW : DISK 에 생성되지 않음, SQL 문

-- t1 테이블 생성시 관리용정보(데이터 딕셔너리)에 다음 정보가 생성됨
-- T1 : 테이블 크기, 위치, 컬럼데이터타입 ....
-- V1 : select empno, ename from emp; SQL문이 만들어져 있음

-- T1과 V1의 차이점
-- T1은 과거 데이터, V1은 실시간 데이터
-- V1은 원본 데이터의 DB의 컬럼을 구별하여 보여줌
-- DB에는 공개컬럼과 비공개컬럼을 가진 테이블이 있는데, 컬럼을 구분하여 보여주고자할 때
-- select * from emp; --> 공개컬럼과 비공개컬럼 모두 조회됨
-- seelct * from v1; --> 공개컬럼만 모두 조회됨(비공개컬럼은 공개되지 않도록 View로 생성)

SELECT * FROM t1; --
SELECT * FROM v1;

/* 뷰 생성/이용 */
CREATE VIEW v1 AS
SELECT empno, ename FROM emp;

SELECT * FROM v1;

SELECT view_name, text FROM user_views; -- view 관리용 정보를 보는 문장

SELECT * FROM emp;
INSERT INTO v1 VALUES ( 7771, 'viewdata');
SELECT * FROM emp; -- 저장되었음 7771, viewdata, null, null ....

SELECT * FROM v1;

-- 교재 p.327 SEQUENCE 키워드 학습 : 장점 - 게시판번호 부여 속도가 빠르다.
-- 2. 게시판 테이블 자료입력시 게시물번호 설정하는 방법
---- 1) 현재 등록게시물 번호 중 최댓값 조회 후 +1 하는 프로그램 코드
--> 프로그래밍 언어를 사용하여 입력함
---- 2) 시퀀스에서 뽑은 숫자를 이용해서 등록
CREATE TABLE 게시판(
번호 NUMBER(5) PRIMARY KEY,
제목 VARCHAR2(10));

CREATE SEQUENCE aa; -- 대기표 발생기 이름 부여(aa)

INSERT INTO 게시판 VALUES(AA.NEXTVAL, '입력1');
INSERT INTO 게시판 VALUES(AA.NEXTVAL, '두번째');
INSERT INTO 게시판 VALUES(AA.NEXTVAL, '입력3');
SELECT * FROM 게시판;
/* 결과
1 입력1
2 두번째
3 입력3
/
SELECT AA.NEXTVAL, dname FROM dept; -- 단점 : 다른 SQL문에서 번호가 부여되어버림
/ 결과
4 ACCOUNTING
5 RESEARCH
6 SALES
7 OPERATIONS
8 INSA
/
INSERT INTO 게시판 VALUES(AA.NEXTVAL, '입력4'); -- 일련번호가 유일성이 없음
SELECT * FROM 게시판;
/ 결과
1 입력1
2 두번째
3 입력3
9 입력4
*/

/* 칠판 판서 - 교재 p.337 사용자 관리
사용자 생성과정

1. 유저명/패스워드 설정
   CREATE USER sql17 IDENTIFIED BY password;
2. 사용자가 할 수 있는 작업 세팅 :
   GRANT create table, create session, create view, create synonym TO 사용자명;
3. 권한 회수
   REVOKE create table, create session, create view, create synonym FROM 사용자명;
4. DB 관련 작업을 그룹화 하였음
   GRANT connect, resource TO 사용자명;

*/
SELECT * FROM user_sys_privs; -- 본인이 할수 있는 권한

SELECT * FROM role_sys_privs;