all the records

<테이블(Table)>

: DB에서 데이터를 저장하는 가장 기본적인 구조

  오라클에는 테이블 이외에 뷰(View), 인덱스(Index), 시퀀스(Sequence)등 여러 개체가 존재
  이 중 물리적인 공간을 갖는 개체를 세그먼트(Segment)라고 분류 

  -> 테이블은 사용자가 직접 조회 가능한 데이터를 저장하는 유일한 세그먼트
  DB란 테이블에 저장된 데이터의 효율성과 무결성을 보장하기 위한 시스템
  
  

1. 테이블 생성과 삭제
테이블: 행(Row). 컬럼(열, Column)로 이루어진 자료구조 형태
   
정보시스템에서 문서의 형태였던 도표의 구조를 도입해서 테이블이라 명명하고 사용
초창기 파일 시스템을 기반으로 한 DB시스템에서 레코드와 필드로 구성된 파일이 도표를 대신
-> RDB(Relational DataBase)가 본격적으로 사용되면서부터 행(Row)과 컬럼(Column)으로 구성된 테이블이 이를 대신함

CREATE TABLE 테이블명(
    컬럼명    데이터타입 [DEFAULT 디폴트값] [컬럼 레벨 제약조건],
    컬럼명    데이터타입 [DEFAULT 디폴트값] [컬럼 레벨 제약조건],
  );

 - 테이블 생성  CREATE TABLE
  1) 데이터 타입
    컬럼에 입력될 데이터의 종류와 크기를 결정(자료형)
  2) DEFAULT
    입력이 누락되었을 때 기본 입력값을 정의
    디폴트값을 지정하지 않으면 null 값이 저장
  3) 컬럼 레벨 제약조건
    PK, FK, UK, CHECK, NOT NULL 등을 지정
  4) 테이블 레벨 제약조건
    PK, FK, UK, CHECK 만 지정
    NOT NULL 은 정의불가.
  

  DROP TABLE 테이블명
  [CASCADE CONSTRAINT];

-DROP
  1) 테이블 삭제
  2) CASCADE CONSTRAINT : 테이블이 다른 테이블로부터 참조되는 경우, 해당 제약조건을 먼저 삭제 후 테이블을 삭제

  SELECT table_name, column_name, 
         data_type, data_length
  FROM user_tab_columns   -- sys 계정의 소유 테이블
  WHERE table_name = 테이블명;

-SELECT
  1) user_tab_columns 데이터 딕셔너리(Data Dictionary)를 통해서 지정한 테이블의 구조를 자세히 검색
  2) data_type : 컬럼의 데이터 타입
  3) data_length : 컬럼의 길이
  
  

2. 테이블 생성시 이름 규칙
  1) 문자로 시작
  2) 30자이내
  3) 영문, 숫자, _,$, # 만 사용
  4) 한글 사용은 가능하지만 코드오류의 우려로 사용지양
  5) 테이블은 동일한 유저 (스키마 , 계정) 안에서 유일해야 한다.
  6) 예약어(명령어등) 사용불가
  7) 대소문자의 구별 X(사용문자와는 관계없이 모든 이름은 대소문자로 저장)
  
  

3. 데이터타입 *자주쓰는타입
  1) 문자타입
  - CHAR, *VARCHAR2, LONG, CLOB
  2) 숫자타입
  - *NUMBER
  3) 날짜타입
  - *DATE
  4) 이진타입
  - RAW, LONG RAW, BLOB, BFILE
  5) ROWID 타입 :  ROWID



4. 데이터타입 상세
1) VARCHAR2(n) : 가변길이 문자타입 (1 < n < 4000 byte)
2) CHAR(n) : 고정길이 문자타입 (1 < n < 4000 byte)
3) NUMBER(n, p) : 숫자타입
    n은 전체자리수, p는 소수점 이하 자리수
    전체자리수를 초과할 경우는 입력 거부, 소수점 이하자리수가 초과되면 반올림입력
    소수점 이하의 값이 없을 시 길이를 지정하지 않는 것이 좋다.
4) DATE : 날짜타입. 출력이나 입력형식과 무관하게 YYYY/MM/DD:HH24:MI:SS 형태로 저장 (기원전 4712년 1월 1일 ~ 서기 9999년 12월 31일)
5) LONG : 2gb까지 저장가능한 가변길이 문자 타입. 조건 검색불가. 테이블당 하나의 LONG컬럼만 정의 가능 or can use CLOB
6) CLOB : LONG을 개선한 타입. 4gb저장 가능. 한테이블에 여러컬럼으로 정의 가능
7) BLOB : 4G까지 저장 가능한 가변길이 이진 타입
8) ROW(n) : 가변길이 이진타입(n < 2000)
9) BFILE : 외부 파일저장을 위한 이진 타입. 4G 이내 파일
10) ROWID : ROWID 를 저장하기 위한 데이터 타입. PL-SQL 프로그래밍에서 많이 사용되며 각 문자는 64진수로 Encoding.
 

-테이블생성 예시

--테이블 생성
--명 board, 컬럼구성: no 게시번호, name 작성자, sub 글제목, content 내용, wdate 작성날짜
 
CREATE TABLE board(
  no NUMBER,
  name VARCHAR2(50),
  sub VARCHAR2(100),
  content VARCHAR2(4000),
  wdate DATE DEFAULT sysdate
);

DESC board;

--테이블 컬럼테이터타입 확인
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'BOARD';
  
--데이터 입력
INSERT INTO board (no)
VALUES (1);

--확정
COMMIT;

SELECT * FROM board;



--테이블을 생성하고 데이터입력을 확인
CREATE TABLE t1(
  no NUMBER(4,2)
);

INSERT INTO t1 VALUES (12.12);
INSERT INTO t1 VALUES (1.789);
SELECT * FROM t1;
INSERT INTO t1 VALUES (123.1);--넘버자릿수 (4자리 소수점 2자리 )를 지정해줘서 입력불가 오류뜸

DROP TABLE t1;
  
  
  
  CREATE TABLE t2(
  name VARCHAR2(3)
);
INSERT INTO t2 VALUES ('AAA');
INSERT INTO t2 VALUES('장');
INSERT INTO t2 VALUES('장강');
INSERT INTO t2 VALUES ('A');

- CHAR와 VARCHAR2의 차이점

CREATE TABLE comp(
  co1 CHAR(4),
  co2 VARCHAR2(4)
);
INSERT INTO comp VALUES('AA', 'AA');
INSERT INTO comp VALUES('AAAA', 'AAAA');
SELECT * FROM comp;

SELECT * FROM comp WHERE co1='AA';
SELECT * FROM comp WHERE co2='AA';
SELECT * FROM comp WHERE co1=co2;

아래 2개는 서로 다른 데이터로 판단됨

--co1 CHAR(4)     'AA'   AA__

