• 문제 : 콘솔 앱 프로젝트 생성 후 '미리 컴파일된 헤더' 사용 옵션만 변경하니 오류 발생

• 원인 사용한다고 명시만 하고, 해당 파일이 추가되어 있지 않아, 파일을 못 찾으니 발생하는 에러

• 해결

  • 단순히 설정 변경만으로 해당 헤더 파일이 생성되는 것은 아니고, pch.h 파일을 생성한 후 미리 컴파일할 대상 목록을 #include로 명시
  • 또는 콘솔 앱 프로젝트 생성이 아닌, Window 데스크톱 마법사 프로젝트 생성으로 하여, 초기 설정 탭에서 미리 컴파일된 헤더 추가를 선택 <- 여기서는 'stdafx.h'가 아닌 pch.h 파일 생성됨

https://gdnn.tistory.com/159

javac -version

없는 명령어라는 오류가 나와서

환경변수 설정 확인....

Path 설정 창 편집 버튼을 눌렀더니 모든 변수가 ;로 연달아서 한번에 나오는 오류...? 발생

거기서 JAVA_HOME\bin 변수 설정 부분을 봤더니

"%JAVA_HOME%\bin"; 로 확인되어서

""를 지웠더니 오류 해결.... ""을 작성한 적은 없었는데..?

javac, java 정상 실행 완료

1. spfile
2. pfile
3. ...?

파일 위치는 아래로 공통됨

$ORACLE_HOME/dbs

해당 위치에 spfile이 없으면, pfile을 읽어들이며 startup 됨

임의로 강제하여 읽어들이는 파일을 지정할 수 있음

startup pfile='pfile 파일 경로'

기동 시에 적용된 파라미터 파일 확인

show parameter spfile

해당 값이 있으면 : spfile 해당 값이 없으면 : pfile

정적 파라미터 / 동적 파라미터

정적 파라미터 : 재기동하지 않아도 즉각 반영되는 파라미터 동적 파라미터 : 변경시에 재기동을 필요로 하는 파라미터

SELECT name, value, issys_modifiable FROM V$PARAMTER;

issys_modifiable

• immediate : alter sytem 명령어로 바로 변경되는
• deferred : alter sytem 명령어로 변경이 되지만, 적용은 다음에 접속되는 세션부터 (session 관련 파라미터)
• false : spfile을 변경해야 적용되는 파라미터 (반영시에 재기동 필요)

pfile

DBA가 임의로 설정하는 파라미터 값

파일명 : initSID.ora

spfile

오라클 엔진에서 설정하는 파라미터 값

파일명 : spfileSID.ora

파라미터 수정 명령어

alter system set parameter=<변경값> scope=<both|memory|spfile> ;

spfile : spfile만 수정 memory : 현재 DB에만 적용이 되며, 재기동하면 설정값이 초기화되므로 정적 파라미터는 memory 옵션으로 변경 불가 (ORA-02095; 지정된 초기화 파라미터는 변경될 수 없습니다) both : spfile, memory 모두 변경됨 (default 설정)

위 명령어로 수정시에, 동적 파라미터만 바로 반영이 된다. 정적 파라미터는 재기동시에만 변경되는 파라미터.

spfile to pfile

create pfile from spfile;

pfile to spfile

create spfile from pfile;

변경여부 확인

V$SPARAMETER : spfile 변경 여부 확인 show parameter or V$PARAMETER : 현재 DB 변경 여부 확인

SELECT name, value FROM V$PARAMETER;

아래와 같은 절차적 언어의 요소를 더하여 향상된 데이터 처리를 가능케 함.

• 선택처리(IF)
• 반복처리(LOOP, WHILE, FOR)

PL/SQL의 구성

DECLARE ~ BEGIN ~ EXCEPTION ~ END;

• 선언부 (DECLARE)
• 실행부 (BEGIN)
• 예외처리부 (EXCEPTION)

CREATE PROCEDURE/FUNCTION : 저장 프로시저 혹은 함수 생성

프로시저와 함수의 구분 : 모듈이 반환하는 값의 수에 의해 결정

함수 : 반환되는 값의 수가 하나 프로시저 : 반환되는 값의 수가 여러개

패키지 : 프로시저/함수의 그룹화

SQL> @---.sql -- 프로시저 생성 (에러가 없으면 생성 완료)

프로시저 디버깅은 DB TOOL을 사용하여 가능함 (SQL Developer)

SQL> execute 프로시저명(매개변수); -- 프로시저 실행

CREATE PROCEDURE TEST1 (v_sub_name in varchar2(30)

is

변수 선언

variable_name {mode} data_type

자체 사용 변수 선언 시, mode는 생략

IS 이전 변수 선언 : 매개변수 선언 (호출 시 입력받는 값) IS 이후 변수 선언 : 모듈에서만 사용되는 변수 (자체 사용 변수)

mode

• IN : 프로시저가 호출될 때 전달되는 변수
• OUT : 프로시저 실행 후 반환되는 변수
• INOUT : 2개의 기능을 모두 갖는 변수

저장 프로시저의 경우, 테이블의 열의 값을 저장하게 됨으로 열의 데이터 타입을 참조하여 선언이 가능함

• %TYPE : 열의 데이터 타입을 참조

v_sub_no enrol.sub_no%type -- 열의 값들을 저장하는 변수 선언

• %ROWTYPE : 테이블의 모든 열의 데이터 타입을 참조

v_enrol enrol%rowtype -- 전체열을 모두 정의하는 변수 선언

ex) 열의 값 참조시

v_enrol.sub_no

실무에서는 스칼라타입(직접 데이터 타입 선언)의 변수보다는 참조타입 변수를 많이 사용함

선택 처리문

if 조건1 then 실행문1; else if 조건문2 then 실행문2; else 실행문 end if;

반복 처리문

1) LOOP ~ END LOOP (반복 횟수를 정할 수 없을 때) 반복을 중단하는 조건문 주의 (무한반복)

LOOP 실행문1; EXIT WHEN 반복 중단 조건문; END LOOP;

2) FOR ~ LOOP (반복 횟수 지정) [REVERSE]로 증분값을 감소하는 방안으로 사용가능함

FOR 변수 IN 초기값..최종값 LOOP 실행문; END LOOP;

예제는 아래와 같다.

FOR v_cnt IN 1..10 LOOP ....... END LOOP

3)WHILE ~ END LOOP (조건이 만족되는 동안 반복실행)

WHILE 반복 조건 실행문; END LOOP;

저장 프로시저 작성 (.sql)

