[오라클 교재] 부록 B. 실습용 테이블을 생성하는 SCRIPT

프로그래밍/DB

by 라제폰 2008. 12. 26. 21:13

B. 실습용 테이블을 생성하는 SCRIPT

1. 실습용SCRIPT란 ?

이 데모용 SCRIPT는 ORACLE제품을 INSTALL하면 INSTALL시 생성되어지는 SCRIPT로 ORACLE을 처음 사용하는 사용자가 SQL,SQL*Plus,PL/SQL을 처음 배우고자 할 때 아주 유용하게 사용되는 SCRIPT이다. 일반 사용자는 SQL*Plus에서 SCOTT 사용자로 Log On하여 사용하면 된다.

2. 실습용 TABLE 생성 절차

1) Personal Oracle7을 Install한다.

2) ORACLE_HOME\DBS\DEMOBLD.SQL FILE을 NOTEPAD로 OPEN한다. ORACLE_HOME DICTORY는 Personal Orclae7을 Install할 때 지정할 수 있다. 지금부터 ORACLE_HOME DICTORY를 C:\ORAWIN95로 사용한다.

3) 약 30 Line에 있는 ENAME CHAR(10)을 ENAME VARCHAR2(10)로, JOB CHAR(9)을 JOB VARCHAR2(9)로 수정한 후 저장하고 종료하여라. 다음 PAGE의 SCRIPT에서 진하게 인쇄된 부분과 동일하게 수정 후 저장하고 종료하면 된다.

4) 시작 → 프로그램 → Oracle for Windows 95 → SQL Plus 3.3 을 선택한다.

5) User Name에는 SCOTT을 입력하고 Password에는 TIGER를 입력하고 Host String에는 2: 또는 생략 가능하다(DEFAULT는 LOCAL변수에 정의되어있는 곳).

6) 날짜 형식을 DD-MON-YY형식으로 SCRIPT를 작성하였다. 날짜 형식이 DD-MON-YY형식이 아닌 경우는 다음의 명령을 먼저 실행하여라. SQL PROMPT(SQL>)에서 ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YY’;을 실행한다.

7) SQL PROMPT(SQL>)에서 @C:\ORAWIN95\DBS\DEMOBLD를 입력하고 Enter한다.

♣ 참고

기존의 SCRIPT는 EMP TABLE의 이름(ename)이 CHAR(10)로 되어 있고, 업무가 CHAR(9)로 되어 있다. 그러나 여러분의 이해를 돕기 위하여 이름은VARCHAR2(10), 업무는 VARCHAR2(9) 바꾸었다.

3. 실습용 SCRIPT의 내용

-- echo Building Oracle demonstration tables. Please wait.

DROP TABLE EMP;

DROP TABLE DEPT;

DROP TABLE BONUS;

DROP TABLE SALGRADE;

DROP TABLE DUMMY;

DROP TABLE ITEM;

DROP TABLE PRICE;

DROP TABLE PRODUCT;

DROP TABLE ORD;

DROP TABLE CUSTOMER;

DROP VIEW SALES;

DROP SEQUENCE ORDID;

DROP SEQUENCE CUSTID;

DROP SEQUENCE PRODID;

CREATE TABLE DEPT (

DEPTNO NUMBER(2) NOT NULL,

DNAME CHAR(14),

LOC CHAR(13),

CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));

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

CREATE TABLE EMP (

EMPNO NUMBER(4) NOT NULL,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2) NOT NULL,

CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),

CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);

CREATE TABLE BONUS (

ENAME CHAR(10),

JOB CHAR(9),

SAL NUMBER,

COMM NUMBER);

CREATE TABLE SALGRADE (

GRADE NUMBER,

LOSAL NUMBER,

HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,700,1200);

INSERT INTO SALGRADE VALUES (2,1201,1400);

INSERT INTO SALGRADE VALUES (3,1401,2000);

INSERT INTO SALGRADE VALUES (4,2001,3000);

INSERT INTO SALGRADE VALUES (5,3001,9999);

CREATE TABLE DUMMY (

DUMMY NUMBER );

INSERT INTO DUMMY VALUES (0);

CREATE TABLE CUSTOMER (

CUSTID NUMBER (6) NOT NULL,

NAME CHAR (45),

ADDRESS CHAR (40),

CITY CHAR (30),

STATE CHAR (2),

ZIP CHAR (9),

AREA NUMBER (3),

PHONE CHAR (9),

REPID NUMBER (4) NOT NULL,

CREDITLIMIT NUMBER (9,2),

COMMENTS LONG,

CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTID),

CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0));

CREATE TABLE ORD (

ORDID NUMBER (4) NOT NULL,

ORDERDATE DATE,

COMMPLAN CHAR (1),

CUSTID NUMBER (6) NOT NULL,

SHIPDATE DATE,

TOTAL NUMBER (8,2) CONSTRAINT TOTAL_ZERO CHECK (TOTAL >= 0),

CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID),

CONSTRAINT ORD_PRIMARY_KEY PRIMARY KEY (ORDID));