--co2 VARCHAR2(4) 'AA'   AA

=> CHAR의 사용은 드뭄, VARCHAR2 를 일관적으로 사용

- DATE 타입

CREATE TABLE hd(
  no NUMBER,
  hdate DATE
);

INSERT INTO hd VALUES (1, sysdate); --1)시분초가 같이 저장됨으로

SELECT * FROM hd;

SELECT *
FROM hd
WHERE hdate='2020/02/27'; --2)이렇게는 검색되지 않음

-->범위검색 해줘야함
SELECT  *
FROM hd
WHERE hdate BETWEEN '2020/02/27' AND '2020/02/28';


  
--비추천방법
--hdate를 문자열로 자동형변환하므로 대용량 테이블에서는 심각한 성능 저하가 일어날 수 있다.
SELECT *
FROM hd
WHERE hdate LIKE '20/02/27%';

1) 우리나라에서 날짜는 DATE 보다 VARCHAR2(8)로 정의. (시분초 사용불가)

2) 외국에서는 TRUNC(sysdate)이용. 시분초를 00:00:00으로 저장

3) sysdate-> 범위검색 요구

<Exercise>

--client 테이블을 삭제하라
DROP TABLE client;

--client 테이블을 생성하라
CREATE TABLE client(
  cnum NUMBER,
  cname VARCHAR2(50),
  addr VARCHAR2(200),
  tel VARCHAR2(20)
);

--server 테이블을 삭제하라
DROP TABLE server;

--server 테이블을 생성하라
CREATE TABLE server(
  cnum NUMBER,
  cname VARCHAR2(50),
  addr VARCHAR2(200),
  tel VARCHAR2(20)
);

[세션 1]

--1) 관우, 장각 학생의 학과 정보를 검색한다
SELECT sno, sname, major
FROM student
WHERE sname IN ('관우', '장각');

--2) 관우 학생의 학과를 경제로 변경시도한다
--   트랜잭션 시작
UPDATE student SET major='경제'
WHERE sname='관우';

--3) 변경이 잘 되었는지
--     관우, 장각 학생의 학과 정보를 검색한다
--     아직 트랜잭션이 완료되지 않은 상태
SELECT sno, sname, major
FROM student
WHERE sname IN ('관우', '장각');

--8) 세션2가 장각의 트랜잭션을 진행중인 것을 모르고 
--   장각의 학과를 천문 학과로 변경을 시도
UPDATE student SET major='천문'
WHERE sname='장각';

--[세션 2]

--4)  세션1에서 변경을 시도한 행을 포함한
--    관우, 장각 학생의 학과 정보를 검색한다
SELECT sno, sname, major
FROM student
WHERE sname IN ('관우', '장각');

--5) 장각 학생의 학과를 경제로 변경시도한다
UPDATE student SET major='경제'
WHERE sname='장각';

--6)  잘 적용되었는지
--    관우, 장각 학생의 학과 정보를 검색한다
SELECT sno, sname, major
FROM student
WHERE sname IN ('관우', '장각');

--7) 관우 학생의 학과를 '물리'로 변경을 시도
--   현재 세션1에 의해 관우 학생의 행은 독점잠금이 걸려있는 상태
UPDATE student SET major='물리'
WHERE sname='관우';

--상대와 DeadLock상태에서 오라클의 에러 발생 후 강제로 마지막 명령만 
--ROLLBACK 에 의해 무한 대기가 풀리게 된다.
SELECT sno, sname, major
FROM student
WHERE sname IN ('관우', '장각');

세션2 - 관우 학생의 변경을 시도 -> 무한대기상태

세션1 - 세션2에 의해 트랜잭션이 진행중인 장각의 변경을 시도했으므로 동시에 무한 대기 상태로 빠져듬

=> 이렇게 2개의 세션 모두 무한 대기상태에 빠져든 것을 DeadLock이라고 함

-DeadLock은 세션 사용자에 의해서는 해결불가.

-오라클은 무한 대기상태에 걸린 세션을 오류 처리하고,

강제로 마지막 명령만 ROLLBACK 함으로써 DeadLock을 처리한다.

<정의>

1. 트랜잭션의 이해

응용 프로그램 개발 과정에서 잘못 설계된 트랜잭션으로 인해 많은 문제가 발생

문제의 대부분은 업무분석의 미숙이나 트랜잭션이 일으키는 잠금(Lock)에 대한 이해가 부족해서 나타나는 것

==> 트랜잭션과 Lock의 이해는 중고급 개발에 있어 매우 중요하다.

2. 트랜잭션이란?

함께 실행되는 작업의 단위를 의미 

사용자의 의도에 따라 여러 개의 문장으로 구성된 트랜잭션은 반드시 동시에 실행(COMMIT)되거나 취소(ROLLBACK)

RDBMS는 트랜잭션을 통해 작업의 단위를 결정함으로써 작업 결과의 신뢰성을 확보

3. 트랜잭션의 예시

:금융 거래 계좌 이체 - A계좌에서 100만원을 B계좌로 이체한다면 최소 2가지 작업이 수행

1) A계좌에서 100만원이 출금

2) B계좌에 100원이 입금

A계좌에서 100만원 출금은 성공했지만 B계좌에 100만원이 입금되기 전에 시스템이 다운된다면

B계좌에는 100만원이 입금되지 않고, A의 계좌에서만 100만원이 없어지게 된다.

-> 이를 방지하기 위해 이 2개의 과정을 하나의 트랜잭션으로 묶어놓아 2개의 과정이 모두 처리되지 않으면 ROLLBACK 하여 거래가 취소되도록 함

==>거래의 신뢰성을 확보

  O    O    X

A -> B -> C -> D

A ~ D의 모든 절차 : 트랜잭션

A~B 성공

B~C 성공

C~D 실패

--> ROLLBACK(취소)처리(트랜잭션 취소)

--> C~D가 성공이면 COMMIT(성공)처리(트랜잭션 완료)

4. 트랜잭션의 시작과 종료

  1) 시작

    이전 트랜잭션이 종료된 이후

    DML(INSERT, UPDATE, DELETE) 문장이나 DDL(CREATE, ALTER, DROP, TRUNCATE), DCL(GRANT, REVOKE)문장이 실행되었을 때 시작

  2) 종료

    2-1)COMMIT 이나 ROLLBACK 명령이 실행시 종료

    2-2)DDL이나 DCL문장이 실행이 완료되면 자동으로 종료

    2-3)사용자의 정상 종료 시 종료

    2-4)데드락(DeadLock)이 걸리면 트랜잭션의 일부만 종료

  트랜잭션은 하나의 세션에서 하나만 시작할 수 있다.

  DML 인 경우 반드시 COMMIT 이나 ROLLBACK 으로 종료한다.

  DDL, DCL 은 오라클 자동 제어

  (세션이란? 오라클서버를 접속한 단위, 하나의 연결)

