testsuite/resources/Test10.11.sql

/* Script for ODS 11 */

SET SQL DIALECT 3;
SET AUTODDL ON;

 CREATE DATABASE 'inet://localhost//tmp/ibx-testsuite/testsuite1.fdb' PAGE_SIZE 8192

 DEFAULT CHARACTER SET NONE;
COMMENT ON DATABASE  IS 'Employee Test DB';

/* Domain definitions */

CREATE DOMAIN ADDRESSLINE AS VARCHAR(30);
CREATE DOMAIN BUDGET AS DECIMAL(12,2)
         DEFAULT 50000;
CREATE DOMAIN COUNTRYNAME AS VARCHAR(15);
CREATE DOMAIN CUSTNO AS INTEGER;
CREATE DOMAIN DEPTNO AS CHAR(3);
CREATE DOMAIN EMPNO AS SMALLINT;
CREATE DOMAIN FB$OUT_TYPE AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80 NOT NULL;
CREATE DOMAIN FIRSTNAME AS VARCHAR(15);
CREATE DOMAIN JOBCODE AS VARCHAR(5);
CREATE DOMAIN JOBGRADE AS SMALLINT;
CREATE DOMAIN LASTNAME AS VARCHAR(20);
CREATE DOMAIN PHONENUMBER AS VARCHAR(20);
CREATE DOMAIN PONUMBER AS CHAR(8);
CREATE DOMAIN PRODTYPE AS VARCHAR(12)
         DEFAULT 'software' NOT NULL;
CREATE DOMAIN PROJNO AS CHAR(5);
CREATE DOMAIN SALARY AS NUMERIC(10,2)
         DEFAULT 0;

/* Table: COUNTRIES, Owner: SYSDBA */

CREATE TABLE COUNTRIES
(
        COUNTRY VARCHAR(64) NOT NULL,
        NONEU   SMALLINT,
        ISO2    CHAR(2),
        ISO3    CHAR(3),
PRIMARY KEY (COUNTRY)
);
COMMENT ON TABLE COUNTRIES IS 'List of Countries';
COMMENT ON COLUMN COUNTRIES.ISO2 IS 'Two Character ISO Country Code';

/* Table: COUNTRY, Owner: SYSDBA */

CREATE TABLE COUNTRY
(
        COUNTRY COUNTRYNAME NOT NULL,
        CURRENCY        VARCHAR(10) NOT NULL,
PRIMARY KEY (COUNTRY)
);

/* Table: CUSTOMER, Owner: SYSDBA */

CREATE TABLE CUSTOMER
(
        CUST_NO CUSTNO NOT NULL,
        CUSTOMER        VARCHAR(25) NOT NULL,
        CONTACT_FIRST   FIRSTNAME,
        CONTACT_LAST    LASTNAME,
        PHONE_NO        PHONENUMBER,
        ADDRESS_LINE1   ADDRESSLINE,
        ADDRESS_LINE2   ADDRESSLINE,
        CITY    VARCHAR(25),
        STATE_PROVINCE  VARCHAR(15),
        COUNTRY COUNTRYNAME,
        POSTAL_CODE     VARCHAR(12),
        ON_HOLD CHAR(1) DEFAULT NULL,
        LONG_REFERENCE  BIGINT,
PRIMARY KEY (CUST_NO)
);

/* Table: DEPARTMENT, Owner: SYSDBA */

CREATE TABLE DEPARTMENT
(
        DEPT_NO DEPTNO NOT NULL,
        DEPARTMENT      VARCHAR(25) NOT NULL,
        HEAD_DEPT       DEPTNO,
        MNGR_NO EMPNO,
        BUDGET  BUDGET,
        LOCATION        VARCHAR(15),
        PHONE_NO        PHONENUMBER DEFAULT '555-1234',
UNIQUE (DEPARTMENT),
PRIMARY KEY (DEPT_NO)
);

/* Table: EMPLOYEE, Owner: SYSDBA */

CREATE TABLE EMPLOYEE
(
        EMP_NO  EMPNO NOT NULL,
        FIRST_NAME      FIRSTNAME NOT NULL,
        LAST_NAME       LASTNAME NOT NULL,
        PHONE_EXT       VARCHAR(4),
        HIRE_DATE       TIMESTAMP DEFAULT 'NOW' NOT NULL,
        DEPT_NO DEPTNO NOT NULL,
        JOB_CODE        JOBCODE NOT NULL,
        JOB_GRADE       JOBGRADE NOT NULL,
        JOB_COUNTRY     COUNTRYNAME NOT NULL,
        SALARY  SALARY NOT NULL,
        FULL_NAME COMPUTED BY (last_name || ', ' || first_name),
PRIMARY KEY (EMP_NO)
);

/* Table: EMPLOYEE_PROJECT, Owner: SYSDBA */

CREATE TABLE EMPLOYEE_PROJECT
(
        EMP_NO  EMPNO NOT NULL,
        PROJ_ID PROJNO NOT NULL,
PRIMARY KEY (EMP_NO, PROJ_ID)
);

/* Table: FB$OUT_TABLE, Owner: SYSDBA */

CREATE GLOBAL TEMPORARY TABLE FB$OUT_TABLE
(
        LINE_NUM        INTEGER,
        CONTENT FB$OUT_TYPE
 ) ON COMMIT PRESERVE ROWS ;

/* Table: JOB, Owner: SYSDBA */

