BEGIN OPEN C1; LOOP FETCH C1 INTO VEMPNO, VENAME, VSAL; EXIT WHEN C1%NOTFOUND; IF VSAL>=3000 THEN LEVEL := '높음'; ELSIF VSAL>=2000 THEN LEVEL := '보통'; ELSE LEVEL := '낮음'; END IF; DBMS_OUTPUT.PUT_LINE(VEMPNO|| ' ' ||VENAME|| ' ' ||VSAL|| ' ' ||LEVEL); END LOOP; CLOSE C1; END; /
Q1) 커서를 사용해서 모든 사원의 이름,급여,전체사원의 급여합을 구해서 출력해 보세요.
DECLARE VENAME EMP.ENAME%TYPE; VSAL EMP.SAL%TYPE; TOTSAL NUMBER(7,2) :=0; -- **초기값 지정해야 결과값 도출됨 CURSOR C1 IS SELECT ENAME, SAL FROM EMP;
BEGIN OPEN C1; LOOP FETCH C1 INTO VENAME, VSAL; EXIT WHEN C1%NOTFOUND; TOTSAL := TOTSAL + VSAL; DBMS_OUTPUT.PUT_LINE(VENAME|| ' ' ||VSAL); END LOOP; DBMS_OUTPUT.PUT_LINE('전체사원의 급여합: '||TOTSAL); --LOOP가 끝난 다음에 위치 CLOSE C1; END; /
예3) 각 부서의 급여합 구하기 (PROCEDURE로 만들기) CREATE OR REPLACE PROCEDURE DEPT_SAL IS CURSOR C1 IS SELECT NVL(SAL,0) SAL,DEPTNO FROM EMP; -- NVL ; NULL 값이 있으면(계산자체가안됨) '0'으로 넣어주는 명령어 / 그 옆에 SAL은 ALIAS D1 NUMBER(5):=0; --10번부서합 D2 NUMBER(5):=0; --20번부서합 D3 NUMBER(5):=0; --30번부서합 BEGIN --FOR 문을 사용하면 OPEN과 CLOSE를 하지 않아도 된다. FOR CURVAL IN C1 LOOP --C1에서 데이터를 꺼내와 CURVAL 변수(SAL/DEPTNO 데이터를 통으로 꺼내와서 저장)에 저장 IF CURVAL.DEPTNO=10 THEN D1 := D1 + CURVAL.SAL; ELSIF CURVAL.DEPTNO=20 THEN D2 := D2 + CURVAL.SAL; ELSIF CURVAL.DEPTNO=30 THEN D3 := D3 + CURVAL.SAL; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('10번부서합:' || D1); DBMS_OUTPUT.PUT_LINE('20번부서합:' || D2); DBMS_OUTPUT.PUT_LINE('30번부서합:' || D3); END; /
EXECUTE DEPT_SAL;
Q2) 커서를 사용해서 모든 사원번호,이름,부서명,급여,보너스,부서번호를 출력하는 프로시져를 만들고 사용해 보세요. 보너스는 부서번호가 10번이면 급여의 10% 부서번호가 20번이면 급여의 20% 부서번호가 30번이면 급여의 30%
((* 문제에 <모든> 사원번호가 있으므로 CURSOR 사용. FOR문 이용해여 작성))
CREATE OR REPLACE PROCEDURE PRINTINFO IS CURSOR C1 IS SELECT EMPNO 사원번호, ENAME 이름, DNAME 부서명, SAL 급여, D.DEPTNO 부서번호 FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO ; BONUS NUMBER(5):=0; BEGIN FOR CURVAL IN C1 LOOP --C1에서 데이터를 꺼내와 CURVAL 변수(SAL/DEPTNO 데이터를 통으로 꺼내와서 저장)에 저장 IF CURVAL.부서번호=10 THEN BONUS := CURVAL.급여 * 0.1; ELSIF CURVAL.부서번호=20 THEN BONUS := CURVAL.급여 * 0.2; ELSIF CURVAL.부서번호=30 THEN BONUS := CURVAL.급여 * 0.3; END IF; DBMS_OUTPUT.PUT_LINE('사원번호:'||CURVAL.사원번호||'이름:'||CURVAL.이름||'부서명:'||CURVAL.부서명||'급여:'||CURVAL.급여||'보너스:'||BONUS); END LOOP;
END; / EXECUTE PRINTINFO;
[6] 트리거(Trigger)
- 데이터베이스가 미리 정해놓은 조건을 만족하거나 어떤 동작이 수행되면 자동으로 수행되는 동작.특정 동작에 대한 이벤트로 인해서 실행되는 프로시져 --INSERT, UPDATE, DELETE (데이터베이스에서의 이벤트) - 형식) CREATE OR REPLACE TRIGGER 트리거이름 TIMING[BEFORE|AFTER] EVENT[INSERT|UPDATE|DELETE] ON 테이블명 [FOR EACH ROW] -행마다 적용할 건지 / DECLARE 변수선언; BEGIN 실행문장; END; / - 트리거유형 1) 문장레벨트리거 : DML발생시 한번만 수행. 영향을 받는 행이 없어도 수행된다. 2) 행레벨트리거 : DML발생시 각 행이 변경될때마다 수행. FOR EACH ROW 옵션을 설정해서 생성한다. :OLD 와 :NEW객체를 사용할 수 있다. --오라클에 의해 생성된 OLD(또는 NEW) 객체를 참조한다는 뜻
OLD는 DELETE(수정, 삭제된 행에 대한 정보를 갖는 객체) 에서만 사용가능, NEW(수정, 추가된 행에 대한 정보를 갖고있음)는 INSERT에서만 사용가능
예1) 부서가 추가되었을 때, '부서가 추가되었습니다' 를 출력하는 트리거 생성하기 CREATE OR REPLACE TRIGGER MSG_DEPT AFTER INSERT ON DEPT BEGIN DBMS_OUTPUT.PUT_LINE('부서가 추가 되었습니다.'); END; / INSERT INTO DEPT VALUES(11, '부서1', '서울');
Q1) 사원이 수정될때(UPDATE) 사원정보가 수정되었어요. 라고 출력하는 트리거 만들고 실행되도록 해보세요.
CREATE OR REPLACE TRIGGER MSG_EMP AFTER UPDATE ON EMP BEGIN DBMS_OUTPUT.PUT_LINE('사원정보가 수정되었습니다.'); END; / INSERT INTO EMP(EMPNO, ENAME) VALUES(91, '다슬송');
UPDATE EMP SET ENAME ='송다슬' WHERE ENAME='다슬송';
=============================================================== 예3) 행레벨트리거 --> FOR EACH ROW옵션을 설정해서 트리거 생성
#문장레벨트리거 - 영향을 받는 행이 없거나 여러 행이 영향을 받아도 무조건 한 번만 수행됨 CREATE OR REPLACE TRIGGER PRINT_MSG AFTER DELETE ON DEPT BEGIN DBMS_OUTPUT.PUT_LINE('부서가 삭제되었어요'); END; /
SQL> DELETE FROM DEPT WHERE DEPTNO=12; 부서가 삭제되었어요 -- 삭제된 행이 없어도 무조건 1번 수행됨
1 row deleted.
#행레벨트리거 - FOR EACH ROW 옵션주기 -- 영향을 받은 행의 갯수만큼만 실행됨 CREATE OR REPLACE TRIGGER PRINT_MSG AFTER DELETE ON DEPT FOR EACH ROW BEGIN -- :OLD 삭제된 부서에 대한 정보를 갖는 객체 DBMS_OUTPUT.PUT_LINE(:OLD.DEPTNO || '부서가 삭제되었어요'); END; /
CREATE OR REPLACE TRIGGER PRINT_DEPTNO AFTER INSERT ON DEPT FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(:NEW.DNAME || '부서가 추가되었습니다.'); END; /
SQL> INSERT INTO DEPT VALUES(55, '부서55', '제주'); 부서55부서가 추가되었습니다. 부서가 추가 되었습니다.
1 row created.
## 트리거 수정 : DELETE 이벤트에서는 :OLD 객체를 통해 삭제된 행의정보를 얻어옴 CREATE OR REPLACE TRIGGER PRINT_MSG AFTER DELETE ON DEPT FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(:OLD.DEPTNO || '부서가 삭제되었어요'); END; /
예) UPDATE CREATE OR REPLACE TRIGGER UPDATE_DEPTNO AFTER UPDATE ON DEPT FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(:OLD.DNAME || '부서의 위치가 ' || :OLD.LOC || ' 에서'|| :NEW.LOC || ' 위치로 변경됨'); END; /
SQL> UPDATE DEPT SET LOC='신촌' WHERE DEPTNO=40; OPERATIONS부서의 위치가 BOSTON 에서신촌 위치로 변경됨
1 row updated.
============================================================ Q1) EMP테이블에 사원정보가 추가되면 사원번호 XX님이 입사하셨습니다 라고 출력되는 트리거 만들고 실행되도록 해보기
CREATE OR REPLACE TRIGGER MSG_EMP AFTER INSERT ON EMP FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('사원번호'|| :NEW.ENAME || ' 님이 입사하셨습니다.'); END; / INSERT INTO EMP(EMPNO) VALUES(88); =============================================================== Q2) EMP테이블에 사원정보가 삭제되면 사원번호 XX님이 퇴사하셨습니다. 라고 출력되는 트리거 만들고 실행되도록 해보기
CREATE OR REPLACE TRIGGER MSG_EMP AFTER DELETE ON EMP FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('사원번호'|| :OLD.EMPNO || ' 님이 퇴사하셨습니다'); END; / DELETE FROM EMP WHERE EMPNO=88; ============================================================ Q3) EMP테이블에서 사원정보가 수정되면 XXX번 사원의 부서가 XXX부서에서 XXX 부서로 변경되었습니다. ->부서번호 사용 라고 출력되는 트리거 만들고 실행되도록 해보기
CREATE OR REPLACE TRIGGER UPDATE_DEPTNO AFTER UPDATE ON EMP FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(:OLD.EMPNO || '부서의 위치가 ' || :OLD.DEPTNO || ' 에서'|| :NEW.DEPTNO || ' 위치로 변경됨'); END; / UPDATE EMP SET DEPTNO=40 WHERE DEPTNO=30;
============================================================ 예) 상품테이블(재고수량) CREATE TABLE ITEM ( CODE CHAR(6) PRIMARY KEY, --상품코드 NAME VARCHAR(12) NOT NULL, -- 상품명 COMPANY VARCHAR2(12), --제조회사 PRICE NUMBER(8), --가격 CNT NUMBER(6) DEFAULT 0 );
INSERT INTO ITEM VALUES('A01','냉장고','삼성',2000000,0); INSERT INTO ITEM VALUES('A02','컴퓨터','삼성',1000000,0); COMMIT;
-- 입고테이블에 데이터 2개 추가해 보세요. 2) DELETE FROM WAREHOUSE; COMMIT;
1) INSERT INTO WAREHOUSE VALUES(1,'A01','2020/03/16',5,179000); INSERT INTO WAREHOUSE VALUES(2,'A02','2020/03/16',10,780000); COMMIT; ============================================================ 3) Q1) 입고테이블에 상품이 입고되면 상품테이블에 재고수량이 증가되도록 TRIGGER를 만들어 보세요. (UPDATE 사용)
INSERT INTO WAREHOUSE VALUES(2, 'A02','2020/03/16',10,780000); --> ITEM테이블의 'A01' 제품의 재고수량이 10개 증가되도록
CREATE OR REPLACE TRIGGER U_ITEM -- UPDATE_ITEM은 오류발생 (UPDATE라는 명령어가 존재하기 때문?) AFTER INSERT ON WAREHOUSE FOR EACH ROW BEGIN UPDATE ITEM SET CNT= (CNT+:NEW.PUTCNT) WHERE CODE= :NEW.CODE; DBMS_OUTPUT.PUT_LINE( :NEW.PUTCNT || '만큼 증가되었습니다.'); END; /
(강사답안) CREATE OR REPLACE TRIGGER INSERT_WAREHOUSE AFTER INSERT ON WAREHOUSE FOR EACH ROW BEGIN UPDATE ITEM SET CNT=CNT + :NEW.PUTCNT WHERE CODE=:NEW.CODE; END; / ============================================================ Q2) 입고정보 삭제하기 DELETE FROM WAREHOUSE WHERE NUM=1; -- 상품테이블의 재고수량이
-- 삭제된 수량만큼 감소되도록(ITEM 테이블에서) 트리거 만들어 보세요.
CREATE OR REPLACE TRIGGER DELETE_WAREHOUSE AFTER DELETE ON WAREHOUSE FOR EACH ROW BEGIN UPDATE ITEM SET CNT=CNT - :OLD.PUTCNT WHERE CODE=:OLD.CODE; END; / ============================================================= Q3) 입고수량 수정하기 UPDATE WAREHOUSE SET PUTCNT=3 WHERE NUM=1; -- A01제품의 재고수량이 수정된 수량만큼 변경되도록 트리거 만들기
CREATE OR REPLACE TRIGGER UPDATE_WAREHOUSE AFTER UPDATE ON WAREHOUSE FOR EACH ROW BEGIN UPDATE ITEM SET CNT = CNT - :OLD.PUTCNT + :NEW.PUTCNT WHERE CODE = :OLD.CODE; -- (NEW.CODE도 가능) END; /
UPDATE WAREHOUSE SET PUTCNT=3 WHERE NUM=1; SELECT * FROM ITEM;
COMMIT;
[7] 예외처리 형식) EXCEPTION WHEN 예외타입1 THEN 예외처리1 WHEN 예외타입2 THEN 예외처리2 .. WHEN OTHERS THEN 예외처리3;--위에서 설정된 예외 이외의 예외를 처리
예1) DECLARE NUM1 NUMBER(5); NUM2 NUMBER(5); NUM3 NUMBER(5); BEGIN NUM1 := &NUM1; NUM2 := &NUM2; NUM3 := NUM1/NUM2; DBMS_OUTPUT.PUT_LINE(NUM1 || '/' || NUM2 ||'=' || NUM3); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT.LINE('0으로 나눌 수 없음'); WHEN OTHERS THEN DBMS_OUTPUT.PUT.LINE('오류발생'); END; /
-- 급여에 대한 정보를 갖는 테이블 CREATE TABLE PAY ( NUM NUMBER(5) PRIMARY KEY, --PK주기위한 의미없는 번호 EMPNO NUMBER(5) REFERENCES EMP(EMPNO), PASL NUMBER(10), -- 급여정보 D DATE -- 지급일 ); CREATE SEQUENCE PAY_SEQ;
INSERT INTO PAY VALUES(PAY_SEQ.NEXTVAL,7369,3000,2020/2/28'); --> 급여가 지급될 사원번호와 급여, 지급일을 파라미터로 전달받아 PAY테이블에 정보가 추가되고 EMP테이블에 급여정보가 수정디는 프로시져를 만들고 호출해 보세요.
CREATE OR REPLACE PROCEDURE EMP_PAY ( VEMPNO EMP.EMPNO%TYPE, VSAL EMP.SAL%TYPE, VDATE PAY.D%TYPE ) IS BEGIN INSERT INTO PAY VALUES(PAY_SEQ.NEXTVAL,VEMPNO,VSAL,VDATE); UPDATE EMP SET SAL=VSAL WHERE EMPNO=VEMPNO; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('오류로 인해 작업을 모두 취소했습니다.'); END; /
<10> TCL (Transaction Control Language) (1) 트랜젝션 : 분리되어서는 안되는 논리적 작업 단위 (ex: 여행사상품예약프로그램<숙소테이블/렌트카/항공권/결제 등등>/계좌이체프로그램<입/출금 테이블>) (cf:스프링; 트랜젝션 처리가 쉬움)
(2) TCL : DML(UPDATE, DELETE, INSERT)문이 실행되어 DBMS에 저장되거나 되돌리기 위해 실행해야 하는 SQL (3) TCL 의 종류 1) COMMIT : SQL구문의 결과를 영구적으로 DB에 반영 2) ROLLBACK : SQL구문의 결과를 취소 3) SAVEPOINT : 트랜젝션의 한 지점에 표시하는 임시 저장점 (4) TCL 의 특성 읽기일관성/잠금현상 : 어떤 사용자가 변경중인 행을 다른 사용자가 변경할 수 없게 하는 기술로써 변경중인 사용자에 의해 COMMIT 또는 ROLLBACK이 실행된 후 변경되는 특성 (5) 트랜젝션의 시작과 종료 1) DBMS에 처음 접속했을 때 2) COMMIT 또는 ROLLBACK했을 때 3) DDL구문(CREATE,DROP,..)이 실행되었을 때 ㄴCOMMIT 되지 않은 이전 DML명령어들이 자동으로 COMMIT된다.
예1) # DB접속 - 트랜잭션 시작 SQL> INSERT INTO DEPT VALUES(51,'부서1','서울'); SQL> INSERT INTO DEPT VALUES(52,'부서2','종로'); SQL> INSERT INTO DEPT VALUES(53,'부서3','종로');
*INSERT 후 COMMIT 하지 않고 EXIT로 종료할 시에 해당부분 자동으로 COMMIT되고 프로 그램 종료.
DEPTNO DNAME LOC ---------- ---------------------------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 51 부서1 서울 71 부서4 종로 52 부서2 종로 53 부서3 종로 72 부서4 송파
예2) SQL> INSERT INTO DEPT VALUES(81,'부서81','송파'); SQL> INSERT INTO DEPT VALUES(82,'부서82','송파');
SQL> SAVEPOINT A; -- SAVEPOINT 설정 Savepoint created.
SQL> INSERT INTO DEPT VALUES(83,'부서83','송파'); SQL> INSERT INTO DEPT VALUES(84,'부서84','송파'); SQL> ROLLBACK TO A; --A 지점이후부터 ROLLBACK됨(83,84번 작업취소) Rollback complete.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC ---------- ---------------------------- ----------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 51 부서1 서울 71 부서4 종로 53 부서3 종로 72 부서4 송파 81 부서81 송파 82 부서82 송파
(11) 시퀀스(ex: 게시판에서 글 번호 순차적으로 정렬되어야 할 때, 수식이 아닌 시퀀스 사용) - 연속되는 숫자값을 자동으로 증감시키는 일련번호를 발생시키는 객체 - 형식 CREATE SEQUENCE 시퀀스명 [INCREMENT BY N] [START WITH N] -- 이미 시퀀스 생성된 이후 특정 번호부터 다시 발생시켜야할 때 사용 [MAXVALUE N | NONMAXVALUE N] [MINVALUE N | NONMINVALUE N] [CYCLE | NOCYCLE ] --최대 또는 최소 값에 도달했을 때 반복할지 정함 [CACHE | NOCACHE ] --미리 한 번에 만들어서 하나씩 꺼냄(하나 씩 만들어서 하나씩 꺼낼 때 보다 성능 빠름)_시퀀스 성능이 다소느리므로 CACHE를 많이 사용. (NOCACHE 성능 많이 떨어짐)-성능향상관련개념-->튜닝. **화면단으로 순차적으로 보이게 로직으로 만들고, 데이터베이스로는 PK로 중복값이 없고 최신글이 앞으로 나오게 잡아준다. (즉 1->2->3->4->7->9 이러한 문제는 중요치 않음)
- 삭제 DROP SEQUENCE 시퀀스명; - 함수 NEXTVAL : 다음값 얻어오기 CURRVAL : 현재발생된 값 얻어오기
예1) CREATE SEQUENCE MYSEQ; SELECT MYSEQ.NEXTVAL FROM DUAL; --'DUAL'은 테이블이 없을 때 임시사용됨
SQL> SELECT MYSEQ.NEXTVAL FROM DUAL;
NEXTVAL ---------- 1 SQL> SELECT MYSEQ.NEXTVAL FROM DUAL;
NEXTVAL ---------- 2 SQL> SELECT MYSEQ.NEXTVAL FROM DUAL;
NEXTVAL ---------- 3
SQL> DROP SEQUENCE MYSEQ;
Sequence dropped.
SQL> SELECT MYSEQ.NEXTVAL FROM DUAL; SELECT MYSEQ.NEXTVAL FROM DUAL * ERROR at line 1: ORA-02289: sequence does not exist
예2) CREATE SEQUENCE MYSEQ1 INCREMENT BY 10 --10씩 증가 START WITH 10; --시작값을 10으로 지정 Sequence created. SELECT MYSEQ1.NEXTVAL FROM DUAL;
SQL> SELECT MYSEQ1.NEXTVAL FROM DUAL;
NEXTVAL ---------- 10
SQL> SELECT MYSEQ1.NEXTVAL FROM DUAL;
NEXTVAL ---------- 20
SQL> SELECT MYSEQ1.CURRVAL FROM DUAL; --현재 발생된 SEQUENCE 값 꺼내오기
CURRVAL ---------- 20
예3) CREATE TABLE BOARD ( NUM NUMBER(5) PRIMARY KEY, --글번호 TITLE VARCHAR2(20), WRITER VARCHAR2(20), CONTENT VARCHAR2(50), REGDATE DATE );
CREATE SEQUENCE BOARD_SEQ; --시퀀스 생성
INSERT INTO BOARD VALUES(BOARD_SEQ.NEXTVAL, 'TEST1', '김씨','테스트1',SYSDATE); INSERT INTO BOARD VALUES(BOARD_SEQ.NEXTVAL, 'TEST2', '이씨','테스트2',SYSDATE);
예) READ ONLY 옵션 설정하기 - INSERT, UPDATE, DELETE는 못하고 읽기(SELECT)만 가능 CREATE OR REPLACE VIEW MYEMP1 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WITH READ ONLY;
SQL> INSERT INTO MYEMP1 VALUES(8001, '김씨', 2000, 20); ㄴERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view
예4) WITH CHECK OPTION - 뷰의 조건에 맞는 데이터들만 DML작업을 할 수 있음 CREATE OR REPLACE VIEW MYEMP1 AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=10 WITH CHECK OPTION;
SQL> INSERT INTO MYEMP1 VALUES(8002,'김씨',2000,20); INSERT INTO MYEMP1 VALUES(8002,'김씨',2000,20) * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation ==> 20번 부서는 VIEW의 조건에 맞지 않으므로 오류!
3) 뷰의 종류 <1> 단일뷰 : 하나의 테이블로 생성되는 뷰(기본적으로 DML작업이 됨) <2> 복합뷰 : 두 개이상의 테이블로 생성되는 뷰(조인된 뷰).DML작업을 못함
예) # 복합뷰만들기 CREATE VIEW MYEMP3 AS SELECT EMPNO,ENAME,JOB,E.DEPTNO,DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;
객체들의 정보를 갖고 있는 테이블 같은 저장공간 --> DATA DICTIONARY SET LINESIZE 100 DESC USER_VIEWS; Name Null? Type ----------------------------------------------------- -------- ---------------------------- VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG TYPE_TEXT_LENGTH NUMBER TYPE_TEXT VARCHAR2(4000) OID_TEXT_LENGTH NUMBER OID_TEXT VARCHAR2(4000) VIEW_TYPE_OWNER VARCHAR2(30) VIEW_TYPE VARCHAR2(30) SUPERVIEW_NAME VARCHAR2(30) EDITIONING_VIEW VARCHAR2(1) READ_ONLY VARCHAR2(1)
4) 뷰 삭제하기 형식) DROP VIEW 뷰이름;
예) SQL> DROP VIEW MYEMP1; -- VIEW 삭제하기 View dropped. SQL> SELECT VIEW_NAME FROM USER_VIEWS; VIEW_NAME ----------- MYEMP2 MYEMP3
Q1) EMP테이블과 DEPT테이블을 이용해서 이름,직업,부서,부서명을 조회하는 뷰를 만들고 사용해 보세요. 사용후 뷰 삭제하기
CREATE VIEW MYEMP0 AS SELECT E.ENAME "이름", E.JOB "직업", D.DEPTNO "부서",D.DNAME "부서명" FROM EMP E, DEPT D WHERE E.DEPTNO= D.DEPTNO ;
SQL> DROP VIEW MYEMP0;
View dropped.
SQL> SELECT * FROM MYEMP0; SELECT * FROM MYEMP0 * ERROR at line 1: ORA-00942: table or view does not exist
**VIEW는 DML작업이 아닌 조회를 위한 목적으로 사용됨**
================================================================= [12] 인덱스 - 인덱스란 포인터(주소값을 갖고있는 것)를 사용하여 행의 검색을 촉진시키는 객체 - 데이터를 빠르게 찾기 위한 이진의 균형탐색 TREE를 이용하여 디스크 입출력횟수를 줄인다. - 인덱스는 논리적으로도 물리적으로도 테이블과 독립적이다. - 언제든지 생성하거나 삭제할 수 있으며 이는 테이블과 다른 인덱스에 영향을 주지 않는다는 의미이다. - 인덱스테이블에는 인덱스키와 ROWID(실제적으로 물리적인 위치)에 대한 정보만을 갖고 있다. - 테이블정의의 PRIMARY KEY나 UNIQUE제약조건을 정의할때 UNIQUE인덱스가 자동으로 생성된다. - 형식) CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명 ASC|DESC)
예) CREATE INDEX IDX_EMP ON EMP(EMPNO DESC);
QL> DESC USER_INDEXES; --사용자가 만든 INDEX에 대한 정보를 갖는 DATA DICTIONARY
Name Null? Type ----------------------------------------------------- -------- --------------------- INDEX_NAME NOT NULL VARCHAR2(30) INDEX_TYPE VARCHAR2(27) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) UNIQUENESS VARCHAR2(9) COMPRESSION VARCHAR2(8) PREFIX_LENGTH NUMBER TABLESPACE_NAME VARCHAR2(30) INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER ... ...
SQL> SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES;
SYS_C007014 BOARD ERROR at line 1: ORA-01418: specified index does not exist
- 삭제 DROP INDEX 인덱스명; DROP INDEX IDX_EMP_EMPNO;
예1) UNIQUE인덱스 : 인덱스로 설정되는 컬럼에 중복값이 들어가면 안됨 CREATE UNIQUE INDEX IDEX_EMP_EMPNO ON EMP(EMPNO DESC);
예2) 사원이름 컬럼을 인덱스로 생성해 보세요.
CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME DESC); Index created.
SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES WHERE INDEX_NAME='IDX_EMP_ENAME'; 또는 (위, 아래 결과 같음) SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDX_%';
- 인덱스 생성 기준 1) 사용자의 SQL에서 WHERE절에 자주 사용되는 컬럼이 대상이 된다. 2) 인덱스 갯수는 사용형태에 따라 적절하게 생성한다. 3) 너무 많은 인덱스는 오히려 성능이 감소된다. 4) 빈번하게 변경되지 않는 테이블에 적용해야 한다. 5) 작은 테이블보다는 큰 테이블에 인덱스를 생성한다.
- 인덱스 생성시 주의사항 DML명령어에 주의해야 한다. INSERT -> 테이블에 데이터가 추가되고 인덱스 테이블에 정렬되어 추가된다. DELETE -> 삭제시 인덱스 테이블에는 삭제되지 않고 삭제되었음을 표시한다. 즉 데이터가 남아있게 되므로 실제데이터가 10만건이라면 인덱스에 데이터는 20만건이 될수도 있다(성능저하) *--오라클은 먼저 인덱스 테이블을 체크하므로 UPDATE -> 수정시 인덱스테이블에 DELETE된후 INSERT 작업이 수행된다.
[13] 사용자계정관리
1) 계정생성 CREATE USER 아이디 IDENTIFIED BY 비밀번호; 예) SQL> CONN system/java1234 -- 계정생성위해 관리자계정으로 접속 SQL> CREATE USER hello1234 IDENTIFIED BY hello0000; --사용자계정만들기 SQL> GRANT connect,resource TO hello1234;-- 접속권한부여하기 (resource; roll 권한부여) SQL> conn hello1234/hello0000;--사용자계정으로 접속 SQL> CREATE TABLE MYEMP(EMPNO NUMBER(4) PRIMARY KEY,ENAME VARCHAR2(100));
2) 계정삭제 (*테이블을 객체라고 부름) DROP USER 아이디; # CASCADE : 삭제할 계정에 데이터가 존재하면 삭제가 안됨. 이때 CASCADE옵션을 주면 삭제 가능
2) IF ~ ELSE 문 형식1) IF 조건식 THEN 실행문; ELSE 실행문; END IF;
예1) 임의의 정수 입력받아 짝수/홀수 판별하기 DECLARE NUM NUMBER(5):=&NUM; BEGIN IF NUM MOD 2=0 THEN --MOD 나머지 값 구하는 연산자 DBMS_OUTPUT.PUT_LINE(NUM ||'은 짝수'); ELSE DBMS_OUTPUT.PUT_LINE(NUM ||'은 홀수'); END IF; END; /
형식2) IF 조건식 THEN 실행문; ELSIF 조건식 THEN 실행문; ELSIF 조건식 THEN 실행문; .. END IF;
Q1) 두 정수를 입력받아 두 수중 큰 수 구하기
DECLARE NUM1 NUMBER(5):=&NUM; NUM2 NUMBER(5):=&NUM; BEGIN IF NUM1>NUM2 THEN DBMS_OUTPUT.PUT_LINE(NUM1 ||'이 큰수'); ELSE DBMS_OUTPUT.PUT_LINE(NUM2 ||'이 큰수'); END IF; END; / Enter value for num: 2 old 2: NUM1 NUMBER(5):=&NUM; new 2: NUM1 NUMBER(5):=2; Enter value for num: 9 old 3: NUM2 NUMBER(5):=&NUM; new 3: NUM2 NUMBER(5):=9; 9이 큰수
PL/SQL procedure successfully completed.
Q2) 두 점수를 입력받아 평균이 80이상이면 '합격' 아니면 '불합격' 출력하기
DECLARE NUM1 NUMBER(5):=&NUM; NUM2 NUMBER(5):=&NUM; BEGIN IF (NUM1 + NUM2)/2 >=80 THEN DBMS_OUTPUT.PUT_LINE('합격'); ELSE DBMS_OUTPUT.PUT_LINE('불합격'); END IF; END; /
3) FOR문
-형식 FOR 변수 IN 초기값..마지막값 LOOP 반복수행할 문장;
END LOOP;
예) 1부터 100까지 합 출력 하시오.
DECLARE TOT NUMBER(5):=0; BEGIN FOR I IN 1..100 LOOP TOT := TOT + I; DBMS_OUTPUT.PUT(I || ' '); --1~100까지 옆으로 출력(자바의 PRINT 동일) , PUT_LINE 은 자바의 PRINTLN END LOOP; DBMS_OUTPUT.PUT_LINE('1부터 100까지합:' || TOT); END; /
Q1) 1부터 100까지 수 중 3의 배수출력하고 3의 배수합 구해서 출력하기 IF NUM MOD 2=0 THEN --MOD 나머지 값 구하는 연산자
DECLARE MULTIPLETHREE NUMBER(5):=0; BEGIN FOR I IN 1..100 LOOP IF I MOD 3=0 THEN MULTIPLETHREE := MULTIPLETHREE + I; DBMS_OUTPUT.PUT(I || ' ' ); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('1부터 100까지 수 중 3의 배수 합:' || MULTIPLETHREE); END; / 3 6 9 12 15 18 21 24 27 30 33 36 39 42 45 48 51 54 57 60 63 66 69 72 75 78 81 84 87 90 93 96 99 1부터 100까지 수 중 3의 배수 합:1683
PL/SQL procedure successfully completed.
Q2) 단 입력받아 구구단 출력하기 DECLARE NUM NUMBER(5) := &NUM; BEGIN FOR I IN 1..9 LOOP DBMS_OUTPUT.PUT_LINE(NUM || '*' || I || '=' || NUM*I); END LOOP; END; /
Q3) 구구단 출력하기(2단부터 9단까지)
2단 2*1=2 2*2=4 ........ 2*9=18 3단 3*1=3 ..
.. 9단 9*1=9 9*2=18 ....... 9*9=81
FOR I IN 2..9 LOOP FOR J IN 1..9 LOOP BEGIN FOR I IN 2..9 LOOP DBMS_OUTPUT.PUT(I || '단'); FOR J IN 1..9 LOOP DBMS_OUTPUT.PUT(I || '*' || J || '=' || I*J || ' '); END LOOP; DBMS_OUTPUT.PUT_LINE(' '); --flush와 같음 (데이터 뽑아오는 기능) END LOOP; END; /
- EXIT : FOR문을 강제로 빠져나가기
예2) 정수입력받아 소수 판별하기
DECLARE NUM NUMBER(5) := &NUM; BEGIN FOR I IN 2..NUM LOOP IF I=NUM THEN DBMS_OUTPUT.PUT_LINE(NUM || '는/은 소수 입니다'); ELSIF NUM MOD I = 0 THEN DBMS_OUTPUT.PUT_LINE(NUM || '는/은 소수가 아닙니다'); EXIT; END IF; DBMS_OUTPUT.PUT_LINE(' '); END LOOP; END; / Enter value for num: 7 old 2: NUM NUMBER(5) := &NUM; new 2: NUM NUMBER(5) := 7; 7는/은 소수 입니다
PL/SQL procedure successfully completed.
방법1) FOR문 안에 IF I = NUM 실수입니다. ELSE IF NUM MOD I = 0 소수입니다. EXIT
방법2) NUM-1 IF NUM MOD I = 0 소수입니다. EXIT ELSIF I = NUM-1 실수입니다.
03.18 시험출제확정내용=============================================== <8> 제약조건<***> - 테이블의 해당 컬럼에 잘못된 값이 입력/변경/삭제되는 것을 방지하기 위해 설정되는 조건(무결성 제약조건) - 결함이 없는 데이터를 관리하기 위함
1) 종류 (1) PRIMARY KEY (기본키) - 하나의 테이블에 하나만 존재하며 각 행을 식별하기 위한 용도로 사용 - NULL값을 허용하지 않고 중복값을 허용안함(NOT NULL + UNIQUE KEY) (2) FOREIGN KEY (외래키,참조키) - 부모테이블의 특정 컬럼(기본키)를 참조하는 컬럼 - 부모테이블에 참조하는 값이 없으면 오류가 발생한다.
자식테이블이 있는 부모테이블의 경우 바로 부모테이블 삭제 안됨 --자식테이블 먼저 삭제 후 부모테이블 삭제
DROP TABLE GRADE; --자식 DROP TABLE STUDENT; --부모
4) 테이블 구조 변경하기
<1> 컬럼추가 ALTER TABLE 테이블명 ADD(컬럼명 타입); 예) ALTER TABLE EMP ADD(EMAIL VARCHAR2(15));
<2> 컬럼변경 ALTER TABLE 테이블명 MODIFY(컬럼명 타입); 예) ALTER TABLE EMP MODIFY(EMAIL VARCHAR2(20));
<3> 컬럼명 변경 ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 바꿀컬럼명; 예) ALTER TABLE EMP RENAME COLUMN EMAIL TO MAIL;
<4> 컬럼삭제 ALTER TABLE 테이블명 DROP COLUMN 컬럼명; 예) ALTER TABLE EMP DROP COLUMN MAIL;
<8> 제약조건<***> - 테이블의 해당 컬럼에 잘못된 값이 입력/변경/삭제되는 것을 방지하기 위해 설정되는 조건(무결성 제약조건) - 결함이 없는 데이터를 관리하기 위함
1) 종류 (1) PRIMARY KEY (기본키) - **하나의 테이블에 하나만 존재**하며 각 행을 식별하기 위한 용도로 사용 - NULL값을 허용하지 않고 중복값을 허용안함(NOT NULL + UNIQUE KEY) (2) FOREIGN KEY (외래키,참조키) - 부모테이블의 특정 컬럼(기본키)를 참조하는 컬럼 - ***부모테이블에 참조하는 값이 없으면 오류가 발생한다***. (3) UNIQUE KEY(유일키) --중복 X - 컬럼의 모든 값이 유일해야 하는 경우에 사용. 중복데이터를 허용하지 않음 - NULL은 허용됨(해당 유일키가 없는경우 안 넣어도 됨)-ex)이메일,전화번호가 없는 회원 (4) CHECK 제약조건(많이사용안됨) - 조건에 맞는 데이터만 입력되도록 조건을 부여하는 제약조건 (5) NOT NULL 조건(많이사용됨) - NULL값을 허용하지 않는 컬럼에 설정(ex: 회원가입시 이름 필수)
예1) 제약조건에 이름을 부여하지 않은 경우 CREATE TABLE DEPT1 ( DEPTNO NUMBER(2) PRIMARY KEY, DNAME VARCHAR2(15) DEFAULT '인사부', LOC CHAR(9) CHECK(LOC IN('서울','부산')) );
INSERT INTO DEPT1(DEPTNO,DNAME,LOC) VALUES(1, '총무부', '서울'); INSERT INTO DEPT1(DEPTNO,LOC) VALUES(2, '부산'); INSERT INTO DEPT1 VALUES(3, '영업부', '서울');
Q1) 부서명이 'ACCOUNTING'에 근무하는 사원들의 이름,급여, 입사일,부서명,부서위치를 조회하시오.
SELECT ENAME, SAL, HIREDATE, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE D.DNAME='ACCOUNTING' AND E.DEPTNO=D.DEPTNO;
ENAME SAL HIREDATE DNAME LOC -------------------- ---------- -------- ---------------------------- -------------------------- CLARK 2450 81/06/09 ACCOUNTING NEW YORK KING 5000 81/11/17 ACCOUNTING NEW YORK MILLER 1300 82/01/23 ACCOUNTING NEW YORK
Q2) 전체 사원의 평균급여보다 많은 사원의 사원번호,이름, 급여,부서명 조회하기
SELECT EMPNO, ENAME, SAL, D.DNAME FROM EMP E, DEPT D WHERE SAL>(SELECT AVG(SAL) FROM EMP) AND E.DEPTNO=D.DEPTNO;
EMPNO ENAME SAL DNAME ---------- -------------------- ---------- ----------- 7566 JONES 2975 RESEARCH 7698 BLAKE 2850 SALES 7782 CLARK 2450 ACCOUNTING 7839 KING 5000 ACCOUNTING 7902 FORD 3000 RESEARCH
Q1) 모든 학생번호,이름, 과목명, 점수를 조회해 보세요. SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME --중복되는 컬럼은 해당 컬럼이 어떤 테이블에 속하는 지 명시 FROM EMP E, DEPT D WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT S.SNUM, S.NAME, G.SUBJECT, G.SCORE FROM STUDENT S, GRADE G WHERE S.SNUM=G.SNUM;
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE FROM EMP E, SALGRADE S WHERE E.SAL>=S.LOSAL AND E.SAL<=S.HISAL;
EMPNO ENAME SAL GRADE ---------- -------------------- ---------- ---------- 7369 SMITH 800 1 7900 JAMES 950 1 7521 WARD 1250 2 7654 MARTIN 1250 2 7934 MILLER 1300 2 7499 ALLEN 1600 3 7844 TURNER 1500 3 7566 JONES 2975 4 7698 BLAKE 2850 4 7782 CLARK 2450 4 7902 FORD 3000 4
EMPNO ENAME SAL GRADE ---------- -------------------- ---------- ---------- 7839 KING 5000 5
3) CROSS조인 (--자주 사용되지 않음) - 2개이상의 테이블에서 조인될때 조인조건에 의해 결합이 발생되지 않는 경우. (실수로 조인결합조건을 안 쓴 경우가 대부분 또는 테스트용데이터를 한번에 --조회되는 검색 속도 측정시 사용 많이 만들고자 할때 사용되는 경우가 있음)
SELECT E.EMPNO, ENAME, D.DNAME FROM EMP E,DEPT D; --조인조건 생략된 상태
EMPNO ENAME DNAME ---------- -------------------- ---------------------------- 7369 SMITH ACCOUNTING 7499 ALLEN ACCOUNTING 7521 WARD ACCOUNTING 7566 JONES ACCOUNTING 7654 MARTIN ACCOUNTING 7698 BLAKE ACCOUNTING 7782 CLARK ACCOUNTING 7839 KING ACCOUNTING 7844 TURNER ACCOUNTING 7900 JAMES ACCOUNTING 7902 FORD ACCOUNTING
EMPNO ENAME DNAME ---------- -------------------- ---------------------------- 7934 MILLER ACCOUNTING 7369 SMITH RESEARCH 7499 ALLEN RESEARCH 7521 WARD RESEARCH 7566 JONES RESEARCH 7654 MARTIN RESEARCH 7698 BLAKE RESEARCH . . . 48 rows selected.
4) SELF 조인 - 참조해야 하는 컬럼이 자신의 테이블의 다른 컬럼인 경우 사용되는 조인
사원 매니저 -------------------- -------------- FORD JONES JAMES BLAKE TURNER BLAKE MARTIN BLAKE WARD BLAKE ALLEN BLAKE MILLER CLARK CLARK KING BLAKE KING JONES KING SMITH FORD
11 rows selected.
5) OUTER 조인 (없는데이터를 추출할 때 성능저하 발생하므로 자주 사용되지 않음) - 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른쪽 테이블에는 데이터가 존재하지 않는 경우 모든 데이터를 추출하는 조인방법
예1) 모든 부서번호와 부서명,근무사원이름을 조회하시오.
SELECT D.DEPTNO, D.DNAME, E.ENAME FROM DEPT D, EMP E WHERE D.DEPTNO=E.DEPTNO;
DEPTNO DNAME ENAME ---------- ---------------------------- -------------------- 20 RESEARCH SMITH 30 SALES ALLEN 30 SALES WARD 20 RESEARCH JONES 30 SALES MARTIN 30 SALES BLAKE 10 ACCOUNTING CLARK 10 ACCOUNTING KING 30 SALES TURNER 30 SALES JAMES 20 RESEARCH FORD
DEPTNO DNAME ENAME ---------- ---------------------------- -------------------- 10 ACCOUNTING MILLER
12 rows selected.
SQL> SELECT D.DEPTNO, D.DNAME, E.ENAME 2 FROM DEPT D, EMP E 3 WHERE D.DEPTNO=E.DEPTNO(+);
DEPTNO DNAME ENAME ---------- ---------------------------- -------------------- 20 RESEARCH SMITH 30 SALES ALLEN 30 SALES WARD 20 RESEARCH JONES 30 SALES MARTIN 30 SALES BLAKE 10 ACCOUNTING CLARK 10 ACCOUNTING KING 30 SALES TURNER 30 SALES JAMES 20 RESEARCH FORD
SELECT D.DEPTNO,D.DNAME,E.ENAME,E.SAL FROM DEPT D,EMP E WHERE D.DEPTNO=E.DEPTNO(+) ORDER BY D.DEPTNO;
DEPTNO DNAME ENAME SAL ---------- ---------------------------- -------------------- ---------- 10 ACCOUNTING CLARK 2450 10 ACCOUNTING MILLER 1300 10 ACCOUNTING KING 5000 20 RESEARCH JONES 2975 20 RESEARCH FORD 3000 20 RESEARCH SMITH 800 30 SALES ALLEN 1600 30 SALES TURNER 1500 30 SALES JAMES 950 30 SALES WARD 1250 30 SALES BLAKE 2850
DEPTNO DNAME ENAME SAL ---------- ---------------------------- -------------------- ---------- 30 SALES MARTIN 1250 40 OPERATIONS
Q2) 모든 학생번호, 이름, 과목명, 점수를 출력해 보세요. (OUTER조인)
DELETE FROM GRADE WHERE SNUM=3; COMMIT;
SELECT S.SNUM, S.NAME, G.SUBJECT, G.SCORE FROM STUDENT S, GRADE G WHERE S.SNUM=G.SNUM(+);
사원번호 이름 매니저이름 매니저직업 ---------- -------------------- -------------------- ------------------ 7902 FORD JONES MANAGER 7900 JAMES BLAKE MANAGER 7844 TURNER BLAKE MANAGER 7654 MARTIN BLAKE MANAGER 7521 WARD BLAKE MANAGER 7499 ALLEN BLAKE MANAGER 7934 MILLER CLARK MANAGER 7782 CLARK KING PRESIDENT 7698 BLAKE KING PRESIDENT 7566 JONES KING PRESIDENT 7369 SMITH FORD ANALYST
1) [INNER] JOIN 형식) SELECT 컬럼명1,.. FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건절 [WHERE절]
예) 사원번호,이름,부서명,부서위치를 조회하시오.
SELECT E.EMPNO, E.ENAME, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
EMPNO ENAME DNAME LOC ---------- -------------------- ---------------------------- ----------------------- 7369 SMITH RESEARCH DALLAS 7499 ALLEN SALES CHICAGO 7521 WARD SALES CHICAGO 7566 JONES RESEARCH DALLAS 7654 MARTIN SALES CHICAGO 7698 BLAKE SALES CHICAGO 7782 CLARK ACCOUNTING NEW YORK 7839 KING ACCOUNTING NEW YORK 7844 TURNER SALES CHICAGO 7900 JAMES SALES CHICAGO 7902 FORD RESEARCH DALLAS
EMPNO ENAME DNAME LOC ---------- -------------------- ---------------------------- ----------------------- 7934 MILLER ACCOUNTING NEW YORK
2) OUTER JOIN 형식) SELECT 컬럼명,.. FROM 테이블1 LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN 테이블2 ON 조인조건절 [WHERE]
예1) 모든 부서번호,부서명,근무사원이름,급여조회
SELECT D.DEPTNO, D.DNAME, E.ENAME, E.SAL FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO=E.DEPTNO;
DEPTNO DNAME ENAME SAL ---------- ---------------------------- -------------------- ---------- 20 RESEARCH SMITH 800 30 SALES ALLEN 1600 30 SALES WARD 1250 20 RESEARCH JONES 2975 30 SALES MARTIN 1250 30 SALES BLAKE 2850 10 ACCOUNTING CLARK 2450 10 ACCOUNTING KING 5000 30 SALES TURNER 1500 30 SALES JAMES 950 20 RESEARCH FORD 3000
DEPTNO DNAME ENAME SAL ---------- ---------------------------- -------------------- ---------- 10 ACCOUNTING MILLER 1300 40 OPERATIONS
--아래도 동일함
SELECT D.DEPTNO, D.DNAME, E.ENAME, E.SAL FROM EMP E RIGHT OUTER JOIN DEPT D ON D.DEPTNO=E.DEPTNO;
Q1) 부서번호,부서명,이름,급여를 조회해 보세요.
SELECT E.DEPTNO, D.DNAME, E.ENAME, E.SAL FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
DEPTNO DNAME ENAME SAL ---------- ---------------------------- -------------------- ---------- 20 RESEARCH SMITH 800 30 SALES ALLEN 1600 30 SALES WARD 1250 20 RESEARCH JONES 2975 30 SALES MARTIN 1250 30 SALES BLAKE 2850 10 ACCOUNTING CLARK 2450 10 ACCOUNTING KING 5000 30 SALES TURNER 1500 30 SALES JAMES 950 20 RESEARCH FORD 3000
DEPTNO DNAME ENAME SAL ---------- ---------------------------- -------------------- ---------- 10 ACCOUNTING MILLER 1300
Q2) 사원이름 'ALLEN'인 사원의 사원번호,이름,급여,부서명을 조회하시오.
SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE ENAME='ALLEN';
EMPNO ENAME SAL DNAME ---------- -------------------- ---------- ------------------- 7499 ALLEN 1600 SALES
Q3) 모든 학생의 학생번호, 이름, 과목명, 점수조회 해보세요. (OUTER JOIN)
SELECT S.SNUM, S.NAME, G.SUBJECT, G.SCORE FROM STUDENT S LEFT OUTER JOIN GRADE G ON S.SNUM = G.SNUM;
(8) UNION,UNION ALL - 두 집합의 결과를 더함 - UNION : 중복데이터를 제거(중복값을 제거하기 위해 정렬해서 수행하므로 성능이 느려진다) - UNION ALL : 중복데이터를 포함 - 규칙 : 두 집합의 SELECT절에 오는 컬럼의 갯수와 타입이 동일해야 하며 컬럼명은 달라도 상관없다.
SELECT DEPTNO_(단행연산자),SUM(SAL)_(다행연산자) 급여합 -->단행연산자와 다행연산자가 같이 쓰일려면 GROUP필요 FROM EMP WHERE SAL>=2000 GROUP BY DEPTNO ORDER BY 급여합 DESC; --ORDER BY 절에 ALIAS 사용가능(급여합 자리 SUM(SAL) 넣어도 같은 결과) --ORDER절이 맨 마지막에 실행되므로 가능
그룹핑하기 전에 비교 함(HAVING X)
Q1) 부서별 최대급여,최소 급여를 구하세요.
SELECT DEPTNO,MIN(SAL) "가장낮은급여",MAX(SAL) "가장높은급여" FROM EMP GROUP BY DEPTNO;