5. 트랜잭션과 언두 세그먼트(Undo segment)

DML 작업을 ROLLBACK 하기 위해서는 작업 이전 데이터를 어딘가에 저장해 두어야 한다.

오라클은 작업이 발생하면 즉시 테이블의 내용을 변경 -> DELETE나 UPDATE문을 실행하면 테이블의 정보또한 즉시 변경

ROLLBACK 실행시 DML작업에 의해서 변경된 정보를 이전 정보로 환원필요,

-> 작업 이전 정보를 저장하고 있지 않다면 ROLLBACK 실행 불가

==> 작업 이전 정보를 저장하고 관리하는 것이 트랜잭션의 기본 기능

이를 위해서 오라클은 Undo Tablespace 와 Undo Segment 라는 물리적 구조를 이용한다.

구조는 자동으로 관리되며 사용자는 구조정보를 직접 볼 수 없으며 트랜잭션에서만 사용된다.

6. 트랜잭션 과정

- DML 문장은 과정이 모두 동일

- 예시

  테이블의 데이터 5를 7에 update 하는 과정

  1) UPDATE 명령

  2) 문장 수행을 위해 오라클은 Undo Segment 를 결정

  3) 테이블에 저장되어있던 원래 값(5)이 Undo Segment에 저장

  4) UPDATE 된 값(7)이 테이블에 저장

  5) 값이 변경된 행(레코드)는 독점 잠금(Lock)이 발생

  6) 테이블에는 공유 잠금(Lock)이 발생

  7) 트랜잭션이 COMMIT되면 잠금은 해제, 변경된 값(7)은 영구히 저장

  8) 트랜잭션이 ROLLBACK 되면 Undo Segment 에 저장했던 원래 값(5)을 테이블로 환원하고 잠금이 해제

7. 독점 잠금(Exclusive Lock)과 공유 잠금(Share Lock)

  1) 독점 잠금 : 현재 세션 이외에는 접근을 불허하는 잠금

  - 트랜잭션으로 행에 잠금이 발생하면 다른 세션에서는 해당 행을 검색할 수 없고 Undo Segment의 정보만 확인 가능

  - 이때 테이블에는 공유 잠금이 발생

  2) 공유 잠금 : DML 작업으로 행이 잠겨있는 테이블에 대해서 DDL (DROP, ALTER)작업을 방지

<실습>

1. 트랜잭션 잠금의 이해

  1) 실습에는 2개의 일반 사용자 세션이 사용된다.

  2) 동일한 계정으로 접속한 SQL Developer를 2개 실행한다.

  3) 2개의 창은 [세션1]과 [세션2]로 구분한다.

--[세션1]

--1)
--SELECT * FROM student WHERE sname='마초';
UPDATE student SET avr = 0.1
WHERE sname='마초';

--2) COMMIT 을 하지 않았으므로 마초의 행에는
--  독점 잠금이 발생하고, student 테이블에는
--  공유 잠금이 발생하였다.
--  student 테이블의 avr 값은 새로 변경되었고
--  Undo Segment 에는 마초의 이전 avr 값이 저장되었다.
SELECT sno, sname, avr
FROM student
WHERE sname='마초';


--4) 트랜잭션을 완료하자
COMMIT;

--[세션 2]

--3) 다른 세션에서 동일한 행을 검색해본다
--    독점 잠금이 세션1에 의해 걸려있으므로
--    Undo Segment 에서 값을 읽어온다.
SELECT sno, sname, avr
FROM student
WHERE sname='마초';


--5) 다시 검색하자
SELECT sno, sname, avr
FROM student
WHERE sname='마초';

2. 트랜잭션에 의한 대기

--[세션 1]

--1) 데이터를 변경하자(UPDATE) - 트랜잭션 시작
--    마초의 행에는 독점 잠금이 발생된다.
--    student 테이블에는 공유 잠금이 발생된다.
--    student 테이블은 새로운 학과인 '사회'로 변경된다.
--    Undo Segment 에는 마초의 변경 이전 학과인
--    '화학' 정보가 저장된다.
SELECT sno, sname, major
FROM student
WHERE sname='마초';

UPDATE student SET major='사회'
WHERE sname='마초';

SELECT sno, sname, major
FROM student
WHERE sname='마초';


--4) 상대방이 독점잠금이 걸린 행을
--   다시 변경하려고 시도하였으나
--   세션1의 트랜잭션이 끝나지 않아
--   무한 대기상태에 걸려있다
--   
--   트랜잭션을 완료한다
COMMIT;

--커밋을 완료하면 상대 세션인 세션2는 대기상태에서 빠져나와
--새로 변경된 데이터를 적용한다
  
  
--5) 세션1의 트랜잭션은 마무리되었고
--   세션2의 트랜잭션은 진행중인 상태
--   현재 검색을 하면 내가 적용한 상태로 검색된다
SELECT sno, sname, major
FROM student
WHERE sname='마초';   
   
--8) 세션2의 트랜잭션이 완료되고 검색을 한다   
SELECT sno, sname, major
FROM student
WHERE sname='마초';
   

--[세션 2]

--2) 다른 세션에서 변경하고 트랜잭션이 
--  완료되지 않은 행을 검색한다
--    Undo Segment 의 데이터를 읽어온다
SELECT sno, sname, major
FROM student
WHERE sname='마초';

--3) 독점 잠금이 걸린 행을 UPDATE 시도한다
UPDATE student SET major='경제'
WHERE sname='마초';


--6) 무한 대기가 풀리고 명령이 실행된 후
--   검색을 한다
SELECT sno, sname, major
FROM student
WHERE sname='마초';  


--7) 트랜잭션 완료
COMMIT;

-- 조인 - ON절, 좌우 외부조인, 교차조인
--1. 직원중에 사수보다 급여가 높은 사람의 이름과 급여, 그리고 이를 관리하는 관리자의 이름과 급여를 검색한다.
SELECT e1.ename 이름, e1.sal 급여, e2.ename 관리자, e2.sal 급여
FROM emp e1, emp e2
WHERE e1.mgr = e2.eno
AND e2.SAL < e1.SAL;

--2. 학생 중 동명이인을 검색한다.
SELECT s1.sname, s1.sno, s2.sname, s2.sno
FROM student s1, student s2
WHERE s1.sname = s2.sname
AND s1.sno != s2.sno;



--    단일 행 함수 - 문자 함수
--1. 'scott의 업무는 analyst입니다'형태로 모든 직원을 검색한다
SELECT ename || '의 업무는 ' || job ||' 입니다.'
FROM emp;

--2. 직원의 연봉을 10자리로 검색한다. 단 공백은 임의의 채움 문자로 채워넣는다
SELECT eno, ename, LPAD((sal*12+NVL(comm,0)), 10, '*') 연봉
FROM emp;