CREATE [OR REPLACE] PROCEDURE 스키마.프로시저명 ( 매개변수명 [MODE] DATA_TYPE, .......... ) IS 변수명 DATA_TYPE, .......... BEGIN ~ EXCEPTION ~ END; /

예제)

CREATE OR REPLACE PROCEDURE test3 (v_stu_no in student.stu_no%type, v_stu_name out student.stu_name%type) IS BEGIN SELECT stu_name INTO v_stu_name FROM student WHERE stu_no = v_stu_no;

END test3; /

SQL> @test3.sql SQL> variable d_stu_name varchar2(12); -- 바인드 변수 선언 SQL> excute test3(20153075, :d_stu_name); -- :d_stu_name SQL> print d_stu_name;

저장 함수 작성

CREATE [OR REPLACE] FUNCTION 스키마.함수명 ( 매개변수명1 [mode] data_type, ..............) return data_type --- 반환 데이터의 자료형 정의 IS variable data_type, ............ BEGIN ........ RETURN variable; EXCEPTION ........ END;

저장 함수를 실행하고 돌아갈 때 하나의 값을 가지게 되며, 이 값은 함수의 이름에 저장됨 따라서, 함수의 이름에 반환 데이터를 저장하기 위한 데이터 타입이 필요함

return data_type;

예제)

CREATE OR REPLACE FUNCTION test6 (v_enr_grade in number) return char IS enr_score char BEGIN IF v_enr_grade >= 90 then enr_score := 'A'; ELSE IF v_enr_grade >= 80 then enr_score := 'B'; ELSE IF v_enr_grade >= 70 then enr_score := 'C'; ELSE IF v_enr_grade >= 60 then enr_score := 'D'; ELSE enr_score := 'F'; END IF; RETURN (enr_score); END test6; /

SQL> @test6.sql SQL> variable d_score char; SQL> excute :d_score := test6(95); SQL> print d_score;

SQL> select enr_grade, test6(enr_grade) as score from enrol where stu_no = '20131001';

select user, host from mysql.user order by 1,2;

계정 생성

CREATE USER [계정명]@[접속 IP] IDENTIFIED BY [비밀번호];

권한 부여

GRANT {권한} ON {테이블명} TO {계정};

EX )

GRANT SELECT, PROCESS, SHOW DATABASES, SHOW VIEW ON . TO [계정명]@[IP];

계정 권한 조회

show grants for '{계정}';

생성 암호화된 비밀번호로 계정 생성

Mysql 8.0

CREATE USER [계정명]@[IP] *identified with mysql_native_password as *'*77B368A87EAD78B0629FC94070814833DAAADE0A';

변경비밀번호 시도 횟수 초과되었을 때

계정 lock 해제

ALTER USER [계정명] account unlock;

INDEX RANGE SCAN - 숫자형 컬럼 인덱스

INDEX RANGE SCAN : 인덱스에서 조건과 일치하는 행을 찾았을 때에, 바로 스캔을 멈추지 않고 아래의 행까지 스캔하는 것

만약 조건절이 UNIQUE 제약 조건(행의 값이 유일무이)이 걸려있다면, INDEX RANGE SCAN을 하지 않음

• 하나만 스캔함

인덱스가 있음에도 불구하고 FULL TABLE SCAN을 한다면, 아래와 같이 힌트를 명시한다

INDEX SCAN : /+index(테이블명 인덱스명)/

select /+index(emp emp_sal)/ ename, sal from emp where sal = 1600;

INDEX RANGE SCAN - 중복된 데이터가 있는 컬럼

중복된 값을 가지는 칼럼에 대해선 CREATE UNIQUE INDEX가 안됨

CREATE INDEX emp_job ON emp(job);

일치하는 행들을 연달아서 스캔하다가, 일치하지 않는 값을 만나면 스캔을 멈추고 rowid로 table access를 함

이럴때는 where절의 좌변을 가공하지 마라

where절의 인덱스 칼럼이 가공되었다면

인덱스 칼럼을 가공하지 말아라

인덱스 컬럼을 가공하여 조건을 걸게 되면, 인덱스 테이블 access를 하지 못하고 full table scan을 하게 됨

문자형 칼럼예시

날짜형 칼럼예시

SELECT 16 NUM1, 24 NUM2 FROM DUAL;

WITH NUM_D AS ( SELECT 16 NUM1, 24 NUM2 FROM DUAL; ) SELECT LEVEL, MOD(NUM1, LEVEL), MOD(NUM2, LEVEL) FROM NUM_D CONNECT BY LEVER <= NUM2;

WITH NUM_D AS ( SELECT 16 NUM1, 24 NUM2 FROM DUAL; ) SELECT LEVEL, MOD(NUM1, LEVEL), MOD(NUM2, LEVEL) FROM NUM_D WHERE MOD(NUM1, LEVEL) = 0 AND MOD(NUM2, LEVEL) = O CONNECT BY LEVER <= NUM2;

WITH NUM_D AS ( SELECT 16 NUM1, 24 NUM2 FROM DUAL; ) SELECT MAX(LEVEL) AS "최대 공약수" FROM NUM_D WHERE MOD(NUM1, LEVEL) = 0 AND MOD(NUM2, LEVEL) = O CONNECT BY LEVER <= NUM2;

ACCEPT P_N1 PROMPT ' 첫번째 숫자를 입력하시오.'; ACCEPT P_N2 PROMPT ' 두번째 숫자를 입력하시오.';

WITH NUM_D AS ( SELECT 16 NUM1, 24 NUM2 FROM DUAL; ) SELECT NUM1, NUM2, (NUM1/MAX(LEVEL))* (NUM2/MAX(LEVEL)) * MAX(LEVEL) AS "최소 공배수" FROM NUM_D WHERE MOD(NUM1, LEVEL) = 0 AND MOD(NUM2, LEVEL) = O CONNECT BY LEVER <= NUM2;

SQL문을 실행하기 전에 내부적으로 생성한 SQL 실행 계획

1. PARSER : 사용자의 SQL문이 문제가 있는지를 확인

2. OPTIMIZER

1) RULE-BASED OPTIMIZER (RBO) : 오라클의 우선 순위에 따라 실행계획 생성 2) COST-BASED OPTIMIZER (CBO) : 비용이 적게드는 방법으로 실행계획 생성 (융통성 있게) - ORACLE DEFAULT

CBO이기 때문에 SQL 튜닝이 필요함 - 그때그때 실행 성능이 달라짐

3. ROW SOURCE GENERATOR : 플랜을 완성

