Sem 3‎ > ‎DBMS (SQL) LAB‎ > ‎

Oracle: 1 Copy & Paste for Table Creation.

posted Sep 3, 2011, 9:08 AM by Neil Mathew   [ updated Oct 2, 2011, 11:49 PM ]

EMP


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 Smith Clerk 7902 17-DEC-80 800   20
7499 Allen Salesman 7698 20-FEB-81 1600 300 30
7521 Ward Salesman 7698 22-FEB-81 1250 500 30
7566 Jones Manager 7839 02-APR-81 2975   20
7654 Martin Salesman 7698 28-SEP-81 1250 1400 30
7698 Blake Manager 7839 01-MAY-81 2850   30
7782 Clark Manager 7839 09-JUN-81 2450   10
7788 Scott Analyst 7566 09-DEC-82 3000   20
7839 King President   17-NOV-81 5000   10
7844 Turner Salesman 7698 08-SEP-81 1500 0 30
7876 Adams Clerk 7788 03-DEC-89 950   30
7902 Ford Analyst 7566 04-DEC-81 3000   20

12 rows selected.


CREATE TABLE


CREATE TABLE EMP_nm ( 
 EMPNO NUMBER(4),
 ENAME VARCHAR2(20),
 JOB CHAR(10),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(9,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2)
 );

INSERT COMMAND:


