TRUST NO ONE。SO I BELIEVE。

*** 5장에서 생성한 E학번 테이블과 D학번 테이블 사용 ***

1. E학번 테이블의 manager_id 칼럼을 삭제하여라.

SQL> alter table E200707083
  2  drop column manager_id;

Table altered.

2. E학번 테이블과 D학번 테이블에 데이터를 추가하여라.

1) D학번 테이블에 NO는 10이고 NAME은 Finance인 행을 추가하여라.

SQL> insert into D200707083 (no,name) values (10, 'Finance');

1 row created.

2) E학번 테이블에 두 개의 행을 추가하여라. 첫 번째  사원은 DEPTID가 10인 Donna Smith(LNAME, FNAME)이고 ID는 200이다. 두 번째 사원은 DEPTID가 54인 Albert Jones이고 ID는 201이다. 결과와 그 결과의 이유를 설명하여라.

Result 1)

SQL> insert into e200707083 (deptid,lname, fname, id)
  2  values (10, 'Donna', 'Smith', 200);

1 row created.

Result 2)

SQL> insert into e200707083 (deptid, lname, fname, id)
  2  values (52, 'Albert', 'Jones', 201);
insert into e200707083 (deptid, lname, fname, id)
*
ERROR at line 1:
ORA-02291: integrity constraint (Y200707083.EMP200707083C_DEPTID_FK) violated -
parent key not found

3) NO가 10, NAME이 Marketing인 데이터를 D학번 테이블에 추가하여라. 결과와 그 결과의 이유를 설명하여라.

SQL> insert into d200707083 (no,name) values (10, 'Marketing');
insert into d200707083 (no,name) values (10, 'Marketing')
*
ERROR at line 1:
ORA-00001: unique constraint (Y200707083.DEPT200707083C_NO_PK) violated

Reason : NO 컬럼에 PK키로 지정되어 있고, 중복된 값이 들어가 있기 때문에.

4) 치환매개변수를 이용하여 D학번 테이블에 Marketing은 NO를 37번, Sales는 54번, Personnel은 75번을 추가하여라.

SQL> insert into d200707083 (no, name)
  2  values (&no, '&name');
Enter value for no: 37
Enter value for name: Marketing
old   2: values (&no, '&name')
new   2: values (37, 'Marketing')

1 row created.

SQL> /
Enter value for no: 54
Enter value for name: Sales
old   2: values (&no, '&name')
new   2: values (54, 'Sales')

1 row created.

SQL> /
Enter value for no: 75
Enter value for name: Personnel
old   2: values (&no, '&name')
new   2: values (75, 'Personnel')

1 row created.

5) Albert Jones의 DEPTID는 54이고 ID는 201, Harry Chin은 각각 75와 202, Rey Guiliani는 37과 203인 행을 E학번 테이블에 추가하여라.

SQL> insert into e200707083 (lname, fname, deptid,
  2  values ('&lname', '&fname', &deptid, &id)
  3  ;
Enter value for lname: Albert
Enter value for fname: Jones
Enter value for deptid:  54
Enter value for id: 201
old   2: values ('&lname', '&fname', &deptid, &id)
new   2: values ('Albert', 'Jones',  54, 201)

1 row created.

SQL> /
Enter value for lname: Harry
Enter value for fname: Chin
Enter value for deptid: 75
Enter value for id: 202
old   2: values ('&lname', '&fname', &deptid, &id)
new   2: values ('Harry', 'Chin', 75, 202)

1 row created.

SQL> /
Enter value for lname: Rey
Enter value for fname: Guiliani
Enter value for deptid: 37
Enter value for id: 203
old   2: values ('&lname', '&fname', &deptid, &id)
new   2: values ('Rey', 'Guiliani', 37, 203)

1 row created.

SQL> select *
  2  from e200707083
  3  ;

        ID LNAME                     FNAME                         DEPTID
---------- ------------------------- ------------------------- ----------
       200 Donna                     Smith                             10
       201 Albert                    Jones                             54
       202 Harry                     Chin                              75
       203 Rey                       Guiliani                          37

6) 추가한 데이터가 영구적으로 반영되도록 하여라.