--3. 학생의 이름을 검색한다. 단 성이 '맹'인 학생은 성을 '사마'로 바꾸어 검색한다.
SELECT sno, sname, REPLACE (sname, '맹', '사마')
FROM student
WHERE sname LIKE '맹%';



--    단일 행 함수 - 숫자, 날짜형 함수
--1. 사원들의 급여가 현재 급여와 동일하다면 입사한 달에 급여는 얼마나 지급되었을지 검색한다.
SELECT eno, ename, hdate, sal/20*(LAST_DAY(TRUNC(hdate))-TRUNC(hdate)) 입사달급여
FROM emp;

--2. 사원들의 현재까지 근무 기간이 몇년 몇개월 몇일인지 검색한다.
SELECT  TRUNC(MONTHS_BETWEEN(sysdate, hdate)/12)||'년 '||
         TRUNC(MOD(MONTHS_BETWEEN(sysdate, hdate),12))||'개월 '||
         TRUNC(sysdate-ADD_MONTHS(hdate,TRUNC(MONTHS_BETWEEN(sysdate, hdate)))+1)||
         '일 입니다.'
FROM emp;



--    단일 행 함수 - 변환 함수
--1. 화학과 1학년 학생들의 4.5 환산 평점을 다음 형식에 따라 소수점 이하 두 자리까지 검색한다.
--   'xxx학생의 4.5 환산 평점은 x.xx입니다'
SELECT sname|| ' 학생의 4.5환산 평점은 '|| ROUND((avr/8*9),2)||'점 입니다.'
FROM student
WHERE major='화학'
AND syear=1;



--   그룹 함수
--1. 30번 부서의 업무별 연봉의 평균을 검색한다
--   단 출력 양식은 소수이하 두 자리까지 통일된 형식으로 출력한다.
SELECT dno, job, AVG(sal*12+NVL(comm,0))
FROM emp
WHERE dno=30
GROUP BY job, dno;

--2. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색한다
SELECT syear, MAX(avr)
FROM student
WHERE major='물리' 
GROUP BY syear;

--3. 학년별로 환산 평점의 평균값을 검색한다.
--   단 출력 양식은 소수이하 두 자리까지 통일된 양식으로 출력한다.
SELECT syear, ROUND(AVG((avr/8*9)), 2) 환산평점
FROM student
GROUP BY syear;



--   그룹 함수와 Having절
--1. 근무 중인 직원이 4명 이상인 부서를 검색한다(부서번호, 인원)
SELECT dno, COUNT(eno) 인원
FROM emp 
GROUP BY dno
HAVING COUNT(eno) >= 4;

--2. 업무별 평균 연봉이 2만불 이상인 업무를 검색한다
SELECT job, AVG(sal*12+NVL(comm,0))
FROM emp
GROUP BY job
HAVING AVG(sal*12+NVL(comm,0)) >= 20000;

--3. 각 학과의 학년별 인원중 인원이 6명 이상인 학년을 검색한다.
SELECT major, syear, COUNT(sno)
FROM student
GROUP BY major, syear
HAVING COUNT(sno)>=6;

--SELECT 절의 다양한 활용
--1. 급여가 10% 인상될 경우 각 사원의 연봉을 검색한다
SELECT eno, ename, sal*1.1
FROM emp;

--2. 1년동안 지급되는 급여와 10% 인상되어 1년 동안 지급되는 급여간에 차액을 검색한다
SELECT eno, ename, ((sal*12*1.1) +NVL(comm, 0))-((sal*12) +NVL(comm, 0)) 급여차액
FROM emp;

--3. 각 학생들의 평균평점과 4.5만점으로 환산한 경우 각각 평점의 상승폭은 얼마인지 검색한다
--   (별명을 이용)
SELECT sno, sname, avr 평균평점, (avr/8*9) 환산, (avr/8*9)-avr 상승폭
FROM student;



--연결 연산자와 중복제거
--1. '__학생의 4.5 환산 평점은 __입니다.'형태로 학생의 환산 평점을 출력한다
SELECT sname||' 학생의 4.5 환산 평점은 '|| avr/8*9 ||' 입니다.'
FROM student;

--2. '__과목의 담당 교수 번호는 __입니다' 형태로 과목의 정보를 출력한다
SELECT cname||' 과목의 담당 교수번호는 '|| pno ||' 입니다.'
FROM course;

--3. 학교에 개설된 과목들은 몇 학점짜리인지 검색한다
SELECT cname, st_num
FROM course
ORDER BY st_num;

--4. 학생들이 수강중인 과목의 과목번호를 검색한다
SELECT DISTINCT cno
FROM score
ORDER BY cno;



--정렬과 묶음 검색
--1. 각 부서별로 사원의 연봉을 검색한다
SELECT * 
FROM emp
ORDER BY dno;

--2. 급여가 10% 인상된 경우 부서별로 각 사원의 연봉을 연봉순으로 검색한다
SELECT eno, ename, sal*1.1 인상급여, dno
FROM emp
ORDER BY dno;

--3. 보너스가 100% 인상된 경우 업무별로 각 사원의 연봉을 연봉순으로 검색한다
SELECT eno, ename, ((sal*12)+(NVL(comm, 0)*2)) 연봉
FROM emp
ORDER BY 연봉 DESC;

--4. 학과별 학년별로 학생의 정보를 성적순으로 입력한다
SELECT major, syear, sname, avr
FROM student
ORDER BY major, syear, avr DESC;

--5. 4.5점으로 환산된 평점을 이용 학과별로 학생의 정보를 성적순으로 입력한다
SELECT major, sno, sname, (avr/8*9) 환산평점
FROM student
ORDER BY major, 환산평점 DESC;



--WHERE 절을 이용한 조건 검색
--1. 학과 및 학년별로 학점이 2.0 미만인 학생을 낮은 성적순으로 검색한다
SELECT major, syear, sno, sname, avr
FROM student
WHERE avr <2
ORDER BY avr;

--2. 학과별로 부임일이 1998년 이전인 교수의 정보를 부임일 순으로 검색한다
SELECT * 
FROM professor
WHERE hiredate < '1999/01/01'
ORDER BY hiredate;

--3. 담당교수가 정해지지 않은 과목을 학점순으로 검색한다
SELECT * 
FROM course
WHERE pno IS NULL
ORDER BY st_num;

--4. 보너스가 급여의 10% 이상인 사원을 검색한다
SELECT * 
FROM emp
WHERE comm > (sal/10);



--관계 연산자와 LIKE 연산자
--1. 화학과 학생중에 성이 '사마'인 학생을 검색한다
SELECT * 
FROM student
WHERE sname LIKE '사마%';

--2. 화학과 학생중 4.5 환산 평점이 3.5이상인 학생을 검색한다
SELECT major, sno, sname, (avr/8*9) 환산평점
FROM student
WHERE (avr/8*9) > 3.5
AND major = '화학';