4. SQL EXECUTION : 실행 계획에 따라서 SQL문 실행

예상 실행계획

SQL문을 실행하기 전에 만든 예상 계획

SQL문 전에 'EXPLAIN PLAN FOR'을 작성하여 실행

EXPLAIN PLAN FOR SELECT ename, sal FROM emp WHERE sal = 1300;

SELECT * FROM TABLE(dbms_xplan.display); -- 예상 실행계획 확인 가능

안쪽에서 바깥 순서로 읽으면 됨 - 1,0 순서

실제 실행계획

SQL문을 실행할 때 사용했던 실행계획

SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME, SAL FROM EMP WHERE SAL=1300;

/+ GATHER_PLAN_STATISTICS */ /+ .... */ : 힌트

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

E-ROWS/A-ROWS : 예상 행 수, 실제 행 수 A-TIME : 수행 시간 BUFFERS : 수행하기 위해 읽어들인 오라클 메모리의 수 -- 수가 작을수록 성능이 좋은 SQL문

예상 실행계획과 다르게 실제 실행계획에서는 버퍼의 수를 확인할 수 있다

힌트

SQL을 실행할 때 옵티마이저로 하여금 힌트대로 실행계획을 생성해달라고 주문

SELECT /*+ GATHER_PLAN_STATISTICS FULL(EMP) */ ENAME, SAL FROM EMP WHERE SAL=1300;

FULL(EMP) : EMP 테이블을 FULL SCAN하라 GATHER_PLAN_STATISTICS : 실제 실행계획을 보여라

인덱스를 생성했을 때

CREATE INDEX emp_sal ON emp(sal);

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(EMP EMP_SAL) */ ENAME, SAL FROM EMP WHERE SAL=1300;

INDEX(EMP EMP_SAL) : EMP_SAL이란 인덱스를 타서 EMP 테이블을 스캔하여라

버퍼의 개수가 2개로 줄었음

조건에서 사용되는 칼럼에 인덱스를 생성한 후 실행하면, 성능이 좋아진다 (사용되는 버퍼의 수가 줄어든다)

인덱스

쿼리문의 검색 속도를 높이는 DB OBJECT

인덱스는 컬럼값과 ROWID로 이루어져있고, 컬럼값은 ASCENDING하게 정렬되어 있음

숫자형 칼럼에 걸린 인덱스에 대한 인덱스 테이블을 출력

SELECT SAL, ROWID FROM EMP; -- 테이블에서 값을 읽어옴

SELECT SAL, ROWID FROM EMP WHERE SAL >= 0; -- 인덱스 테이블을 출력함

문자형 칼럼에 걸린 인덱스에 대한 인덱스 테이블을 출력

SELECT ENAME, ROWID FROM EMP WHERE ENAME > ' '; -- 인덱스 테이블 출력

실제 실행 계획을 출력해보면, 인덱스 테이블에서만 읽어왔다는 걸 알 수 있음

문자형 칼럼에 걸린 인덱스에 대한 인덱스 테이블을 출력

SELECT HIREDATE, ROWID FROM EMP WHERE HIREDATE < TO_DATE('9999/12/31','RRRR/MM/DD');

우리나라 남자들이 가장 많이 걸리는 암?

select * from cancer where 성 != "남녀전체" and 발생연도='1999' and 발생자수 is not null and 암종 != '모든암' and 성='남자' order by 발생자수 desc fetch first 4 rowd only;

연설문을 오라클 테이블에 문장별로 저장

SELECT REGEXP_SUBSTR('I NEVER GRADUATED FROM COLLEGE', '[^ ]+', 1, 2) word from dual;

해당 문장에서 공백이 아닌 어절 여러개([^ ])를 검색해서, 그중 첫번째부터 검색해서 두번째에 나오는 것을 출력

출력문을 어절 단위로 출력

select regexp_substr(lower(speech_text), '[^ ]+', 1, a) word from speech, (select level a from dual connect by level <=52);

테이블(연설문) 전체 데이터 중에서 가장 많이 나온 단어 순서로 출력

select word, count(*) from ( select regexp_substr(lower(speech_text), '[^ ]+', 1, a) word from speech, (select level a from dual connect by level <=52)

  )

where word is not null group by word order by count(*) desc;

스피치 연설문 테이블을 어절로 나눈 결과를 뷰로 생성함

create or replace view speech_view as select regexp_substr(lower(speech_text), '[^ ]+', 1, a) word from speech, (select level a from dual connect by level <=52);

스피치 연설문 중에서 긍정단어와 일치하는 단어의 개수를 출력

select count(word) as 긍정단어 from speech_view where lower(word) in (select lower(p_text) from positive);

select * from crime_day unpivot (cnt for day_cnt in (sun_cnt, mon_cnt, tue_cnt, wed_cnt, thu_cnt, fri_cnt, sat_cnt));

unpivot : 칼럼과 행이 변환됨

절도 범죄에 대해서 가장 많이 발생한 요일별로 순위매겨서 출력

select day_cnt, cnt, rank() over (order by cnt desc) rnk from crime_day_unpivot where trim(crime_type) = '절도';

1위인 칼럼만 출력하고 싶으니, from절의 서브쿼리로 넣어야 함

select * from (select day_cnt, cnt, rank() over (order by cnt desc) rnk from crime_day_unpivot where trim(crime_type) = '절도' ) where rnk = 1;

대학등록금이 비싼 순서대로 순위를 매겨서 출력

select university, tuition_fee, rank() over (order by tuition_fee desc nulls last) 순위 from university_fee;

select * from ( select university, tuition_fee, rank() over (order by tuition_fee desc nulls last) 순위 from university_fee; ) where 순위 = 1;

계층형 질의문 + with 절

with loop_table as (select level as num from dual connect by level <= 9) select '2' || ' x '|| num || ' = '|| 2*num as "2단" from loop_table;

1번부터 100번까지의 합?

select sum(level) from dual connect by level <= 100;

1번부터 100번까지의 합에서 55를 뺀 값?

select sum(level) from dual where level != 55 connect by level <= 100;

구구단 1~9단 출력

with loop_table as (select level as num from dual connect by level <= 9), gugu_table as (select level + 1 as gugu from dual connect by level <= 8) select to_char(a.num) || ' x ' || to_char(b.gugu) || ' = ' || to_char(b.gugu * a.num) as 구구단 from loop_table a, gugu_table b; -- 전체에 대해서 조인

2,5,7단만 빼고 출력