CREATE TABLE JOB
(
        JOB_CODE        JOBCODE NOT NULL,
        JOB_GRADE       JOBGRADE NOT NULL,
        JOB_COUNTRY     COUNTRYNAME NOT NULL,
        JOB_TITLE       VARCHAR(25) NOT NULL,
        MIN_SALARY      SALARY NOT NULL,
        MAX_SALARY      SALARY NOT NULL,
        JOB_REQUIREMENT BLOB SUB_TYPE TEXT SEGMENT SIZE 400,
        LANGUAGE_REQ    VARCHAR(15)[1:5],
PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
);

/* Table: PROJECT, Owner: SYSDBA */

CREATE TABLE PROJECT
(
        PROJ_ID PROJNO NOT NULL,
        PROJ_NAME       VARCHAR(20) NOT NULL,
        PROJ_DESC       BLOB SUB_TYPE TEXT SEGMENT SIZE 800,
        TEAM_LEADER     EMPNO,
        PRODUCT PRODTYPE,
UNIQUE (PROJ_NAME),
PRIMARY KEY (PROJ_ID)
);

/* Table: PROJ_DEPT_BUDGET, Owner: SYSDBA */

CREATE TABLE PROJ_DEPT_BUDGET
(
        FISCAL_YEAR     INTEGER NOT NULL,
        PROJ_ID PROJNO NOT NULL,
        DEPT_NO DEPTNO NOT NULL,
        QUART_HEAD_CNT  INTEGER[1:4],
        PROJECTED_BUDGET        BUDGET,
PRIMARY KEY (FISCAL_YEAR, PROJ_ID, DEPT_NO)
);

/* Table: SALARY_HISTORY, Owner: SYSDBA */

CREATE TABLE SALARY_HISTORY
(
        EMP_NO  EMPNO NOT NULL,
        CHANGE_DATE     TIMESTAMP DEFAULT 'NOW' NOT NULL,
        UPDATER_ID      VARCHAR(20) NOT NULL,
        OLD_SALARY      SALARY NOT NULL,
        PERCENT_CHANGE  DOUBLE PRECISION DEFAULT 0 NOT NULL,
        NEW_SALARY COMPUTED BY (old_salary + old_salary * percent_change / 100),
PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID)
);

/* Table: SALES, Owner: SYSDBA */

CREATE TABLE SALES
(
        PO_NUMBER       PONUMBER NOT NULL,
        CUST_NO CUSTNO NOT NULL,
        SALES_REP       EMPNO,
        ORDER_STATUS    VARCHAR(7) DEFAULT 'new' NOT NULL,
        ORDER_DATE      TIMESTAMP DEFAULT 'NOW' NOT NULL,
        SHIP_DATE       TIMESTAMP,
        DATE_NEEDED     TIMESTAMP,
        PAID    CHAR(1) DEFAULT 'n',
        QTY_ORDERED     INTEGER DEFAULT 1 NOT NULL,
        TOTAL_VALUE     DECIMAL(9,2) NOT NULL,
        DISCOUNT        FLOAT DEFAULT 0 NOT NULL,
        ITEM_TYPE       PRODTYPE,
        AGED COMPUTED BY (ship_date - order_date),
PRIMARY KEY (PO_NUMBER)
);

/* External Function declarations */

DECLARE EXTERNAL FUNCTION ADDDAY
TIMESTAMP, INTEGER
RETURNS TIMESTAMP
ENTRY_POINT 'addDay' MODULE_NAME 'fbudf'
;


DECLARE EXTERNAL FUNCTION I64TRUNCATE
NUMERIC(18,0) BY DESCRIPTOR, NUMERIC(18,0) BY DESCRIPTOR
RETURNS PARAMETER 2
ENTRY_POINT 'fbtruncate' MODULE_NAME 'fbudf'
;


DECLARE EXTERNAL FUNCTION TESTIT
TIMESTAMP, INTEGER NULL, CSTRING(10) CHARACTER SET NONE, CSTRING(10) CHARACTER SET NONE BY DESCRIPTOR
RETURNS CSTRING(32) CHARACTER SET NONE FREE_IT
ENTRY_POINT 'testit' MODULE_NAME 'fbudf'
;


/* Data Starts */

/* Inserting data into Table: COUNTRY */

INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('USA','Dollar');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('England','Pound');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Canada','CdnDlr');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Switzerland','SFranc');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Japan','Yen');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Italy','Euro');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('France','Euro');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Germany','Euro');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Australia','ADollar');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Hong Kong','HKDollar');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Netherlands','Euro');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Belgium','Euro');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Austria','Euro');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Fiji','FDollar');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Russia','Ruble');
INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES('Romania','RLeu');
COMMIT;

/* Inserting data into Table: CUSTOMER */

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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
COMMIT;

/* Inserting data into Table: DEPARTMENT */

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');
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');
INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('600','Engineering','000',2,1100000,'Monterey','(408) 555-1234');
INSERT INTO DEPARTMENT (DEPT_NO,DEPARTMENT,HEAD_DEPT,MNGR_NO,BUDGET,LOCATION,PHONE_NO) VALUES('900','Finance','000',46,400000,'Monterey','(408) 555-1234');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
COMMIT;

/* Inserting data into Table: EMPLOYEE */

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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
COMMIT;

/* Inserting data into Table: EMPLOYEE_PROJECT */

INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(144,'DGPII');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(113,'DGPII');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(24,'DGPII');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(8,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(136,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(15,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(71,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(145,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(44,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(4,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(83,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(138,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(45,'VBASE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(20,'GUIDE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(24,'GUIDE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(113,'GUIDE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(8,'GUIDE');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(4,'MAPDB');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(71,'MAPDB');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(46,'MKTPR');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(105,'MKTPR');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(12,'MKTPR');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(85,'MKTPR');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(110,'MKTPR');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(34,'MKTPR');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(8,'MKTPR');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(14,'MKTPR');
INSERT INTO EMPLOYEE_PROJECT (EMP_NO,PROJ_ID) VALUES(52,'MKTPR');
COMMIT;

/* Inserting data into Table: JOB */

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.
',NULL);
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
with a proven track record.
MBA or J.D. degree.
',NULL);
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.
',NULL);
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.
An advanced degree.
',NULL);
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.
3-5 years in management,
plus 2-4 years engineering experience.
',NULL);
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.
',NULL);
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.
Strong organizational and communication skills required.
BA degree preferred.
',NULL);
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.
Facility with word processing and data entry.
AA degree preferred.
',NULL);
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);
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);
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.
10+ years experience in high tech environment.
',NULL);
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.
3-5 years experience.
Knowledgeable with spreadsheets and databases.
',NULL);
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.
Spreadsheet, data entry, and word processing knowledge required.
',NULL);
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.
Strong analytical skills.
CPA/MBA required.
',NULL);
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.
Ph.D/MS/BS or equivalent experience.
',NULL);
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.
BA/BS required.
MS degree preferred.
',NULL);
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.
BA/BS and/or MS degrees required.
Customer support experience desired.
Knowledge of Japanese and English.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">Japanese
</elt>
 <elt ix="2">Mandarin
</elt>
 <elt ix="3">English
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
',NULL);
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
2-4 years experience in technical support.
Knowledge of several European languages helpful.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">English
</elt>
 <elt ix="2">German
</elt>
 <elt ix="3">French
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
2-4 years technical experience.
',NULL);
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
software documentation.
A bachelor''s degree or equivalent.
Programming experience required.
Excellent language skills.
',NULL);
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.
Some programming experience required.
2-4 years of technical writing.
',NULL);
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
in a high tech environment.
Excellent communication skills.
BA or equivalent.
',NULL);
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
in a high tech environment.
Excellent communication skills.
BA or equivalent.
Knowledge of several European languages helpful.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">English
</elt>
 <elt ix="2">German
</elt>
 <elt ix="3">French
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
Excellent communications, negotiation, and analytical skills.
Experience in establishing long term customer relationships.
Some knowledge of Spanish required.
Travel required.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">English
</elt>
 <elt ix="2">Spanish
</elt>
 <elt ix="3">
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
Excellent communications, negotiation, and analytical skills.
Experience in establishing long term customer relationships.
Knowledge of several European languages helpful.
Travel required.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">English
</elt>
 <elt ix="2">German
</elt>
 <elt ix="3">French
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
Excellent communications, negotiation, and analytical skills.
Experience in establishing long term customer relationships.
Travel required.
English plus speaking knowledge of French required.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">English
</elt>
 <elt ix="2">French
</elt>
 <elt ix="3">
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
Excellent communications, negotiation, and analytical skills.
Experience in establishing long term customer relationships.
Knowledge of German required; one or more other European language helpful.
Travel required.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">German
</elt>
 <elt ix="2">French
</elt>
 <elt ix="3">English
</elt>
 <elt ix="4">Italian
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
Excellent communications, negotiation, and analytical skills.
Experience in establishing long term customer relationships.
Knowledge of Japanese required.
Travel required.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">Japanese
</elt>
 <elt ix="2">English
</elt>
 <elt ix="3">
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
Excellent communications, negotiation, and analytical skills.
Experience in establishing long term customer relationships.
Fluency in Italian; some knowledge of German helpful.
Travel required.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">Italian
</elt>
 <elt ix="2">German
</elt>
 <elt ix="3">French
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
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.
Excellent communications, negotiation, and analytical skills.
Experience in establishing long term customer relationships.
Fluency in French; some knowledge of German/Spanish helpful.
Travel required.
',<array dim = "1" sqltype = "448" length = "15" relation_name = "JOB" column_name = "LANGUAGE_REQ" charset = "NONE" bounds="1:5">
 <elt ix="1">English
</elt>
 <elt ix="2">French
</elt>
 <elt ix="3">Spanish
</elt>
 <elt ix="4">
</elt>
 <elt ix="5">
</elt>
</array>
);
COMMIT;

/* Inserting data into Table: PROJECT */

INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('VBASE','Video Database','Design a video data base management system for
controlling on-demand video distribution.
',45,'software');
INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('DGPII','DigiPizza','Develop second generation digital pizza maker
with flash-bake heating element and
digital ingredient measuring system.
',24,'other');
INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('GUIDE','AutoMap','Develop a prototype for the automobile version of
the hand-held map browsing device.
',20,'hardware');
INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('MAPDB','MapBrowser port','Port the map browsing database software to run
on the automobile model.
',4,'software');
INSERT INTO PROJECT (PROJ_ID,PROJ_NAME,PROJ_DESC,TEAM_LEADER,PRODUCT) VALUES('HWRII','Translator upgrade','Integrate the hand-writing recognition module into the
universal language translator.
',NULL,'software');
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.
Set up a field office in Australia and Singapore.
',85,'N/A');
COMMIT;

/* Inserting data into Table: PROJ_DEPT_BUDGET */

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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">1</elt>
 <elt ix="4">0</elt>
</array>
,200000);
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">
 <elt ix="1">3</elt>
 <elt ix="2">2</elt>
 <elt ix="3">1</elt>
 <elt ix="4">0</elt>
</array>
,450000);
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">
 <elt ix="1">0</elt>
 <elt ix="2">0</elt>
 <elt ix="3">0</elt>
 <elt ix="4">1</elt>
</array>
,20000);
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">
 <elt ix="1">2</elt>
 <elt ix="2">1</elt>
 <elt ix="3">0</elt>
 <elt ix="4">0</elt>
</array>
,40000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">0</elt>
 <elt ix="4">0</elt>
</array>
,60000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">0</elt>
 <elt ix="4">0</elt>
</array>
,11000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">1</elt>
 <elt ix="4">1</elt>
</array>
,20000);
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">
 <elt ix="1">2</elt>
 <elt ix="2">3</elt>
 <elt ix="3">2</elt>
 <elt ix="4">1</elt>
</array>
,400000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">2</elt>
 <elt ix="4">2</elt>
</array>
,100000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">1</elt>
 <elt ix="4">2</elt>
</array>
,80000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">1</elt>
 <elt ix="4">2</elt>
</array>
,100000);
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">
 <elt ix="1">4</elt>
 <elt ix="2">5</elt>
 <elt ix="3">6</elt>
 <elt ix="4">6</elt>
</array>
,1000000);
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">
 <elt ix="1">2</elt>
 <elt ix="2">2</elt>
 <elt ix="3">0</elt>
 <elt ix="4">3</elt>
</array>
,200000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">2</elt>
 <elt ix="4">2</elt>
</array>
,100000);
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">
 <elt ix="1">7</elt>
 <elt ix="2">7</elt>
 <elt ix="3">4</elt>
 <elt ix="4">4</elt>
</array>
,1200000);
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">
 <elt ix="1">2</elt>
 <elt ix="2">3</elt>
 <elt ix="3">3</elt>
 <elt ix="4">3</elt>
</array>
,800000);
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">
 <elt ix="1">4</elt>
 <elt ix="2">5</elt>
 <elt ix="3">6</elt>
 <elt ix="4">6</elt>
</array>
,2000000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">1</elt>
 <elt ix="4">1</elt>
</array>
,1200000);
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">
 <elt ix="1">4</elt>
 <elt ix="2">5</elt>
 <elt ix="3">5</elt>
 <elt ix="4">3</elt>
</array>
,1900000);
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">
 <elt ix="1">4</elt>
 <elt ix="2">3</elt>
 <elt ix="3">2</elt>
 <elt ix="4">2</elt>
</array>
,900000);
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">
 <elt ix="1">2</elt>
 <elt ix="2">2</elt>
 <elt ix="3">2</elt>
 <elt ix="4">1</elt>
</array>
,400000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">2</elt>
 <elt ix="4">3</elt>
</array>
,300000);
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">
 <elt ix="1">3</elt>
 <elt ix="2">3</elt>
 <elt ix="3">1</elt>
 <elt ix="4">1</elt>
