EMP

 

 

 

DEPT

 

 

 

cmd창에서 보기 편하기 위한 세팅

COL EMPNO FORMAT 9999
COL ENAME FORMAT A10
COL DNAME FORMAT A10
COL JOB FORMAT A10
COL MGR FORMAT 9999
COL SAL FORMAT 99999
COL DEPTNO FORMAT 999

 

같은 이름의 테이블 삭제

DROP TABLE DEPT CASCADE CONSTRAINT;
DROP TABLE EMP CASCADE CONSTRAINT;

 

DEPT 테이블 생성

CREATE TABLE DEPT(
     DEPTNO NUMBER PRIMARY KEY,
     DNAME VARCHAR2(20),
     LOC VARCHAR2(20)
); 

 

EMP 테이블 생성

CREATE TABLE EMP(
     EMPNO NUMBER PRIMARY KEY,
     ENAME VARCHAR2(15),
     JOB VARCHAR2(20),
     MGR NUMBER,
     HIREDATE DATE,
     SAL NUMBER,
     COMM NUMBER,
     DEPTNO NUMBER,
     CONSTRAINT E_D_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);

 

DEPT 데이터 삽입

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');

COMMIT;

 

EMP 데이터 삽입

INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (1005, 'JAIN', 10);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,TO_DATE('1980-12-17','yyyy-mm-dd'),800,null,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('1981-02-20','yyyy-mm-dd'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('1981-02-22','yyyy-mm-dd'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,TO_DATE('1981-04-02','yyyy-mm-dd'),2975,null,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('1981-09-28','yyyy-mm-dd'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1981-05-01','yyyy-mm-dd'),2850,null,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('1981-06-09','yyyy-mm-dd'),2450,null,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('1987-04-19','yyyy-mm-dd'),3000,null,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',null,TO_DATE('1981-11-17','yyyy-mm-dd'),5000,null,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('1981-09-08','yyyy-mm-dd'),1500,null,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('1987-05-23','yyyy-mm-dd'),1100,null,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,TO_DATE('1981-12-03','yyyy-mm-dd'),950,null,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,TO_DATE('1981-12-03','yyyy-mm-dd'),3000,null,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7566,TO_DATE('1982-01-23','yyyy-mm-dd'),1300,null,10);

COMMIT; 

 

 

1. 부서번호가 10번인 사원들의 사원번호, 이름, 월급을 출력하시오.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO=10;

 

 

2. 사원번호가 7369인 사원의 이름, 입사일, 부서번호를 출력하시오.
SELECT ENAME, HIREDATE, DEPTNO
FROM EMP
WHERE EMPNO=7369;

 

 

3. 이름이 ALLEN인 사원의 모든 사원 정보를 출력하시오.

SELECT *
FROM EMP
WHERE ENAME='ALLEN';

 

 

4. JOB이 MANAGER가 아닌 사원의 모든 정보를 출력하시오.

SELECT *
FROM EMP
WHERE JOB <> 'MANAGER' OR JOB IS NULL;

 

SELECT *
FROM EMP
WHERE JOB != 'MANAGER' OR JOB IS NULL;

 

SELECT *
FROM EMP
WHERE NOT JOB = 'MANAGER' OR JOB IS NULL; 

 

 

5. 급여가 800 이상인 사원의 이름, 급여, 부서번호를 출력하시오.

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL>=800;

 

 

6. 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름, 월급, 부서번호를 출력하시오.

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE HIREDATE > TO_DATE('19810402','yyyymmdd')
and HIREDATE < TO_DATE('19821209','yyyymmdd');

 

--> 8개 행 검색. 1981년 4월 2일에 해당하는 사원 1명 있는데, 문제의 의도가 기준을 포함한다면 =을 붙여준다.

BETWEEN을 사용하면 포함해서 검색!

 

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE HIREDATE BETWEEN TO_DATE('19810402','yyyymmdd') AND TO_DATE('19821209','yyyymmdd');

-->9개 행 검색

 

 

7. 이름이 S로 시작하는 사원의 사원번호, 이름, 입사일, 부서번호를 출력하시오.

SELECT EMPNO,ENAME,HIREDATE,DEPTNO
FROM EMP
WHERE ENAME LIKE 'S%';

 

 

8. 이름 중 S자가 들어가 있는 사원의 사원정보를 출력하시오.

SELECT EMPNO,ENAME,HIREDATE,DEPTNO
FROM EMP
WHERE ENAME LIKE '%S%';

 

 

9. 부서가 30이고 급여가 1500이상인 사원의 이름, 부서명 급여를 출력하시오.

SELECT e.ENAME, d.DNAME, e.SAL
FROM EMP e, DEPT d
WHERE e.DEPTNO=d.DEPTNO
AND e.DEPTNO=30 AND e.SAL >= 1500;

 

 

10. 급여가 1500 이상이고 부서번호가 30번인 사원 중 JOB이 MANAGER인 사람의 정보를 출력하시오.

SELECT e.ENAME, d.DNAME, e.SAL
FROM EMP e, DEPT d
WHERE e.DEPTNO=d.DEPTNO
AND e.DEPTNO=30 AND e.SAL >= 1500 AND e.JOB='MANAGER';

 

 

11. 부서번호가 30번인 사원들 중 사원번호, 이름, 입사일을 출력하는데 사원번호를 오름차순으로 정렬하시오.

SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE DEPTNO=30
ORDER BY 1;

 

 

12. 부서번호별로 정렬하여 모든 정보를 출력하는데 부서별로 급여가 높은 사원부터 출력하시오.

SELECT *
FROM EMP
ORDER BY DEPTNO, SAL DESC NULLS LAST;

 

 

13. 부서별 평균급여를 출력하시오. (단, 평균급여는 소수 둘째자리까지 출력하시오)

SELECT DEPTNO, ROUND(AVG(SAL),2)
FROM EMP
GROUP BY DEPTNO
ORDER BY 1;

 

 

14. 사원수가 5명이 넘는 부서의 부서명과 사원수를 출력하시오.

SELECT DNAME, COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
GROUP BY DNAME
HAVING COUNT(*) >= 5;

 

 

15. 사원 JAMES의 이름, 부서명, 급여와 그의 매니저 이름, 부서명을 출력하시오.

SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO)
ORDER BY DEPTNO;

-------------------------------------------------------------------------------------------

SELECT *

FROM EMP

WHERE SAL IN (SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO); 

-------------------------------------------------------------------------------------------

SELECT *

FROM EMP e, (SELECT DEPTNO, MAX(SAL) MAX_SAL

FROM EMP

GROUP BY DEPTNO) d

WHERE e.DEPTNO = d.DEPTNO

AND e.SAL = d.MAX_SAL

 

 

16. 급여가 20번 부서의 평균급여보다 많고 사원을 관리하는 사원으로서 20번 부서에 속하지 않은 사원의 모든 정보를 출력하시오.

SELECT E1.EMPNO,E1.ENAME,E1.SAL,D1.DNAME,E2.EMPNO MGRNO, E2.ENAME MGRNAME,D2.DNAME MGRDNAME
FROM EMP E1, EMP E2, DEPT D1,DEPT D2
WHERE E2.EMPNO=E1.MGR
AND E1.DEPTNO=D1.DEPTNO
AND E2.DEPTNO=D2.DEPTNO
AND E1.ENAME='JAMES'; 

-------------------------------------------------------------------------------------------

SELECT e1.ENAME, e1.DNAME, e1.SAL, e2.ENAME, e2.DNAME
FROM (SELECT ENAME, DNAME, MGR, SAL
           FROM EMP e, DEPT d
           WHERE e.DEPTNO=d.DEPTNO
           AND ENAME='JAMES') e1,
          (SELECT EMPNO, ENAME, DNAME
           FROM EMP e, DEPT d
           WHERE e.DEPTNO=d.DEPTNO) e2
WHERE e1.MGR=e2.EMPNO 

 

 

17. 급여가 20번 부서의 평균 급여보다 많고 사원을 관리하는 사원으로서 20번 부서에 속하지 않은 사원의 모든 정보를 출력하시오.

SELECT *
FROM EMP
WHERE EMPNO IN (SELECT DISTINCT MGR FROM EMP)
AND DEPTNO !=20
AND SAL > (SELECT AVG(SAL)
    FROM EMP
    WHERE DEPTNO=20);

 

 

 

 

 

DB TEST.txt