with loop_table as (select level as num from dual connect by level <= 9), gugu_table as (select level as gugu from dual connect by level <= 9) select to_char(a.num) || ' x ' || to_char(b.gugu) || ' = ' || to_char(b.gugu * a.num) as 구구단 from loop_table a, gugu_table b where a.num in (2,5,7);

직각 삼각형 출력

with loop_table as (select level as num from dual connect by level <= 8) select lpad('', num, '') as star from loop_table;

반대로 출력하기

with loop_table as (select 9-level as num from dual connect by level <= 8) select lpad('', num, '') as star from loop_table;

삼각형 출력

with loop_table as (select level as num from dual connect by level <=8) select lpad(' ', 10-level, ' ') || lpad('', num, '') as "Triangle" from loop_table;

메인 쿼리와 서브 쿼리문을 실행할 때의 시간이 오래 걸릴 때에 사용함

SELECT job, sum(sal)
FROM emp
GROUP BY job
having sum(sal) > (select avg(sum(sal))
                    from emp
                    group by job);

20분(메인 쿼리) + 20분(서브 쿼리) = 40분

아래와 같이 작성하면, 실행 시간 단축 (동일한 결과)

WITH job_subsal as (SELECT job, sum(sal) as 토탈
                    FROM emp
                    GROUP BY job)
SELECT job, 토탈
FROM job_subsal
WHERE 토탈 > (SELECT avg(토탈)
                FROM job_subsal);

job_subsal : 임시 테이블명 (as절의 select 결과를 job_subsal의 데이터로 함)

아래 쿼리문에서 사용된 후, 사라지는 테이블

with절에서 20분을 소요하고, 아래에서는 이미 생성된 테이블에 대해서 쿼리문을 실행하는 것이므로 빠르게 수행됨

with절을 너무 많이 남발하면, 디스크 공간에 많은 temp table이 생겨남... (주의)

부서번호별 토탈월급을 출력하는데 부서번호별 토탈월급들의 평균값보다 더 큰 것만 출력

with dept_sumsal as (select deptno, sum(sal) as sumsal from emp group by deptno) select deptno, sumsal from dept_sumsal where sumsal > (select avg(sumsal) from dept_sumsal);

WITH절 - SUBQUERY FACTORING

FROM절 서브쿼리로는 구현할수 없는 부분을 구현 가능하게 함

WITH job_sumsal as (select job, sum(sal) 토탈 from emp group by job), deptno_sumsal as (select deptno, sum(sal) 토탈 from emp group by deptno having sum(sal) > (select avg(토탈) + 3000 from job_sumsal)

                   )

select deptno, 토탈 from deptno_sumsal;

입사한 년도와 입사한 년도별 토탈월급을 출력하는데 부서번호별 토탈월급들의 평균값보다 더 큰 것만 출력

