오라클 실습 예제

-- 테이블 부터 생성하자

rem  DROP TABLES

DROP TABLE EMP;

DROP TABLE DEPT;

rem  create TABLES

CREATE TABLE DEPT

        (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

         DNAME VARCHAR2(14),

LOC   VARCHAR2(13) ) ;

CREATE TABLE EMP

        (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

ENAME VARCHAR2(10),

  JOB   VARCHAR2(9),

MGR   NUMBER(4),

HIREDATE DATE,

SAL   NUMBER(7,2),

COMM  NUMBER(7,2),

DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

rem  insert DEPT 4 data

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

rem  insert EMP  14 data

INSERT INTO EMP  VALUES (7369,'SMITH','CLERK',    7902,to_date('17-12-1980','dd-mm-yyyy'), 800,NULL,20);

INSERT INTO EMP  VALUES (7499,'ALLEN','SALESMAN', 7698,to_date('20-2-1981', 'dd-mm-yyyy'),1600,300, 30);

INSERT INTO EMP  VALUES (7521,'WARD','SALESMAN',  7698,to_date('22-2-1981', 'dd-mm-yyyy'),1250,500, 30);

INSERT INTO EMP  VALUES (7566,'JONES','MANAGER',  7839,to_date('2-4-1981',  'dd-mm-yyyy'),2975,NULL,20);

INSERT INTO EMP  VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981', 'dd-mm-yyyy'),1250,1400,30);

INSERT INTO EMP  VALUES (7698,'BLAKE','MANAGER',  7839,to_date('1-5-1981',  'dd-mm-yyyy'),2850,NULL,30);

INSERT INTO EMP  VALUES (7782,'CLARK','MANAGER',  7839,to_date('9-6-1981',  'dd-mm-yyyy'),2450,NULL,10);

INSERT INTO EMP  VALUES (7788,'SCOTT','ANALYST',  7566,to_date('13-07-1987','dd-mm-yyyy'),3000,NULL,20);

INSERT INTO EMP  VALUES (7839,'KING','PRESIDENT', NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

INSERT INTO EMP  VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981',  'dd-mm-yyyy'),1500,0,   30);

INSERT INTO EMP  VALUES (7876,'ADAMS','CLERK',    7788,to_date('13-07-1987','dd-mm-yyyy'),1100,NULL,20);

INSERT INTO EMP  VALUES (7900,'JAMES','CLERK',    7698,to_date('3-12-1981', 'dd-mm-yyyy'), 950,NULL,30);

INSERT INTO EMP  VALUES (7902,'FORD','ANALYST',   7566,to_date('3-12-1981', 'dd-mm-yyyy'),3000,NULL,20);

INSERT INTO EMP  VALUES (7934,'MILLER','CLERK',   7782,to_date('23-1-1982', 'dd-mm-yyyy'),1300,NULL,10);

COMMIT;

--아래부터는 문제

--사원 릴레이션에서 30번 부서에 소속된 사원들을 검색

SELECT *

FROM EMP

WHERE DEPTNO = 30;

--SCOTT이 속한 부서이거나 SALES부서의 부서번호를 검색

SELECT DEPTNO

FROM DEPT

WHERE DEPTNO = (SELECT DEPTNO FROM WHERE ENAME LIKE 'SCOTT')

OR DNAME LIKE 'SALES';

--직급이 MANAGER인 사원의 이름, 봉급, 소속 부서명을 검색

SELECT E.ENAME, D.DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO

AND JOB LIKE 'MAN%';

--A로 시작하는 사원중에서 급여가 250이상 400이하인 사원들의 모든 정보를 급여가 큰 순으로 검색 (또는)

SELECT *

FROM EMP

WHERE ENAME

LIKE 'A%'

OR SAL BETWEEN '2500'

AND '4000'

ORDER BY SAL DESC;

--직급이 SALESMAN인 사원들의 이름, 현재급여, 급여가 15% 인상되었을 떄의 값을 검색

SELECT ENAME, SAL, to_number(SAL*1.5)

FROM EMP

WHERE JOB LIKE 'SALESMAN';

--사원명, 부서명, 관리자명을 검색하기 위한 SQL문

select e.ename 사원명, m.ename 관리자명, d.dname 부서명

from emp e, emp m, dept d

where e.mgr=m.empno and d.deptno=e.deptno;

--부서명별 평균 급여, 최소급여, 최대급여를 검색

SELECT d.dname 부서명, MAX(e.SAL)"최대급여",MIN(e.SAL)"최소급여"

FROM EMP e, dept d

GROUP BY d.dname

ORDER BY d.dname;

--평균급여가 2000원이상인 부서에 대하여 부서번호별로 평균급여를 검색하기 위한 SQL문

SELECT deptno 부서번호, AVG(sal)

FROM emp

WHERE sal >= 2000

GROUP BY deptno

ORDER BY deptno;

--평균급여보다 많은 급여를 받는 사원들의 이름, 직급, 급여를 검색

SELECT ENAME, JOB, SAL

FROM EMP

WHERE SAL > ( SELECT AVG(SAL)

FROM EMP);