INSERT INTO EMP_NM VALUES(7369,'Smith','Clerk',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP_NM VALUES(7499,'Allen','Salesman',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP_NM VALUES(7521,'Ward','Salesman',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP_NM VALUES(7566,'Jones','Manager',7839,'02-APR-81',2975,NULL,20);
INSERT INTO EMP_NM VALUES(7654,'Martin','Salesman',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP_NM VALUES(7698,'Blake','Manager',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO EMP_NM VALUES(7782,'Clark','Manager',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO EMP_NM VALUES(7788,'Scott','Analyst',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP_NM VALUES(7839,'King','President',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP_NM VALUES(7844,'Turner','Salesman',7698,'08-SEP-81',1500,0,30);
INSERT INTO EMP_NM VALUES(7876,'Adams','Clerk',7788,'03-DEC-89',950,NULL,30);
INSERT INTO EMP_NM VALUES(7902,'Ford','Analyst',7566,'04-DEC-81',3000,NULL,20);

DEPT
DEPTNO DNAME LOC
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston

4 rows selected.


CREATE TABLE 


CREATE TABLE DEPT_NM ( 
 DEPTNO NUMBER(2),
 DNAME varchar2(20),
 LOC varchar2(10)
 );

INSERT COMMAND:


INSERT INTO DEPT_NM VALUES(10,'Accounting','New York');
INSERT INTO DEPT_NM VALUES(20,'Research','Dallas');
INSERT INTO DEPT_NM VALUES(30,'Sales','Chicago');
INSERT INTO DEPT_NM VALUES(40,'Operations','Boston');

ITEM_MASTER

ITNO NAME QOH CLA UOM ROL ROQ RATE
1090 Hammer 234 A pcs 12 34 400.9
1089 Saw 456 B pcs 17 23 800.89
1088 Lawn Mover 123 C pcs 21 21 5000.88
1087 Dish Washer 234 A pcs 76 45 950.87
1067 Baking Oven 145 A pcs 87 34 6000.67
1063 Spark Plug 150 C watt 34 67 750.63
1609 Alternator 168 B watt 50 56 750.09
1890 Battery 189 A volt 30 40 300.9
1378 Piston 234 B pcs 45 50 250.78

9 rows selected.


CREATE TABLE


CREATE TABLE ITEM_MASTER (
ITNO NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
QOH NUMBER(5) DEFAULT 100,
CLASS VARCHAR2(1) NOT NULL CHECK (CLASS IN ('A','B','C')),
UOM VARCHAR2(4),
ROL NUMBER(5),
ROQ NUMBER(5),
RATE NUMBER(8,2) NOT NULL
);
OR 


CREATE TABLE ITEM_MASTER (
Itno number(4) primary key,
name varchar2(20) NOT NULL,
qoh number(5) DEFAULT 100,
class varchar2(1) NOT NULL,
uom varchar2(4),
rol number(5),
roq number(5),
rate number(8,2) NOT NULL,
CHECK (CLASS IN ('A','B','C'))
);


INSERT COMMAND:


INSERT INTO ITEM_MASTER VALUES (1090,'Hammer',234,'A','pcs',12,34,400.90);
INSERT INTO ITEM_MASTER VALUES (1089,'Saw',456,'B','pcs',17,23,800.89);
INSERT INTO ITEM_MASTER VALUES (1088,'Lawn Mover',123,'C','pcs',21,21,5000.88);
INSERT INTO ITEM_MASTER VALUES (1087,'Dish Washer',234,'A','pcs',76,45,950.87);
INSERT INTO ITEM_MASTER VALUES (1067,'Baking Oven',145,'A','pcs',87,34,6000.67);
INSERT INTO ITEM_MASTER VALUES (1063,'Spark Plug',150,'C','watt',34,67,750.63);
INSERT INTO ITEM_MASTER VALUES (1609,'Alternator',168,'B','watt',50,56,750.09);
INSERT INTO ITEM_MASTER VALUES (1890,'Battery',189,'A','volt',30,40,300.90);
INSERT INTO ITEM_MASTER VALUES (1378,'Piston',234,'B','pcs',45,50,250.78);


TRANSACTION

ITNO TYPE QTY RECEIPTNO DOT
1090 receive 500 A4333 01-JAN-09
1090 issue 100 A4336 23-FEB-10
1609 receive 215 A2143 23-FEB-10
1090 issue 150 A4343 12-MAR-10
1087 issue 300 B4143 01-SEP-10
1087 receive 50 A4143 16-DEC-10
1087 receive 50 A4144 20-DEC-10
1087 receive 50 A4145 30-DEC-10
1087 receive 50 A4149 11-JUN-11
1890 issue 25 C4143 15-JUL-11
1087 receive 50 A4151 20-JUL-11
1090 issue 200 A4133 01-AUG-11
1890 receive 15 C4113 10-SEP-11
1089 issue 125 C4041 19-SEP-11
1090 issue 120 A6336 25-SEP-11
1087 receive 50 A4155 26-SEP-11

16 rows selected.



CREATE TABLE:


CREATE TABLE TRANSACTION_NM (
ITNO NUMBER(4),
TYPE VARCHAR2(10),
QTY NUMBER(4),
RECEIPTNO VARCHAR2(20),
DOT DATE
);



INSERT COMMAND:


INSERT INTO TRANSACTION_NM VALUES(1090,'receive',500,'A4333','01-JAN-09');
INSERT INTO TRANSACTION_NM VALUES(1090,'issue',100,'A4336','23-FEB-10');
INSERT INTO TRANSACTION_NM VALUES(1609,'receive',215,'A2143','23-FEB-10');
INSERT INTO TRANSACTION_NM VALUES(1090,'issue',150,'A4343','12-MAR-10');
INSERT INTO TRANSACTION_NM VALUES(1087,'issue',300,'B4143','01-SEP-10');
INSERT INTO TRANSACTION_NM VALUES(1087,'receive',50,'A4143','16-DEC-10');
INSERT INTO TRANSACTION_NM VALUES(1087,'receive',50,'A4144','20-DEC-10');
INSERT INTO TRANSACTION_NM VALUES(1087,'receive',50,'A4145','30-DEC-10');
INSERT INTO TRANSACTION_NM VALUES(1087,'receive',50,'A4149','11-JUN-11');
INSERT INTO TRANSACTION_NM VALUES(1890,'issue',25,'C4143','15-JUL-11');
INSERT INTO TRANSACTION_NM VALUES(1087,'receive',50,'A4151','20-JUL-11');
INSERT INTO TRANSACTION_NM VALUES(1090,'issue',200,'A4133','01-AUG-11');
INSERT INTO TRANSACTION_NM VALUES(1890,'receive',15,'C4113','10-SEP-11');
INSERT INTO TRANSACTION_NM VALUES(1089,'issue',125,'C4041','19-SEP-11');
INSERT INTO TRANSACTION_NM VALUES(1090,'issue',120,'A6336','25-SEP-11');
INSERT INTO TRANSACTION_NM VALUES(1087,'receive',50,'A4155','26-SEP-11');