with deptno_sumsal as (select deptno, sum(sal) 토탈 from emp group by deptno), hire_sumsal as (select to_char(hiredate,'RRRR') hire_year, sum(sal) 토탈 from emp group by to_char(hiredate,'RRRR) having sum(sal) > (select avg(토탈) from deptno_sumsal) ) select hire_year, 토탈 from hire_sumsal;

변경 이력 확인

1. COMMIT이 진행되어야 VERSION이 업데이트 됨
2. versions_starttime의 초기값은 null로 되어있으므로, null first를 order by절에 작성하면 순서대로 확인하기 용이함
3. to_timestamp 값은 특정 시점을 시작지점으로 설정할 수 있음 between timestamp 구문에서 종료시점도 설정 가능함

SELECT ename, sal, deptno, versions_starttime, versions_endtime, versions_operation FROM emp VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('21/08/11 13:21:10','RRRR-MM-DD HH24:MI:SS') -- 조회하려는 시간의 시작시간 AND MAXVALUE -- 현재 시간 WHERE ename = 'KING' ORDER BY versions_starttime NULLS FIRST; -- 시간이 지남에 따라 정렬/ NULL값을 제일 처음에

FLASHBACK TRANSACTION QUERY

예제 125번까지 보고 나서 20강 10분 정도에 다시 돌아와서 실습

실수로 DELETE FROM -- ; COMMIT; 을 실행하여 테이블 내 데이터가 모두 사라졌을 때 복구할 수 있는 기능

아무때나 복구할 수 있는 것은 아님

show parameter undo;

undo_retention : 복구할 수 있는 최대 시간 (900초)

롤백은 마지막 커밋한 부분까지만 돌릴 수 있어서, ROLLBACK으로 살아나지 못함

SELECT * FROM emp AS OF TIMESTAMP(systimestamp - interval '5' minute); -- 현재 시간에서 5분전의 데이터를 조회

현재 시각을 출력하는 쿼리문

select systimestamp from dual;

INSERT INTO emp SELECT * FROM emp AS OF TIMESTAMP(systimestamp - interval '5' minute); -- 현재 시간에서 5분전의 데이터를 다시 emp 테이블에 입력

commit;

사원 테이블의 월급 칼럼을 10분전 상태로 되돌리시오 - commit이후임

merge into emp e using (select empno, sal from emp as of timestamp(systimestamp - interval '10' minute) ) s on (e.empno = s.empno) when matched then update set e.sal = s.sal;

FLASHBACK TABLE : 실수로 삭제한 데이터 복구

show parameter undo; undo_retention 파라미터 값 이내로만 복구 가능

1. 테이블을 복구(FLASHBACK)이 가능한 상태로 변경

ALTER TABLE emp ENABLE ROW MOVEMENT;

2. FLASHBACK

FLASHBACK TABLE emp TO TIMESTAMP(systimestamp - interval '5' minute); -- 현재 시간에서 5분을 뺀 시간으로 emp 테이블을 복구함

3. commit

commit;

## FLASHBACK DROP : 실수로 삭제한 데이터 복구

DROP(테이블 완전 삭제)에도 테이블 복구가 가능하다. (휴지통을 통해서)

SELECT * FROM USER_RECYCLEBIN; //휴지통 조회

인덱스와 테이블이 DROP된 상태로 확인됨

FLASHBACK TABLE emp TO BEFORE DROP; -- 삭제된 테이블 복구/ 휴지통을 비우는 작업을 하지 않았다면 복구가능

PURGE RECYCLEBIN; -- 휴지통을 비우는 명령어

VIEW를 사용하는 이유 : 보안

VIEW 특정 테이블에 대해서 일부 데이터만 볼 수 있도록 해주는 것 다른 데이터를 못 보게 할 때 사용 (개인정보 보안) 실제 데이터를 가지고 있지 않고(복사의 형태가 아님), 테이블의 데이터를 바라보고 있음 (포인터와 비슷한 느낌..?)

CREATE VIEW 뷰이름 AS (서브쿼리절);

CREATE VIEW emp_view AS (SELECT empno, ename, sal, job, deptno FROM emp WHERE job = 'SALESMAN');

SELECT * FROM emp_view;

VIEW에 대해서 데이터 수정을 할 경우, 원본 테이블의 값도 변경이 됨

VIEW

VIEW를 사용하는 이유 : 복잡한 쿼리문을 간단하게 검색하고자 할때

자주 실행되는 쿼리문의 결과를 view로 미리 생성해놓고, 해당 뷰의 전체를 출력하는 쿼리문을 실행 복잡한 쿼리문을 매번 작성하여 실행할 필요없이, 뷰만 조회하면 확인 가능함 - 쿼리문이 간결해짐

CREATE VIEW emp_view2 AS (SELECT deptno, round(avg(sal)) AS avgsal -- 칼럼 별칭을 반드시 지정해줘야 함 FROM emp GROUP BY deptno);

select * from emp_view2;

INDEX : 데이터 검색 속도를 높이기

CREATE INDEX 인덱스명 ON 테이블명(기준 칼럼명);

대용량 테이블에 대해서 쿼리를 빠르게 하기 위해 필요 책의 목차와 같은 느낌...?

객체 : TABLE, VIEW, INDEX

인덱스가 없다면 정렬되지 않은 칼럼 값에 대해서 조건에 일치하는 행을 찾아야함(전체를 읽어야 함) - 검색 성능이 느려짐

인덱스 인덱스 기준 데이터와 그와 일치하는 테이블의 행의 위치를 찾을 수 있는 인덱스가 저장된 인덱스 테이블이 존재함 인덱스 테이블의 데이터는 정렬된 데이터로, 빠르게 해당 값을 찾을 수 있음. 인덱스 테이블 내의 해당 값을 찾아서 ROWID를 기준으로 테이블 내의 행의 위치를 찾아감

인덱스가 없을 시 FULL TABLE SCAN을 하게 되는 것에 비해 매우 빠른 검색 성능을 보임

CREATE INDEX emp_sal -- 인덱스명은 의미 있게 : '테이블명_기준칼럼명' ON emp(sal);

rowid란 칼럼은 원래 테이블에 존재함, 쿼리문 작성 시에 rowid 칼럼을 명시해줘야지 확인 가능 rowid : row의 물리적인 주소 (file 번호 + 블럭 번호 + row 번호)

rowid는 테이블, 인덱스에 존재함

월급에 걸린 인덱스를 확인하고 싶으면 아래와 같이 작성함

SELECT sal, rowid FROM emp WHERE sal >= 0; -- 숫자의 경우 ">= 0" / 문자의 경우 "> ' '"

order by 절이 없음에도, sal칼럼이 오름차순으로 정렬되어있음

SEQUENCE : 절대로 중복되지 않는 번호 생성하기

CREATE SEQUENCE 시퀀스명; -- 옵션없이 생성 CREATE SEQUENCE seq1;

데이터를 입력할 시 특정 칼럼에 입력되는 순서대로의 번호를 입력하고 싶을 때 사용

SELECT seq1.nextval FROM dual;

연속하여 실행할수록 1씩 증가한 값을 출력함

CREATE SEQUENCE 시퀀스명 INCREMENT BY 증감숫자 START WITH 시작문자 NOMINVALUE OR MINVALUE 최솟값 NOMAXVALUE OR MAXVALUE 최댓값 CYCLE OR NOCYCLE CACHE OR NOCACHE -- 메모리에 시퀀스 값을 미리 할당

CREATE SEQUENCE seq2 START WITH 1 MAXVALUE 100 INCREMENT BY 1 -- 1부터 1씩 증가해서 최댓값 100까지 NOCYCLE; -- 100까지 나온 이후에 1로 다시 부여하지 않고, 끝내겠다는 의미

100을 넘어갈 시에 'NEXTVALUE EXCEEDS MAXVALUE는 사례로 될 수 없습니다'라는 오류 발생

CREATE SEQUENCE dept_seq1 START WITH 50 INCREMENT BY 10; -- 50부터 시작하고 10씩 증가하는 시퀀스 생성

START WITH와 CONNECT BY PRIOR절을 작성하면 LEVEL이라는 칼럼을 출력할 수 있음

SELECT ename, level, sal, job FROM emp START WITH ename = 'KING' -- KING을 서열 1위로 두고, 이후로 서열화 CONNECT BY PRIOR empno = mgr; -- 사원번호와 mgr이 연결됨

KING을 1위로 하여, MGR로 연결된 사원들을 서열화 함

SELECT RPAD(' ',level3) || ename, sal, job -- RPAD(' ',level3) : 서열만큼 공백이 채워져서 계층화되어 출력됨 FROM emp START WITH ename = 'KING' -- KING을 서열 1위로 두고, 이후로 서열화 CONNECT BY PRIOR empno = mgr; -- 사원번호와 mgr이 연결됨

서열이 2위인 사원들의 이름과 서열 직업 출력하시오

SELECT RPAD(' ',level3) || ename, sal, job -- RPAD(' ',level3) FROM emp WHERE level = 2 START WITH ename = 'KING' -- KING을 서열 1위로 두고, 이후로 서열화 CONNECT BY PRIOR empno = mgr; -- 사원번호와 mgr이 연결됨

계층형 질의문으로 서열을 주고 데이터 출력하기

특정 행 포함 아래 서열들을 포함시키지 않고 출력하는 방법

SELECT RPAD(' ',level3) || ename, sal, job -- RPAD(' ',level3) FROM emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr AND ename != 'BLAKE';

해당 조건을 WHERE절이 아니라 CONNECT BY절에 작성하여야 함 WHERE절에 작성하면 해당 행만 제외됨

SCOTT과 FORD를 포함한 그들의 팀원들이 제외하고 출력되도록 하시오

SELECT RPAD(' ',level3) || ename, sal, job -- RPAD(' ',level3) FROM emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr AND ename NOT IN ('SCOTT','FORD');

계층형 질의문으로 서열을 주고 데이터 출력하기

특정 조건대로 서열화하여 출력하기

SELECT RPAD(' ',level3) || ename, sal, job -- RPAD(' ',level3) FROM emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY sal desc; -- 서열 순서를 유지하면서 월급 높은 순대로 출력/같은 레벨상의 행들은 월급 높은 순으로 정렬되어 출력됨

SIBLINGS 옵션을 작성하지 않으면 아래와 같이 서열 순서가 깨져서 나옴

BLAKE를 포함한 팀원들만 출력하는데 서열을 유지한 상태로 월급이 낮은 사원부터 출력하시오

SELECT RPAD(' ',level3) || ename, sal, job -- RPAD(' ',level3) FROM emp WHERE level = 2 START WITH ename = 'BLAKE' CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY sal ASC;

SIBLINGS를 작성하지 않으면 단순히 서열 관계없이 월급이 낮은 순대로만 출력하게 됨

계층형 질의문으로 서열을 주고 데이터 출력하기

SYS_CONNECT_BY_PATH() : 서열 순서를 가로로 출력할 수 있음

SELECT ename, sys_connect_by_path(ename,'/') as path FROM emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr;

다음과 같이 월급도 같이 출력되게 하시오

SELECT ename || '(' || sal || ')', sys_connect_by_path(ename || '(' || sal || ')' , '/') as path FROM emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr;

DDL : 테이블 생성/ 관리/ 삭제

CREATE TABLE : 일반 테이블 생성

CREATE TABLE 테이블명 (칼럼명 데이터타입,칼럼명 데이터타입,칼럼명 데이터타입,...);

예시)

CREATE TABLE emp3 (empno number(10), -- 숫자 10자리 ename varchar1(10), -- 문자(영어) 10자리 (한글은 한 글자당 2바이트이므로 5글자) sal number(10,2), -- 총 10자리가 숫자인데, 그 중 2자리는 소수점 hiredate date); -- 날짜

CREATE TEMPORARY TABLE : 임시 테이블 생성

CREATE GLOBAL TEMPORARY TABLE 테이블명 ( ............) ON COMMIT DELETE ROWS;

COMMIT시 데이터가 사라짐 세션 종료 후 재접속시 데이터는 사라지고, 테이블만 존재

어떤 작업을 위해서 임시로 데이터를 저장해야 할 때가 있음

영구 테이블로 임시 데이터를 저장하고 사용할 시, 후에 삭제하는 과정이 부담이 발생할 수 있음 -- 임시 데이터의 테이블을 지우려다가, 지우면 안되는 테이블을 지우게 될 수도 있음 (삭제 과정은 확인을 많이 필요로 하는 작업)

섣불리 필요없을 것 같은 테이블임에도 섣불리 지워내지 못해서, 데이터 낭비가 발생하게 되는 경우를 방지

CREATE GLOBAL TEMPORARY TABLE emp3 ( empno number(10) ename varchar2(10) sal number(10) ) ON COMMIT DELETE ROWS; -- COMMIT 실행시에 데이터가 삭제 됨 (테이블은 그대로고, 데이터만 사라짐)

임시테이블 옵션

1. ON COMMIT DELETE ROWS : 커밋하면 데이터를 지워라
2. ON COMMIT PRESERVE ROWS : 세션을 종료하면 데이터를 지워라 (커밋하면 데이터가 저장됨)

SELECT절 마지막에 'FOR UPDATE'

SELECT를 실행하면서, 해당 행에 대해서 LOCK을 걸 수 있음 검색하는 동안에 그 누구도 갱신이 불가능함

SELECT문을 실행했음에도 COMMIT을 실행해야지, 접근이 가능함

SELECT ENAME, SAL, DEPTNO FROM EMP WHERE ENAME = 'JONES' FOR UPDATE;

서브 쿼리를 사용하여 데이터 입력 (INSERT)

INSERT문 아래에 입력하고자 하는 데이터를 가져오는 서브쿼리 입력 단일 행이 아닌 다중 행을 입력하고자 할때 사용

CREATE TABLE EMP2 AS SELECT * FROM EMP;

테이블을 백업할 때 많이 사용되는 쿼리문

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

데이터 없이 테이블 구조만 복사함

INSERT INTO emp2(empno, ename, sal, deptno) SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10;

부서 테이블과 같은 구조의 테이블을 DEPT2라는 이름으로 생성하고 부서번호가 20, 30번의 모든 컬럼의 데이터를 DEPT2에 입력

CREATE TABLE DEPT2 AS SELECT * FROM DEPT WHERE 1=2;

INSERT INTO DEPT2 SELECT * FROM DEPT WHERE DEPTNO IN (20,30);

서브쿼리를 사용하여 데이터 수정 (UPDATE)

SET절에 서브쿼리 작성

UPDATE emp SET sal = (SELECT sal FROM emp WHERE ename = 'ALLEN') WHERE job= 'SALESMAN';

부서번호가 30번인 사원들의 직업을 MARTIN의 직업으로 변경하세요

UPDATE emp SET job = (SELECT job FROM emp WHERE ename = 'MARTIN') WHERE DEPTNO = 30;

서브쿼리를 사용하여 데이터 삭제 (DELETE)

DELETE FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SCOTT');

서브쿼리를 사용하여 데이터 합치기 (MERGE)

ALTER TABLE dept ADD sumsal number(10);

sum(sel)에 대한 쿼리문 SELECT deptno, sum(sal) FROM emp group by deptno;

MERGE INTO dept d USING (SELECT deptno, sum(sal) as sumsal -- using 절에 작성함 FROM emp GROUP BY deptno) v ON (d.deptno = v.deptno) WHEN MATCHED THEN UPDATE SET d.sumsal = v.sumsal;

merge문 작성 시 alias는 매우 중요 into절의 테이블 값(혹은 서브쿼리) using절의 테이블 값(혹은 서브쿼리) alias 작성은 구분이 쉽게 되도록 작성

부서 테이블에 cnt라는 칼럼을 추가하고, 해당 부서 번호의 인원수로 값을 갱신하시오

alter table dept add cnt number(10);

merge into dept d USING (SELECT deptno, count(*) as cnt FROM emp GROUP BY DEPT) c ON (d.deptno = c.deptno) WHEN MATCHED THEN UPDATE SET d.cnt = c.cnt;

예시)