</array>
,1500000);
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">
 <elt ix="1">1</elt>
 <elt ix="2">1</elt>
 <elt ix="3">0</elt>
 <elt ix="4">0</elt>
</array>
,150000);
COMMIT;

/* Inserting data into Table: SALARY_HISTORY */

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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
COMMIT;

/* Inserting data into Table: SALES */

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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
COMMIT;
/* Data Ends */

/*  Index definitions for all user tables */

CREATE INDEX CUSTNAMEX ON CUSTOMER(CUSTOMER);
CREATE INDEX CUSTREGION ON CUSTOMER(COUNTRY, CITY);
CREATE DESCENDING INDEX BUDGETX ON DEPARTMENT(BUDGET);
CREATE INDEX NAMEX ON EMPLOYEE(LAST_NAME, FIRST_NAME);
CREATE DESCENDING INDEX MAXSALX ON JOB(JOB_COUNTRY, MAX_SALARY);
CREATE INDEX MINSALX ON JOB(JOB_COUNTRY, MIN_SALARY);
CREATE UNIQUE INDEX PRODTYPEX ON PROJECT(PRODUCT, PROJ_NAME);
CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY(CHANGE_DATE);
CREATE INDEX UPDATERX ON SALARY_HISTORY(UPDATER_ID);
CREATE INDEX NEEDX ON SALES(DATE_NEEDED);
CREATE DESCENDING INDEX QTYX ON SALES(ITEM_TYPE, QTY_ORDERED);
CREATE INDEX SALESTATX ON SALES(ORDER_STATUS, PAID);
ALTER TABLE CUSTOMER ADD FOREIGN KEY (COUNTRY) REFERENCES COUNTRY (COUNTRY);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (HEAD_DEPT) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MNGR_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY) REFERENCES JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY);
ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);
ALTER TABLE JOB ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);
ALTER TABLE PROJECT ADD FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);
ALTER TABLE SALARY_HISTORY ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE SALES ADD FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER (CUST_NO);
ALTER TABLE SALES ADD FOREIGN KEY (SALES_REP) REFERENCES EMPLOYEE (EMP_NO);

CREATE SEQUENCE CUST_NO_GEN;
ALTER SEQUENCE CUST_NO_GEN RESTART WITH 1016;
CREATE SEQUENCE EMP_NO_GEN;
ALTER SEQUENCE EMP_NO_GEN RESTART WITH 158;
CREATE SEQUENCE FB$OUT_SEQ;
ALTER SEQUENCE FB$OUT_SEQ RESTART WITH 1;

