ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/testsuite/resources/Test10.sql
Revision: 315
Committed: Thu Feb 25 11:56:36 2021 UTC (3 years, 2 months ago) by tony
Content type: application/sql
File size: 95066 byte(s)
Log Message: 
Updated for IBX 4 release

File Contents

# Content
1 SET SQL DIALECT 3;
2 SET AUTODDL ON;
3
4 CREATE DATABASE 'inet://localhost//tmp/ibx-testsuite/testsuite1.fdb' PAGE_SIZE 8192
5
6 DEFAULT CHARACTER SET NONE;
7 COMMENT ON DATABASE IS 'Employee Test DB';
8
9 /* Domain definitions */
10
11 CREATE DOMAIN ADDRESSLINE AS VARCHAR(30);
12 CREATE DOMAIN BUDGET AS DECIMAL(12, 2)
13 DEFAULT 50000;
14 CREATE DOMAIN COUNTRYNAME AS VARCHAR(15);
15 CREATE DOMAIN CUSTNO AS INTEGER;
16 CREATE DOMAIN DEPTNO AS CHAR(3);
17 CREATE DOMAIN EMPNO AS SMALLINT;
18 CREATE DOMAIN FB$OUT_TYPE AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80 NOT NULL;
19 CREATE DOMAIN FIRSTNAME AS VARCHAR(15);
20 CREATE DOMAIN JOBCODE AS VARCHAR(5);
21 CREATE DOMAIN JOBGRADE AS SMALLINT;
22 CREATE DOMAIN LASTNAME AS VARCHAR(20);
23 CREATE DOMAIN PHONENUMBER AS VARCHAR(20);
24 CREATE DOMAIN PONUMBER AS CHAR(8);
25 CREATE DOMAIN PRODTYPE AS VARCHAR(12)
26 DEFAULT 'software' NOT NULL;
27 CREATE DOMAIN PROJNO AS CHAR(5);
28 CREATE DOMAIN SALARY AS NUMERIC(10, 2)
29 DEFAULT 0;
30
31 /* Table: COUNTRIES, Owner: SYSDBA */
32
33 CREATE TABLE COUNTRIES
34 (
35 COUNTRY VARCHAR(64) NOT NULL,
36 NONEU SMALLINT,
37 ISO2 CHAR(2),
38 ISO3 CHAR(3),
39 PRIMARY KEY (COUNTRY)
40 );
41 COMMENT ON TABLE COUNTRIES IS 'List of Countries';
42 COMMENT ON COLUMN COUNTRIES.ISO2 IS 'Two Character ISO Country Code';
43
44 /* Table: COUNTRY, Owner: SYSDBA */
45
46 CREATE TABLE COUNTRY
47 (
48 COUNTRY COUNTRYNAME NOT NULL,
49 CURRENCY VARCHAR(10) NOT NULL,
50 PRIMARY KEY (COUNTRY)
51 );
52
53 /* Table: CUSTOMER, Owner: SYSDBA */
54
55 CREATE TABLE CUSTOMER
56 (
57 CUST_NO CUSTNO NOT NULL,
58 CUSTOMER VARCHAR(25) NOT NULL,
59 CONTACT_FIRST FIRSTNAME,
60 CONTACT_LAST LASTNAME,
61 PHONE_NO PHONENUMBER,
62 ADDRESS_LINE1 ADDRESSLINE,
63 ADDRESS_LINE2 ADDRESSLINE,
64 CITY VARCHAR(25),
65 STATE_PROVINCE VARCHAR(15),
66 COUNTRY COUNTRYNAME,
67 POSTAL_CODE VARCHAR(12),
68 ON_HOLD CHAR(1) DEFAULT NULL,
69 LONG_REFERENCE BIGINT,
70 PRIMARY KEY (CUST_NO)
71 );
72
73 /* Table: DEPARTMENT, Owner: SYSDBA */
74
75 CREATE TABLE DEPARTMENT
76 (
77 DEPT_NO DEPTNO NOT NULL,
78 DEPARTMENT VARCHAR(25) NOT NULL,
79 HEAD_DEPT DEPTNO,
80 MNGR_NO EMPNO,
81 BUDGET BUDGET,
82 LOCATION VARCHAR(15),
83 PHONE_NO PHONENUMBER DEFAULT '555-1234',
84 UNIQUE (DEPARTMENT),
85 PRIMARY KEY (DEPT_NO)
86 );
87
88 /* Table: EMPLOYEE, Owner: SYSDBA */
89
90 CREATE TABLE EMPLOYEE
91 (
92 EMP_NO EMPNO NOT NULL,
93 FIRST_NAME FIRSTNAME NOT NULL,
94 LAST_NAME LASTNAME NOT NULL,
95 PHONE_EXT VARCHAR(4),
96 HIRE_DATE TIMESTAMP DEFAULT 'NOW' NOT NULL,
97 DEPT_NO DEPTNO NOT NULL,
98 JOB_CODE JOBCODE NOT NULL,
99 JOB_GRADE JOBGRADE NOT NULL,
100 JOB_COUNTRY COUNTRYNAME NOT NULL,
101 SALARY SALARY NOT NULL,
102 FULL_NAME COMPUTED BY (last_name || ', ' || first_name),
103 PRIMARY KEY (EMP_NO)
104 );
105
106 /* Table: EMPLOYEE_PROJECT, Owner: SYSDBA */
107
108 CREATE TABLE EMPLOYEE_PROJECT
109 (
110 EMP_NO EMPNO NOT NULL,
111 PROJ_ID PROJNO NOT NULL,
112 PRIMARY KEY (EMP_NO, PROJ_ID)
113 );
114
115 /* Table: FB$OUT_TABLE, Owner: SYSDBA */
116
117 CREATE GLOBAL TEMPORARY TABLE FB$OUT_TABLE
118 (
119 LINE_NUM INTEGER,
120 CONTENT FB$OUT_TYPE
121 ) ON COMMIT PRESERVE ROWS ;
122
123 /* Table: ITEST, Owner: SYSDBA */
124
125 CREATE TABLE ITEST
126 (
127 "KEY" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0) NOT NULL,
128 SOMETEXT VARCHAR(64),
129 PRIMARY KEY ("KEY")
130 );
131
132 /* Table: ITEST2, Owner: SYSDBA */
133
134 CREATE TABLE ITEST2
135 (
136 "KEY" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0 INCREMENT BY 1) NOT NULL,
137 SOMETEXT VARCHAR(64),
138 PRIMARY KEY ("KEY")
139 );
140
141 /* Table: JOB, Owner: SYSDBA */
142
143 CREATE TABLE JOB
144 (
145 JOB_CODE JOBCODE NOT NULL,
146 JOB_GRADE JOBGRADE NOT NULL,
147 JOB_COUNTRY COUNTRYNAME NOT NULL,
148 JOB_TITLE VARCHAR(25) NOT NULL,
149 MIN_SALARY SALARY NOT NULL,
150 MAX_SALARY SALARY NOT NULL,
151 JOB_REQUIREMENT BLOB SUB_TYPE TEXT SEGMENT SIZE 400,
152 LANGUAGE_REQ VARCHAR(15)[1:5],
153 PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
154 );
155
156 /* Table: PROJECT, Owner: SYSDBA */
157
158 CREATE TABLE PROJECT
159 (
160 PROJ_ID PROJNO NOT NULL,
161 PROJ_NAME VARCHAR(20) NOT NULL,
162 PROJ_DESC BLOB SUB_TYPE TEXT SEGMENT SIZE 800,
163 TEAM_LEADER EMPNO,
164 PRODUCT PRODTYPE,
165 UNIQUE (PROJ_NAME),
166 PRIMARY KEY (PROJ_ID)
167 );
168
169 /* Table: PROJ_DEPT_BUDGET, Owner: SYSDBA */
170
171 CREATE TABLE PROJ_DEPT_BUDGET
172 (
173 FISCAL_YEAR INTEGER NOT NULL,
174 PROJ_ID PROJNO NOT NULL,
175 DEPT_NO DEPTNO NOT NULL,
176 QUART_HEAD_CNT INTEGER[1:4],
177 PROJECTED_BUDGET BUDGET,
178 PRIMARY KEY (FISCAL_YEAR, PROJ_ID, DEPT_NO)
179 );
180
181 /* Table: SALARY_HISTORY, Owner: SYSDBA */
182
183 CREATE TABLE SALARY_HISTORY
184 (
185 EMP_NO EMPNO NOT NULL,
186 CHANGE_DATE TIMESTAMP DEFAULT 'NOW' NOT NULL,
187 UPDATER_ID VARCHAR(20) NOT NULL,
188 OLD_SALARY SALARY NOT NULL,
189 PERCENT_CHANGE DOUBLE PRECISION DEFAULT 0 NOT NULL,
190 NEW_SALARY COMPUTED BY (old_salary + old_salary * percent_change / 100),
191 PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID)
192 );
193
194 /* Table: SALES, Owner: SYSDBA */
195
196 CREATE TABLE SALES
197 (
198 PO_NUMBER PONUMBER NOT NULL,
199 CUST_NO CUSTNO NOT NULL,
200 SALES_REP EMPNO,
201 ORDER_STATUS VARCHAR(7) DEFAULT 'new' NOT NULL,
202 ORDER_DATE TIMESTAMP DEFAULT 'NOW' NOT NULL,
203 SHIP_DATE TIMESTAMP,
204 DATE_NEEDED TIMESTAMP,
205 PAID CHAR(1) DEFAULT 'n',
206 QTY_ORDERED INTEGER DEFAULT 1 NOT NULL,
207 TOTAL_VALUE DECIMAL(9, 2) NOT NULL,
208 DISCOUNT FLOAT DEFAULT 0 NOT NULL,
209 ITEM_TYPE PRODTYPE,
210 AGED COMPUTED BY (ship_date - order_date),
211 PRIMARY KEY (PO_NUMBER)
212 );
213
214 /* External Function declarations */
215
216 DECLARE EXTERNAL FUNCTION ADDDAY
217 TIMESTAMP, INTEGER
218 RETURNS TIMESTAMP
219 ENTRY_POINT 'addDay' MODULE_NAME 'fbudf'
220
221 ;
222
223
224 DECLARE EXTERNAL FUNCTION I64TRUNCATE
225 NUMERIC(18, 0) BY DESCRIPTOR, NUMERIC(18, 0) BY DESCRIPTOR
226 RETURNS PARAMETER 2
227 ENTRY_POINT 'fbtruncate' MODULE_NAME 'fbudf'
228
229 ;
230
231
232 DECLARE EXTERNAL FUNCTION TESTIT
233 TIMESTAMP, INTEGER NULL, CSTRING(10) CHARACTER SET NONE, CSTRING(10) CHARACTER SET NONE BY DESCRIPTOR
234 RETURNS CSTRING(32) CHARACTER SET NONE FREE_IT
235 ENTRY_POINT 'testit' MODULE_NAME 'fbudf'
236
237 ;
238
239
240 /* Data Starts */
241
242 /* Inserting data into Table: COUNTRY */
243
244 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('USA','Dollar');
245 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('England','Pound');
246 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Canada','CdnDlr');
247 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Switzerland','SFranc');
248 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Japan','Yen');
249 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Italy','Euro');
250 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('France','Euro');
251 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Germany','Euro');
252 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Australia','ADollar');
253 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Hong Kong','HKDollar');
254 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Netherlands','Euro');
255 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Belgium','Euro');
256 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Austria','Euro');
257 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Fiji','FDollar');
258 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Russia','Ruble');
259 INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Romania','RLeu');
260 COMMIT;
261
262 /* Inserting data into Table: CUSTOMER */
263
264 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1001,'Signature Design','Dale J.','Little','(619) 530-2710','15500 Pacific Heights Blvd.',NULL,'San Diego','CA','USA','92121',NULL,NULL);
265 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1002,'Dallas Technologies','Glen','Brown','(214) 960-2233','P. O. Box 47000',NULL,'Dallas','TX','USA','75205','*',NULL);
266 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1003,'Buttle, Griffith and Co.','James','Buttle','(617) 488-1864','2300 Newbury Street','Suite 101','Boston','MA','USA','02115',NULL,NULL);
267 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1004,'Central Bank','Elizabeth','Brocket','61 211 99 88','66 Lloyd Street',NULL,'Manchester',NULL,'England','M2 3LA',NULL,NULL);
268 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1005,'DT Systems, LTD.','Tai','Wu','(852) 850 43 98','400 Connaught Road',NULL,'Central Hong Kong',NULL,'Hong Kong',NULL,NULL,NULL);
269 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1006,'DataServe International','Tomas','Bright','(613) 229 3323','2000 Carling Avenue','Suite 150','Ottawa','ON','Canada','K1V 9G1',NULL,NULL);
270 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1007,'Mrs. Beauvais',NULL,'Mrs. Beauvais',NULL,'P.O. Box 22743',NULL,'Pebble Beach','CA','USA','93953',NULL,NULL);
271 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1008,'Anini Vacation Rentals','Leilani','Briggs','(808) 835-7605','3320 Lawai Road',NULL,'Lihue','HI','USA','96766',NULL,NULL);
272 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1009,'Max','Max',NULL,'22 01 23','1 Emerald Cove',NULL,'Turtle Island',NULL,'Fiji',NULL,'*',NULL);
273 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1010,'MPM Corporation','Miwako','Miyamoto','3 880 77 19','2-64-7 Sasazuka',NULL,'Tokyo',NULL,'Japan','150',NULL,NULL);
274 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1011,'Dynamic Intelligence Corp','Victor','Granges','01 221 16 50','Florhofgasse 10',NULL,'Zurich',NULL,'Switzerland','8005',NULL,NULL);
275 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1012,'3D-Pad Corp.','Michelle','Roche','1 43 60 61','22 Place de la Concorde',NULL,'Paris',NULL,'France','75008',NULL,NULL);
276 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1013,'Lorenzi Export, Ltd.','Andreas','Lorenzi','02 404 6284','Via Eugenia, 15',NULL,'Milan',NULL,'Italy','20124',NULL,NULL);
277 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1014,'Dyno Consulting','Greta','Hessels','02 500 5940','Rue Royale 350',NULL,'Brussels',NULL,'Belgium','1210',NULL,NULL);
278 INSERT INTO CUSTOMER (CUST_NO,CUSTOMER,CONTACT_FIRST,CONTACT_LAST,PHONE_NO,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE_PROVINCE,COUNTRY,POSTAL_CODE,ON_HOLD,LONG_REFERENCE) VALUES(1015,'GeoTech Inc.','K.M.','Neppelenbroek','(070) 44 91 18','P.0.Box 702',NULL,'Den Haag',NULL,'Netherlands','2514',NULL,NULL);
279 COMMIT;
280
281 /* Inserting data into Table: DEPARTMENT */
282
283 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('000','Corporate Headquarters',NULL,105,1000000,'Monterey','(408) 555-1234');
284 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('100','Sales and Marketing','000',85,2000000,'San Francisco','(415) 555-1234');
285 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('600','Engineering','000',2,1100000,'Monterey','(408) 555-1234');
286 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('900','Finance','000',46,400000,'Monterey','(408) 555-1234');
287 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('180','Marketing','100',NULL,1500000,'San Francisco','(415) 555-1234');
288 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('620','Software Products Div.','600',NULL,1200000,'Monterey','(408) 555-1234');
289 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('621','Software Development','620',NULL,400000,'Monterey','(408) 555-1234');
290 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('622','Quality Assurance','620',9,300000,'Monterey','(408) 555-1234');
291 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('623','Customer Support','620',15,650000,'Monterey','(408) 555-1234');
292 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('670','Consumer Electronics Div.','600',107,1150000,'Burlington, VT','(802) 555-1234');
293 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('671','Research and Development','670',20,460000,'Burlington, VT','(802) 555-1234');
294 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('672','Customer Services','670',94,850000,'Burlington, VT','(802) 555-1234');
295 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('130','Field Office: East Coast','100',11,500000,'Boston','(617) 555-1234');
296 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('140','Field Office: Canada','100',72,500000,'Toronto','(416) 677-1000');
297 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('110','Pacific Rim Headquarters','100',34,600000,'Kuaui','(808) 555-1234');
298 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('115','Field Office: Japan','110',118,500000,'Tokyo','3 5350 0901');
299 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('116','Field Office: Singapore','110',NULL,300000,'Singapore','3 55 1234');
300 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('120','European Headquarters','100',36,700000,'London','71 235-4400');
301 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('121','Field Office: Switzerland','120',141,500000,'Zurich','1 211 7767');
302 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('123','Field Office: France','120',134,400000,'Cannes','58 68 11 12');
303 INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('125','Field Office: Italy','120',121,400000,'Milan','2 430 39 39');
304 COMMIT;
305
306 /* Inserting data into Table: EMPLOYEE */
307
308 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(2,'Robert','Nelson','250','1988.12.28 00:00:00.000','600','VP',2,'USA',105900);
309 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(4,'Bruce','Young','233','1988.12.28 00:00:00.000','621','Eng',2,'USA',97500);
310 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(5,'Kim','Lambert','22','1989.02.06 00:00:00.000','130','Eng',2,'USA',102750);
311 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(8,'Leslie','Johnson','410','1989.04.05 00:00:00.000','180','Mktg',3,'USA',64635);
312 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(9,'Phil','Forest','229','1989.04.17 00:00:00.000','622','Mngr',3,'USA',75060);
313 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(11,'K. J.','Weston','34','1990.01.17 00:00:00.000','130','SRep',4,'USA',86292.94);
314 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(12,'Terri','Lee','256','1990.05.01 00:00:00.000','000','Admin',4,'USA',53793);
315 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(14,'Stewart','Hall','227','1990.06.04 00:00:00.000','900','Finan',3,'USA',69482.63);
316 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(15,'Katherine','Young','231','1990.06.14 00:00:00.000','623','Mngr',3,'USA',67241.25);
317 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(20,'Chris','Papadopoulos','887','1990.01.01 00:00:00.000','671','Mngr',3,'USA',89655);
318 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(24,'Pete','Fisher','888','1990.09.12 00:00:00.000','671','Eng',3,'USA',81810.19);
319 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(28,'Ann','Bennet','5','1991.02.01 00:00:00.000','120','Admin',5,'England',22935);
320 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(29,'Roger','De Souza','288','1991.02.18 00:00:00.000','623','Eng',3,'USA',69482.63);
321 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(34,'Janet','Baldwin','2','1991.03.21 00:00:00.000','110','Sales',3,'USA',61637.8);
322 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(36,'Roger','Reeves','6','1991.04.25 00:00:00.000','120','Sales',3,'England',33620.63);
323 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(37,'Willie','Stansbury','7','1991.04.25 00:00:00.000','120','Eng',4,'England',39224.06);
324 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(44,'Leslie','Phong','216','1991.06.03 00:00:00.000','623','Eng',4,'USA',56034.38);
325 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(45,'Ashok','Ramanathan','209','1991.08.01 00:00:00.000','621','Eng',3,'USA',80689.5);
326 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(46,'Walter','Steadman','210','1991.08.09 00:00:00.000','900','CFO',1,'USA',116100);
327 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(52,'Carol','Nordstrom','420','1991.10.02 00:00:00.000','180','PRel',4,'USA',42742.5);
328 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(61,'Luke','Leung','3','1992.02.18 00:00:00.000','110','SRep',4,'USA',68805);
329 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(65,'Sue Anne','O''Brien','877','1992.03.23 00:00:00.000','670','Admin',5,'USA',31275);
330 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(71,'Jennifer M.','Burbank','289','1992.04.15 00:00:00.000','622','Eng',3,'USA',53167.5);
331 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(72,'Claudia','Sutherland',NULL,'1992.04.20 00:00:00.000','140','SRep',4,'Canada',100914);
332 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(83,'Dana','Bishop','290','1992.06.02 00:00:00.000','621','Eng',3,'USA',62550.02);
333 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(85,'Mary S.','MacDonald','477','1992.06.01 00:00:00.000','100','VP',2,'USA',111262.5);
334 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(94,'Randy','Williams','892','1992.08.08 00:00:00.000','672','Mngr',4,'USA',56295);
335 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(105,'Oliver H A','Bender','255','1992.10.08 00:00:00.000','000','CEO',1,'USA',212850);
336 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(107,'Kevin','Cook','894','1993.02.01 00:00:00.000','670','Dir',2,'USA',111262.5);
337 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(109,'Kelly','Brown','202','1993.02.04 00:00:00.000','600','Admin',5,'USA',27000.01);
338 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(110,'Yuki','Ichida','22','1993.02.04 00:00:00.000','115','Eng',3,'Japan',6000000);
339 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(113,'Mary','Page','845','1993.04.12 00:00:00.000','671','Eng',4,'USA',48000);
340 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(114,'Bill','Parker','247','1993.06.01 00:00:00.000','623','Eng',5,'USA',35000);
341 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(118,'Takashi','Yamamoto','23','1993.07.01 00:00:00.000','115','SRep',4,'Japan',7480000);
342 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(121,'Roberto','Ferrari','1','1993.07.12 00:00:00.000','125','SRep',4,'USA',33000.01);
343 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(127,'Michael','Yanowski','492','1993.08.09 00:00:00.000','100','SRep',4,'USA',44000);
344 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(134,'Jacques','Glon',NULL,'1993.08.23 00:00:00.000','123','SRep',4,'France',38500);
345 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(136,'Scott','Johnson','265','1993.09.13 00:00:00.000','623','Doc',3,'USA',60000);
346 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(138,'T.J.','Green','218','1993.11.01 00:00:00.000','621','Eng',4,'USA',36000);
347 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(141,'Pierre','Osborne',NULL,'1994.01.03 00:00:00.000','121','SRep',4,'Switzerland',110000);
348 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(144,'John','Montgomery','820','1994.03.30 00:00:00.000','672','Eng',5,'USA',35000);
349 INSERT INTO EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY) VALUES(145,'Mark','Guckenheimer','221','1994.05.02 00:00:00.000','622','Eng',5,'USA',32000);
350 COMMIT;
351
352 /* Inserting data into Table: EMPLOYEE_PROJECT */
353
354 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(144,'DGPII');
355 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(113,'DGPII');
356 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(24,'DGPII');
357 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(8,'VBASE');
358 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(136,'VBASE');
359 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(15,'VBASE');
360 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(71,'VBASE');
361 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(145,'VBASE');
362 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(44,'VBASE');
363 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(4,'VBASE');
364 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(83,'VBASE');
365 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(138,'VBASE');
366 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(45,'VBASE');
367 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(20,'GUIDE');
368 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(24,'GUIDE');
369 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(113,'GUIDE');
370 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(8,'GUIDE');
371 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(4,'MAPDB');
372 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(71,'MAPDB');
373 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(46,'MKTPR');
374 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(105,'MKTPR');
375 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(12,'MKTPR');
376 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(85,'MKTPR');
377 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(110,'MKTPR');
378 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(34,'MKTPR');
379 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(8,'MKTPR');
380 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(14,'MKTPR');
381 INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(52,'MKTPR');
382 COMMIT;
383
384 /* Inserting data into Table: ITEST */
385
386 INSERT INTO ITEST ("KEY",SOMETEXT) VALUES(1,'Testing');
387 INSERT INTO ITEST ("KEY",SOMETEXT) VALUES(3,'Another');
388 INSERT INTO ITEST ("KEY",SOMETEXT) VALUES(4,'Test');
389 INSERT INTO ITEST ("KEY",SOMETEXT) VALUES(7,'Test');
390 INSERT INTO ITEST ("KEY",SOMETEXT) VALUES(8,'Again');
391 INSERT INTO ITEST ("KEY",SOMETEXT) VALUES(9,'Test');
392 INSERT INTO ITEST ("KEY",SOMETEXT) VALUES(10,' 2');
393 COMMIT;
394
395 /* Inserting data into Table: JOB */
396
397 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('CEO',1,'USA','Chief Executive Officer',130000,250000,'No specific requirements.
398 ',NULL);
399 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('CFO',1,'USA','Chief Financial Officer',85000,140000,'15+ years in finance or 5+ years as a CFO
400 with a proven track record.
401 MBA or J.D. degree.
402 ',NULL);
403 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('VP',2,'USA','Vice President',80000,130000,'No specific requirements.
404 ',NULL);
405 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Dir',2,'USA','Director',75000,120000,'5-10 years as a director in computer or electronics industries.
406 An advanced degree.
407 ',NULL);
408 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Mngr',3,'USA','Manager',60000,100000,'BA/BS required.
409 3-5 years in management,
410 plus 2-4 years engineering experience.
411 ',NULL);
412 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Mngr',4,'USA','Manager',30000,60000,'5+ years office management experience.
413 ',NULL);
414 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Admin',4,'USA','Administrative Assistant',35000,55000,'3-5 years experience in executive environment.
415 Strong organizational and communication skills required.
416 BA degree preferred.
417 ',NULL);
418 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Admin',5,'USA','Administrative Assistant',20000,40000,'2-4 years clerical experience.
419 Facility with word processing and data entry.
420 AA degree preferred.
421 ',NULL);
422 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Admin',5,'England','Administrative Assistant',13400,26800,NULL,NULL);
423 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('PRel',4,'USA','Public Relations Rep.',25000,65000,NULL,NULL);
424 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Mktg',3,'USA','Marketing Analyst',40000,80000,'MBA required.
425 10+ years experience in high tech environment.
426 ',NULL);
427 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Mktg',4,'USA','Marketing Analyst',20000,50000,'BA/BS required. MBA preferred.
428 3-5 years experience.
429 Knowledgeable with spreadsheets and databases.
430 ',NULL);
431 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Accnt',4,'USA','Accountant',28000,55000,'CPA with 3-5 years experience.
432 Spreadsheet, data entry, and word processing knowledge required.
433 ',NULL);
434 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Finan',3,'USA','Financial Analyst',35000,85000,'5-10 years of accounting and financial experience.
435 Strong analytical skills.
436 CPA/MBA required.
437 ',NULL);
438 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Eng',2,'USA','Engineer',70000,110000,'Distinguished engineer.
439 Ph.D/MS/BS or equivalent experience.
440 ',NULL);
441 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Eng',3,'USA','Engineer',50000,90000,'5+ years experience.
442 BA/BS required.
443 MS degree preferred.
444 ',NULL);
445 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Eng',3,'Japan','Engineer',5400000,9720000,'5+ years experience.
446 BA/BS and/or MS degrees required.
447 Customer support experience desired.
448 Knowledge of Japanese and English.
449 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
450 <elt ix="1">Japanese
451 </elt>
452 <elt ix="2">Mandarin
453 </elt>
454 <elt ix="3">English
455 </elt>
456 <elt ix="4">
457 </elt>
458 <elt ix="5">
459 </elt>
460 </array>
461 );
462 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Eng',4,'USA','Engineer',30000,65000,'BA/BS and 3-5 years experience.
463 ',NULL);
464 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Eng',4,'England','Engineer',20100,43550,'BA/BS and
465 2-4 years experience in technical support.
466 Knowledge of several European languages helpful.
467 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
468 <elt ix="1">English
469 </elt>
470 <elt ix="2">German
471 </elt>
472 <elt ix="3">French
473 </elt>
474 <elt ix="4">
475 </elt>
476 <elt ix="5">
477 </elt>
478 </array>
479 );
480 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Eng',5,'USA','Engineer',25000,35000,'BA/BS preferred.
481 2-4 years technical experience.
482 ',NULL);
483 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Doc',3,'USA','Technical Writer',38000,60000,'4+ years writing highly technical
484 software documentation.
485 A bachelor''s degree or equivalent.
486 Programming experience required.
487 Excellent language skills.
488 ',NULL);
489 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Doc',5,'USA','Technical Writer',22000,40000,'BA in English/journalism or excellent language skills.
490 Some programming experience required.
491 2-4 years of technical writing.
492 ',NULL);
493 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Sales',3,'USA','Sales Co-ordinator',40000,70000,'Experience in sales and public relations
494 in a high tech environment.
495 Excellent communication skills.
496 BA or equivalent.
497 ',NULL);
498 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('Sales',3,'England','Sales Co-ordinator',26800,46900,'Experience in sales and public relations
499 in a high tech environment.
500 Excellent communication skills.
501 BA or equivalent.
502 Knowledge of several European languages helpful.
503 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
504 <elt ix="1">English
505 </elt>
506 <elt ix="2">German
507 </elt>
508 <elt ix="3">French
509 </elt>
510 <elt ix="4">
511 </elt>
512 <elt ix="5">
513 </elt>
514 </array>
515 );
516 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('SRep',4,'USA','Sales Representative',20000,100000,'Computer/electronics industry sales experience.
517 Excellent communications, negotiation, and analytical skills.
518 Experience in establishing long term customer relationships.
519 Some knowledge of Spanish required.
520 Travel required.
521 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
522 <elt ix="1">English
523 </elt>
524 <elt ix="2">Spanish
525 </elt>
526 <elt ix="3">
527 </elt>
528 <elt ix="4">
529 </elt>
530 <elt ix="5">
531 </elt>
532 </array>
533 );
534 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('SRep',4,'England','Sales Representative',13400,67000,'Computer/electronics industry sales experience.
535 Excellent communications, negotiation, and analytical skills.
536 Experience in establishing long term customer relationships.
537 Knowledge of several European languages helpful.
538 Travel required.
539 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
540 <elt ix="1">English
541 </elt>
542 <elt ix="2">German
543 </elt>
544 <elt ix="3">French
545 </elt>
546 <elt ix="4">
547 </elt>
548 <elt ix="5">
549 </elt>
550 </array>
551 );
552 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('SRep',4,'Canada','Sales Representative',26400,132000,'Computer/electronics industry sales experience.
553 Excellent communications, negotiation, and analytical skills.
554 Experience in establishing long term customer relationships.
555 Travel required.
556 English plus speaking knowledge of French required.
557 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
558 <elt ix="1">English
559 </elt>
560 <elt ix="2">French
561 </elt>
562 <elt ix="3">
563 </elt>
564 <elt ix="4">
565 </elt>
566 <elt ix="5">
567 </elt>
568 </array>
569 );
570 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('SRep',4,'Switzerland','Sales Representative',28000,149000,'Computer/electronics industry sales experience.
571 Excellent communications, negotiation, and analytical skills.
572 Experience in establishing long term customer relationships.
573 Knowledge of German required; one or more other European language helpful.
574 Travel required.
575 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
576 <elt ix="1">German
577 </elt>
578 <elt ix="2">French
579 </elt>
580 <elt ix="3">English
581 </elt>
582 <elt ix="4">Italian
583 </elt>
584 <elt ix="5">
585 </elt>
586 </array>
587 );
588 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('SRep',4,'Japan','Sales Representative',2160000,10800000,'Computer/electronics industry sales experience.
589 Excellent communications, negotiation, and analytical skills.
590 Experience in establishing long term customer relationships.
591 Knowledge of Japanese required.
592 Travel required.
593 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
594 <elt ix="1">Japanese
595 </elt>
596 <elt ix="2">English
597 </elt>
598 <elt ix="3">
599 </elt>
600 <elt ix="4">
601 </elt>
602 <elt ix="5">
603 </elt>
604 </array>
605 );
606 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('SRep',4,'Italy','Sales Representative',20000,100000,'Computer/electronics industry sales experience.
607 Excellent communications, negotiation, and analytical skills.
608 Experience in establishing long term customer relationships.
609 Fluency in Italian; some knowledge of German helpful.
610 Travel required.
611 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
612 <elt ix="1">Italian
613 </elt>
614 <elt ix="2">German
615 </elt>
616 <elt ix="3">French
617 </elt>
618 <elt ix="4">
619 </elt>
620 <elt ix="5">
621 </elt>
622 </array>
623 );
624 INSERT INTO JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY,JOB_TITLE,MIN_SALARY,MAX_SALARY,JOB_REQUIREMENT,LANGUAGE_REQ) VALUES('SRep',4,'France','Sales Representative',20000,100000,'Computer/electronics industry sales experience.
625 Excellent communications, negotiation, and analytical skills.
626 Experience in establishing long term customer relationships.
627 Fluency in French; some knowledge of German/Spanish helpful.
628 Travel required.
629 ',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
630 <elt ix="1">English
631 </elt>
632 <elt ix="2">French
633 </elt>
634 <elt ix="3">Spanish
635 </elt>
636 <elt ix="4">
637 </elt>
638 <elt ix="5">
639 </elt>
640 </array>
641 );
642 COMMIT;
643
644 /* Inserting data into Table: PROJECT */
645
646 INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('VBASE','Video Database','Design a video data base management system for
647 controlling on-demand video distribution.
648 ',45,'software');
649 INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('DGPII','DigiPizza','Develop second generation digital pizza maker
650 with flash-bake heating element and
651 digital ingredient measuring system.
652 ',24,'other');
653 INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('GUIDE','AutoMap','Develop a prototype for the automobile version of
654 the hand-held map browsing device.
655 ',20,'hardware');
656 INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('MAPDB','MapBrowser port','Port the map browsing database software to run
657 on the automobile model.
658 ',4,'software');
659 INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('HWRII','Translator upgrade','Integrate the hand-writing recognition module into the
660 universal language translator.
661 ',NULL,'software');
662 INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('MKTPR','Marketing project 3','Expand marketing and sales in the Pacific Rim.
663 Set up a field office in Australia and Singapore.
664 ',85,'N/A');
665 COMMIT;
666
667 /* Inserting data into Table: PROJ_DEPT_BUDGET */
668
669 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'GUIDE','100',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
670 <elt ix="1">1</elt>
671 <elt ix="2">1</elt>
672 <elt ix="3">1</elt>
673 <elt ix="4">0</elt>
674 </array>
675 ,200000);
676 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'GUIDE','671',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
677 <elt ix="1">3</elt>
678 <elt ix="2">2</elt>
679 <elt ix="3">1</elt>
680 <elt ix="4">0</elt>
681 </array>
682 ,450000);
683 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1993,'MAPDB','621',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
684 <elt ix="1">0</elt>
685 <elt ix="2">0</elt>
686 <elt ix="3">0</elt>
687 <elt ix="4">1</elt>
688 </array>
689 ,20000);
690 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'MAPDB','621',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
691 <elt ix="1">2</elt>
692 <elt ix="2">1</elt>
693 <elt ix="3">0</elt>
694 <elt ix="4">0</elt>
695 </array>
696 ,40000);
697 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'MAPDB','622',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
698 <elt ix="1">1</elt>
699 <elt ix="2">1</elt>
700 <elt ix="3">0</elt>
701 <elt ix="4">0</elt>
702 </array>
703 ,60000);
704 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'MAPDB','671',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
705 <elt ix="1">1</elt>
706 <elt ix="2">1</elt>
707 <elt ix="3">0</elt>
708 <elt ix="4">0</elt>
709 </array>
710 ,11000);
711 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'HWRII','670',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
712 <elt ix="1">1</elt>
713 <elt ix="2">1</elt>
714 <elt ix="3">1</elt>
715 <elt ix="4">1</elt>
716 </array>
717 ,20000);
718 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'HWRII','621',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
719 <elt ix="1">2</elt>
720 <elt ix="2">3</elt>
721 <elt ix="3">2</elt>
722 <elt ix="4">1</elt>
723 </array>
724 ,400000);
725 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'HWRII','622',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
726 <elt ix="1">1</elt>
727 <elt ix="2">1</elt>
728 <elt ix="3">2</elt>
729 <elt ix="4">2</elt>
730 </array>
731 ,100000);
732 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'MKTPR','623',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
733 <elt ix="1">1</elt>
734 <elt ix="2">1</elt>
735 <elt ix="3">1</elt>
736 <elt ix="4">2</elt>
737 </array>
738 ,80000);
739 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'MKTPR','672',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
740 <elt ix="1">1</elt>
741 <elt ix="2">1</elt>
742 <elt ix="3">1</elt>
743 <elt ix="4">2</elt>
744 </array>
745 ,100000);
746 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'MKTPR','100',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
747 <elt ix="1">4</elt>
748 <elt ix="2">5</elt>
749 <elt ix="3">6</elt>
750 <elt ix="4">6</elt>
751 </array>
752 ,1000000);
753 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'MKTPR','110',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
754 <elt ix="1">2</elt>
755 <elt ix="2">2</elt>
756 <elt ix="3">0</elt>
757 <elt ix="4">3</elt>
758 </array>
759 ,200000);
760 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'MKTPR','000',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
761 <elt ix="1">1</elt>
762 <elt ix="2">1</elt>
763 <elt ix="3">2</elt>
764 <elt ix="4">2</elt>
765 </array>
766 ,100000);
767 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1995,'MKTPR','623',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
768 <elt ix="1">7</elt>
769 <elt ix="2">7</elt>
770 <elt ix="3">4</elt>
771 <elt ix="4">4</elt>
772 </array>
773 ,1200000);
774 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1995,'MKTPR','672',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
775 <elt ix="1">2</elt>
776 <elt ix="2">3</elt>
777 <elt ix="3">3</elt>
778 <elt ix="4">3</elt>
779 </array>
780 ,800000);
781 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1995,'MKTPR','100',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
782 <elt ix="1">4</elt>
783 <elt ix="2">5</elt>
784 <elt ix="3">6</elt>
785 <elt ix="4">6</elt>
786 </array>
787 ,2000000);
788 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1995,'MKTPR','110',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
789 <elt ix="1">1</elt>
790 <elt ix="2">1</elt>
791 <elt ix="3">1</elt>
792 <elt ix="4">1</elt>
793 </array>
794 ,1200000);
795 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'VBASE','621',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
796 <elt ix="1">4</elt>
797 <elt ix="2">5</elt>
798 <elt ix="3">5</elt>
799 <elt ix="4">3</elt>
800 </array>
801 ,1900000);
802 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1995,'VBASE','621',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
803 <elt ix="1">4</elt>
804 <elt ix="2">3</elt>
805 <elt ix="3">2</elt>
806 <elt ix="4">2</elt>
807 </array>
808 ,900000);
809 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'VBASE','622',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
810 <elt ix="1">2</elt>
811 <elt ix="2">2</elt>
812 <elt ix="3">2</elt>
813 <elt ix="4">1</elt>
814 </array>
815 ,400000);
816 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1994,'VBASE','100',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
817 <elt ix="1">1</elt>
818 <elt ix="2">1</elt>
819 <elt ix="3">2</elt>
820 <elt ix="4">3</elt>
821 </array>
822 ,300000);
823 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1995,'VBASE','100',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
824 <elt ix="1">3</elt>
825 <elt ix="2">3</elt>
826 <elt ix="3">1</elt>
827 <elt ix="4">1</elt>
828 </array>
829 ,1500000);
830 INSERT INTO PROJ_DEPT_BUDGET (FISCAL_YEAR,PROJ_ID,DEPT_NO,QUART_HEAD_CNT,PROJECTED_BUDGET) VALUES(1996,'VBASE','100',<array dim = "1" sqltype = "496" length = "4" relation_name = "PROJ_DEPT_BUDGET" column_name = "QUART_HEAD_CNT" scale = "0" bounds="1:4">
831 <elt ix="1">1</elt>
832 <elt ix="2">1</elt>
833 <elt ix="3">0</elt>
834 <elt ix="4">0</elt>
835 </array>
836 ,150000);
837 COMMIT;
838
839 /* Inserting data into Table: SALARY_HISTORY */
840
841 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(28,'1992.12.15 00:00:00.000','admin2',20000,10);
842 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(2,'1992.12.15 00:00:00.000','admin2',98000,8.0612);
843 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(4,'1992.12.15 00:00:00.000','admin2',90000,8.3333);
844 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(5,'1992.12.15 00:00:00.000','admin2',95000,8.1578);
845 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(11,'1992.12.15 00:00:00.000','admin2',70000,7.5);
846 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(12,'1992.12.15 00:00:00.000','admin2',48000,7.5);
847 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(14,'1992.12.15 00:00:00.000','admin2',62000,7.5);
848 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(15,'1992.12.15 00:00:00.000','admin2',60000,7.5);
849 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(20,'1992.12.15 00:00:00.000','admin2',80000,7.5);
850 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(24,'1992.12.15 00:00:00.000','admin2',73000,7.5);
851 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(29,'1992.12.15 00:00:00.000','admin2',62000,7.5);
852 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(34,'1992.12.15 00:00:00.000','admin2',55000,7.5);
853 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(36,'1992.12.15 00:00:00.000','admin2',30000,7.5);
854 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(37,'1992.12.15 00:00:00.000','admin2',35000,7.5);
855 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(44,'1992.12.15 00:00:00.000','admin2',50000,7.5);
856 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(45,'1992.12.15 00:00:00.000','admin2',72000,7.5);
857 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(8,'1993.09.08 00:00:00.000','elaine',62000,4.25);
858 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(9,'1993.09.08 00:00:00.000','elaine',72000,4.25);
859 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(11,'1993.09.08 00:00:00.000','elaine',75250,4.25);
860 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(12,'1993.09.08 00:00:00.000','elaine',51600,4.25);
861 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(14,'1993.09.08 00:00:00.000','elaine',66650,4.25);
862 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(15,'1993.09.08 00:00:00.000','elaine',64500,4.25);
863 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(20,'1993.09.08 00:00:00.000','elaine',86000,4.25);
864 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(24,'1993.09.08 00:00:00.000','elaine',78475,4.25);
865 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(28,'1993.09.08 00:00:00.000','elaine',22000,4.25);
866 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(29,'1993.09.08 00:00:00.000','elaine',66650,4.25);
867 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(34,'1993.09.08 00:00:00.000','elaine',59125,4.2499);
868 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(36,'1993.09.08 00:00:00.000','elaine',32250,4.25);
869 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(37,'1993.09.08 00:00:00.000','elaine',37625,4.2499);
870 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(44,'1993.09.08 00:00:00.000','elaine',53750,4.25);
871 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(45,'1993.09.08 00:00:00.000','elaine',77400,4.25);
872 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(52,'1993.09.08 00:00:00.000','elaine',41000,4.25);
873 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(61,'1993.09.08 00:00:00.000','elaine',60000,4.25);
874 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(65,'1993.09.08 00:00:00.000','elaine',30000,4.25);
875 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(71,'1993.09.08 00:00:00.000','elaine',51000,4.25);
876 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(72,'1993.09.08 00:00:00.000','elaine',88000,4.25);
877 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(83,'1993.09.08 00:00:00.000','elaine',60000,4.25);
878 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(94,'1993.09.08 00:00:00.000','elaine',54000,4.25);
879 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(46,'1993.12.20 00:00:00.000','tj',120000,-3.25);
880 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(85,'1993.12.20 00:00:00.000','tj',115000,-3.25);
881 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(105,'1993.12.20 00:00:00.000','tj',220000,-3.25);
882 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(107,'1993.12.20 00:00:00.000','tj',115000,-3.25);
883 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(11,'1993.12.20 00:00:00.000','elaine',78448.13,9.9999);
884 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(61,'1993.12.20 00:00:00.000','elaine',62550,10);
885 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(72,'1993.12.20 00:00:00.000','elaine',91740,10);
886 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(118,'1993.12.20 00:00:00.000','elaine',6800000,10);
887 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(121,'1993.12.20 00:00:00.000','elaine',30000,10);
888 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(127,'1993.12.20 00:00:00.000','elaine',40000,10);
889 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(134,'1993.12.20 00:00:00.000','elaine',35000,10);
890 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(28,'2017.11.06 16:46:27.145','SYSDBA',22935,0);
891 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(28,'2017.11.06 16:47:01.376','SYSDBA',22935.01,0);
892 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(12,'2017.12.29 12:18:48.909','SYSDBA',53793,0);
893 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(12,'2017.12.29 12:18:58.051','SYSDBA',53793.01,0);
894 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(83,'2018.01.04 10:57:34.245','SYSDBA',62550,0);
895 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(121,'2018.03.13 15:29:08.850','SYSDBA',33000,0);
896 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(109,'2018.03.13 17:12:06.340','SYSDBA',27000,0);
897 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(34,'2018.03.13 17:13:20.671','SYSDBA',61637.81,0);
898 INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES(83,'2018.03.13 17:13:35.845','SYSDBA',62550.01,0);
899 COMMIT;
900
901 /* Inserting data into Table: SALES */
902
903 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V91E0210',1004,11,'shipped','1991.03.04 00:00:00.000','1991.03.05 00:00:00.000',NULL,'y',10,5000,0.100000001490116,'hardware');
904 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V92E0340',1004,11,'shipped','1992.10.15 00:00:00.000','1992.10.16 00:00:00.000','1992.10.17 00:00:00.000','y',7,70000,0,'hardware');
905 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V92J1003',1010,61,'shipped','1992.07.26 00:00:00.000','1992.08.04 00:00:00.000','1992.09.15 00:00:00.000','y',15,2985,0,'software');
906 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93J2004',1010,118,'shipped','1993.10.30 00:00:00.000','1993.12.02 00:00:00.000','1993.11.15 00:00:00.000','y',3,210,0,'software');
907 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93J3100',1010,118,'shipped','1993.08.20 00:00:00.000','1993.08.20 00:00:00.000',NULL,'y',16,18000.4,0.100000001490116,'software');
908 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V92F3004',1012,11,'shipped','1992.10.15 00:00:00.000','1993.01.16 00:00:00.000','1993.01.16 00:00:00.000','y',3,2000,0,'software');
909 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93F3088',1012,134,'shipped','1993.08.27 00:00:00.000','1993.09.08 00:00:00.000',NULL,'n',10,10000,0,'software');
910 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93F2030',1012,134,'open','1993.12.12 00:00:00.000',NULL,NULL,'y',15,450000.49,0,'hardware');
911 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93F2051',1012,134,'waiting','1993.12.18 00:00:00.000',NULL,'1994.03.01 00:00:00.000','n',1,999.98,0,'software');
912 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93H0030',1005,118,'open','1993.12.12 00:00:00.000',NULL,'1994.01.01 00:00:00.000','y',20,5980,0.200000002980232,'software');
913 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V94H0079',1005,61,'open','1994.02.13 00:00:00.000',NULL,'1994.04.20 00:00:00.000','n',10,9000,0.0500000007450581,'software');
914 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9324200',1001,72,'shipped','1993.08.09 00:00:00.000','1993.08.09 00:00:00.000','1993.08.17 00:00:00.000','y',1000,560000,0.200000002980232,'hardware');
915 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9324320',1001,127,'shipped','1993.08.16 00:00:00.000','1993.08.16 00:00:00.000','1993.09.01 00:00:00.000','y',1,0,1,'software');
916 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9320630',1001,127,'open','1993.12.12 00:00:00.000',NULL,'1993.12.15 00:00:00.000','n',3,60000,0.200000002980232,'hardware');
917 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9420099',1001,127,'open','1994.01.17 00:00:00.000',NULL,'1994.06.01 00:00:00.000','n',100,3399.15,0.150000005960464,'software');
918 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9427029',1001,127,'shipped','1994.02.07 00:00:00.000','1994.02.10 00:00:00.000','1994.02.10 00:00:00.000','n',17,422210.97,0,'hardware');
919 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9333005',1002,11,'shipped','1993.02.03 00:00:00.000','1993.03.03 00:00:00.000',NULL,'y',2,600.5,0,'software');
920 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9333006',1002,11,'shipped','1993.04.27 00:00:00.000','1993.05.02 00:00:00.000','1993.05.02 00:00:00.000','n',5,20000,0,'other');
921 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9336100',1002,11,'waiting','1993.12.27 00:00:00.000','1994.01.01 00:00:00.000','1994.01.01 00:00:00.000','n',150,14850,0.0500000007450581,'software');
922 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9346200',1003,11,'waiting','1993.12.31 00:00:00.000',NULL,'1994.01.24 00:00:00.000','n',3,0,1,'software');
923 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9345200',1003,11,'shipped','1993.11.11 00:00:00.000','1993.12.02 00:00:00.000','1993.12.01 00:00:00.000','y',900,27000,0.300000011920929,'software');
924 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9345139',1003,127,'shipped','1993.09.09 00:00:00.000','1993.09.20 00:00:00.000','1993.10.01 00:00:00.000','y',20,12582.12,0.100000001490116,'software');
925 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93C0120',1006,72,'shipped','1993.03.22 00:00:00.000','1993.05.31 00:00:00.000','1993.04.17 00:00:00.000','y',1,47.5,0,'other');
926 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93C0990',1006,72,'shipped','1993.08.09 00:00:00.000','1993.09.02 00:00:00.000',NULL,'y',40,399960.5,0.100000001490116,'hardware');
927 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V9456220',1007,127,'open','1994.01.04 00:00:00.000',NULL,'1994.01.30 00:00:00.000','y',1,3999.99,0,'hardware');
928 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93S4702',1011,121,'shipped','1993.10.27 00:00:00.000','1993.10.28 00:00:00.000','1993.12.15 00:00:00.000','y',4,120000,0,'hardware');
929 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V94S6400',1011,141,'waiting','1994.01.06 00:00:00.000',NULL,'1994.02.15 00:00:00.000','y',20,1980.72,0.400000005960464,'software');
930 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93H3009',1008,61,'shipped','1993.08.01 00:00:00.000','1993.12.02 00:00:00.000','1993.12.01 00:00:00.000','n',3,9000,0.0500000007450581,'software');
931 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93H0500',1008,61,'open','1993.12.12 00:00:00.000',NULL,'1993.12.15 00:00:00.000','n',3,16000,0.200000002980232,'hardware');
932 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93F0020',1009,61,'shipped','1993.10.10 00:00:00.000','1993.11.11 00:00:00.000','1993.11.11 00:00:00.000','n',1,490.69,0,'software');
933 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93I4700',1013,121,'open','1993.10.27 00:00:00.000',NULL,'1993.12.15 00:00:00.000','n',5,2693,0,'hardware');
934 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93B1002',1014,134,'shipped','1993.09.20 00:00:00.000','1993.09.21 00:00:00.000','1993.09.25 00:00:00.000','y',1,100.02,0,'software');
935 INSERT INTO SALES (PO_NUMBER,CUST_NO,SALES_REP,ORDER_STATUS,ORDER_DATE,SHIP_DATE,DATE_NEEDED,PAID,QTY_ORDERED,TOTAL_VALUE,DISCOUNT,ITEM_TYPE) VALUES('V93N5822',1015,134,'shipped','1993.12.18 00:00:00.000','1994.01.14 00:00:00.000',NULL,'n',2,1500,0,'software');
936 COMMIT;
937 /* Data Ends */
938
939 /* Index definitions for all user tables */
940
941 CREATE INDEX CUSTNAMEX ON CUSTOMER(CUSTOMER);
942 CREATE INDEX CUSTREGION ON CUSTOMER(COUNTRY, CITY);
943 CREATE DESCENDING INDEX BUDGETX ON DEPARTMENT(BUDGET);
944 CREATE INDEX NAMEX ON EMPLOYEE(LAST_NAME, FIRST_NAME);
945 CREATE DESCENDING INDEX MAXSALX ON JOB(JOB_COUNTRY, MAX_SALARY);
946 CREATE INDEX MINSALX ON JOB(JOB_COUNTRY, MIN_SALARY);
947 CREATE UNIQUE INDEX PRODTYPEX ON PROJECT(PRODUCT, PROJ_NAME);
948 CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY(CHANGE_DATE);
949 CREATE INDEX UPDATERX ON SALARY_HISTORY(UPDATER_ID);
950 CREATE INDEX NEEDX ON SALES(DATE_NEEDED);
951 CREATE DESCENDING INDEX QTYX ON SALES(ITEM_TYPE, QTY_ORDERED);
952 CREATE INDEX SALESTATX ON SALES(ORDER_STATUS, PAID);
953 ALTER TABLE CUSTOMER ADD FOREIGN KEY (COUNTRY) REFERENCES COUNTRY (COUNTRY);
954 ALTER TABLE DEPARTMENT ADD FOREIGN KEY (HEAD_DEPT) REFERENCES DEPARTMENT (DEPT_NO);
955 ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MNGR_NO) REFERENCES EMPLOYEE (EMP_NO);
956 ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);
957 ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY) REFERENCES JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY);
958 ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
959 ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);
960 ALTER TABLE JOB ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);
961 ALTER TABLE PROJECT ADD FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE (EMP_NO);
962 ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);
963 ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);
964 ALTER TABLE SALARY_HISTORY ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
965 ALTER TABLE SALES ADD FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER (CUST_NO);
966 ALTER TABLE SALES ADD FOREIGN KEY (SALES_REP) REFERENCES EMPLOYEE (EMP_NO);
967
968 CREATE SEQUENCE CUST_NO_GEN;
969 ALTER SEQUENCE CUST_NO_GEN RESTART WITH 1016;
970 CREATE SEQUENCE EMP_NO_GEN;
971 ALTER SEQUENCE EMP_NO_GEN RESTART WITH 158;
972 CREATE SEQUENCE FB$OUT_SEQ;
973 ALTER SEQUENCE FB$OUT_SEQ RESTART WITH 1;
974
975 /* View: DEPTLIST, Owner: SYSDBA */
976
977 CREATE VIEW DEPTLIST (
978 EMP_NO,
979 FIRST_NAME,
980 LAST_NAME,
981 PHONE_EXT,
982 HIRE_DATE,
983 DEPT_NO,
984 JOB_CODE,
985 JOB_GRADE,
986 JOB_COUNTRY,
987 SALARY,
988 FULL_NAME,
989 DEPT_PATH,
990 DEPT_KEY_PATH
991 ) AS
992 with recursive Depts As (
993 Select DEPT_NO, DEPARTMENT, HEAD_DEPT, cast(DEPARTMENT as VarChar(256)) as DEPT_PATH,
994 cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH
995 From DEPARTMENT Where HEAD_DEPT is NULL
996 UNION ALL
997 Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH || ' / ' || D.DEPARTMENT as DEPT_PATH,
998 Depts.DEPT_KEY_PATH || ';' || D.DEPT_NO as DEPT_KEY_PATH
999 From DEPARTMENT D
1000 JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO
1001 )
1002
1003 Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE,
1004 A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH
1005 From EMPLOYEE A
1006 JOIN Depts D On D.DEPT_NO = A.DEPT_NO
1007 ;
1008
1009 /* View: PHONE_LIST, Owner: SYSDBA */
1010
1011 CREATE VIEW PHONE_LIST (
1012 EMP_NO,
1013 FIRST_NAME,
1014 LAST_NAME,
1015 PHONE_EXT,
1016 LOCATION,
1017 PHONE_NO
1018 ) AS
1019 SELECT
1020 emp_no, first_name, last_name, phone_ext, location, phone_no
1021 FROM employee, department
1022 WHERE employee.dept_no = department.dept_no
1023 ;
1024
1025 /* Add Domain Check Constraints */
1026
1027
1028 ALTER DOMAIN BUDGET ADD CONSTRAINT
1029 CHECK (VALUE > 10000 AND VALUE <= 2000000);
1030
1031 ALTER DOMAIN CUSTNO ADD CONSTRAINT
1032 CHECK (VALUE > 1000);
1033 ALTER DOMAIN DEPTNO ADD CONSTRAINT
1034 CHECK (VALUE = '000' OR (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL);
1035
1036
1037
1038 ALTER DOMAIN JOBCODE ADD CONSTRAINT
1039 CHECK (VALUE > '99999');
1040 ALTER DOMAIN JOBGRADE ADD CONSTRAINT
1041 CHECK (VALUE BETWEEN 0 AND 6);
1042
1043
1044 ALTER DOMAIN PONUMBER ADD CONSTRAINT
1045 CHECK (VALUE STARTING WITH 'V');
1046 ALTER DOMAIN PRODTYPE ADD CONSTRAINT
1047 CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));
1048 ALTER DOMAIN PROJNO ADD CONSTRAINT
1049 CHECK (VALUE = UPPER (VALUE));
1050 ALTER DOMAIN SALARY ADD CONSTRAINT
1051 CHECK (VALUE > 0);
1052 ALTER TABLE JOB ADD
1053 CHECK (min_salary < max_salary);
1054
1055 ALTER TABLE JOB ADD
1056 CHECK (min_salary < max_salary);
1057
1058 ALTER TABLE EMPLOYEE ADD
1059 CHECK ( salary >= (SELECT min_salary FROM job WHERE
1060 job.job_code = employee.job_code AND
1061 job.job_grade = employee.job_grade AND
1062 job.job_country = employee.job_country) AND
1063 salary <= (SELECT max_salary FROM job WHERE
1064 job.job_code = employee.job_code AND
1065 job.job_grade = employee.job_grade AND
1066 job.job_country = employee.job_country));
1067
1068 ALTER TABLE PROJ_DEPT_BUDGET ADD
1069 CHECK (FISCAL_YEAR >= 1993);
1070
1071 ALTER TABLE SALARY_HISTORY ADD
1072 CHECK (percent_change between -50 and 50);
1073
1074 ALTER TABLE CUSTOMER ADD
1075 CHECK (on_hold IS NULL OR on_hold = '*');
1076
1077 ALTER TABLE SALES ADD
1078 CHECK (order_status in
1079 ('new', 'open', 'shipped', 'waiting'));
1080
1081 ALTER TABLE SALES ADD
1082 CHECK (ship_date >= order_date OR ship_date IS NULL);
1083
1084 ALTER TABLE SALES ADD
1085 CHECK (date_needed > order_date OR date_needed IS NULL);
1086
1087 ALTER TABLE SALES ADD
1088 CHECK (paid in ('y', 'n'));
1089
1090 ALTER TABLE SALES ADD
1091 CHECK (qty_ordered >= 1);
1092
1093 ALTER TABLE SALES ADD
1094 CHECK (total_value >= 0);
1095
1096 ALTER TABLE SALES ADD
1097 CHECK (discount >= 0 AND discount <= 1);
1098
1099 ALTER TABLE SALES ADD
1100 CHECK (NOT (order_status = 'shipped' AND ship_date IS NULL));
1101
1102 ALTER TABLE SALES ADD
1103 CHECK (NOT (order_status = 'shipped' AND
1104 EXISTS (SELECT on_hold FROM customer
1105 WHERE customer.cust_no = sales.cust_no
1106 AND customer.on_hold = '*')));
1107
1108
1109 /* Exceptions */
1110
1111 CREATE EXCEPTION CUSTOMER_CHECK 'Overdue balance -- can not ship.';
1112 CREATE EXCEPTION CUSTOMER_ON_HOLD 'This customer is on hold.';
1113 CREATE EXCEPTION ORDER_ALREADY_SHIPPED 'Order status is "shipped."';
1114 CREATE EXCEPTION REASSIGN_SALES 'Reassign the sales records before deleting this employee.';
1115 CREATE EXCEPTION UNKNOWN_EMP_ID 'Invalid employee number or project id.';
1116 COMMIT WORK;
1117 SET AUTODDL OFF;
1118 SET TERM ^ ;
1119
1120 /* Package Definitions */
1121
1122 CREATE PACKAGE FB$OUT
1123 AS
1124
1125 begin
1126 procedure enable;
1127 procedure disable;
1128
1129 procedure put_line (line fb$out_type);
1130 procedure clear;
1131
1132 procedure get_lines returns (lines fb$out_type);
1133 end
1134 ^
1135
1136 CREATE PACKAGE SELECT$TEST
1137 AS
1138
1139 Begin
1140 Procedure ShowItems(IPARAM Integer) RETURNS (OutParam integer);
1141 End
1142 ^
1143
1144 SET TERM ; ^
1145 COMMIT WORK;
1146 SET AUTODDL ON;
1147 COMMIT WORK;
1148 SET AUTODDL OFF;
1149 SET TERM ^ ;
1150
1151 /* Stored procedures Definitions*/
1152
1153 CREATE PROCEDURE SHOW_LANGS
1154 (
1155 CODE VARCHAR(5) CHARACTER SET NONE,
1156 GRADE SMALLINT,
1157 CTY VARCHAR(15) CHARACTER SET NONE
1158 )
1159 RETURNS
1160 (
1161 LANGUAGES VARCHAR(15) CHARACTER SET NONE
1162 )
1163 AS
1164 BEGIN SUSPEND; EXIT; END
1165
1166 ^
1167
1168 CREATE PROCEDURE ADD_EMP_PROJ
1169 (
1170 EMP_NO SMALLINT,
1171 PROJ_ID CHAR(5) CHARACTER SET NONE
1172 )
1173 AS
1174 BEGIN EXIT; END
1175
1176 ^
1177
1178 CREATE PROCEDURE ALL_LANGS
1179 RETURNS
1180 (
1181 CODE VARCHAR(5) CHARACTER SET NONE,
1182 GRADE VARCHAR(5) CHARACTER SET NONE,
1183 COUNTRY VARCHAR(15) CHARACTER SET NONE,
1184 LANG VARCHAR(15) CHARACTER SET NONE
1185 )
1186 AS
1187 BEGIN SUSPEND; EXIT; END
1188
1189 ^
1190
1191 CREATE PROCEDURE DELETE_EMPLOYEE
1192 (
1193 EMP_NUM INTEGER
1194 )
1195 AS
1196 BEGIN EXIT; END
1197
1198 ^
1199
1200 CREATE PROCEDURE DEPT_BUDGET
1201 (
1202 DNO CHAR(3) CHARACTER SET NONE
1203 )
1204 RETURNS
1205 (
1206 TOT DECIMAL(12, 2)
1207 )
1208 AS
1209 BEGIN SUSPEND; EXIT; END
1210
1211 ^
1212
1213 CREATE PROCEDURE GET_EMP_PROJ
1214 (
1215 EMP_NO SMALLINT
1216 )
1217 RETURNS
1218 (
1219 PROJ_ID CHAR(5) CHARACTER SET NONE
1220 )
1221 AS
1222 BEGIN SUSPEND; EXIT; END
1223
1224 ^
1225
1226 CREATE PROCEDURE "Has Space"
1227 (
1228 ARG1 INTEGER
1229 )
1230 AS
1231 BEGIN EXIT; END
1232
1233 ^
1234
1235 CREATE PROCEDURE MAIL_LABEL
1236 (
1237 CUST_NO INTEGER
1238 )
1239 RETURNS
1240 (
1241 LINE1 CHAR(40) CHARACTER SET NONE,
1242 LINE2 CHAR(40) CHARACTER SET NONE,
1243 LINE3 CHAR(40) CHARACTER SET NONE,
1244 LINE4 CHAR(40) CHARACTER SET NONE,
1245 LINE5 CHAR(40) CHARACTER SET NONE,
1246 LINE6 CHAR(40) CHARACTER SET NONE
1247 )
1248 AS
1249 BEGIN SUSPEND; EXIT; END
1250
1251 ^
1252
1253 CREATE PROCEDURE ORG_CHART
1254 RETURNS
1255 (
1256 HEAD_DEPT CHAR(25) CHARACTER SET NONE,
1257 DEPARTMENT CHAR(25) CHARACTER SET NONE,
1258 MNGR_NAME CHAR(20) CHARACTER SET NONE,
1259 TITLE CHAR(5) CHARACTER SET NONE,
1260 EMP_CNT INTEGER
1261 )
1262 AS
1263 BEGIN SUSPEND; EXIT; END
1264
1265 ^
1266
1267 CREATE PROCEDURE SHIP_ORDER
1268 (
1269 PO_NUM CHAR(8) CHARACTER SET NONE
1270 )
1271 AS
1272 BEGIN EXIT; END
1273
1274 ^
1275
1276 CREATE PROCEDURE SUB_TOT_BUDGET
1277 (
1278 HEAD_DEPT CHAR(3) CHARACTER SET NONE
1279 )
1280 RETURNS
1281 (
1282 TOT_BUDGET DECIMAL(12, 2),
1283 AVG_BUDGET DECIMAL(12, 2),
1284 MIN_BUDGET DECIMAL(12, 2),
1285 MAX_BUDGET DECIMAL(12, 2)
1286 )
1287 AS
1288 BEGIN SUSPEND; EXIT; END
1289
1290 ^
1291
1292 CREATE PROCEDURE "UC SPACE"
1293 (
1294 ARG1 INTEGER
1295 )
1296 AS
1297 BEGIN EXIT; END
1298
1299 ^
1300
1301 CREATE PROCEDURE "iCASE"
1302 AS
1303 BEGIN EXIT; END
1304
1305 ^
1306
1307 SET TERM ; ^
1308 COMMIT WORK;
1309 SET AUTODDL ON;
1310 COMMIT WORK;
1311 SET AUTODDL OFF;
1312 SET TERM ^ ;
1313
1314 /* Stored Function declarations */
1315
1316 CREATE FUNCTION F (X INTEGER)
1317 RETURNS INTEGER
1318 AS BEGIN END
1319
1320 ^
1321
1322
1323 SET TERM ; ^
1324 COMMIT WORK;
1325 SET AUTODDL ON;
1326 SET TERM ^ ;
1327
1328
1329 /* Triggers only will work for SQL triggers */
1330
1331 CREATE TRIGGER "After Create Database"
1332 ACTIVE ON CONNECT POSITION 0
1333 as
1334 begin
1335 End
1336 ^
1337 CREATE TRIGGER "BEFORE CREATE TABLE"
1338 ACTIVE BEFORE CREATE TABLE POSITION 0
1339 AS BEGIN END
1340 ^
1341 CREATE TRIGGER "AFTER CREATE TABLE"
1342 ACTIVE AFTER CREATE TABLE POSITION 0
1343 AS BEGIN END
1344 ^
1345 CREATE TRIGGER "BEFORE ALTER TABLE"
1346 ACTIVE BEFORE ALTER TABLE POSITION 0
1347 AS BEGIN END
1348 ^
1349 CREATE TRIGGER "AFTER ALTER TABLE"
1350 ACTIVE AFTER ALTER TABLE POSITION 0
1351 AS BEGIN END
1352 ^
1353 CREATE TRIGGER "AFTER CREATE/Alter TABLE"
1354 ACTIVE AFTER CREATE TABLE OR ALTER TABLE POSITION 0
1355 AS BEGIN END
1356 ^
1357 CREATE TRIGGER "BEFORE DROP TABLE"
1358 ACTIVE BEFORE Drop TABLE POSITION 0
1359 AS BEGIN END
1360 ^
1361 CREATE TRIGGER "AFTER DROP TABLE"
1362 ACTIVE AFTER Drop TABLE POSITION 0
1363 AS BEGIN END
1364 ^
1365 CREATE TRIGGER "BEFORE CREATE PROCEDURE"
1366 ACTIVE BEFORE CREATE PROCEDURE POSITION 0
1367 AS BEGIN END
1368 ^
1369 CREATE TRIGGER "AFTER CREATE PROCEDURE"
1370 ACTIVE AFTER CREATE PROCEDURE POSITION 0
1371 AS BEGIN END
1372 ^
1373 CREATE TRIGGER "BEFORE ALTER PROCEDURE"
1374 ACTIVE BEFORE ALTER PROCEDURE POSITION 0
1375 AS BEGIN END
1376 ^
1377 CREATE TRIGGER "AFTER ALTER PROCEDURE"
1378 ACTIVE AFTER ALTER PROCEDURE POSITION 0
1379 AS BEGIN END
1380 ^
1381 CREATE TRIGGER "BEFORE DROP PROCEDURE"
1382 ACTIVE BEFORE Drop PROCEDURE POSITION 0
1383 AS BEGIN END
1384 ^
1385 CREATE TRIGGER "AFTER DROP PROCEDURE"
1386 ACTIVE AFTER Drop PROCEDURE POSITION 0
1387 AS BEGIN END
1388 ^
1389 CREATE TRIGGER "BEFORE CREATE FUNCTION"
1390 ACTIVE BEFORE CREATE FUNCTION POSITION 0
1391 AS BEGIN END
1392 ^
1393 CREATE TRIGGER "AFTER CREATE FUNCTION"
1394 ACTIVE AFTER CREATE FUNCTION POSITION 0
1395 AS BEGIN END
1396 ^
1397 CREATE TRIGGER "BEFORE ALTER FUNCTION"
1398 ACTIVE BEFORE ALTER FUNCTION POSITION 0
1399 AS BEGIN END
1400 ^
1401 CREATE TRIGGER "AFTER ALTER FUNCTION"
1402 ACTIVE AFTER ALTER FUNCTION POSITION 0
1403 AS BEGIN END
1404 ^
1405 CREATE TRIGGER "BEFORE DROP FUNCTION"
1406 ACTIVE BEFORE Drop FUNCTION POSITION 0
1407 AS BEGIN END
1408 ^
1409 CREATE TRIGGER "AFTER DROP FUNCTION"
1410 ACTIVE AFTER Drop FUNCTION POSITION 0
1411 AS BEGIN END
1412 ^
1413 CREATE TRIGGER "BEFORE CREATE TRIGGER"
1414 ACTIVE BEFORE CREATE TRIGGER POSITION 0
1415 AS BEGIN END
1416 ^
1417 CREATE TRIGGER "AFTER CREATE TRIGGER"
1418 ACTIVE AFTER CREATE TRIGGER POSITION 0
1419 AS BEGIN END
1420 ^
1421 CREATE TRIGGER "BEFORE ALTER TRIGGER"
1422 ACTIVE BEFORE ALTER TRIGGER POSITION 0
1423 AS BEGIN END
1424 ^
1425 CREATE TRIGGER "AFTER ALTER TRIGGER"
1426 ACTIVE AFTER ALTER TRIGGER POSITION 0
1427 AS BEGIN END
1428 ^
1429 CREATE TRIGGER "BEFORE DROP TRIGGER"
1430 ACTIVE BEFORE Drop TRIGGER POSITION 0
1431 AS BEGIN END
1432 ^
1433 CREATE TRIGGER "AFTER DROP TRIGGER"
1434 ACTIVE AFTER Drop TRIGGER POSITION 0
1435 AS BEGIN END
1436 ^
1437 CREATE TRIGGER "BEFORE CREATE EXCEPTION"
1438 ACTIVE BEFORE CREATE EXCEPTION POSITION 0
1439 AS BEGIN END
1440 ^
1441 CREATE TRIGGER "AFTER CREATE EXCEPTION"
1442 ACTIVE AFTER CREATE EXCEPTION POSITION 0
1443 AS BEGIN END
1444 ^
1445 CREATE TRIGGER "BEFORE ALTER EXCEPTION"
1446 ACTIVE BEFORE ALTER EXCEPTION POSITION 0
1447 AS BEGIN END
1448 ^
1449 CREATE TRIGGER "AFTER ALTER EXCEPTION"
1450 ACTIVE AFTER ALTER EXCEPTION POSITION 0
1451 AS BEGIN END
1452 ^
1453 CREATE TRIGGER "BEFORE DROP EXCEPTION"
1454 ACTIVE BEFORE Drop EXCEPTION POSITION 0
1455 AS BEGIN END
1456 ^
1457 CREATE TRIGGER "AFTER DROP EXCEPTION"
1458 ACTIVE AFTER Drop EXCEPTION POSITION 0
1459 AS BEGIN END
1460 ^
1461 CREATE TRIGGER "BEFORE CREATE VIEW"
1462 ACTIVE BEFORE CREATE VIEW POSITION 0
1463 AS BEGIN END
1464 ^
1465 CREATE TRIGGER "AFTER CREATE VIEW"
1466 ACTIVE AFTER CREATE VIEW POSITION 0
1467 AS BEGIN END
1468 ^
1469 CREATE TRIGGER "BEFORE ALTER VIEW"
1470 ACTIVE BEFORE ALTER VIEW POSITION 0
1471 AS BEGIN END
1472 ^
1473 CREATE TRIGGER "AFTER ALTER VIEW"
1474 ACTIVE AFTER ALTER VIEW POSITION 0
1475 AS BEGIN END
1476 ^
1477 CREATE TRIGGER "BEFORE DROP VIEW"
1478 ACTIVE BEFORE Drop VIEW POSITION 0
1479 AS BEGIN END
1480 ^
1481 CREATE TRIGGER "AFTER DROP VIEW"
1482 ACTIVE AFTER Drop VIEW POSITION 0
1483 AS BEGIN END
1484 ^
1485 CREATE TRIGGER "BEFORE CREATE DOMAIN"
1486 ACTIVE BEFORE CREATE DOMAIN POSITION 0
1487 AS BEGIN END
1488 ^
1489 CREATE TRIGGER "AFTER CREATE DOMAIN"
1490 ACTIVE AFTER CREATE DOMAIN POSITION 0
1491 AS BEGIN END
1492 ^
1493 CREATE TRIGGER "BEFORE ALTER DOMAIN"
1494 ACTIVE BEFORE ALTER DOMAIN POSITION 0
1495 AS BEGIN END
1496 ^
1497 CREATE TRIGGER "AFTER ALTER DOMAIN"
1498 ACTIVE AFTER ALTER DOMAIN POSITION 0
1499 AS BEGIN END
1500 ^
1501 CREATE TRIGGER "BEFORE DROP DOMAIN"
1502 ACTIVE BEFORE Drop DOMAIN POSITION 0
1503 AS BEGIN END
1504 ^
1505 CREATE TRIGGER "AFTER DROP DOMAIN"
1506 ACTIVE AFTER Drop DOMAIN POSITION 0
1507 AS BEGIN END
1508 ^
1509 CREATE TRIGGER "BEFORE CREATE ROLE"
1510 ACTIVE BEFORE CREATE ROLE POSITION 0
1511 AS BEGIN END
1512 ^
1513 CREATE TRIGGER "AFTER CREATE ROLE"
1514 ACTIVE AFTER CREATE ROLE POSITION 0
1515 AS BEGIN END
1516 ^
1517 CREATE TRIGGER "BEFORE ALTER ROLE"
1518 ACTIVE BEFORE ALTER ROLE POSITION 0
1519 AS BEGIN END
1520 ^
1521 CREATE TRIGGER "AFTER ALTER ROLE"
1522 ACTIVE AFTER ALTER ROLE POSITION 0
1523 AS BEGIN END
1524 ^
1525 CREATE TRIGGER "BEFORE DROP ROLE"
1526 ACTIVE BEFORE Drop ROLE POSITION 0
1527 AS BEGIN END
1528 ^
1529 CREATE TRIGGER "AFTER DROP ROLE"
1530 ACTIVE AFTER Drop ROLE POSITION 0
1531 AS BEGIN END
1532 ^
1533 CREATE TRIGGER "BEFORE CREATE INDEX"
1534 ACTIVE BEFORE CREATE INDEX POSITION 0
1535 AS BEGIN END
1536 ^
1537 CREATE TRIGGER "AFTER CREATE INDEX"
1538 ACTIVE AFTER CREATE INDEX POSITION 0
1539 AS BEGIN END
1540 ^
1541 CREATE TRIGGER "BEFORE ALTER INDEX"
1542 ACTIVE BEFORE ALTER INDEX POSITION 0
1543 AS BEGIN END
1544 ^
1545 CREATE TRIGGER "AFTER ALTER INDEX"
1546 ACTIVE AFTER ALTER INDEX POSITION 0
1547 AS BEGIN END
1548 ^
1549 CREATE TRIGGER "BEFORE DROP INDEX"
1550 ACTIVE BEFORE Drop INDEX POSITION 0
1551 AS BEGIN END
1552 ^
1553 CREATE TRIGGER "AFTER DROP INDEX"
1554 ACTIVE AFTER Drop INDEX POSITION 0
1555 AS BEGIN END
1556 ^
1557 CREATE TRIGGER "BEFORE CREATE SEQUENCE"
1558 ACTIVE BEFORE CREATE SEQUENCE POSITION 0
1559 AS BEGIN END
1560 ^
1561 CREATE TRIGGER "AFTER CREATE SEQUENCE"
1562 ACTIVE AFTER CREATE SEQUENCE POSITION 0
1563 AS BEGIN END
1564 ^
1565 CREATE TRIGGER "BEFORE ALTER SEQUENCE"
1566 ACTIVE BEFORE ALTER SEQUENCE POSITION 0
1567 AS BEGIN END
1568 ^
1569 CREATE TRIGGER "AFTER ALTER SEQUENCE"
1570 ACTIVE AFTER ALTER SEQUENCE POSITION 0
1571 AS BEGIN END
1572 ^
1573 CREATE TRIGGER "BEFORE DROP SEQUENCE"
1574 ACTIVE BEFORE Drop SEQUENCE POSITION 0
1575 AS BEGIN END
1576 ^
1577 CREATE TRIGGER "AFTER DROP SEQUENCE"
1578 ACTIVE AFTER Drop SEQUENCE POSITION 0
1579 AS BEGIN END
1580 ^
1581 CREATE TRIGGER "BEFORE CREATE USER"
1582 ACTIVE BEFORE CREATE USER POSITION 0
1583 AS BEGIN END
1584 ^
1585 CREATE TRIGGER "AFTER CREATE USER"
1586 ACTIVE AFTER CREATE USER POSITION 0
1587 AS BEGIN END
1588 ^
1589 CREATE TRIGGER "BEFORE ALTER USER"
1590 ACTIVE BEFORE ALTER USER POSITION 0
1591 AS BEGIN END
1592 ^
1593 CREATE TRIGGER "AFTER ALTER USER"
1594 ACTIVE AFTER ALTER USER POSITION 0
1595 AS BEGIN END
1596 ^
1597 CREATE TRIGGER "BEFORE DROP USER"
1598 ACTIVE BEFORE Drop USER POSITION 0
1599 AS BEGIN END
1600 ^
1601 CREATE TRIGGER "AFTER DROP USER"
1602 ACTIVE AFTER Drop USER POSITION 0
1603 AS BEGIN END
1604 ^
1605 CREATE TRIGGER "BEFORE CREATE COLLATION"
1606 ACTIVE BEFORE CREATE COLLATION POSITION 0
1607 AS BEGIN END
1608 ^
1609 CREATE TRIGGER "AFTER CREATE COLLATION"
1610 ACTIVE AFTER CREATE COLLATION POSITION 0
1611 AS BEGIN END
1612 ^
1613 CREATE TRIGGER "BEFORE DROP COLLATION"
1614 ACTIVE BEFORE Drop COLLATION POSITION 0
1615 AS BEGIN END
1616 ^
1617 CREATE TRIGGER "AFTER DROP COLLATION"
1618 ACTIVE AFTER Drop COLLATION POSITION 0
1619 AS BEGIN END
1620 ^
1621 CREATE TRIGGER "BEFORE ALTER CHARACTER SET"
1622 ACTIVE BEFORE ALTER CHARACTER SET POSITION 0
1623 AS BEGIN END
1624 ^
1625 CREATE TRIGGER "AFTER ALTER CHARACTER SET"
1626 ACTIVE AFTER ALTER CHARACTER SET POSITION 0
1627 AS BEGIN END
1628 ^
1629 CREATE TRIGGER "BEFORE CREATE PACKAGE"
1630 ACTIVE BEFORE CREATE PACKAGE POSITION 0
1631 AS BEGIN END
1632 ^
1633 CREATE TRIGGER "AFTER CREATE PACKAGE"
1634 ACTIVE AFTER CREATE PACKAGE POSITION 0
1635 AS BEGIN END
1636 ^
1637 CREATE TRIGGER "BEFORE ALTER PACKAGE"
1638 ACTIVE BEFORE ALTER PACKAGE POSITION 0
1639 AS BEGIN END
1640 ^
1641 CREATE TRIGGER "AFTER ALTER PACKAGE"
1642 ACTIVE AFTER ALTER PACKAGE POSITION 0
1643 AS BEGIN END
1644 ^
1645 CREATE TRIGGER "BEFORE DROP PACKAGE"
1646 ACTIVE BEFORE Drop PACKAGE POSITION 0
1647 AS BEGIN END
1648 ^
1649 CREATE TRIGGER "AFTER DROP PACKAGE"
1650 ACTIVE AFTER Drop PACKAGE POSITION 0
1651 AS BEGIN END
1652 ^
1653 CREATE TRIGGER "BEFORE CREATE PACKAGE BODY"
1654 ACTIVE BEFORE CREATE PACKAGE BODY POSITION 0
1655 AS BEGIN END
1656 ^
1657 CREATE TRIGGER "AFTER CREATE PACKAGE BODY"
1658 ACTIVE AFTER CREATE PACKAGE BODY POSITION 0
1659 AS BEGIN END
1660 ^
1661 CREATE TRIGGER "BEFORE DROP PACKAGE BODY"
1662 ACTIVE BEFORE Drop PACKAGE BODY POSITION 0
1663 AS BEGIN END
1664 ^
1665 CREATE TRIGGER "AFTER DROP PACKAGE BODY"
1666 ACTIVE AFTER Drop PACKAGE BODY POSITION 0
1667 AS BEGIN END
1668 ^
1669 CREATE TRIGGER "BEFORE ANY DDL STATEMENT"
1670 ACTIVE BEFORE ANY DDL STATEMENT POSITION 0
1671 AS BEGIN END
1672 ^
1673 CREATE TRIGGER "AFTER ANY DDL STATEMENT"
1674 ACTIVE AFTER ANY DDL STATEMENT POSITION 0
1675 AS BEGIN END
1676 ^
1677 CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
1678 ACTIVE BEFORE INSERT POSITION 0
1679 AS
1680 BEGIN
1681 if (new.cust_no is null) then
1682 new.cust_no = gen_id(cust_no_gen, 1);
1683 END
1684 ^
1685 CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
1686 ACTIVE BEFORE INSERT POSITION 0
1687 AS
1688 BEGIN
1689 if (new.emp_no is null) then
1690 new.emp_no = gen_id(emp_no_gen, 1);
1691 END
1692 ^
1693 CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
1694 ACTIVE AFTER UPDATE POSITION 0
1695 AS
1696 BEGIN
1697 IF (old.salary <> new.salary) THEN
1698 INSERT INTO salary_history
1699 (emp_no, change_date, updater_id, old_salary, percent_change)
1700 VALUES (
1701 old.emp_no,
1702 'NOW',
1703 user,
1704 old.salary,
1705 (new.salary - old.salary) * 100 / old.salary);
1706 END
1707 ^
1708 CREATE TRIGGER POST_NEW_ORDER FOR SALES
1709 ACTIVE AFTER INSERT POSITION 0
1710 AS
1711 BEGIN
1712 POST_EVENT 'new_order';
1713 END
1714 ^
1715 COMMIT WORK ^
1716 SET TERM ;^
1717 COMMIT WORK;
1718 SET AUTODDL OFF;
1719 SET TERM ^ ;
1720
1721 /* Package Definitions */
1722
1723 CREATE PACKAGE BODY FB$OUT
1724 AS
1725
1726 begin
1727 procedure enable
1728 as
1729 begin
1730 rdb$set_context('USER_SESSION', 'fb$out.enabled', '1');
1731 end
1732
1733 procedure disable
1734 as
1735 begin
1736 rdb$set_context('USER_SESSION', 'fb$out.enabled', null);
1737 end
1738
1739 procedure put_line (line fb$out_type)
1740 as
1741 begin
1742 if (rdb$get_context('USER_SESSION', 'fb$out.enabled') = '1') then
1743 begin
1744 in autonomous transaction do
1745 begin
1746 insert into fb$out_table (line_num, content)
1747 values (next value for fb$out_seq, :line);
1748 end
1749 end
1750 end
1751
1752 procedure clear
1753 as
1754 begin
1755 in autonomous transaction do
1756 delete from fb$out_table;
1757 end
1758
1759 procedure get_lines returns (lines fb$out_type)
1760 as
1761 declare line fb$out_type;
1762 begin
1763 lines = '';
1764
1765 in autonomous transaction do
1766 begin
1767 for select content from fb$out_table order by line_num into line
1768 do
1769 begin
1770 if (octet_length(lines) > 0) then
1771 lines = lines || ascii_char(13) || ascii_char(10);
1772
1773 lines = lines || :line;
1774 end
1775 end
1776
1777 execute procedure clear;
1778 end
1779 end
1780 ^
1781
1782 CREATE PACKAGE BODY SELECT$TEST
1783 AS
1784
1785 Begin
1786 Procedure ShowItems(IPARAM Integer) RETURNS (OutParam integer)
1787 As
1788 Begin
1789 SUSPEND;
1790 End
1791 End
1792 ^
1793
1794 SET TERM ; ^
1795 COMMIT WORK;
1796 SET AUTODDL ON;
1797 COMMIT WORK;
1798 SET AUTODDL OFF;
1799 SET TERM ^ ;
1800
1801 /* Stored procedure Bodies */
1802
1803
1804 ALTER PROCEDURE SHOW_LANGS
1805 (
1806 CODE VARCHAR(5) CHARACTER SET NONE,
1807 GRADE SMALLINT,
1808 CTY VARCHAR(15) CHARACTER SET NONE
1809 )
1810 RETURNS
1811 (
1812 LANGUAGES VARCHAR(15) CHARACTER SET NONE
1813 )
1814 AS
1815 DECLARE VARIABLE i INTEGER;
1816 BEGIN
1817 i = 1;
1818 WHILE (i <= 5) DO
1819 BEGIN
1820 SELECT language_req[:i] FROM joB
1821 WHERE ((job_code = :code) AND (job_grade = :grade) AND (job_country = :cty)
1822 AND (language_req IS NOT NULL))
1823 INTO :languages;
1824 IF (languages = ' ') THEN /* Prints 'NULL' instead of blanks */
1825 languages = 'NULL';
1826 i = i +1;
1827 SUSPEND;
1828 END
1829 END
1830 ^
1831
1832
1833 ALTER PROCEDURE ADD_EMP_PROJ
1834 (
1835 EMP_NO SMALLINT,
1836 PROJ_ID CHAR(5) CHARACTER SET NONE
1837 )
1838 AS
1839 BEGIN
1840 BEGIN
1841 INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
1842 WHEN SQLCODE -530 DO
1843 EXCEPTION unknown_emp_id;
1844 END
1845 END
1846 ^
1847
1848
1849 ALTER PROCEDURE ALL_LANGS
1850 RETURNS
1851 (
1852 CODE VARCHAR(5) CHARACTER SET NONE,
1853 GRADE VARCHAR(5) CHARACTER SET NONE,
1854 COUNTRY VARCHAR(15) CHARACTER SET NONE,
1855 LANG VARCHAR(15) CHARACTER SET NONE
1856 )
1857 AS
1858 BEGIN
1859 FOR SELECT job_code, job_grade, job_country FROM job
1860 INTO :code, :grade, :country
1861
1862 DO
1863 BEGIN
1864 FOR SELECT languages FROM show_langs
1865 (:code, :grade, :country) INTO :lang DO
1866 SUSPEND;
1867 /* Put nice separators between rows */
1868 code = '=====';
1869 grade = '=====';
1870 country = '===============';
1871 lang = '==============';
1872 SUSPEND;
1873 END
1874 END
1875 ^
1876
1877
1878 ALTER PROCEDURE DELETE_EMPLOYEE
1879 (
1880 EMP_NUM INTEGER
1881 )
1882 AS
1883 DECLARE VARIABLE any_sales INTEGER;
1884 BEGIN
1885 any_sales = 0;
1886
1887 /*
1888 * If there are any sales records referencing this employee,
1889 * can't delete the employee until the sales are re-assigned
1890 * to another employee or changed to NULL.
1891 */
1892 SELECT count(po_number)
1893 FROM sales
1894 WHERE sales_rep = :emp_num
1895 INTO :any_sales;
1896
1897 IF (any_sales > 0) THEN
1898 BEGIN
1899 EXCEPTION reassign_sales;
1900 END
1901
1902 /*
1903 * If the employee is a manager, update the department.
1904 */
1905 UPDATE department
1906 SET mngr_no = NULL
1907 WHERE mngr_no = :emp_num;
1908
1909 /*
1910 * If the employee is a project leader, update project.
1911 */
1912 UPDATE project
1913 SET team_leader = NULL
1914 WHERE team_leader = :emp_num;
1915
1916 /*
1917 * Delete the employee from any projects.
1918 */
1919 DELETE FROM employee_project
1920 WHERE emp_no = :emp_num;
1921
1922 /*
1923 * Delete old salary records.
1924 */
1925 DELETE FROM salary_history
1926 WHERE emp_no = :emp_num;
1927
1928 /*
1929 * Delete the employee.
1930 */
1931 DELETE FROM employee
1932 WHERE emp_no = :emp_num;
1933
1934 END
1935 ^
1936
1937
1938 ALTER PROCEDURE DEPT_BUDGET
1939 (
1940 DNO CHAR(3) CHARACTER SET NONE
1941 )
1942 RETURNS
1943 (
1944 TOT DECIMAL(12, 2)
1945 )
1946 AS
1947 DECLARE VARIABLE sumb DECIMAL(12, 2);
1948 DECLARE VARIABLE rdno CHAR(3);
1949 DECLARE VARIABLE cnt INTEGER;
1950 BEGIN
1951 tot = 0;
1952
1953 SELECT budget FROM department WHERE dept_no = :dno INTO :tot;
1954
1955 SELECT count(budget) FROM department WHERE head_dept = :dno INTO :cnt;
1956
1957 IF (cnt = 0) THEN
1958 SUSPEND;
1959
1960 FOR SELECT dept_no
1961 FROM department
1962 WHERE head_dept = :dno
1963 INTO :rdno
1964 DO
1965 BEGIN
1966 EXECUTE PROCEDURE dept_budget :rdno RETURNING_VALUES :sumb;
1967 tot = tot + sumb;
1968 END
1969
1970 SUSPEND;
1971 END
1972 ^
1973
1974
1975 ALTER PROCEDURE GET_EMP_PROJ
1976 (
1977 EMP_NO SMALLINT
1978 )
1979 RETURNS
1980 (
1981 PROJ_ID CHAR(5) CHARACTER SET NONE
1982 )
1983 AS
1984 BEGIN
1985 FOR SELECT proj_id
1986 FROM employee_project
1987 WHERE emp_no = :emp_no
1988 INTO :proj_id
1989 DO
1990 SUSPEND;
1991 END
1992 ^
1993
1994
1995 ALTER PROCEDURE "Has Space"
1996 (
1997 ARG1 INTEGER
1998 )
1999 AS
2000 Begin End
2001 ^
2002
2003
2004 ALTER PROCEDURE MAIL_LABEL
2005 (
2006 CUST_NO INTEGER
2007 )
2008 RETURNS
2009 (
2010 LINE1 CHAR(40) CHARACTER SET NONE,
2011 LINE2 CHAR(40) CHARACTER SET NONE,
2012 LINE3 CHAR(40) CHARACTER SET NONE,
2013 LINE4 CHAR(40) CHARACTER SET NONE,
2014 LINE5 CHAR(40) CHARACTER SET NONE,
2015 LINE6 CHAR(40) CHARACTER SET NONE
2016 )
2017 AS
2018 DECLARE VARIABLE customer VARCHAR(25);
2019 DECLARE VARIABLE first_name VARCHAR(15);
2020 DECLARE VARIABLE last_name VARCHAR(20);
2021 DECLARE VARIABLE addr1 VARCHAR(30);
2022 DECLARE VARIABLE addr2 VARCHAR(30);
2023 DECLARE VARIABLE city VARCHAR(25);
2024 DECLARE VARIABLE state VARCHAR(15);
2025 DECLARE VARIABLE country VARCHAR(15);
2026 DECLARE VARIABLE postcode VARCHAR(12);
2027 DECLARE VARIABLE cnt INTEGER;
2028 BEGIN
2029 line1 = '';
2030 line2 = '';
2031 line3 = '';
2032 line4 = '';
2033 line5 = '';
2034 line6 = '';
2035
2036 SELECT customer, contact_first, contact_last, address_line1,
2037 address_line2, city, state_province, country, postal_code
2038 FROM CUSTOMER
2039 WHERE cust_no = :cust_no
2040 INTO :customer, :first_name, :last_name, :addr1, :addr2,
2041 :city, :state, :country, :postcode;
2042
2043 IF (customer IS NOT NULL) THEN
2044 line1 = customer;
2045 IF (first_name IS NOT NULL) THEN
2046 line2 = first_name || ' ' || last_name;
2047 ELSE
2048 line2 = last_name;
2049 IF (addr1 IS NOT NULL) THEN
2050 line3 = addr1;
2051 IF (addr2 IS NOT NULL) THEN
2052 line4 = addr2;
2053
2054 IF (country = 'USA') THEN
2055 BEGIN
2056 IF (city IS NOT NULL) THEN
2057 line5 = city || ', ' || state || ' ' || postcode;
2058 ELSE
2059 line5 = state || ' ' || postcode;
2060 END
2061 ELSE
2062 BEGIN
2063 IF (city IS NOT NULL) THEN
2064 line5 = city || ', ' || state;
2065 ELSE
2066 line5 = state;
2067 line6 = country || ' ' || postcode;
2068 END
2069
2070 SUSPEND;
2071 END
2072 ^
2073
2074
2075 ALTER PROCEDURE ORG_CHART
2076 RETURNS
2077 (
2078 HEAD_DEPT CHAR(25) CHARACTER SET NONE,
2079 DEPARTMENT CHAR(25) CHARACTER SET NONE,
2080 MNGR_NAME CHAR(20) CHARACTER SET NONE,
2081 TITLE CHAR(5) CHARACTER SET NONE,
2082 EMP_CNT INTEGER
2083 )
2084 AS
2085 DECLARE VARIABLE mngr_no INTEGER;
2086 DECLARE VARIABLE dno CHAR(3);
2087 BEGIN
2088 FOR SELECT h.department, d.department, d.mngr_no, d.dept_no
2089 FROM department d
2090 LEFT OUTER JOIN department h ON d.head_dept = h.dept_no
2091 ORDER BY d.dept_no
2092 INTO :head_dept, :department, :mngr_no, :dno
2093 DO
2094 BEGIN
2095 IF (:mngr_no IS NULL) THEN
2096 BEGIN
2097 mngr_name = '--TBH--';
2098 title = '';
2099 END
2100
2101 ELSE
2102 SELECT full_name, job_code
2103 FROM employee
2104 WHERE emp_no = :mngr_no
2105 INTO :mngr_name, :title;
2106
2107 SELECT COUNT(emp_no)
2108 FROM employee
2109 WHERE dept_no = :dno
2110 INTO :emp_cnt;
2111
2112 SUSPEND;
2113 END
2114 END
2115 ^
2116
2117
2118 ALTER PROCEDURE SHIP_ORDER
2119 (
2120 PO_NUM CHAR(8) CHARACTER SET NONE
2121 )
2122 AS
2123 DECLARE VARIABLE ord_stat CHAR(7);
2124 DECLARE VARIABLE hold_stat CHAR(1);
2125 DECLARE VARIABLE cust_no INTEGER;
2126 DECLARE VARIABLE any_po CHAR(8);
2127 BEGIN
2128 SELECT s.order_status, c.on_hold, c.cust_no
2129 FROM sales s, customer c
2130 WHERE po_number = :po_num
2131 AND s.cust_no = c.cust_no
2132 INTO :ord_stat, :hold_stat, :cust_no;
2133
2134 /* This purchase order has been already shipped. */
2135 IF (ord_stat = 'shipped') THEN
2136 BEGIN
2137 EXCEPTION order_already_shipped;
2138 END
2139
2140 /* Customer is on hold. */
2141 ELSE IF (hold_stat = '*') THEN
2142 BEGIN
2143 EXCEPTION customer_on_hold;
2144 END
2145
2146 /*
2147 * If there is an unpaid balance on orders shipped over 2 months ago,
2148 * put the customer on hold.
2149 */
2150 FOR SELECT po_number
2151 FROM sales
2152 WHERE cust_no = :cust_no
2153 AND order_status = 'shipped'
2154 AND paid = 'n'
2155 AND ship_date < CAST('NOW' AS TIMESTAMP) - 60
2156 INTO :any_po
2157 DO
2158 BEGIN
2159 EXCEPTION customer_check;
2160
2161 UPDATE customer
2162 SET on_hold = '*'
2163 WHERE cust_no = :cust_no;
2164
2165 END
2166
2167 /*
2168 * Ship the order.
2169 */
2170 UPDATE sales
2171 SET order_status = 'shipped', ship_date = 'NOW'
2172 WHERE po_number = :po_num;
2173
2174 END
2175 ^
2176
2177
2178 ALTER PROCEDURE SUB_TOT_BUDGET
2179 (
2180 HEAD_DEPT CHAR(3) CHARACTER SET NONE
2181 )
2182 RETURNS
2183 (
2184 TOT_BUDGET DECIMAL(12, 2),
2185 AVG_BUDGET DECIMAL(12, 2),
2186 MIN_BUDGET DECIMAL(12, 2),
2187 MAX_BUDGET DECIMAL(12, 2)
2188 )
2189 AS
2190 BEGIN
2191 SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)
2192 FROM department
2193 WHERE head_dept = :head_dept
2194 INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
2195 SUSPEND;
2196 END
2197 ^
2198
2199
2200 ALTER PROCEDURE "UC SPACE"
2201 (
2202 ARG1 INTEGER
2203 )
2204 AS
2205 Begin End
2206 ^
2207
2208
2209 ALTER PROCEDURE "iCASE"
2210 AS
2211 Begin End
2212 ^
2213
2214 SET TERM ; ^
2215 COMMIT WORK;
2216 SET AUTODDL ON;
2217 COMMIT WORK;
2218 SET AUTODDL OFF;
2219 SET TERM ^ ;
2220
2221 /* Stored Function Body */
2222
2223 ALTER FUNCTION F (X INTEGER)
2224 RETURNS INTEGER
2225 AS
2226 BEGIN
2227 RETURN X+1;
2228 END
2229 ^
2230
2231
2232 SET TERM ; ^
2233 COMMIT WORK;
2234 SET AUTODDL ON;
2235
2236 /* Comments on System Objects */
2237
2238 COMMENT ON CHARACTER SET UTF8 IS 'Default Character set';
2239
2240 /* Grant Roles for this database */
2241
2242
2243 /* Role: TESTROLE, Owner: SYSDBA */
2244
2245 CREATE ROLE TESTROLE SET SYSTEM PRIVILEGES TO CREATE_DATABASE;
2246
2247 /* Grant permissions for this database */
2248
2249 GRANT REFERENCES ON TABLE COUNTRIES TO USER BOB ;
2250 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE COUNTRY TO USER PUBLIC WITH GRANT OPTION ;
2251 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE CUSTOMER TO USER PUBLIC WITH GRANT OPTION ;
2252 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE DEPARTMENT TO USER PUBLIC WITH GRANT OPTION ;
2253 GRANT SELECT ON TABLE EMPLOYEE TO USER ALICE WITH GRANT OPTION GRANTED BY BOB ;
2254 GRANT SELECT,Update(FIRST_NAME,LAST_NAME) ON TABLE EMPLOYEE TO USER BOB ;
2255 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE EMPLOYEE TO USER PUBLIC WITH GRANT OPTION ;
2256 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE EMPLOYEE_PROJECT TO USER PUBLIC WITH GRANT OPTION ;
2257 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE FB$OUT_TABLE TO PACKAGE FB$OUT ;
2258 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE JOB TO USER PUBLIC WITH GRANT OPTION ;
2259 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE PHONE_LIST TO USER PUBLIC WITH GRANT OPTION ;
2260 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE PROJECT TO USER PUBLIC WITH GRANT OPTION ;
2261 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE PROJ_DEPT_BUDGET TO USER PUBLIC WITH GRANT OPTION ;
2262 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE SALARY_HISTORY TO USER PUBLIC WITH GRANT OPTION ;
2263 GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE SALES TO USER PUBLIC WITH GRANT OPTION ;
2264 GRANT RDB$ADMIN TO ALICE;
2265
2266 GRANT RDB$ADMIN TO BOB;
2267
2268 GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ TO USER PUBLIC WITH GRANT OPTION ;
2269 GRANT EXECUTE ON PROCEDURE ALL_LANGS TO USER PUBLIC WITH GRANT OPTION ;
2270 GRANT EXECUTE ON PROCEDURE DELETE_EMPLOYEE TO USER PUBLIC WITH GRANT OPTION ;
2271 GRANT EXECUTE ON PROCEDURE DEPT_BUDGET TO USER PUBLIC WITH GRANT OPTION ;
2272 GRANT EXECUTE ON PROCEDURE GET_EMP_PROJ TO USER PUBLIC WITH GRANT OPTION ;
2273 GRANT EXECUTE ON PROCEDURE MAIL_LABEL TO USER PUBLIC WITH GRANT OPTION ;
2274 GRANT EXECUTE ON PROCEDURE ORG_CHART TO USER PUBLIC WITH GRANT OPTION ;
2275 GRANT EXECUTE ON PROCEDURE SHIP_ORDER TO USER PUBLIC WITH GRANT OPTION ;
2276 GRANT EXECUTE ON PROCEDURE SHOW_LANGS TO USER PUBLIC WITH GRANT OPTION ;
2277 GRANT EXECUTE ON PROCEDURE SUB_TOT_BUDGET TO USER PUBLIC WITH GRANT OPTION ;
2278 GRANT EXECUTE ON PACKAGE FB$OUT TO USER PUBLIC ;
2279 GRANT CREATE TABLE TO USER BOB ;
2280 GRANT CREATE COLLATION TO USER BOB ;
2281 GRANT CREATE CHARACTER SET TO USER ALICE ;
2282 GRANT CREATE COLLATION TO USER ALICE ;
2283 GRANT CREATE DOMAIN TO USER ALICE ;
2284 GRANT CREATE EXCEPTION TO USER ALICE ;
2285 GRANT CREATE FILTER TO USER ALICE ;
2286 GRANT CREATE FUNCTION TO USER ALICE ;
2287 GRANT CREATE GENERATOR TO USER ALICE ;
2288 GRANT CREATE PACKAGE TO USER ALICE ;
2289 GRANT CREATE PROCEDURE TO USER ALICE ;
2290 GRANT CREATE ROLE TO USER ALICE ;
2291 GRANT CREATE TABLE TO USER ALICE ;
2292 GRANT CREATE VIEW TO USER ALICE ;
2293 GRANT DROP ANY TABLE TO USER ALICE ;
2294 GRANT ALTER ANY VIEW TO USER ALICE ;
2295 GRANT DROP ANY GENERATOR TO USER ALICE ;