alter table emp add loc varchar2(10);

merge into emp e using dept d on (e.deptno = d.deptno) when matched then -- 일치하면 update set e.loc = d.loc -- 칼럼 업데이트 when not matched then -- 일치하지 않으면 insert (e.empno, e.deptno, e.loc) values (1111, d.deptno, d.loc) -- 행 삽입

사원 테이블에 부서명 칼럼을 추가하고 해당 사원의 부서명으로 값을 갱신하시오

alter table emp add dname varchar2(10)

merge into emp e using dept d on (e.deptno = d.deptno when matched then update set e.dname = d.dname;

LOCK

데이터의 일관성을 보장하기 위해서 다른 세션이 어느 하나의 행에 DML문을 실행하고 COMMIT을 하기 전까지, 다른 세션에서 동일한 행을 갱신할 수 없도록 막는 기능

DML문을 실행 후 COMMIT을 하지 않으면, 다른 세션의 데이터와 달라지게 됨 여기서 오는 일관성에 관한 문제를 방지하기 위해, 아예 해당 데이터(테이블 또는 행)에 대한 접근을 막음

DML을 수행한 해당 세션에서 COMMIT을 실행하면, LOCK 상태가 해제됨

세션 1 UPDATE문의 실행결과가 COMMIT으로 영구저장 될때까지, 세션 2는 접근이 불가능함 (LOCK 상태)

세션1이 COMMIT을 수행한 후, 바로 LOCK이 해제되며, UPDATE문이 실행됨

오른쪽 세션에서 아직 COMMIT을 실행하지 않았기에 9000을 출력함

어느 세션이든 최종적으로 COMMIT한 데이터를 보게 됨

세션에서 데이터를 갱신하고 있으면, 다른 세션에선 절대 갱신이 불가능하도록 하는 것

ALLEN의 전체 행에 대해서 LOCK을 걸기 때문에, 실행이 불가능하다

UPDATE하는 필드에만 LOCK을 거는 것이 아니라, 전체 행에 대해서 LOCK을 건다

1. QUERY문 :

SELECT FROM WHERE GROUP BY HAVING ORDER BY

2. DML문 (Data Manipulation Language) : insert, update, delete, merge
3. DDL문
4. DCL문
5. TCL문

INSERT

INSERT INTO 테이블명(컬럼명1,컬럼명2,컬럼명3 ...) VALUES (값1, 값2, 값3, ....)

문자 값 : 싱글 쿼테이션 마크 날짜 값 : to_date() 함수

예시) INSERT INTO emp(empno, ename, sal, job, hiredate) VALUES (2812, 'JACK', 3500, 'ANALYST', to_date('2019/06/05', 'RRRR/MM/DD'));

