Nt1310 Unit 3 Assignment Pdf

Part 1

1. Write a script which will Create the needed tables as follows:
a. Table name : DEPT
i. Column datatype Mandatory? PK? FK? Deptno NUMBER(2) Yes Yes dname VARCHAR2(30) Yes No loc VARCHAR2(20) No No

Ans: CREATE TABLE dept (deptno NUMBER(2) NOT NULL, dname VARCHAR2(30) NOT NULL, loc VARCHAR2(20) CONSTRAINT deptno_pk PRIMARY KEY (deptno));

b. Table name: EMP
i. Column datatype Mandatory? PK? FK? Empno NUMBER(4) Yes Yes
Hiredate DATE No No Job VARCHAR2(15) No No Mgrno number(4) no no yes ( to emp.empno) Salary number(8,2) no no Comm number(8,2) no no Deptno number(2) no no yes ( to
Use SQL*Loader
For dept table:
Control File: load data infile 'dept.dat ' "var 3" into table dept fields terminated by ', ' optionally enclosed by '" ' (col1 NUMBER(2), col2 VARCHAR2(30), col3 VARCHAR2(20) )

From Command Prompt: SQLLDR CONTROL=dept.ctl, LOG=bar.log, BAD=baz.bad, DATA=dept.dat USERID=student/student, ERRORS=999, LOAD=2000, DISCARD=toss.dis, DISCARDMAX=5

For emp table:
Control File: load data infile 'emp.dat ' "var 3" into table emp fields terminated by ', ' optionally enclosed by '" ' (col1 NUMBER(4), col2 VARCHAR(2), col3 DATE, col4 VARCHAR2(15), col5 number(4), col6 number(8,2), col7 number(8,2), col8 number(2))

From Command Prompt: SQLLDR CONTROL=emp.ctl, LOG=bar.log, BAD=baz.bad, DATA=emp.dat USERID=student/student, ERRORS=999, LOAD=2000, DISCARD=toss.dis, DISCARDMAX=5

DEPT table
Deptno
Dname
Loc
10
Accounting
Boston
20
Sales