CREATE TABLE ITEM (

ORDID NUMBER (4) NOT NULL,

ITEMID NUMBER (4) NOT NULL,

PRODID NUMBER (6),

ACTUALPRICE NUMBER (8,2),

QTY NUMBER (8),

ITEMTOT NUMBER (8,2),

CONSTRAINT ITEM_FOREIGN_KEY FOREIGN KEY (ORDID) REFERENCES ORD (ORDID),

CONSTRAINT ITEM_PRIMARY_KEY PRIMARY KEY (ORDID,ITEMID));

CREATE TABLE PRODUCT (

PRODID NUMBER (6) CONSTRAINT PRODUCT_PRIMARY_KEY PRIMARY KEY,

DESCRIP CHAR (30));

CREATE TABLE PRICE (

PRODID NUMBER (6) NOT NULL,

STDPRICE NUMBER (8,2),

MINPRICE NUMBER (8,2),

STARTDATE DATE,

ENDDATE DATE);

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('96711', 'CA', '7844', '598-6609',

'JOCKSPORTS',

'100', '5000', 'BELMONT', '415', '345 VIEWRIDGE',

'Very friendly people to work with -- sales rep likes to be called Mike.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('94061', 'CA', '7521', '368-1223',

'TKB SPORT SHOP',

'101', '10000', 'REDWOOD CITY', '415', '490 BOLI RD.',

'Rep called 5/8 about change in order - contact shipping.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('95133', 'CA', '7654', '644-3341',

'VOLLYRITE',

'102', '7000', 'BURLINGAME', '415', '9722 HAMILTON',

'Company doing heavy promotion beginning 10/89. Prepare for large orders during

winter.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('97544', 'CA', '7521', '677-9312',

'JUST TENNIS',

'103', '3000', 'BURLINGAME', '415', 'HILLVIEW MALL',

'Contact rep about new line of tennis rackets.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('93301', 'CA', '7499', '996-2323',

'EVERY MOUNTAIN',

'104', '10000', 'CUPERTINO', '408', '574 SURRY RD.',

'Customer with high market share (23%) due to aggressive advertising.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('91003', 'CA', '7844', '376-9966',

'K + T SPORTS',

'105', '5000', 'SANTA CLARA', '408', '3476 EL PASEO',

'Tends to order large amounts of merchandise at once. Accounting is considering

raising their credit limit. Usually pays on time.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('94301', 'CA', '7521', '364-9777',

'SHAPE UP',

'106', '6000', 'PALO ALTO', '415', '908 SEQUOIA',

'Support intensive. Orders small amounts (< 800) of merchandise at a time.');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('93301', 'CA', '7499', '967-4398',

'WOMENS SPORTS',

'107', '10000', 'SUNNYVALE', '408', 'VALCO VILLAGE',

'First sporting goods store geared exclusively towards women. Unusual promotion

al style and very willing to take chances towards new products!');

INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,

CITY, AREA, ADDRESS, COMMENTS)

VALUES ('55649', 'MN', '7844', '566-9123',

'NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER',

'108', '8000', 'HIBBING', '612', '98 LONE PINE WAY', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('101.4', '08-JAN-87', '610', '07-JAN-87', '101', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('45', '11-JAN-87', '611', '11-JAN-87', '102', 'B');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('5860', '20-JAN-87', '612', '15-JAN-87', '104', 'C');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('2.4', '30-MAY-86', '601', '01-MAY-86', '106', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('56', '20-JUN-86', '602', '05-JUN-86', '102', 'B');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('698', '30-JUN-86', '604', '15-JUN-86', '106', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('8324', '30-JUL-86', '605', '14-JUL-86', '106', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('3.4', '30-JUL-86', '606', '14-JUL-86', '100', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('97.5', '15-AUG-86', '609', '01-AUG-86', '100', 'B');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('5.6', '18-JUL-86', '607', '18-JUL-86', '104', 'C');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('35.2', '25-JUL-86', '608', '25-JUL-86', '104', 'C');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('224', '05-JUN-86', '603', '05-JUN-86', '102', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('4450', '12-MAR-87', '620', '12-MAR-87', '100', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('6400', '01-FEB-87', '613', '01-FEB-87', '108', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('23940', '05-FEB-87', '614', '01-FEB-87', '102', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('764', '10-FEB-87', '616', '03-FEB-87', '103', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('1260', '04-FEB-87', '619', '22-FEB-87', '104', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('46370', '03-MAR-87', '617', '05-FEB-87', '105', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('710', '06-FEB-87', '615', '01-FEB-87', '107', '');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('3510.5', '06-MAR-87', '618', '15-FEB-87', '102', 'A');

INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN)

VALUES ('730', '01-JAN-87', '621', '15-MAR-87', '100', 'A');

INSERT INTO ITEM (QTY, PRODID, ORDID, ITEMTOT, ITEMID, ACTUALPRICE)

VALUES ('1', '100890', '610', '58', '3', '58');