--3. 화학과 이외 학과 학생의 평점을 각 학과 및 학년별로 검색한다
SELECT major, syear, sno, sname, avr
FROM student
WHERE major !='화학'
ORDER BY major, syear;

--4. 교수가 정해진 과목중에 화학관련 과목을 검색한다
--   (이름에 '화학'이란 문자열이 있는 과목)
SELECT * 
FROM course
WHERE cname LIKE '%화학%'
AND pno IS NOT NULL;

--5. 2000년 이후 부임한 교수 중에 직위가 부교수인 교수를 검색한다
SELECT * 
FROM professor
WHERE orders = '부교수'
AND hiredate > '1999/12/31';



--BETWEEN 과 IN 연산자
--1. 평점이 3.0에서 4.0사이의 학생을 학과별로 검색한다
SELECT * 
FROM student
WHERE avr BETWEEN 3 AND 4
ORDER BY major;

--2. 물리, 화학과 학생중 4.5 환산 평점이 3.5에서 4.0사이인 학생을 검색한다
SELECT major, sno, sname, (avr/8*9) 환산평점
FROM student
WHERE (avr/8*9) BETWEEN 3.5 AND 4
AND major IN ('물리', '화학');

--3. 물리, 화학과 학생의 정보를 학년별 성적순으로 검색한다
SELECT * 
FROM student
WHERE major IN ('물리', '화학')
ORDER BY syear, avr DESC;

--4. 물리, 화학과 교수중에 1999년에서 2000년 사이에 부임한 교수의 정보를 직위별로 검색한다
SELECT * 
FROM professor
WHERE hiredate BETWEEN '1999/01/01' AND '2001/01/01'
ORDER BY orders;


   
--등가조인/비등가조인
--1. 김연아의 입사일보다 빨리 부임한 교수의 명단을 검색한다
SELECT *
FROM professor
WHERE TO_DATE(hiredate) < (SELECT TO_DATE(hdate)
                                           FROM emp
                                           WHERE ename= '김연아');

--2. 송강 교수의 과목을 수강하는 학생의 기말고사 점수를 성적 순으로 검색한다
SELECT * 
FROM st_score 
WHERE cno IN(SELECT cno
                      FROM course
                      WHERE pno IN(SELECT pno
                                            FROM professor
                                            WHERE pname ='송강'))
ORDER BY result DESC;                            

--3. 송강 교수가 강의하는 과목에서 평가 점수가 A인 학생의 명단을 과목명과 함께 검색한다
SELECT sno, sname, cname, grade 
FROM st_score 
NATURAL JOIN scgrade
WHERE cno IN(SELECT cno
                      FROM course
                      WHERE pno IN(SELECT pno
                                            FROM professor
                                            WHERE pname ='송강'))
AND result BETWEEN  loscore AND  hiscore
AND grade='A';

--4. 화학과 1학년 학생에게 강의하는 교수의 명단을 검색한다
SELECT * 
FROM professor 
WHERE pno IN(SELECT pno 
                      FROM course
                      WHERE cno IN(SELECT cno 
                                            FROM score
                                            WHERE sno IN(SELECT sno
                                                                  FROM student
                                                                  WHERE syear=1 AND major='화학')));



--조인-자기참조조인,외부조인
--1. 직원중에 자신의 관리자보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색한다
SELECT e1.eno 사번, e1.ename 사원, e1.sal 급여, e1.mgr 관리자사번,
          e2.eno 관리자사번, e2.ename 관리자, e2.sal 급여
FROM emp e1, emp e2
WHERE e1.mgr=e2.eno
AND e1.sal> e2.sal;

--2. 각 부서의 지역과 부서장을 검색한다
SELECT d.dno, dname, loc, mgr, e.eno, ename
FROM dept d, emp e
WHERE d.dno=e.dno
AND eno=director;



--조인-자연조인과 Using절을 이용한 조인
--1. 송강 교수의 과목을 수강하는 학생의 기말고사 점수를 성적순으로 검색한다
SELECT sno, sname, cname, result
FROM student
JOIN score USING (sno)
JOIN course USING (cno)
JOIN professor USING (pno)
WHERE pname ='송강'
ORDER BY result DESC;

--2. 화학과 1학년 학생에게 강의하는 교수의 명단을 검색한다.
SELECT DISTINCT pname
FROM professor
JOIN course USING (pno)
JOIN score USING (cno)
JOIN student USING (sno)
WHERE major='화학'
AND syear = 1;



--단일행 서브쿼리를 사용하세요
--1. 화학과 1학년 학생중 평점이 평균 이하인 학생을 검색한다
SELECT major, syear, sname, AVG(avr)
FROM student 
GROUP BY major, syear, sname
HAVING AVG(avr) <(SELECT AVG(avr)
                              FROM student)
AND major ='화학' AND syear=1
ORDER BY AVG(avr);

--2. 일반 화학을 수강하는 학생중 성적이 가장 낮은 학생의 이름을 검색한다
--  조인을 전혀 사용하지 않고 서브 쿼리만으로 SQL문을 작성한다
SELECT sno, sname
FROM student
WHERE sno IN(SELECT sno
                      FROM score
                       WHERE result =(SELECT MIN(result)
                                               FROM score
                                               WHERE cno = (SELECT cno
                                                                     FROM course
                                                                     WHERE cname='일반화학' )));
  

<서브쿼리를 이용한 DML문장>

• 다량의 DML작업을 수행하는 경우 서브 쿼리문을 이용
• IMSERT 작업에 대량 데이터를 입력할 시 서브쿼리를 이용하면 매우 효율적
• 메모리를 통하지않고 데이터를 직접 디스크에 입력하는 다이렉트 로드를 같이 사용하면 효율성 배가

서브쿼리를 이용한 DML문
INSERT INTO [+APPEND] 테이블 [NOLOGGING]
[(컬럼, 컬럼, . . )]
SELECT 문장;

- 서브쿼리(SELECT문)에 검색된 행을 입력 값으로 사용한다.
- 한번에 많은 행을 입력할수있다.
- 컬럼리스트와 SELECT문의 컬럼이 1:1로 대응 되어야 한다.
- SELECT문에 사용한 서브 쿼리문과는 달리 괄호를 쓰지 않는다.
- [+ APPEND] : 다이렉트 로드함으로 대량의 입력작업을 더 빠르게 작업한다.
- [NOLOGGING] : 로그정보를 남기지 않음으로 입력작업이 빨라진다. 그러나 장애가 발생시 복구불가능

UPDATE 테이블
SET 컬럼명 = (SELECT 문장),
    컬럼명 = (SELECT 문장), ...
[WHERE 조건];

UPDATE 테이블
SET (컬럼, 컬럼, ...) = (SELECT 문장)
[WHERE 조건];