INSERT INTO 테이블명 VALUES (값1, 값2, 값3, ....)

위와 같이 작성시, 모든 칼럼에 대한 값을 작성하여야 함

COMMIT(영구 저장)/ ROLLBACK(취소)

ROLLBACK : INSERT, UPDATE, DELETE 실행 결과를 취소 COMMIT : INSERT, UPDATE, DELETE 실행 결과를 영구히 저장

UPDATE

UPDATE 테이블명 SET 칼럼명 = 값 -- 세팅값 지정 WHERE 조건; -- 어떤 행을 바꿀건지

예시) UPDATE emp SET sal = 3200 WHERE ename = 'SCOTT';

DELETE/TRUNCATE/DROP : 데이터 삭제

DELETE FROM 테이블명 WHERE 조건;

WHERE절을 작성하지 않으면 테이블 전체를 지우게 된다. - ROLLBACK 가능함

TRUNCATE TABLE 테이블명; -- ROLLBACK 불가능함 (COMMIT을 포함한 명령)

DROP TABLE 테이블명; -- 휴지통 기능이 있어 다시 복구 할 수 있음

관리자인 사원들의 이름을 출력하시오

  SELECT ENAME,SAL,JOB
  FROM EMP
  WHERE EMPNO IN (SELECT MGR
                  FROM EMP);

예제) 관리자가 아닌 사원들의 이름을 출력하시오

  SELECT ENAME
  FROM EMP
  WHERE EMPNO NOT IN (SELECT MGR
                  FROM EMP);

위 쿼리에서 결과가 안 나온다.. MGR 칼럼에 NULL값이 포함되어 있기 때문

NOT IN = '!=ALL'

TRUE AND TREU AND ... AND NULL = NULL

칼럼 값 중에 NULL이 있으면 전체 결과 값이 NULL이 되버림 (NULL은 TRUE/FALSE도 아닌 알 수 없는 값)

따라서 아래와 같이 서브쿼리의 결과 값에서 NULL값이 포함되지 않도록 WHERE절을 작성하여야 함

  SELECT ENAME
  FROM EMP
  WHERE EMPNO NOT IN (SELECT MGR
                  FROM EMP
                  WHERE MGR IS NOT NULL);

  SELECT ENAME
  FROM EMP
  WHERE EMPNO NOT IN (SELECT NVL(MGR,-1)
                  FROM EMP);

서브 쿼리에서 NOT IN을 쓸 때에는 NULL값이 반환값으로 들어가지 않도록 해야 함

EXISTS/ NOT EXIST

보통은 서브쿼리부터 실행되는데, EXIST문을 작성하면 메인쿼리부터 실행됨

  SELECT *
  FROM DEPT D
  WHERE EXISTS (SELECT *
                  FROM EMP E
                  WHERE E.DEPTNO = D.DEPTNO);

먼저 메인쿼리를 실행하여, DEPTNO의 전체 테이블 정보를 가져온 후, 서브 쿼리의 DEPT.DEPTNO 조건문에 값을 대입하면서 실행된다.

존재하지 않는 컬럼만 출력하려면, NOT EXIST

  SELECT *
  FROM DEPT D
  WHERE NOT EXISTS (SELECT *
                  FROM EMP E
                  WHERE E.DEPTNO = D.DEPTNO);

HAVING절의 서브 쿼리

  SELECT JOB, SUM(SAL)
  FROM EMP
  GROUP BY JOB
  HAVING SUM(SAL) > (SELECT SUM(SAL)  -- 그룹함수에 대한 조건은 HAVING절에 작성
                      FROM EMP
                      WHERE JOB='SALESMAN');

예제) 부서번호, 부서번호별 인원수를 출력하는데 10번 부서번호의 인원수보다 더 큰 것만 출력하시오

  SELECT DEPTNO, COUNT(*) 
  FROM EMP 
  GROUP BY DEPTNO
  HAVING COUNT(*) > (SELECT COUNT(*)
                      FROM EMP
                      WHERE DEPTNO = 10);

FROM절의 서브쿼리

분석함수는 WHERE절에는 작성 불가능

  SELECT *
  FROM (SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) RNK
          FROM EMP
        );

위와 같이 실행하면 FROM절 안의 서브쿼리가 가장 먼저 실행됨 - 서브쿼리의 결과를 메모리에 올려놓음

