employee.sql

jvcl driver development envionment

    AVG_BUDGET NUMERIC(15,2),
    MIN_BUDGET NUMERIC(15,2),
    MAX_BUDGET NUMERIC(15,2))
AS
BEGIN
  EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/

CREATE TABLE COUNTRY (
    COUNTRY   COUNTRYNAME NOT NULL,
    CURRENCY  VARCHAR(10) CHARACTER SET NONE NOT NULL
);


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


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


CREATE TABLE EMPLOYEE (
    EMP_NO       EMPNO NOT NULL,
    FIRST_NAME   FIRSTNAME NOT NULL,
    LAST_NAME    LASTNAME NOT NULL,
    PHONE_EXT    VARCHAR(4) CHARACTER SET NONE,
    HIRE_DATE    DATE 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)
);


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


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


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


CREATE TABLE PROJECT (
    PROJ_ID      PROJNO NOT NULL,
    PROJ_NAME    VARCHAR(20) CHARACTER SET NONE NOT NULL,
    PROJ_DESC    BLOB SUB_TYPE 1 SEGMENT SIZE 800,
    TEAM_LEADER  EMPNO,
    PRODUCT      PRODTYPE
);


CREATE TABLE SALARY_HISTORY (
    EMP_NO          EMPNO NOT NULL,
    CHANGE_DATE     DATE DEFAULT 'NOW' NOT NULL,
    UPDATER_ID      VARCHAR(20) CHARACTER SET NONE 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)
);


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


CREATE TABLE TBLOB (
    STREAM  BLOB SUB_TYPE 0 SEGMENT SIZE 80
);





/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: PHONE_LIST */
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;


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', 'Lira');
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('France', 'FFranc');
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('Germany', 'D-Mark');
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('Australia', 'ADollar');
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('Hong Kong', 'HKDollar');
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('Netherlands', 'Guilder');
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('Belgium', 'BFranc');
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('Austria', 'Schilling');
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('Fiji', 'FDollar');

COMMIT WORK;

INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CONTACT_FIRST, CONTACT_LAST, PHONE_NO, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE_PROVINCE, COUNTRY, POSTAL_CODE, ON_HOLD) VALUES (1001, 'Signature Design', 'Dale J.', 'Little', '[phone]', '15500 Pacific Heights Blvd.', NULL, 'San Diego', 'CA', 'USA', '92121', 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) VALUES (1002, 'Dallas Technologies', 'Glen', 'Brown', '[phone]', 'P. O. Box 47000', NULL, 'Dallas', 'TX', 'USA', '75205', '*');
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CONTACT_FIRST, CONTACT_LAST, PHONE_NO, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE_PROVINCE, COUNTRY, POSTAL_CODE, ON_HOLD) VALUES (1003, 'Buttle, Griffith and Co.', 'James', 'Buttle', '[phone]', '2300 Newbury Street', 'Suite 101', 'Boston', 'MA', 'USA', '02115', 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) VALUES (1004, 'Central Bank', 'Elizabeth', 'Brocket', '61 211 99 88', '66 Lloyd Street', NULL, 'Manchester', NULL, 'England', 'M2 3LA', 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) VALUES (1005, 'DT Systems, LTD.', 'Tai', 'Wu', '(852) 850 43 98', '400 Connaught Road', NULL, 'Central Hong Kong', NULL, 'Hong Kong', 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) VALUES (1006, 'DataServe International', 'Tomas', 'Bright', '[phone]', '2000 Carling Avenue', 'Suite 150', 'Ottawa', 'ON', 'Canada', 'K1V 9G1', 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) VALUES (1007, 'Mrs. Beauvais', NULL, 'Mrs. Beauvais', NULL, 'P.O. Box 22743', NULL, 'Pebble Beach', 'CA', 'USA', '93953', 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) VALUES (1008, 'Anini Vacation Rentals', 'Leilani', 'Briggs', '[phone]', '3320 Lawai Road', NULL, 'Lihue', 'HI', 'USA', '96766', 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) VALUES (1009, 'Max', 'Max', NULL, '22 01 23', '1 Emerald Cove', NULL, 'Turtle Island', NULL, 'Fiji', 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) VALUES (1010, 'MPM Corporation', 'Miwako', 'Miyamoto', '3 880 77 19', '2-64-7 Sasazuka', NULL, 'Tokyo', NULL, 'Japan', '150', 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) VALUES (1011, 'Dynamic Intelligence Corp', 'Victor', 'Granges', '01 221 16 50', 'Florhofgasse 10', NULL, 'Zurich', NULL, 'Switzerland', '8005', 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) VALUES (1012, '3D-Pad Corp.', 'Michelle', 'Roche', '1 43 60 61', '22 Place de la Concorde', NULL, 'Paris', NULL, 'France', '75008', 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) VALUES (1013, 'Lorenzi Export, Ltd.', 'Andreas', 'Lorenzi', '02 404 6284', 'Via Eugenia, 15', NULL, 'Milan', NULL, 'Italy', '20124', 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) VALUES (1014, 'Dyno Consulting', 'Greta', 'Hessels', '02 500 5940', 'Rue Royale 350', NULL, 'Brussels', NULL, 'Belgium', '1210', 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) VALUES (1015, 'GeoTech Inc.', 'K.M.', 'Neppelenbroek', '(070) 44 91 18', 'P.0.Box 702', NULL, 'Den Haag', NULL, 'Netherlands', '2514', NULL);

COMMIT WORK;

INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('000', 'Corporate Headquarters', NULL, 105, 1000000, 'Monterey', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('100', 'Sales and Marketing', '000', 85, 2000000, 'San Francisco', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('600', 'Engineering', '000', 2, 1100000, 'Monterey', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('900', 'Finance', '000', 46, 400000, 'Monterey', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('180', 'Marketing', '100', NULL, 1500000, 'San Francisco', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('620', 'Software Products Div.', '600', NULL, 1200000, 'Monterey', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('621', 'Software Development', '620', NULL, 400000, 'Monterey', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('622', 'Quality Assurance', '620', 9, 300000, 'Monterey', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('623', 'Customer Support', '620', 15, 650000, 'Monterey', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('670', 'Consumer Electronics Div.', '600', 107, 1150000, 'Burlington, VT', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('671', 'Research and Development', '670', 20, 460000, 'Burlington, VT', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('672', 'Customer Services', '670', 94, 850000, 'Burlington, VT', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('130', 'Field Office: East Coast', '100', 11, 500000, 'Boston', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('140', 'Field Office: Canada', '100', 72, 500000, 'Toronto', '[phone]');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT, HEAD_DEPT, MNGR_NO, BUDGET, LOCATION, PHONE_NO) VALUES ('110', 'Pacific Rim Headquarters', '100', 34, 600000, 'Kuaui', '[phone]');
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');