/* View: DEPTLIST, Owner: SYSDBA */

CREATE VIEW DEPTLIST (
  EMP_NO,
  FIRST_NAME,
  LAST_NAME,
  PHONE_EXT,
  HIRE_DATE,
  DEPT_NO,
  JOB_CODE,
  JOB_GRADE,
  JOB_COUNTRY,
  SALARY,
  FULL_NAME,
  DEPT_PATH,
  DEPT_KEY_PATH
) AS
with recursive Depts As (
Select DEPT_NO, DEPARTMENT, HEAD_DEPT, cast(DEPARTMENT  as VarChar(256)) as DEPT_PATH,
cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH
From DEPARTMENT Where HEAD_DEPT is NULL
UNION ALL
Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH ||  ' / ' || D.DEPARTMENT as DEPT_PATH,
Depts.DEPT_KEY_PATH || ';' || D.DEPT_NO as DEPT_KEY_PATH
From DEPARTMENT  D
JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO
)

Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE,
A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH
From EMPLOYEE A
JOIN Depts D On D.DEPT_NO = A.DEPT_NO
;

/* View: PHONE_LIST, Owner: SYSDBA */

CREATE VIEW PHONE_LIST (
  EMP_NO,
  FIRST_NAME,
  LAST_NAME,
  PHONE_EXT,
  LOCATION,
  PHONE_NO
) AS
SELECT
    emp_no, first_name, last_name, phone_ext, location, phone_no
    FROM employee, department
    WHERE employee.dept_no = department.dept_no
;

/* Add Domain Check Constraints */


ALTER DOMAIN BUDGET ADD CONSTRAINT
         CHECK (VALUE > 10000 AND VALUE <= 2000000);

ALTER DOMAIN CUSTNO ADD CONSTRAINT
         CHECK (VALUE > 1000);
ALTER DOMAIN DEPTNO ADD CONSTRAINT
         CHECK (VALUE = '000' OR (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL);


ALTER DOMAIN JOBCODE ADD CONSTRAINT
         CHECK (VALUE > '99999');
ALTER DOMAIN JOBGRADE ADD CONSTRAINT
         CHECK (VALUE BETWEEN 0 AND 6);


ALTER DOMAIN PONUMBER ADD CONSTRAINT
         CHECK (VALUE STARTING WITH 'V');
ALTER DOMAIN PRODTYPE ADD CONSTRAINT
         CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));
ALTER DOMAIN PROJNO ADD CONSTRAINT
         CHECK (VALUE = UPPER (VALUE));
ALTER DOMAIN SALARY ADD CONSTRAINT
         CHECK (VALUE > 0);
ALTER TABLE JOB ADD
CHECK (min_salary < max_salary);

ALTER TABLE JOB ADD
CHECK (min_salary < max_salary);

ALTER TABLE EMPLOYEE ADD
CHECK ( salary >= (SELECT min_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country) AND
            salary <= (SELECT max_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country));

ALTER TABLE PROJ_DEPT_BUDGET ADD
CHECK (FISCAL_YEAR >= 1993);

ALTER TABLE SALARY_HISTORY ADD
CHECK (percent_change between -50 and 50);

ALTER TABLE CUSTOMER ADD
CHECK (on_hold IS NULL OR on_hold = '*');

ALTER TABLE SALES ADD
CHECK (order_status in
                            ('new', 'open', 'shipped', 'waiting'));

ALTER TABLE SALES ADD
CHECK (ship_date >= order_date OR ship_date IS NULL);

ALTER TABLE SALES ADD
CHECK (date_needed > order_date OR date_needed IS NULL);

ALTER TABLE SALES ADD
CHECK (paid in ('y', 'n'));

ALTER TABLE SALES ADD
CHECK (qty_ordered >= 1);

ALTER TABLE SALES ADD
CHECK (total_value >= 0);

ALTER TABLE SALES ADD
CHECK (discount >= 0 AND discount <= 1);

ALTER TABLE SALES ADD
CHECK (NOT (order_status = 'shipped' AND ship_date IS NULL));

ALTER TABLE SALES ADD
CHECK (NOT (order_status = 'shipped' AND
            EXISTS (SELECT on_hold FROM customer
                    WHERE customer.cust_no = sales.cust_no
                    AND customer.on_hold = '*')));


/*  Exceptions */

CREATE EXCEPTION CUSTOMER_CHECK 'Overdue balance -- can not ship.';
CREATE EXCEPTION CUSTOMER_ON_HOLD 'This customer is on hold.';
CREATE EXCEPTION ORDER_ALREADY_SHIPPED 'Order status is "shipped."';
CREATE EXCEPTION REASSIGN_SALES 'Reassign the sales records before deleting this employee.';
CREATE EXCEPTION UNKNOWN_EMP_ID 'Invalid employee number or project id.';
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^;

/* Stored procedures Definitions*/

CREATE PROCEDURE SHOW_LANGS
(
  CODE VARCHAR(5) CHARACTER SET NONE,
  GRADE SMALLINT,
  CTY VARCHAR(15) CHARACTER SET NONE
)
RETURNS
(
  LANGUAGES VARCHAR(15) CHARACTER SET NONE
)
AS
BEGIN SUSPEND; EXIT; END
^

CREATE PROCEDURE ADD_EMP_PROJ
(
  EMP_NO SMALLINT,
  PROJ_ID CHAR(5) CHARACTER SET NONE
)
AS
BEGIN EXIT; END
^

