-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathPROJECT_script3.txt
347 lines (268 loc) · 14.9 KB
/
PROJECT_script3.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
--------------------------------------------------------
-- SEQUENCES
--------------------------------------------------------
set linesize 115
set pagesize 30
DROP sequence EMP_EMP_NO_SEQ;
Create sequence EMP_EMP_NO_SEQ
INCREMENT BY 1
START WITH 1001
MAXVALUE 9999
NOCACHE
NOCYCLE;
DROP sequence DEPT_DEP_ID_SEQ ;
create sequence DEPT_DEP_ID_SEQ
INCREMENT BY 1
START WITH 11000
MAXVALUE 11099
NOCACHE
NOCYCLE;
--------------------------------------------------------
-- DDL for Table DEPT
--------------------------------------------------------
DROP TABLE DEPT cascade constraints;
CREATE TABLE DEPT
(DEPT_ID NUMBER PRIMARY KEY,
DEPT_NAME VARCHAR2(40)) TABLESPACE USERS;
Insert into DEPT (DEPT_ID,DEPT_NAME) values (11000,'ADMINISTRATION');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (11001,'MARKETING');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (11002,'SHIPPING');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (11003,'IT');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (11004,'SALES');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (11005,'HR');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (11006,'ACCOUNTING');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (11007,'CEO OFFICE');
COMMIT;
--------------------------------------------------------
-- DDL for Table LOC
--------------------------------------------------------
DROP TABLE LOC cascade constraints;
CREATE TABLE LOC
(
LOCATION_ID Number PRIMARY KEY,
CITY VARCHAR2(40),
STATE VARCHAR2(40),
COUNTRY VARCHAR2(40))
TABLESPACE "USERS" ;
Insert into LOC (LOCATION_ID,CITY,STATE,COUNTRY) values (101,'SAN JOSE','CA','USA');
Insert into LOC (LOCATION_ID,CITY,STATE,COUNTRY) values (102,'FREMONT','CA','USA');
Insert into LOC (LOCATION_ID,CITY,STATE,COUNTRY) values (103,'LA','CA','USA');
Insert into LOC (LOCATION_ID,CITY,STATE,COUNTRY) values (104,'NEW YORK','MANHATTAN','USA');
Insert into LOC (LOCATION_ID,CITY,STATE,COUNTRY) values (105,'LAS VEGAS','NEVADA','USA');
Insert into LOC (LOCATION_ID,CITY,STATE,COUNTRY) values (106,'ORLANDO','FLORIDA','USA');
COMMIT;
--------------------------------------------------------
-- DDL for Table EMP
--------------------------------------------------------
DROP TABLE EMP cascade constraints;
CREATE TABLE EMP
(
EMPLOYEE_NO Number PRIMARY KEY,
FIRST_NAME Varchar2(40),
LAST_NAME Varchar2(40),
DEPT_ID Number,
LOCATION_ID Number,
MANAGER_ID Number,
POSITION Varchar2(40),
STATUS Char(1),
SALARY Number,
COMMISION_PCT Number,
DOB Date ) TABLESPACE "USERS" ;
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1022,'JASON','TENG',11003,105,1036,'SOFTWARE MANAGER','A',4000,3,to_date('07-JUL-96','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1029,'WATSON','DENG',11005,103,1011,'HR MANAGER','A',4500,3,to_date('08-AUG-97','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1030,'VINCENT','WANG',11003,106,1022,'SOFTWARE DEVELOPER','A',5000,null,to_date('09-SEP-98','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1031,'GEORGE','CHEN',11006,102,1035,'ACCOUNT MANAGER','A',5500,null,to_date('10-OCT-99','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1032,'JODEN','HIGGINS',11002,101,1036,'LOGISTIC MANAGER','A',6000,null,to_date('11-NOV-00','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1033,'JERRY','XU',11004,106,1015,'SALAES EXECUTIVE','A',6500,null,to_date('12-DEC-01','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1034,'BILLY','SHI',11001,103,1035,'PRODUCT MANGER','A',7000,null,to_date('01-JAN-02','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1035,'IRIS','CHEN',11007,102,1039,'CFO','A',7500,null,to_date('02-FEB-03','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1036,'KIKO','SUN',11007,104,1039,'VP','A',8000,null,to_date('03-MAR-04','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1037,'IVY','WANG',11003,105,1022,'SOFTWARE DEVELOPER','A',8500,null,to_date('04-APR-05','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1038,'BEN','CHIENG',11003,101,1022,'QA','A',9000,null,to_date('05-MAY-06','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1039,'ESTELLA','PAN',11007,106,null,'CEO','A',9500,null,to_date('06-JUN-07','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1040,'PEGGY','KUO',11004,102,1033,'SALES SUPPORT','A',10000,null,to_date('07-JUL-08','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1041,'GRACE','LIN',11006,101,1031,'ACCOUNT EXECUTIVE','A',15000,null,to_date('08-AUG-09','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1011,'ELEN','ABEL',11000,101,1031,'SR.ADMIN MANAGER','A',1000,null,to_date('01-JAN-90','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1012,'PETER','WHITE',11001,102,1034,'MARKETING MANAGER','A',1500,null,to_date('02-FEB-91','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1013,'ALEX','LI',11002,103,1032,'LOGISTIC EXECUTIVE','A',2000,2,to_date('03-MAR-92','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1014,'AUSTIN','HO',11003,104,1022,'SR.DEVELOPER','A',2500,2,to_date('04-APR-93','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1015,'RAYSON','LEE',11004,105,1036,'SALES MANAGER','A',3000,null,to_date('05-MAY-94','DD-MON-RR'));
Insert into EMP (EMPLOYEE_NO,FIRST_NAME,LAST_NAME,DEPT_ID,LOCATION_ID,MANAGER_ID,POSITION,STATUS,SALARY,COMMISION_PCT,DOB)
values
(1016,'TINLOT','HU',11003,106,1022,'SYS.PROGRAMMER','A',3500,null,to_date('06-JUN-95','DD-MON-RR'));
COMMIT;
--------------------------------------------------------
-- DDL for Table FAM
--------------------------------------------------------
DROP TABLE FAM cascade constraints;
CREATE TABLE FAM
(EMPLOYEE_NO Number,
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" ;
ALTER TABLE FAM ADD CONSTRAINT FK_EMPNO_FAM FOREIGN KEY (EMPLOYEE_NO)
REFERENCES EMP(EMPLOYEE_NO);
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1011,2,'S','ABEL','ELEN','M',to_date('01-JAN-90','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1011,3,'D','ABEL','ELEN','F',to_date('01-JAN-90','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1012,1,'S','WHITE','PETER','M',to_date('02-FEB-91','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1014,1,'S','HO','AUSTIN','M',to_date('04-APR-93','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1015,1,'S','LEE','RAYSON','M',to_date('05-MAY-94','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1016,1,'S','HU','TINLOT','M',to_date('06-JUN-95','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1022,1,'S','TENG','JASON','M',to_date('07-JUL-96','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1029,1,'S','DENG','WATSON','M',to_date('08-AUG-97','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1030,1,'S','WANG','VINCENT','M',to_date('09-SEP-98','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1031,1,'S','CHEN','GEORGE','M',to_date('10-OCT-99','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1031,2,'W','CHEN','GEORGE','F',to_date('10-OCT-99','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1032,1,'W','HIGGINS','JODEN','F',to_date('11-NOV-00','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1033,1,'D','XU','JERRY','F',to_date('12-DEC-01','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1034,1,'S','SHI','BILLY','M',to_date('01-JAN-02','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1034,2,'W','SHI','BILLY','F',to_date('01-JAN-02','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1035,1,'S','CHEN','IRIS','M',to_date('02-FEB-03','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1035,2,'W','CHEN','IRIS','F',to_date('02-FEB-03','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1036,1,'D','SUN','KIKO','F',to_date('03-MAR-04','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1036,2,'S','SUN','KIKO','M',to_date('03-MAR-04','DD-MON-RR'));
Insert into FAM (EMPLOYEE_NO,FAMILY_NO,MEMBER,LAST_NAME,FIRST_NAME,GENDER,DOB) values (1011,1,'W','ABEL','ELEN','F',to_date('01-JAN-90','DD-MON-RR'));
COMMIT;
--------------------------------------------------------
-- DDL for Table COMM
--------------------------------------------------------
DROP TABLE COMM cascade constraints;
CREATE TABLE COMM
(EMPLOYEE_NO NUMBER,
TYPE VARCHAR2(10), PRIMARY KEY (EMPLOYEE_NO, TYPE),
DATA VARCHAR2(40)) TABLESPACE "USERS" ;
ALTER TABLE COMM ADD CONSTRAINT FK_EMPNO FOREIGN KEY (EMPLOYEE_NO)
REFERENCES EMP(EMPLOYEE_NO);
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1011,'EMAIL','elen23@gmail.com');
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1011,'TEL','501-456-2345');
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1012,'CELL','502-345-1243');
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1012,'FAX','542-456-2487');
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1013,'EMAIL','alex@yahoo.in');
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1013,'CELL','502-595-5689');
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1014,'FAX','542-850-5634');
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1015,'TEL','501-567-0945');
Insert into COMM (EMPLOYEE_NO,TYPE,DATA) values (1016,'CELL','502-890-7654');
COMMIT;
--------------------------------------------------------
-- DDL for Table TIMEDB
--------------------------------------------------------
DROP TABLE TIMEDB cascade constraints;
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);
Insert into TIMEDB (EMPLOYEE_NO,WORK_DAY,WORK_HOURS) values (1040,to_date('04-APR-17','DD-MON-RR'),8);
Insert into TIMEDB (EMPLOYEE_NO,WORK_DAY,WORK_HOURS) values (1040,to_date('03-APR-17','DD-MON-RR'),8);
Insert into TIMEDB (EMPLOYEE_NO,WORK_DAY,WORK_HOURS) values (1040,to_date('02-APR-17','DD-MON-RR'),8);
Insert into TIMEDB (EMPLOYEE_NO,WORK_DAY,WORK_HOURS) values (1040,to_date('01-APR-17','DD-MON-RR'),8);
COMMIT;
--------------------------------------------------------
-- View
--------------------------------------------------------
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;
--------------------------------------------------------
-- External Table
--------------------------------------------------------
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;
select * from proj_emp;
--------------------------------------------------------
-- Reports
--------------------------------------------------------
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;
select * from proj_emp;