실행 순서 : (서브쿼리 내부) FROM -> (서브쿼리 내부) SELECT -> (메인쿼리) FROM -> (메인쿼리) WHERE -> (메인쿼리) SELECT

  SELECT *
  FROM (SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) RNK
          FROM EMP
        )
  WHERE RNK = 1;

예제) 직업이 SALESMAN인 사원들 중에서 가장 먼저 입사한 사원들의 이름과 입사일을 출력하시오

  SELECT *
  FROM (SELECT ENAME, HIREDATE, RANK() OVER (ORDER BY HIREDATE DESC) RANK
       FROM EMP
       WHERE JOB = 'SALESMAN')
  WHERE RANK = 1;

SELECT 절의 서브 쿼리

그룹함수의 결과를 일반 칼럼과 같이 출력하고 싶을 때 사용

  SELECT ENAME, SAL (SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN') AS MAX1,
                      (SELECT MIN(SAL) FROM EMP WHERE JOB='SALESMAN') AS MIN1
  FROM EMP
  WHERE JOB='SALESMAN';

SELECT 문의 6가지 절 (서브쿼리 사용 유무)

SELECT (O) FROM (O) WHERE (O) GROUP BY (X) HAVING (O) ORDER BY (O)

예제) 부서번호가 20번인 사원들의 이름과 월급을 출력하고, 그 옆에 20번 부서번호인 사원들의 평균 월급이 출력되게 하시오

  SELECT ENAME, SAL ,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20) 평균
  FROM EMP
  WHERE DEPTNO = 20;

JOIN은 데이터를 양옆으로 연결

UNION ALL

SELECT DEPTNO, SUL(SAL) FROM EMP GROUP BY DEPTNO;

SELECT SUM(EMP) FROM EMP;

위 두 쿼리문의 결과를 위아래로 연결

SELECT DEPTNO, SUL(SAL) FROM EMP GROUP BY DEPTNO UNION ALL SELECT SUM(EMP) FROM EMP;

위와 같이 실행하면 질의 블록은 부정확한 수의 결과 열을 가지고 있습니다라는 오류 발생 두 개의 쿼리에서 컬럼의 개수가 동일해야함

SELECT DEPTNO, SUL(SAL) FROM EMP GROUP BY DEPTNO UNION ALL SELECT NULL,SUM(EMP) -- 컬럼을 추가할 게 없으니 NULL로 작성 FROM EMP;

위 아래 두 개의 쿼리문에서, 컬럼의 개수와 데이터 타입이 동일하여야 함.

null/ deptno는 일치하지 않아도 출력이 되지만, to_number(null)로 수행하는 것이 성능이 더 좋음

SELECT DEPTNO, SUL(SAL) FROM EMP GROUP BY DEPTNO UNION ALL SELECT to_number(NULL),SUM(EMP) -- number 형식으로 바꿈 FROM EMP;

컬럼명도 되도록이면 동일하게 작성 - order by절에서 정렬시 사용

SELECT DEPTNO, SUL(SAL) FROM EMP GROUP BY DEPTNO UNION ALL SELECT to_number(NULL) as deptno,SUM(EMP) -- 컬럼명 동일 (deptno) FROM EMP order by deptno asc; -- order by절은 맨 아래에만 작성 가능

예제) 직업과 직업별 토탈월급을 출력하는데 맨 아래에 전체 토탈 월급도 출력하세요

  select job, sum(sal)
  from emp
  group by job
  union all
  select to_char(null) as job, sum(sal) -- 컬럼 개수, 타입, 컬럼명 일치
  from emp
  order by job asc;

UNION

????????????????????????????????????????????????????????????????????????? UNION : 데이터를 정렬하여 출력 UNION ALL : 데이터를 정렬하지 않고, 단순히 붙여서 출력

INTERSECT : 데이터 교집합 출력

두 쿼리문에 대해서 중복되는 결과만 출력하며, 제일 첫번째 칼럼을 기준으로 정렬하여 출력함

SELECT ENAME, SAL, JOB, DEPTNO
    FROM EMP
    WHERE DEPTNO IN (10,20)
INTERSECT
SELECT ENAME, SAL, JOB, DEPTNO
    FROM EMP
    WHERE DEPTNO IN (20,30);

사원 테이블과 부서 테이블과의 공통된 부서번호가 무엇인지 출력하시오

select deptno from emp intersect select deptno from dept;

MINUS : 데이터 차집합 출력

위 쿼리문에서 아래 쿼리문의 결과를 뺀 결과를 출력함

SELECT ENAME, SAL, JOB, DEPTNO
    FROM EMP
    WHERE DEPTNO IN (10,20)
MINUS
SELECT ENAME, SAL, JOB, DEPTNO
    FROM EMP
    WHERE DEPTNO IN (20,30);

DEPTNO = 10인 행만 출력

SELECT ENAME, SAL, JOB, DEPTNO
    FROM EMP
    WHERE DEPTNO IN (20,30)
MINUS
SELECT ENAME, SAL, JOB, DEPTNO
    FROM EMP
    WHERE DEPTNO IN (10,20);

DEPTNO = 30인 행만 출력

따라서, 위 두개의 쿼리 실행 결과는 다름

부서테이블에는 존재 하는데 사원테이블에는 존재하지 않는 부서번호는?

SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP;

단일행 서브쿼리 (중요..)

서브 쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우 연산자 : =,!=,^=,<>,>,<,>=,<=

  SELECT ENAME, SAL -- 메인쿼리
  FROM EMP
  WHERE SAL > (SELECT SAL  -- 서브쿼리
              FROM EMP
              WHERE ENAME = 'JONE');

ALLEN 보다 더 늦게 입사한 사원들의 이름과 월급을 출력하시오

  SELECT ENAME, SAL
  FROM EMP
  WHERE HIREDATE > (SELECT HIREDATE
                      FROM EMP
                      WHERE ENAME = 'ALLEN');

다중 행 서브쿼리

서브 쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우 연산자 : IN, NOT IN, >ALL, <ALL, >ANY, <ANY ,

  SELECT ENAME, SAL
  FROM EMP
  WHERE SAL IN (SELECT SAL
              FROM EMP
              WHERE JOB = 'SALESMAN');

서브쿼리에서 다중 행을 반환하므로, IN 연산자 사용

다중 행 서브쿼리 : IN 단일 행 서브쿼리 : =

부서번호가 20번인 사원들과 같은 직업을 갖는 사원들의 이름과 직업을 출력

  SELECT ENAME, JOB
  FROM EMP
  WHERE JOB IN (SELECT JOB
              FROM EMP
              WHERE DEPTNO = 20);