CREATE PROCEDURE ALL_LANGS
RETURNS
(
  CODE VARCHAR(5) CHARACTER SET NONE,
  GRADE VARCHAR(5) CHARACTER SET NONE,
  COUNTRY VARCHAR(15) CHARACTER SET NONE,
  LANG VARCHAR(15) CHARACTER SET NONE
)
AS
BEGIN SUSPEND; EXIT; END
^

CREATE PROCEDURE DELETE_EMPLOYEE
(
  EMP_NUM INTEGER
)
AS
BEGIN EXIT; END
^

CREATE PROCEDURE DEPT_BUDGET
(
  DNO CHAR(3) CHARACTER SET NONE
)
RETURNS
(
  TOT DECIMAL(12,2)
)
AS
BEGIN SUSPEND; EXIT; END
^

CREATE PROCEDURE GET_EMP_PROJ
(
  EMP_NO SMALLINT
)
RETURNS
(
  PROJ_ID CHAR(5) CHARACTER SET NONE
)
AS
BEGIN SUSPEND; EXIT; END
^

CREATE PROCEDURE "Has Space"
(
  ARG1 INTEGER
)
AS
BEGIN EXIT; END
^

CREATE PROCEDURE MAIL_LABEL
(
  CUST_NO INTEGER
)
RETURNS
(
  LINE1 CHAR(40) CHARACTER SET NONE,
  LINE2 CHAR(40) CHARACTER SET NONE,
  LINE3 CHAR(40) CHARACTER SET NONE,
  LINE4 CHAR(40) CHARACTER SET NONE,
  LINE5 CHAR(40) CHARACTER SET NONE,
  LINE6 CHAR(40) CHARACTER SET NONE
)
AS
BEGIN SUSPEND; EXIT; END
^

CREATE PROCEDURE ORG_CHART
RETURNS
(
  HEAD_DEPT CHAR(25) CHARACTER SET NONE,
  DEPARTMENT CHAR(25) CHARACTER SET NONE,
  MNGR_NAME CHAR(20) CHARACTER SET NONE,
  TITLE CHAR(5) CHARACTER SET NONE,
  EMP_CNT INTEGER
)
AS
BEGIN SUSPEND; EXIT; END
^

CREATE PROCEDURE SHIP_ORDER
(
  PO_NUM CHAR(8) CHARACTER SET NONE
)
AS
BEGIN EXIT; END
^

CREATE PROCEDURE SUB_TOT_BUDGET
(
  HEAD_DEPT CHAR(3) CHARACTER SET NONE
)
RETURNS
(
  TOT_BUDGET DECIMAL(12,2),
  AVG_BUDGET DECIMAL(12,2),
  MIN_BUDGET DECIMAL(12,2),
  MAX_BUDGET DECIMAL(12,2)
)
AS
BEGIN SUSPEND; EXIT; END
^

CREATE PROCEDURE "UC SPACE"
(
  ARG1 INTEGER
)
AS
BEGIN EXIT; END
^

CREATE PROCEDURE "iCASE"
AS
BEGIN EXIT; END
^

SET TERM ;^
COMMIT WORK;
SET AUTODDL ON;
SET TERM ^;


/* Triggers only will work for SQL triggers */

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    if (new.cust_no is null) then
    new.cust_no = gen_id(cust_no_gen, 1);
END
^

CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    if (new.emp_no is null) then
    new.emp_no = gen_id(emp_no_gen, 1);
END
^

CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
    IF (old.salary <> new.salary) THEN
        INSERT INTO salary_history
            (emp_no, change_date, updater_id, old_salary, percent_change)
        VALUES (
            old.emp_no,
            'NOW',
            user,
            old.salary,
            (new.salary - old.salary) * 100 / old.salary);
END
^

CREATE TRIGGER POST_NEW_ORDER FOR SALES
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
    POST_EVENT 'new_order';
END
^

COMMIT WORK^
SET TERM ;^
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^;

/* Stored procedure Bodies */

ALTER PROCEDURE SHOW_LANGS
(
  CODE VARCHAR(5) CHARACTER SET NONE,
  GRADE SMALLINT,
  CTY VARCHAR(15) CHARACTER SET NONE
)
RETURNS
(
  LANGUAGES VARCHAR(15) CHARACTER SET NONE
)
AS
DECLARE VARIABLE i INTEGER;
BEGIN
  i = 1;
  WHILE (i <= 5) DO
  BEGIN
    SELECT language_req[:i] FROM joB
    WHERE ((job_code = :code) AND (job_grade = :grade) AND (job_country = :cty)
           AND (language_req IS NOT NULL))
    INTO :languages;
    IF (languages = ' ') THEN  /* Prints 'NULL' instead of blanks */
       languages = 'NULL';
    i = i +1;
    SUSPEND;
  END
END
^

ALTER PROCEDURE ADD_EMP_PROJ
(
  EMP_NO SMALLINT,
  PROJ_ID CHAR(5) CHARACTER SET NONE
)
AS
BEGIN
        BEGIN
        INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
        WHEN SQLCODE -530 DO
                EXCEPTION unknown_emp_id;
        END
END
^

ALTER PROCEDURE ALL_LANGS
RETURNS
(
  CODE VARCHAR(5) CHARACTER SET NONE,
  GRADE VARCHAR(5) CHARACTER SET NONE,
  COUNTRY VARCHAR(15) CHARACTER SET NONE,
  LANG VARCHAR(15) CHARACTER SET NONE
)
AS
BEGIN
        FOR SELECT job_code, job_grade, job_country FROM job
                INTO :code, :grade, :country

        DO
        BEGIN
            FOR SELECT languages FROM show_langs
                    (:code, :grade, :country) INTO :lang DO
                SUSPEND;
            /* Put nice separators between rows */
            code = '=====';
            grade = '=====';
            country = '===============';
            lang = '==============';
            SUSPEND;
        END
    END
