테이블(Table) 생성
1. EMP 테이블에 있는 모든 CONTRAINT 를 조회하는 SELECT 문을 작성하여라.
-------------------------------------------------------------------
SQL> select constraint_name, table_name, status
2 from user_constraints;
CONSTRAINT_NAME TABLE_NAME STATUS
-------------------- --------------- ----------------
FK_DEPTNO EMP ENABLED
PK_DEPT DEPT ENABLED
PK_EMP EMP ENABLED
SQL>
2. EMP 테이블에서 SAL, COMM을 제외한 모든 COLUMN과 행을 포함하는 EMP_DEMO 테이블을 생성하는 SQL 문을 작성하여라.
-------------------------------------------------------------------
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 create table emp_demo
2 as select empno,ename,job,mgr,hiredate,deptno
3* from emp
SQL> /
테이블이 생성되었습니다.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
---------------------------------------- -------------- ----------
BONUS TABLE
ACCOUNT TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
EMP_BACK TABLE
TIMESTAMP_TEST TABLE
DATE_TEST TABLE
INTERVAL_YEAR_TEST TABLE
INTERVAL_YEAR_TEST_2 TABLE
EMP_30 TABLE
TNAME TABTYPE CLUSTERID
---------------------------------------- -------------- ----------
EMP_TEMP TABLE
EMP_DEMO TABLE
13 개의 행이 선택되었습니다.
SQL> select * from emp_demo;
EMPNO ENAME JOB MGR HIREDATE DEPTNO
------- ---------- --------------- ------ -------- --------
7369 SMITH CLERK 7902 80/12/17 20
7499 ALLEN SALESMAN 7698 81/02/20 30
7521 WARD SALESMAN 7698 81/02/22 30
7566 JONES MANAGER 7839 81/04/02 20
7654 MARTIN SALESMAN 7698 81/09/28 30
7698 BLAKE MANAGER 7839 81/05/01 30
7782 CLARK MANAGER 7839 81/06/09 10
7788 SCOTT ANALYST 7566 87/04/19 20
7839 KING PRESIDENT 81/11/17 10
7844 TURNER SALESMAN 7698 81/09/08 30
7876 ADAMS CLERK 7788 87/05/23 20
EMPNO ENAME JOB MGR HIREDATE DEPTNO
------- ---------- --------------- ------ -------- --------
7900 JAMES CLERK 7698 81/12/03 30
7902 FORD ANALYST 7566 81/12/03 20
7934 MILLER CLERK 7782 82/01/23 10
14 개의 행이 선택되었습니다.
SQL>
3. EMP 테이블과 DEPT 테이블을 이용하여 아래의 내용을 포함하는 테이블(EMP_DEPT) 을 생성하여라.
| EMPNO ENAME JOB DNAME LOC ------- -------------------- ------------------ -------------------- ---------- 7369 SMITH CLERK RESEARCH DALLAS 7499 ALLEN SALESMAN SALES CHICAGO 7521 WARD SALESMAN SALES CHICAGO 7566 JONES MANAGER RESEARCH DALLAS 7654 MARTIN SALESMAN SALES CHICAGO 7698 BLAKE MANAGER SALES CHICAGO 7782 CLARK MANAGER ACCOUNTING NEW YORK 7788 SCOTT ANALYST RESEARCH DALLAS 7839 KING PRESIDENT ACCOUNTING NEW YORK 7844 TURNER SALESMAN SALES CHICAGO 7876 ADAMS CLERK RESEARCH DALLAS EMPNO ENAME JOB DNAME LOC ------- -------------------- ------------------ -------------------- ---------- 7900 JAMES CLERK SALES CHICAGO 7902 FORD ANALYST RESEARCH DALLAS 7934 MILLER CLERK ACCOUNTING NEW YORK 14 개의 행이 선택되었습니다. |
-------------------------------------------------------------------
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 create table emp_dept
2 as select empno, ename, job, dname, loc
3 from emp, dept
4* where emp.deptno=dept.deptno
SQL> /
테이블이 생성되었습니다.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------------- ----------
ACCOUNT TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
EMP_DEPT TABLE
6 개의 행이 선택되었습니다.
SQL> select * from emp_dept;
EMPNO ENAME JOB DNAME LOC
------- -------------------- ------------------ -------------------- ----------
7369 SMITH CLERK RESEARCH DALLAS
7499 ALLEN SALESMAN SALES CHICAGO
7521 WARD SALESMAN SALES CHICAGO
7566 JONES MANAGER RESEARCH DALLAS
7654 MARTIN SALESMAN SALES CHICAGO
7698 BLAKE MANAGER SALES CHICAGO
7782 CLARK MANAGER ACCOUNTING NEW YORK
7788 SCOTT ANALYST RESEARCH DALLAS
7839 KING PRESIDENT ACCOUNTING NEW YORK
7844 TURNER SALESMAN SALES CHICAGO
7876 ADAMS CLERK RESEARCH DALLAS
EMPNO ENAME JOB DNAME LOC
------- -------------------- ------------------ -------------------- ----------
7900 JAMES CLERK SALES CHICAGO
7902 FORD ANALYST RESEARCH DALLAS
7934 MILLER CLERK ACCOUNTING NEW YORK
14 개의 행이 선택되었습니다.
SQL>
4. EMP 테이블과 SALGRADE 테이블을 이용하여 아래의 내용을 포함하는 테이블(EMP_GRADE)을 생성하여라.
| EMPNO ENAME JOB SAL COMM GRADE ------- --------------- ------------------ ---------- ---------- ---------- 7839 KING PRESIDENT 5000 5 7902 FORD ANALYST 3000 4 7788 SCOTT ANALYST 3000 4 7782 CLARK MANAGER 2450 4 7698 BLAKE MANAGER 2850 4 7566 JONES MANAGER 2975 4 7499 ALLEN SALESMAN 1600 300 3 7844 TURNER SALESMAN 1500 0 3 7654 MARTIN SALESMAN 1250 1400 2 7521 WARD SALESMAN 1250 500 2 7934 MILLER CLERK 1300 2 EMPNO ENAME JOB SAL COMM GRADE ------- --------------- ------------------ ---------- ---------- ---------- 7900 JAMES CLERK 950 1 7876 ADAMS CLERK 1100 1 7369 SMITH CLERK 800 1 14 개의 행이 선택되었습니다. |
-------------------------------------------------------------------
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 create table emp_grade
2 as select empno, ename, job, sal, comm, grade
3 from emp e, salgrade s
4 where e.sal between s.losal and s.hisal
5* order by grade desc
SQL> /
테이블이 생성되었습니다.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------------- ----------
ACCOUNT TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
EMP_DEPT TABLE
EMP_GRADE TABLE
7 개의 행이 선택되었습니다.
SQL> select * from emp_grade;
EMPNO ENAME JOB SAL COMM GRADE
------- --------------- ------------------ ---------- ---------- ----------
7839 KING PRESIDENT 5000 5
7902 FORD ANALYST 3000 4
7788 SCOTT ANALYST 3000 4
7782 CLARK MANAGER 2450 4
7698 BLAKE MANAGER 2850 4
7566 JONES MANAGER 2975 4
7499 ALLEN SALESMAN 1600 300 3
7844 TURNER SALESMAN 1500 0 3
7654 MARTIN SALESMAN 1250 1400 2
7521 WARD SALESMAN 1250 500 2
7934 MILLER CLERK 1300 2
EMPNO ENAME JOB SAL COMM GRADE
------- --------------- ------------------ ---------- ---------- ----------
7900 JAMES CLERK 950 1
7876 ADAMS CLERK 1100 1
7369 SMITH CLERK 800 1
14 개의 행이 선택되었습니다.
SQL>
5. DEPT 테이블의 PRIMARY KEY를 DISABLE 하는 SQL문을 작성하여라.
-------------------------------------------------------------------
SQL> select constraint_name, table_name, status
2 from user_constraints;
CONSTRAINT_NAME TABLE_NAME STATUS
-------------------- --------------- ----------------
FK_DEPTNO EMP ENABLED
PK_DEPT DEPT ENABLED
PK_EMP EMP ENABLED
EMP_DEPT_EMPNO_PK EMP_DEPT ENABLED
SQL> alter table dept
2 disable constraint pk_dept cascade;
테이블이 변경되었습니다.
SQL> select constraint_name, table_name, status
2 from user_constraints;
CONSTRAINT_NAME TABLE_NAME STATUS
-------------------- --------------- ----------------
PK_EMP EMP ENABLED
EMP_DEPT_EMPNO_PK EMP_DEPT ENABLED
FK_DEPTNO EMP DISABLED
PK_DEPT DEPT DISABLED
SQL>
6. 3번에서 생성한 테이블에 EMPNO 를 PRIMARY KEY로 설정하는 SQL 문을 작성하여라.
-------------------------------------------------------------------
SQL> select constraint_name, table_name, status
2 from user_constraints;
CONSTRAINT_NAME TABLE_NAME STATUS
-------------------- --------------- ----------------
FK_DEPTNO EMP ENABLED
PK_DEPT DEPT ENABLED
PK_EMP EMP ENABLED
SQL> alter table emp_dept
2 add constraint emp_dept_empno_pk primary key (empno);
테이블이 변경되었습니다.
SQL> select constraint_name, table_name, status
2 from user_constraints;
CONSTRAINT_NAME TABLE_NAME STATUS
-------------------- --------------- ----------------
FK_DEPTNO EMP ENABLED
PK_DEPT DEPT ENABLED
PK_EMP EMP ENABLED
EMP_DEPT_EMPNO_PK EMP_DEPT ENABLED
SQL>
7. 4번에서 생성한 테이블에 SAL의 정밀도를 정수 부분을 12자리 소수 이하 4자리로 변경하는 SQL 문을 작성하여라.
-------------------------------------------------------------------
SQL> desc emp_grade;
이름 널? 유형
----------------------------------------- -------- ---------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER(7,2)
COMM NUMBER(7,2)
GRADE NUMBER
SQL> alter table emp_grade
2 modify (sal number(12,4));
테이블이 변경되었습니다.
SQL> desc emp_grade;
이름 널? 유형
----------------------------------------- -------- ---------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER(12,4)
COMM NUMBER(7,2)
GRADE NUMBER
SQL>
8. 3번에서 작성한 테이블의 내용과 저장 장소를 모두 해제하는 SQL문을 작성하여라.
-------------------------------------------------------------------
SQL>
9. 3번과 4번에서 생성한 테이블을 모두 삭제하는 SQL문을 작성하여라.
-------------------------------------------------------------------
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------------- ----------
ACCOUNT TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
EMP_DEPT TABLE
EMP_GRADE TABLE
7 개의 행이 선택되었습니다.
SQL> drop table emp_dept;
테이블이 삭제되었습니다.
SQL> drop table emp_grade;
테이블이 삭제되었습니다.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------------- ----------
ACCOUNT TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
BIN$jr2PWdnSTJKmi2sf6zZ0HQ==$0 TABLE
BIN$LC/p1TG4TlWvst5GHK7TXw==$0 TABLE
7 개의 행이 선택되었습니다.
SQL>
'ORACLE > Excercise' 카테고리의 다른 글
| Oracle - 연습문제 #7 (0) | 2008.12.29 |
|---|---|
| Oracle - 연습문제 #6 (0) | 2008.12.29 |
| Oracle - 연습문제 #5 (0) | 2008.12.27 |
| Oracle - 연습문제 #4 (0) | 2008.12.27 |
| Oracle - 연습문제 #3 (0) | 2008.12.26 |
| Oracle - 연습문제 #2 (0) | 2008.12.26 |