- UPDATE 문의 SET 절에는 단일 행 서브쿼리문이나 다중 열 서브 쿼리문을 이용할 수 있다.
- 다중열인 경우도 반드시 결과 행은 하나여야 한다.
- 이 때 검색되는 Data는 반드시 수정되는 컬럼명과 1:1 대응돼야 한다.
- SET 절에는 단일행 서브쿼리만 허용되지만 WHERE절의 서브 쿼리는 연산자에 따라 달라진다.

-- emp2 테이블에 사번과 연봉을 입력한다
 DESC emp2;
 SELECT * FROM emp2;
 
INSERT INTO emp2(eno, asal)
SELECT eno, sal*12+NVL(comm,0) FROM emp;
   
ROLLBACK;

-> 데이터 타입이 일치하지 않으면 에러 발생

-데이터 입력이 가능한 경우

1) 데이터 타입이 같고 저장 컬럼의 크기가 입력 데이터 크기보다 클 경우

2) 데이터 타입이 달라도 자동 형변환이 가능하고 저장 컬럼의 크기가 입력 데이터 크기보다 클 경우

- '10' => 10

--   숫자형 => 문자형 컬럼
INSERT INTO emp2(eno, asal)
SELECT eno, hdate FROM emp;

INSERT INTO emp2(eno, asal)
SELECT eno, ename FROM emp;


--아래 경우는 자동형변환 되어 들어간다
INSERT INTO emp2(eno, asal)
SELECT eno, dno FROM emp;
 
SELECT * FROM emp2; 


--각 사원의 정보와 근무지를 emp3 테이블에 저장하라
SELECT * FROM emp3;
DESC emp3;

<다이렉트 로드>

• 일괄적인 대량의 DML 작업이 메모리에서 일어나면 비효율이 발생
• 이를 피하기 위해 대량 DML 작업에서는 메모리를 통하지 않고 디스크에 직접 작업을 수행
• ==>다이렉트 로드(Direct Load)
• DML 작업을 다이렉트로드하기 위해서는 /*+ APPEND */를 문장에 삽입

/**/

: /*는 평소에는 주석으로 사용되지만 + 기호를 붙여줌으로써 주석이 아닌 오라클에 옵션을 전달하는 용도로 사용

- 오라클은 안전한 시스템 운영을 위해 장애 발생 시 복구할 수 있도록 DB에 변경을 가하는 모든 작업의 내용을

Redo Log영역에 로그 정보로 보관

- 대량의 DML작업은 작업 성능을 위해 로그 정보를 남기지 않고 NOLOGGING 옵션을 사용 -> 장애에 대비한 백업을 필요

==> 대량의 작업에서 다이렉트 로드와 NOLOGGING 방식을 사용하면 작업 성능은 비약적으로 향상되지만 작업의 안전도는 떨어지게 된다.

- 다이렉트 로드를 이용해서 대량의 데이터를 테이블에 입력한 경우 트랜잭션을 마무리 하지 않고 검색하게 되면 'ORA-12838: 병렬로 수정한 후 객체를 읽거나 수정할 수 없습니다'라는 에러를 만나게 된다.

==> COMMIT 을 수행한 이후 검색

--서브 쿼리를 이용한 UPDATE 문을 수행한다.

SELECT *
FROM emp
WHERE ename='윤고은';

--윤고은의 급여와 보너스를 각각 김연아의 급여와 손하늘의 보너스와 동일하게 수정한다
UPDATE emp
SET sal=(SELECT sal
         FROM emp
         WHERE ename='김연아'),
    comm = (SELECT comm
            FROM emp
            WHERE ename='손하늘')
WHERE ename='윤고은';

SELECT * FROM emp;



--제갈민과 동일한 부서의 사원들의 급여를
--제갈민의 급여와 동일하게 수정한다
SELECT * FROM emp
WHERE dno = (SELECT dno
            FROM emp
            WHERE ename = '제갈민');

UPDATE emp
SET sal=(SELECT sal
            FROM emp
            WHERE ename = '제갈민')
WHERE dno = (SELECT dno
            FROM emp
            WHERE ename = '제갈민');
            

--이초록의 급여, 보너스를 김연아와 동일하게 수정한다
UPDATE emp
SET (sal, comm) =(SELECT sal, comm
         FROM emp
         WHERE ename='김연아')
WHERE ename='이초록';


ROLLBACK;

<Exrecise>

--1. 일반 화학 과목을 수강하는 학생의 성적을 4.5만점 기준으로 수정한다
UPDATE student  SET avr = (avr/8*9)
WHERE sno IN(SELECT sno
                      FROM score
                      WHERE cno IN (SELECT cno
                                             FROM course
                                             WHERE cname='일반화학'));



--2. 화학과 교수의 과목중에 학점이 3학점 미만인 과목을 모두 3학점으로 수정한다
UPDATE course SET st_num = 3
WHERE st_num<3
AND pno IN(SELECT pno
                      FROM professor
                      WHERE section ='화학');



--3. 학생의 기말고사 성적을 모두 st_score 테이블에 저장한다
INSERT /*+ APPEND */  INTO st_score NOLOGGING
(sno, sname, major, syear, cno, cname, result)
SELECT student.sno, sname, major, syear, course.cno, cname, result
FROM student
JOIN score ON student.sno = score.sno
JOIN course ON score.cno=course.cno;

SELECT * FROM st_score;

COMMIT;

DELETE FROM st_score;



--4. st_score 테이블에 각 학생의 평점을 학과별, 과목별로 입력한다. 
--검색할 때 대부분 학과별 검색을 수행하기 때문에 이들 데이터가 학과별로 몰려있으면 성능을 향상시킬 수 있다

INSERT /*+ APPEND */  INTO st_score NOLOGGING
(sno, sname, major, syear, cno, cname, result)
SELECT student.sno, sname, major, syear, course.cno, cname, result
FROM student
JOIN score ON student.sno = score.sno
JOIN course ON score.cno = course.cno
ORDER BY major, cname;



--5. 화학과 학생이 수강하는 과목을 강의하는 교수의 부임일자를 1년 늦도록 수정한다.

UPDATE professor SET hiredate =(hiredate+365)
WHERE pno IN(SELECT pno
                      FROM course
                      WHERE cno IN (SELECT cno 
                                              FROM st_score
                                              WHERE major='화학'));

DML(Data Manipulation Language)란?

; 데이터베이스에 데이터를 입력, 수정, 삭제하는 명령

- SELECT 문과는 다르게 데이터베이스의 내용을 변경하는 작업을 수행함으로 주의깊게 계획되고 실행되어야 한다.

- 이렇게 DML 작업은 이전과는 달리 안전한 작업과정과 결과가 요구됨으로 이를 위해 오라클은 DML 작업을 트랜잭션이라는 단위로 관리한다.

<INSERT>