SQL> commit;

Commit complete.

3. E학번 테이블과 D학번 테이블의 데이터를 변경하고 삭제하여라.

1) D학번 테이블의 Personnel NAME을 Human Resources로 변경하여라.

SQL> update d200707083 set name = 'Human Resources'
  2  where name = 'Personnel'
  3  ;

1 row updated.

2) E학번 테이블의 202번 LNAME을 Korsgaard로 변경하여라.

SQL>  update e200707083 set lname = 'Korsgaard'
  2   where id = 202;

1 row updated.

3) D학번 테이블에서 NO가 54인 데이터를 삭제하여라. 결과와 그 결과의 이유를 설명하여라.

SQL> delete from d200707083
  2  where no = 54
  3  ;
delete from d200707083
*
ERROR at line 1:
ORA-02292: integrity constraint (Y200707083.EMP200707083C_DEPTID_FK) violated -
child record found

Reason : D학번 테이블의 DEPTID 값이 FK 값으로 지정되어 있기 때문에.

4) E학번 테이블에서 Albert Jones를 삭제하여라.

SQL> delete from e200707083
  2* where lname = 'Albert' and fname = 'Jones'
SQL> /

1 row deleted.

5) D학번 테이블에서 NO가 54인 데이터를 다시 삭제하여라. 결과와 그 결과의 이유를 설명하여라.

SQL> delete from d200707083
  2  where no  = 54
  3  ;

1 row deleted.

Reason : FK로 지정되어 있는 값이 먼저 삭제되었기 때문에.

6) 변경한 데이터가 영구적으로 반영되도록 하여라.

SQL> commit;

Commit complete.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

이올린에 북마크하기(0) 이올린에 추천하기(0)

SQL

SQL*PLUS

PL/SQL

DB <- SQL*PLUS(ed, set....) < ?

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

이올린에 북마크하기(0) 이올린에 추천하기(0)

5. 이름이 ‘ALLEN’인 사원의 부서 이름을 출력하여라.

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select e.dept, d.dname
  2  from emp e, dept d
  3* where e.deptno = d.deptno and ename = 'ALLEN'
SQL> ;
SQL> /

DNAME
--------------
SALES

6. ‘ALLEN’과 직무가 같은 사원의 이름, 부서 이름, 급여, 부서 위치를 출력하여라.

SQL> select e.ename, d.dname, e.sal, d.loc
  2  from emp e, dept d
  3  where e.deptno = d.deptno and e.job = (select job from emp where ename = 'ALLEN')
  4  ;

ENAME      DNAME                 SAL LOC
---------- -------------- ---------- -------------
ALLEN      SALES                1600 CHICAGO
WARD       SALES                1250 CHICAGO
MARTIN     SALES                1250 CHICAGO
TURNER     SALES                1500 CHICAGO

7. 전체 사원의 평균 급여보다 급여가 많은 사원의 사원 번호, 이름, 부서 이름, 입사일, 부서 위치, 급여를 출력하여라.

SQL> select e.empno, e.ename, d.dname, e.hiredate, d.loc, e.sal
  2  from emp e, dept d
  3  where e.deptno = d.deptno and e.sal > (select avg(sal) from emp)
  4  ;

     EMPNO ENAME      DNAME          HIREDATE LOC                  SAL
---------- ---------- -------------- -------- ------------- ----------
      7566 JONES      RESEARCH       81/04/02 DALLAS              2975
      7698 BLAKE      SALES          81/05/01 CHICAGO             2850
      7782 CLARK      ACCOUNTING     81/06/09 NEW YORK            2450
      7788 SCOTT      RESEARCH       87/04/19 DALLAS              3000
      7839 KING       ACCOUNTING     81/11/17 NEW YORK            5000
      7902 FORD       RESEARCH       81/12/03 DALLAS              3000

6 개의 행이 선택되었습니다.

