PROJECT_script1.txt

DROP TABLE EMP cascade constraints;

CREATE TABLE EMP
(
EMPLOYEE_NO	Number PRIMARY KEY,
FIRST_NAME	Varchar2(40),
LAST_NAME	Varchar2(40),
DEPT_ID	Number REFERENCES DEPT(DEPT_ID),
LOCATION_ID	Number REFERENCES LOC(LOCATION_ID),
MANAGER_ID	Number,
POSITION	Varchar2(40),
STATUS	Char(1),
SALARY	Number,
COMMISION_PCT	Number,
DOB	Date
) TABLESPACE "USERS" ;

DROP TABLE LOC;

CREATE TABLE LOC
(
LOCATION_ID	Number PRIMARY KEY,
CITY	VARCHAR2(40),
STATE	VARCHAR2(40),
COUNTRY	VARCHAR2(40))
TABLESPACE "USERS" ;

DROP TABLE COMM;

CREATE TABLE COMM
(EMPLOYEE_NO NUMBER REFERENCES EMP(EMPLOYEE_NO),
TYPE VARCHAR2(10),
PRIMARY KEY (EMPLOYEE_NO, TYPE),
DATA VARCHAR2(40)) TABLESPACE "USERS" ;

DROP TABLE FAM;

CREATE TABLE FAM
(EMPLOYEE_NO	Number REFERENCES EMP(EMPLOYEE_NO),
FAMILY_NO	number,
PRIMARY KEY (EMPLOYEE_NO, FAMILY_NO),
MEMBER 	CHAR(1),
LAST_NAME	Varchar2(40),
FIRST_NAME	Varchar2(40),
GENDER	CHAR(1),
DOB	DATE) TABLESPACE "USERS" ;

DROP TABLE TIME;

CREATE TABLE TIMEDB
(
EMPLOYEE_NO	Number,
WORK_DAY        DATE,
PRIMARY KEY (EMPLOYEE_NO, WORK_DAY),
WORK_HOURS      NUMBER
) TABLESPACE "USERS" ;

ALTER TABLE TIMEDB ADD CONSTRAINT FK_EMPNO_TIM FOREIGN KEY (EMPLOYEE_NO)
    REFERENCES EMP(EMPLOYEE_NO);


create or replace view EMP_SALARY
as
select e.EmpLOYEE_NO "ID" , substr(e.First_Name,1,20) "Name", substr(d.dept_name,1,20) "Department", substr(l.City,1,20) "City", substr(e.position,1,20) "Position", e.salary * 12 "Annual_Salary"
from emp e, dept d, loc l
where e.dept_id = d.dept_id
  and e.location_id = l.location_id
order by d.dept_name, l.city;


ALTER TABLE COMM ADD CONSTRAINT FK_EMPNO FOREIGN KEY (EMPLOYEE_NO)
    REFERENCES EMP(EMPLOYEE_NO);

ALTER TABLE FAMM ADD CONSTRAINT FK_EMPNO_FAM FOREIGN KEY (EMPLOYEE_NO)
    REFERENCES EMP(EMPLOYEE_NO);

insert into timedb
values
(1040,'4-APR-17',8);
insert into timedb
values
(1040,'3-APR-17',8);
insert into timedb
values
(1040,'2-APR-17',8);
insert into timedb
values
(1040,'1-APR-17',8);


SET LINESIZE 108
SET PAGESIZE 30
SET FEEDBACK OFF

COLUMN report_date_col NEW_VALUE report_date
col report_date_col noprint
SELECT TO_CHAR( SYSDATE ,'DD-Mon-YYYY') AS report_date_col FROM dual;

TTITLE CENTER 'Employee Annual Salary Report' SKIP 1 - 
LEFT 'Date : ' &report_date RIGHT 'Page:' SQL.PNO SKIP 2

BTITLE CENTER 'Confidential'

BREAK ON DEPARTMENT
--COLUMN LAST_NAME HEADING 'Last Name'
--COLUMN FIRST_NAME HEADING 'First Name'
COLUMN Annual_Salary  HEADING 'Annual Salary'
COLUMN Annual_Salary justify left format $9,99,990.00

select * from emp_salary ;

SET FEEDBACK ON
TTITLE OFF
BTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS




SELECT last_name ||' reports to '||
PRIOR LAST_NAME "Walk Top Down"
from emp
start with employee_no = 1039
CONNECT BY PRIOR EMPLOYEE_NO = MANAGER_ID;


select LPAD(last_name, LENGTH(LAST_NAME)+(LEVEL*2)-2,'=>')
AS ORG_CHART FROM EMP
START WITH employee_no = 1039
CONNECT BY PRIOR EMPLOYEE_NO = MANAGER_ID;

DROP TABLE PROJ_EMP;

CREATE DIRECTORY EMP_DIR_PROJA AS '/home/NPU_NETLAB/13026gk/txt';


CREATE TABLE PROJ_EMP
(EMPNO NUMBER,
 FNAME CHAR(20),
 LNAME CHAR(20),
 BIRTHDATE DATE)
 ORGANIZATION EXTERNAL 
(TYPE ORACLE_LOADER DEFAULT DIRECTORY EMP_DIR_PROJA
 ACCESS PARAMETERS 
(RECORDS DELIMITED BY NEWLINE 
 BADFILE 'BAD_PEMP'
 LOGFILE 'LOG_PEMP'
 FIELDS TERMINATED BY ','
(EMPNO CHAR, 
 FNAME CHAR,
 LNAME CHAR,
 BIRTHDATE CHAR DATE_FORMAT DATE MASK "DD-MON-YYYY")) LOCATION ('PROJEMP.TXT'))
PARALLEL 5
REJECT LIMIT 200;