INSERT INTO 테이블 [(컬럼, 컬럼, ...)]
VALUES (값, 값, ...);

1) INSERT 문은 하나의 행만을 삽입한다

2) 컬럼 리스트와 VALUE 절의 값은 반드시 1:1로 대응된다

3) 테이블의 모든 컬럼에 값을 입력하는 경우 컬럼 리스트를 생략할 수 있다.

  VALUE 절에 기술하는 값의 순서는 DESC 명령으로 확인된 테이블의 컬럼 순서와 일치해야 한다.

4) 입력되는 값이 숫자가 아닌 경우 반드시 단일 인용부호를 사용한다.

5) INSERT 문장에서 생략된 컬럼은 null 값이 입력된다.

6) 명시적인 null 값은 null을 기술한다.

7) 값 대신 DEFAULT 를 기술하면 DEFAULT 값이 입력된다.

<UPDATE>

UPDATE 테이블
SET 컬럼=값, 컬럼=값, ...
[WHERE 조건];

1) 컬럼의 값을 수정한다

2) WHERE 절을 생략하면 모든 행의 지정된 컬럼 값이 수정된다

3) 여러 컬럼의 값을 동시에 수정할 수 있다

4) 값 대신에 DEFAULT 를 기술하면 테이블에 정의된 DEFAULT 값으로 변경된다

<DELETE>

DELETE FROM 테이블
[WHERE 조건];

1) 행단위로 데이터를 삭제한다

2) 조건이 없는 경우 테이블의 모든 행을 삭제

3) 테이블을 삭제해도 테이블의 물리적 구조는 변하지 않음 (공간을 차지)

<DCL(Data Control Language)

; COMMIT, ROLLBACK>

COMMIT;

1) 작업을 완료한다

2) 모든 DML문장을 수행한 후 작업을 완료할때 반드시 필요

ROLLBACK;

1) 작업을 취소한다

2) 모든 DML문장을 수행한 후 작업을 취소할 때 반드시 필요

--dept테이블의 모든 데이터를 삭제한후 롤백과 커밋을 수행, 결과확인
DELETE FROM dept;

ROLLBACK;

SELECT *
FROM dept;


--emp와 dept테이블의 모든 데이터 삭제
DELETE FROM emp;
DELETE FROM dept;

SELECT *
FROM dept;

SELECT *
FROM emp;

ROLLBACK; -- COMMIT을 실행하기 전에는 몇 단계의 명령문을 실행하던 ROLLBACK으로 복구할 수 있다.


--테이블에 데이터 추가 
--세션포맷설정
ALTER SESSION SET
nls_date_format='YYYY-MM-DD';

ALTER SESSION SET
nls_date_format='YY-MM-DD';

ALTER SESSION SET
nls_date_format='YYYY-MM-DD:HH24:MI:SS';

DESC emp;

--추가할 데이터들
--1901 권준연 요리      2020-02-01 5000 2000 10
--1902 박서준 개발        2020-01-12 6000 3000 20
--1903 연우   가수        2020-02-12  2000 100 30
--1904 이은희 백수          2020-02-10 9999 9999 40

INSERT INTO emp 
(eno, ename, job, mgr, hdate, sal, comm, dno)
VALUES
('1901', '권준연', '요리', NULL, '2020-02-01',
5000, 2000, '10');

INSERT INTO emp 
(eno, ename, job, mgr, hdate, sal, comm, dno)
VALUES
('1902', '박서준', '개발', NULL, '2020-01-12',
6000, 3000, '20');

INSERT INTO emp 
VALUES
('1904', '이은희', '백수', NULL, '2020-02-10',
9999, 9999, '40');

INSERT INTO emp 
VALUES
('1903', '연우', '가수', NULL, '2020-02-12',
2000, 100, '30');

SELECT *
FROM emp;
  
ROLLBACK;

COMMIT;


--추가
--emp
--사번: 1910
--이름: 수지
--입사일: 2020-01-02

INSERT INTO emp(eno, ename, hdate)
VALUES('1910', '수지', '2020-01-02');

INSERT INTO emp(eno, ename, hdate)
VALUES('1911', '장나라', '2020-01-02:12:23:39');--세션 날짜 포맷이 안맞아서 오류가 뜸


--만약 이렇게 넣고 싶다면 1)현재 세션 포맷자체을 변경하거나
ALTER SESSION SET
nls_date_format='YYYY-MM-DD:HH24:MI:SS';

INSERT INTO emp(eno, ename, hdate)
VALUES('1911', '장나라', '2020-01-02:12:23:39');


--2) TO_DATE로 변환하여 입력. 세션포맷이 달라도 입력가능
INSERT INTO emp(eno, ename, hdate)
VALUES('1912', '모모', TO_DATE('2020-01-02:12:23:39', 'YYYY-MM-DD:HH24:MI:SS'));


--TO_CHAR를 이용하면 원하는 방식의 세션포맷으로 출력 가능
SELECT eno, ename, TO_CHAR(hdate, 'YYYY-MM-DD:HH24:MI:SS')
FROM emp;



--날짜에 적용되는 디폴트값을 확인
ALTER SESSION SET
nls_date_format='YYYY-MM-DD:HH24:MI:SS';

INSERT INTO emp(eno, hdate)
VALUES ('1920', TO_DATE('2020', 'YYYY'));
INSERT INTO emp(eno, hdate)
VALUES ('1921', TO_DATE('99', 'YY')); -- 현재 년도 기준
INSERT INTO emp(eno, hdate)
VALUES ('1922', TO_DATE('99', 'RR')); -- 가까운 년도

SELECT *
FROM emp;

ALTER SESSION SET
nls_date_format='YYYY-MM-DD';


--이승철의 부서번호를 10번으로 수정하고 급여를 10% 인상하자
--1) 이승철 행을 DELETE 후 INSERT
--2) 이승철 행을 UPDATE
SELECT *
FROM emp
WHERE ename='이승철';

UPDATE emp SET dno='10', sal=sal*1.1
WHERE ename='이승철';

<Exercise>

--1. emp 테이블에 소녀시대 멤버를 임의로 입력하라
--   단, 시간은 현재시간을 입력하세요
INSERT INTO emp(eno, ename, hdate)
VALUES('1930', '임윤아', sysdate);

INSERT INTO emp(eno, ename, hdate)
VALUES('1931', '티파니', sysdate);

INSERT INTO emp(eno, ename, hdate)
VALUES('1932', '태연', sysdate);

INSERT INTO emp(eno, ename, hdate)
VALUES('1933', '서현', sysdate);

SELECT * FROM emp;



--2. 모든 학생의 성적을 4.5만점 기준으로 수정하라
UPDATE student SET avr=(avr/8*9);


--3. 모든 교수의 부임일자를 100일 앞으로 수정하라
UPDATE professor SET hiredate=(hiredate-100);