^

ALTER PROCEDURE DELETE_EMPLOYEE
(
  EMP_NUM INTEGER
)
AS
DECLARE VARIABLE any_sales INTEGER;
BEGIN
        any_sales = 0;

        /*
         *      If there are any sales records referencing this employee,
         *      cannot delete the employee until the sales are re-assigned
         *      to another employee or changed to NULL.
         */
        SELECT count(po_number)
        FROM sales
        WHERE sales_rep = :emp_num
        INTO :any_sales;

        IF (any_sales > 0) THEN
        BEGIN
                EXCEPTION reassign_sales;
        END

        /*
         *      If the employee is a manager, update the department.
         */
        UPDATE department
        SET mngr_no = NULL
        WHERE mngr_no = :emp_num;

        /*
         *      If the employee is a project leader, update project.
         */
        UPDATE project
        SET team_leader = NULL
        WHERE team_leader = :emp_num;

        /*
         *      Delete the employee from any projects.
         */
        DELETE FROM employee_project
        WHERE emp_no = :emp_num;

        /*
         *      Delete old salary records.
         */
        DELETE FROM salary_history
        WHERE emp_no = :emp_num;

        /*
         *      Delete the employee.
         */
        DELETE FROM employee
        WHERE emp_no = :emp_num;

END
^

ALTER PROCEDURE DEPT_BUDGET
(
  DNO CHAR(3) CHARACTER SET NONE
)
RETURNS
(
  TOT DECIMAL(12,2)
)
AS
DECLARE VARIABLE sumb DECIMAL(12,2);
        DECLARE VARIABLE rdno CHAR(3);
        DECLARE VARIABLE cnt INTEGER;
BEGIN
        tot = 0;

        SELECT budget FROM department WHERE dept_no = :dno INTO :tot;

        SELECT count(budget) FROM department WHERE head_dept = :dno INTO :cnt;

        IF (cnt = 0) THEN
                SUSPEND;

        FOR SELECT dept_no
                FROM department
                WHERE head_dept = :dno
                INTO :rdno
        DO
                BEGIN
                        EXECUTE PROCEDURE dept_budget :rdno RETURNING_VALUES :sumb;
                        tot = tot + sumb;
                END

        SUSPEND;
END
^

ALTER PROCEDURE GET_EMP_PROJ
(
  EMP_NO SMALLINT
)
RETURNS
(
  PROJ_ID CHAR(5) CHARACTER SET NONE
)
AS
BEGIN
        FOR SELECT proj_id
                FROM employee_project
                WHERE emp_no = :emp_no
                INTO :proj_id
        DO
                SUSPEND;
END
^

ALTER PROCEDURE "Has Space"
(
  ARG1 INTEGER
)
AS
Begin End
^

ALTER PROCEDURE MAIL_LABEL
(
  CUST_NO INTEGER
)
RETURNS
(
  LINE1 CHAR(40) CHARACTER SET NONE,
  LINE2 CHAR(40) CHARACTER SET NONE,
  LINE3 CHAR(40) CHARACTER SET NONE,
  LINE4 CHAR(40) CHARACTER SET NONE,
  LINE5 CHAR(40) CHARACTER SET NONE,
  LINE6 CHAR(40) CHARACTER SET NONE
)
AS
DECLARE VARIABLE customer       VARCHAR(25);
        DECLARE VARIABLE first_name             VARCHAR(15);
        DECLARE VARIABLE last_name              VARCHAR(20);
        DECLARE VARIABLE addr1          VARCHAR(30);
        DECLARE VARIABLE addr2          VARCHAR(30);
        DECLARE VARIABLE city           VARCHAR(25);
        DECLARE VARIABLE state          VARCHAR(15);
        DECLARE VARIABLE country        VARCHAR(15);
        DECLARE VARIABLE postcode       VARCHAR(12);
        DECLARE VARIABLE cnt            INTEGER;
BEGIN
        line1 = '';
        line2 = '';
        line3 = '';
        line4 = '';
        line5 = '';
        line6 = '';

        SELECT customer, contact_first, contact_last, address_line1,
                address_line2, city, state_province, country, postal_code
        FROM CUSTOMER
        WHERE cust_no = :cust_no
        INTO :customer, :first_name, :last_name, :addr1, :addr2,
                :city, :state, :country, :postcode;

        IF (customer IS NOT NULL) THEN
                line1 = customer;
        IF (first_name IS NOT NULL) THEN
                line2 = first_name || ' ' || last_name;
        ELSE
                line2 = last_name;
        IF (addr1 IS NOT NULL) THEN
                line3 = addr1;
        IF (addr2 IS NOT NULL) THEN
                line4 = addr2;

        IF (country = 'USA') THEN
        BEGIN
                IF (city IS NOT NULL) THEN
                        line5 = city || ', ' || state || '  ' || postcode;
                ELSE
                        line5 = state || '  ' || postcode;
        END
        ELSE
        BEGIN
                IF (city IS NOT NULL) THEN
                        line5 = city || ', ' || state;
                ELSE
                        line5 = state;
                line6 = country || '    ' || postcode;
        END

        SUSPEND;
END
^