8. 10번 부서 사원들 중에서 20번 부서의 사원과 같은 직무를 갖는 사원의 사원 번호, 이름, 부서 이름, 입사일, 부서 위치를 출력하여라.

SQL> select d.deptno, e.ename, d.dname, e.hiredate, d.loc
  2  from emp e, dept d
  3  where e.deptno = d.deptno and e.job in (select job from emp where deptno = 20)
  4  ;

    DEPTNO ENAME      DNAME          HIREDATE LOC
---------- ---------- -------------- -------- -------------
        20 SCOTT      RESEARCH       87/04/19 DALLAS
        20 FORD       RESEARCH       81/12/03 DALLAS
        20 SMITH      RESEARCH       80/12/17 DALLAS
        20 ADAMS      RESEARCH       87/05/23 DALLAS
        10 MILLER     ACCOUNTING     82/01/23 NEW YORK
        30 JAMES      SALES          81/12/03 CHICAGO
        20 JONES      RESEARCH       81/04/02 DALLAS
        10 CLARK      ACCOUNTING     81/06/09 NEW YORK
        30 BLAKE      SALES          81/05/01 CHICAGO

9 개의 행이 선택되었습니다.

9. ‘ALLEN’의 급여보다 많고 ‘SCOTT’의 급여보다 적은 사원의 사원 번호, 이름, 부서 이름, 부서 위치, 급여를 급여가 많은 순으로 출력하여라.

SQL> select e.empno, e.ename, d.dname, d.loc, e.sal
  2  from emp e, dept d
  3  where e.deptno = d.deptno and e.sal > (select sal from emp where ename = 'ALLEN') and e.sal < (
select sal from emp where ename = 'SCOTT')
  4  ;

     EMPNO ENAME      DNAME          LOC                  SAL
---------- ---------- -------------- ------------- ----------
      7566 JONES      RESEARCH       DALLAS              2975
      7698 BLAKE      SALES          CHICAGO             2850
      7782 CLARK      ACCOUNTING     NEW YORK            2450

16. 모든 사원들의 평균 급여 보다 많이 받는 사원들의 사원 번호와 이름을 출력하여라.

SQL> select d.deptno, e.ename
  2  from emp e, dept d
  3  where e.deptno = d.deptno and e.sal > (select avg(sal) from emp)
  4  ;

    DEPTNO ENAME
---------- ----------
        20 JONES
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        20 FORD

6 개의 행이 선택되었습니다.

17. ‘FORD’와 부서가 같은 사원들의 이름, 부서 이름, 직무, 급여를 출력하여라.

SQL> select e.ename, e.ename, e.job, e.sal
  2  from emp e, dept d
  3  where e.deptno = d.deptno and e.deptno=(select d.deptno from emp e, dept d where e.deptno = d.d
eptno and e.ename = 'FORD')
  4  ;

ENAME      ENAME      JOB              SAL
---------- ---------- --------- ----------
SMITH      SMITH      CLERK            800
JONES      JONES      MANAGER         2975
SCOTT      SCOTT      ANALYST         3000
ADAMS      ADAMS      CLERK           1100
FORD       FORD       ANALYST         3000

18. 부서 이름이 ‘SALES’인 사원들의 평균 급여보다 많고, 부서 이름이 ‘RESEARCH’인 사원들의 평균 급여보다 적은 사원들의 이름, 부서 번호, 급여, 직무를 출력하여라.

SQL> select e.ename ,e.deptno,e.sal,e.job from emp e ,dept d
  2  where e.deptno = d.deptno and
  3  e.sal >(select avg(e.sal) from emp e ,dept d
  4  where e.deptno = d.deptno and  d.dname ='SALES') and
  5  e.sal < (select avg(e.sal) from emp e ,dept d
  6  where e.deptno = d.deptno and  d.dname ='RESEARCH')
  7  ;

ENAME          DEPTNO        SAL JOB
---------- ---------- ---------- ---------
ALLEN              30       1600 SALESMAN

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

이올린에 북마크하기(0) 이올린에 추천하기(0)