[oracle] 무결성 제약조건, JOIN, 서브쿼리 실습

->테이블 딕셔너리(테이블 확인 가능) 보라색 부분만 수정

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'CH10_MEMBER';

CHAP10 - 1

CREATE TABLE CH10_MEMBER(
 ID VARCHAR2(20) CONSTRAINT CH10_MEMBER_ID_PK PRIMARY KEY,
 NAME VARCHAR2(20) NOT NULL,
 REGNO VARCHAR2(20) NOT NULL,
 HP VARCHAR2(13) CONSTRAINT CH10_MEMBER_HP_UQ UNIQUE,
 ADDRESS VARCHAR2(100),
 
 CONSTRAINT CH10_MEMBER_REGNO_UQ UNIQUE(REGNO)
);

 

CHAP10 - 2

CREATE TABLE CH10_BOOK(
 CODE NUMBER(4) CONSTRAINT CH10_BOOK_CODE_PK PRIMARY KEY,
 TITLE VARCHAR2(20) NOT NULL,
 COUNT NUMBER(6),
 PRICE NUMBER(10),
 PUBLIISH VARCHAR2(50) 
);

 

CHAP10 - 3

CREATE TABLE CH10_ORDER(
 NO VARCHAR2(10) CONSTRAINT CH10_ORDER_NO_PK PRIMARY KEY,
 ID VARCHAR2(20) CONSTRAINT CH10_ORDER_ID_FK REFERENCES CH10_MEMBER(ID),
 CODE NUMBER(4) CONSTRAINT CH10_ORDER_CODE_FK REFERENCES CH10_BOOK(CODE),
 COUNT NUMBER(6),
 OR_DATE DATE
);

1. 부서번호가 30번인 사원들의 이름, 직급, 부서번호, 부서위치를 출력해보자
->SELECT E.ENAME, E.JOB, D.DEPTNO, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO= D.DEPTNO
AND E.DEPTNO=30;

 

2. 'DALLAS'에서 근무하는 사원의 이름, 직급, 부서번호, 부서이름을 출력하시오
->SELECT E.ENAME, E.JOB, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE D.LOC='DALLAS';

 

3. 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하시오.
->SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.ENAME LIKE '%A%';

 

CHAP12

1. SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN 3000 AND 5000;

 

2. SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.JOB='MANAGER';

 

3. SELECT E.ENAME, E.HIREDATE
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='ACCOUNTING';

 

4. SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.COMM IS NOT NULL;

 

5. SELECT E.ENAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND D.LOC='NEW YORK';

 

6. SELECT E.ENAME, E.SAL, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.SAL=<1200;

 

7. SELECT E2.ENAME SCOTT의동료
FROM EMP E1, EMP E2
WHERE E1.DEPTNO=E2.DEPTNO AND E1.ENAME='SCOTT' AND E2.ENAME<>'SCOTT';

 

8. SELECT E1.ENAME, E1.JOB
FROM EMP E1, EMP E2
WHERE E1.MGR=E2.EMPNO AND E2.ENAME='KING';

 

 

1. 문제) EMP와 DEPT Table을 JOIN하여 부서번호, 부서명, 이름, 급여를 출력하라.

해답) SELECT a.deptno, b.dname, a.ename, a.sal
         FROM emp a, dept b
         WHERE a.deptno = b.deptno;

 

2. 문제) 이름이 ‘ALLEN’인 사원의 부서명을 출력하라.

해답) SELECT b.dname
         FROM emp a, dept b
         WHERE a.deptno = b.deptno and a.ename = 'ALLEN';

 

3. 문제) DEPT Table에 있는 모든 부서를 출력하고, EMP Table에 있는 DATA와 JOIN하여 모든 사원의 이름, 부서번호, 부서명, 급여를 출력하라.
해답) SELECT a.ename, a.deptno, b.dname, a.sal
         FROM  emp a, dept b
         WHERE a.deptno = b.deptno;

 

4. 문제) EMP Table에 있는 EMPNO와 MGR을 이용하여 서로의 관계를 다음과 같이 출력하라. ‘SMTH의 매니저는 FORD이다’

해답)  SELECT a.empno, a.ename, b.mgr
          FROM emp a, emp b
          WHERE a.empno = b.mgr and a.ename = 'FORD';

5. 문제) ‘ALLEN:의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하라.

해답) SELECT a.ename, b.dname, a.sal, b.loc, a.job
         FROM emp a, dept b
         WHERE a.job = (select job from emp where ename =ALLEN’);

 

6. 문제) ‘JONES’가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일, 급여를 출력하라.

해답) SELECT empno, ename, hiredate, sal
         FROM emp
         WHERE deptno = (SELECT deptno 
                         FROM emp WHERE ename =’JONES’);

 

7. 문제) 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름,
부서명, 입사일, 지역, 급여를 출력하라.

해답) SELECT a.empno, a.ename, a.hiredate, b.loc, a.sal
         FROM emp a, dept b
         WHERE a.sal > (select avg(sal) from emp);

 

8. 문제) 10번 부서 사람들 중에서 20번 부서의 사원과 같은 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일, 지역을 출력하라.

해답) SELECT a.empno, a.ename, b.dname, a.hiredate, b.loc
         FROM emp a, dept b
         WHERE a.deptno = b.deptno and
               a.job in (select job from emp where deptno = 20) 
               and a.deptno = 10;

 

9. 문제) 10번 부서 중에서 30번 부서에는 없는 업무를 하는 사원의
사원번호, 이름, 부서명, 입사일, 지역을 출력하라.
해답)  SELECT a.empno, a.ename, b.dname, a.hiredate, b.loc
          FROM emp a, dept b
          WHERE a.deptno = b.deptno and a.deptno = 10 
                  and a.job not in (select job 
                                    from emp where deptno = 30);

 

10. 문제) 10번 부서와 같은 일을 하는 사원의 사원번호, 이름, 부서명,지역, 급여를 급여가 많은 순으로 출력하라.
해답) SELECT a.empno, a.ename, b.dname, b.loc, a.sal
          FROM emp a, dept b
          WHERE a.deptno = b.deptno 
                and a.job in (select job 
                              from emp where deptno = 10)
          ORDER BY a.sal;

 

11. 문제) ‘MARTIN’이나 ‘SCOTT의 급여와 같은 사원의 사원번호, 이름,급여를 출력하라.
해답) SELECT a.empno, a.ename, a.sal
          FROM  emp a
          WHERE a.ename IN ('MARTIN', 'SCOTT');

 

12. 문제) 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.

해답) SELECT empno, ename, sal
          FROM emp
          WHERE sal > (select max(sal) from emp where deptno = 30);

 

13. 문제) 급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.

해답)  SELECT empno, ename, sal
           FROM emp
           WHERE sal > (select min(sal) from emp where deptno = 30);