ALTER PROCEDURE ORG_CHART
RETURNS
(
  HEAD_DEPT CHAR(25) CHARACTER SET NONE,
  DEPARTMENT CHAR(25) CHARACTER SET NONE,
  MNGR_NAME CHAR(20) CHARACTER SET NONE,
  TITLE CHAR(5) CHARACTER SET NONE,
  EMP_CNT INTEGER
)
AS
DECLARE VARIABLE mngr_no INTEGER;
        DECLARE VARIABLE dno CHAR(3);
BEGIN
        FOR SELECT h.department, d.department, d.mngr_no, d.dept_no
                FROM department d
                LEFT OUTER JOIN department h ON d.head_dept = h.dept_no
                ORDER BY d.dept_no
                INTO :head_dept, :department, :mngr_no, :dno
        DO
        BEGIN
                IF (:mngr_no IS NULL) THEN
                BEGIN
                        mngr_name = '--TBH--';
                        title = '';
                END

                ELSE
                        SELECT full_name, job_code
                        FROM employee
                        WHERE emp_no = :mngr_no
                        INTO :mngr_name, :title;

                SELECT COUNT(emp_no)
                FROM employee
                WHERE dept_no = :dno
                INTO :emp_cnt;

                SUSPEND;
        END
END
^

ALTER PROCEDURE SHIP_ORDER
(
  PO_NUM CHAR(8) CHARACTER SET NONE
)
AS
DECLARE VARIABLE ord_stat CHAR(7);
        DECLARE VARIABLE hold_stat CHAR(1);
        DECLARE VARIABLE cust_no INTEGER;
        DECLARE VARIABLE any_po CHAR(8);
BEGIN
        SELECT s.order_status, c.on_hold, c.cust_no
        FROM sales s, customer c
        WHERE po_number = :po_num
        AND s.cust_no = c.cust_no
        INTO :ord_stat, :hold_stat, :cust_no;

        /* This purchase order has been already shipped. */
        IF (ord_stat = 'shipped') THEN
        BEGIN
                EXCEPTION order_already_shipped;
        END

        /*      Customer is on hold. */
        ELSE IF (hold_stat = '*') THEN
        BEGIN
                EXCEPTION customer_on_hold;
        END

        /*
         *      If there is an unpaid balance on orders shipped over 2 months ago,
         *      put the customer on hold.
         */
        FOR SELECT po_number
                FROM sales
                WHERE cust_no = :cust_no
                AND order_status = 'shipped'
                AND paid = 'n'
                AND ship_date < CAST('NOW' AS TIMESTAMP) - 60
                INTO :any_po
        DO
        BEGIN
                EXCEPTION customer_check;

                UPDATE customer
                SET on_hold = '*'
                WHERE cust_no = :cust_no;

        END

        /*
         *      Ship the order.
         */
        UPDATE sales
        SET order_status = 'shipped', ship_date = 'NOW'
        WHERE po_number = :po_num;

END
^

ALTER PROCEDURE SUB_TOT_BUDGET
(
  HEAD_DEPT CHAR(3) CHARACTER SET NONE
)
RETURNS
(
  TOT_BUDGET DECIMAL(12,2),
  AVG_BUDGET DECIMAL(12,2),
  MIN_BUDGET DECIMAL(12,2),
  MAX_BUDGET DECIMAL(12,2)
)
AS
BEGIN
        SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)
                FROM department
                WHERE head_dept = :head_dept
                INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
        SUSPEND;
END
^

ALTER PROCEDURE "UC SPACE"
(
  ARG1 INTEGER
)
AS
Begin End
^

ALTER PROCEDURE "iCASE"
AS
Begin End
^

SET TERM ;^
COMMIT WORK;
SET AUTODDL ON;

/* Comments on System Objects */

COMMENT ON CHARACTER SET UTF8 IS 'Default Character set';

/* Grant Roles for this database */


/* Grant permissions for this database */

GRANT REFERENCES ON TABLE COUNTRIES TO USER BOB  ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE COUNTRY TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE CUSTOMER TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE DEPARTMENT TO USER PUBLIC  WITH GRANT OPTION ;
GRANT SELECT ON TABLE EMPLOYEE TO USER ALICE  WITH GRANT OPTION GRANTED BY BOB ;
GRANT SELECT,Update(FIRST_NAME,LAST_NAME) ON TABLE EMPLOYEE TO USER BOB  ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE EMPLOYEE TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE EMPLOYEE_PROJECT TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE JOB TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE PHONE_LIST TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE PROJECT TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE PROJ_DEPT_BUDGET TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE SALARY_HISTORY TO USER PUBLIC  WITH GRANT OPTION ;
GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON TABLE SALES TO USER PUBLIC  WITH GRANT OPTION ;
GRANT RDB$ADMIN TO ALICE;

GRANT RDB$ADMIN TO BOB;

GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE ALL_LANGS TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE DELETE_EMPLOYEE TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE DEPT_BUDGET TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE GET_EMP_PROJ TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE MAIL_LABEL TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE ORG_CHART TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE SHIP_ORDER TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE SHOW_LANGS TO USER PUBLIC  WITH GRANT OPTION ;
GRANT EXECUTE ON PROCEDURE SUB_TOT_BUDGET TO USER PUBLIC  WITH GRANT OPTION ;
Revision:	349
Committed:	Mon Oct 18 08:39:40 2021 UTC (2 years, 6 months ago) by tony
Content type:	application/sql
File size:	83095 byte(s)
Log Message:	FIxes Merged