--4. 화학과 2학년 학생의 정보를 삭제하라
DELETE FROM student
WHERE major = '화학'
AND syear=2;


--5. 조교수의 정보를 삭제하라
DELETE FROM professor
WHERE orders = '조교수';


--6. 원래 정보로 회복하라
ROLLBACK;

<서브쿼리 다중행>

SELECT 컬럼, . . 
FROM 테이블
WHERE 컬럼 <다중행연산자> (SELECT문 : Sub Query문);

1) 서브쿼리에 여러개의 행이 검색되는 쿼리를 다중행 서브쿼리라고 한다
2) 다중행 서브쿼리는 다중행 연산자를 이용하낟
3) 다중행 연산자의 종류

 - IN : 검색된 값중에 하나만 일치하면 참이다
 - ANY : 검색된 값중에 조건에 맞는 것이 하나 이상 있으면 참이다.
 - ALL : 모든 검색 값과 조건이 맞아야 참이다.

--20번 부서원들과 동일한 관리자로부터 관리받는 사원을 검색한다
--1) 20번 부서원들의 관리자가 여러 명일 수 있다
--2) 서브쿼리의 결과가 몇 개일지 예측할 수 없는 경우
--   다중 행 연산자를 이용하여 다중 행 서브쿼리를 작성한다
   
SELECT dno, eno, ename
FROM emp
WHERE mgr IN (SELECT mgr
              FROM emp
              WHERE dno='20')
AND dno!='20';

--10번 부서원들보다 급여가 낮은 사원을 검색한다
SELECT eno, ename, sal
FROM emp
WHERE sal < ALL (SELECT sal
                 FROM emp
                 WHERE dno='10')
AND dno!='10';

SELECT eno, ename, sal
FROM emp
WHERE sal < (SELECT MIN(sal)
                 FROM emp
                 WHERE dno='10')
AND dno!='10';

4) 다중행 연산자와 그룹 함수

 - ALL : 모두 다 보다

 - ANY : 모두 중에 하나라도

 - 컬럼 > ALL => 컬럼 > MAX : 가장 큰 값보다 크다

 - 컬럼 < ALL => 컬럼 < MIN : 가장 작은 값보다 작다

 - 컬럼 > ANY => 컬럼 > MIN : 가장 작은 값보다 크다

 - 컬럼 < ANY => 컬럼 < MAX : 가장 큰 값보다 작다

--20번 부서원들과 보너스가 같은 사원을 검색한다

SELECT eno, ename, comm, dno
FROM emp
WHERE comm IN (SELECT comm
               FROM emp
               WHERE dno='20')
AND dno!='20';

SELECT comm
FROM emp
WHERE dno='20';

SELECT eno, ename, comm, dno
FROM emp
WHERE comm IN (SELECT NVL(comm,0)
               FROM emp
               WHERE dno='20')
AND dno!='20';

<서브쿼리 다중열>

SELECT 컬럼,...
FROM 테이블
WHERE (컬럼1, 컬럼2, ...) IN (SELECT 문 : Sub Query 문);

1) 서브 쿼리의 SELECT 문에 여러 개의 컬럼을 검색

2) 여러 개의 컬럼을 검색하는 서브 쿼리 문을 이용할 때는 반드시 비교 대상 컬럼과 1:1 대응되어야 한다

3) 다중 열 서브 쿼리에서 서브 쿼리의 검색 결과가 하나의 행이라면 '='연산자 사용이 가능하지만 되도록 'IN'연산자를 사용한다.

--손하늘과 동일한 관리자의 관리를 받으면서 업무도 같은 사원을 검색한다.

SELECT eno, ename, mgr, job
FROM emp
WHERE (mgr, job) IN (SELECT mgr, job
                     FROM emp
                     WHERE ename='손하늘')
AND ename!='손하늘';


--손하늘 사원이 1명만 존재한다면 다중 행 서브쿼리로 변경할 수도 있다. 
--손하늘 사원이 여러 명이면 다중 열 서브쿼리로만 가능하다.
SELECT eno, ename, mgr, job
FROM emp
WHERE mgr IN (SELECT mgr
              FROM emp
              WHERE ename='손하늘')
AND job IN (SELECT job
            FROM emp
            WHERE ename='손하늘')
AND ename!='손하늘';

SELECT eno, ename, mgr, job
FROM emp
WHERE mgr = (SELECT mgr
              FROM emp
              WHERE ename='손하늘')
AND job = (SELECT job
            FROM emp
            WHERE ename='손하늘')
AND ename!='손하늘';


--각 부서별로 최소 급여를 받는 사원의 정보를 검색한다.(이름, 급여)
SELECT dno, ename, sal
FROM emp
WHERE (dno, sal) IN (SELECT dno, MIN(sal)
                     FROM emp
                     GROUP BY dno)
ORDER BY dno;

<Exercise>

--1. 화학과 학생과 평점이 동일한 학생들을 검색한다
SELECT major, sno, sname, avr
FROM student
WHERE avr IN(SELECT avr
                    FROM student
                    WHERE major='화학')
AND major !='화학'
ORDER BY avr;


--2. 화학과 교수와 부임일이 같은 사원을 검색한다
SELECT section, pname, hiredate
FROM professor 
WHERE hiredate IN(SELECT hdate
                          FROM emp)
AND section = '화학';


--3. 화학과 학생과 같은 학년에서 평점이 동일한 학생들을 검색한다.
SELECT major, syear, sno, sname, avr
FROM student
WHERE (syear, avr) IN(SELECT syear, avr
                    FROM student
                    WHERE major='화학')
AND major !='화학'
ORDER BY syear,avr;


--4. 10번 부서 사원들과 연봉이 동일한 사원을 검색한다
SELECT dno, eno, ename, (sal*12+NVL(comm,0)) 연봉
FROM emp
WHERE (sal*12+NVL(comm,0)) IN(SELECT (sal*12+NVL(comm,0))
                    FROM emp
                    WHERE dno=10)
AND dno !=10;


--5. 기말고사 성적이 핵 화학과목보다 우수한 과목의 과목명과 담당 교수명을 검색한다
SELECT cname, pname, AVG(result)
FROM score 
JOIN course ON course.cno=score.cno
JOIN professor ON professor.pno=course.pno
GROUP BY cname, pname
HAVING AVG(result) > (SELECT AVG(result)
                        FROM score
                        JOIN course ON course.cno=score.cno
                        WHERE cname='핵화학')
AND cname != '핵화학';


--6. 10번 부서 사원들과 급여 및 연봉이 동일한 사원을 검색한다

SELECT dno, eno, ename, sal 급여, (sal*12+NVL(comm,0)) 연봉
FROM emp
WHERE (sal, (sal*12+NVL(comm,0))) IN (SELECT sal, (sal*12+NVL(comm,0))
                                                        FROM emp
                                                        WHERE dno =10)
AND dno != 10;