/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
** 
** The following sample of source code ("Sample") is owned by International 
** Business Machines Corporation or one of its subsidiaries ("IBM") and is 
** copyrighted and licensed, not sold. You may use, copy, modify, and 
** distribute the Sample in any form without payment to IBM, for the purpose of 
** assisting you in the development of your applications.
** 
** The Sample code is provided to you on an "AS IS" basis, without warranty of 
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
** not allow for the exclusion or limitation of implied warranties, so the above 
** limitations or exclusions may not apply to you. IBM shall not be liable for 
** any damages you suffer as a result of using, copying, modifying or 
** distributing the Sample, even if IBM has been advised of the possibility of 
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: dbsample.sqc
**    
** SAMPLE: Creates a sample database
** 
**         This program creates a sample database with both DB2 workstation 
**         and host system (OS/390 and AS/400) sample tables and views.
**         The sample database consists of eleven tables. The database
**         name is  DBSAMPLE, and the table names are ORG, STAFF,  
**         DEPARTMENT, EMPLOYEE, EMP_ACT, PROJECT, EMP_PHOTO and EMP_RESUME,
**         SALES, CL_SCHED, and IN_TRAY. 
**                                                                         
**         If the DBSAMPLE database already exists, the program does not 
**         recreate it and continues.  DBSAMPLE's access plan is then bound 
**         to the database, and the tables are created and populated.
**         When the program completes, database connection is reset.
**                                                                  
** DB2 APIs USED:                                                     
**         db2DatabaseRestart -- RESTART DATABASE                     
**         sqlecrea -- CREATE DATABASE                                   
**         sqlefrce -- FORCE APPLICATION                               
**         sqlabndx -- BIND PACKAGE  
**                                                                         
** SQL STATEMENTS USED:                                             
**         INCLUDE                                              
**         CONNECT                                                
**         BEGIN                                                
**         END                                                     
**         GRANT                                                   
**         CONNECT                                              
**         EXECUTE                                              
**         COMMIT                                                      
**                 
** STRUCTURES USED:    
**         sqlca     
**         sqlopt 
**         sqloptions 
**         VARCHAR 
**
**                           
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For information on DB2 APIs, see the Administrative API Reference.
**
** For the latest information on programming, building, and running DB2 
** applications, visit the DB2 Information Center: 
**     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include <db2ApiDf.h>
#include <sqle819a.h>
#include "utilemb.h"


EXEC SQL INCLUDE SQLCA;

int execi(char s[1000]);

EXEC SQL BEGIN DECLARE SECTION;
struct VARCHAR {
  short len;
  char  stmt[1000];
  } sqlstmt = {14,"create schema "};/* Varchar for statement text    */
EXEC SQL END DECLARE SECTION;

int DbConnect(char *, char *, char *);
int DbConnectCheck(char *, char *, char *);
int DbRestart(char *, char *, char *);
int DbDisconnect(void);
int AllApplicationsConnectedToAllDatabasesForceOff(void);
int BndFileCreate(char *);
int PackageCreate(char *);


int main(int argc, char *argv[])
{
  int rc = 0;
  char nodeName[SQL_INSTNAME_SZ + 1];
  char sourceFileBaseName[50 + 1];

  EXEC SQL BEGIN DECLARE SECTION;
    char dbAlias[15];
    char user[128 + 1];
    char pswd[15];
  EXEC SQL END DECLARE SECTION;

  /* check the command line arguments */
  rc = CmdLineArgsCheck3(argc, argv, dbAlias, nodeName, user, pswd);
  if (rc != 0)
  {
    return rc;
  }
  
  printf("\nTHIS SAMPLE CREATES A SAMPLE DATABASE.");
  printf("\n");

  strcpy(dbAlias, "dbsample");   
  printf("Database name is %s", dbAlias);
  printf("\n"); 

  rc = DbConnectCheck(dbAlias, user, pswd);
  if(rc == 0)
  {
    printf("\nDBSAMPLE ALREADY EXISTS!\n");
    printf("\n");
    return rc;
  }
  if(rc == 1)
  {
    printf("\nCreate Database DBSAMPLE\n");
    printf("\n");
  }
  
  sqlecrea(dbAlias, NULL, NULL, NULL, NULL, 0, NULL, &sqlca);
 
  printf("\nConnect DBSAMPLE");
  
  rc = DbConnect(dbAlias, user, pswd);
  
  EMB_SQL_CHECK("Check Connection");

  strcpy(sourceFileBaseName, "./dbsample");

  rc = PackageCreate(sourceFileBaseName);

  rc = DbRestart(dbAlias, user, pswd);

  EXEC SQL GRANT CONNECT ON DATABASE TO PUBLIC;
 
  EMB_SQL_CHECK("Grant Connection");
 
  EXEC SQL GRANT BINDADD ON DATABASE TO PUBLIC;

  EMB_SQL_CHECK("Grant BindAdd");

  EXEC SQL GRANT CREATETAB ON DATABASE TO PUBLIC;

  EMB_SQL_CHECK("Grant createtab");

  EXEC SQL GRANT EXECUTE ON PROGRAM DBSAMPLE TO PUBLIC;

  EMB_SQL_CHECK("Grant execute"); 


  printf("\ncreate table");
/*********************************************************************/
/*                                                                   */
/* ORG                                                               */
/*                                                                   */
/*********************************************************************/
/*
execi(
"CREATE TABLE ORG (DEPTNUMB SMALLINT NOT NULL,  DEPTNAME VARCHAR(14),MANAGER SMALLINT,  DIVISION VARCHAR(10),  LOCATION
VARCHAR(13))"    );
*/
execi(
"CREATE TABLE ORG "
"             (DEPTNUMB SMALLINT NOT NULL, "
"              DEPTNAME VARCHAR(14),MANAGER SMALLINT, "
"              DIVISION VARCHAR(10), "
"              LOCATION VARCHAR(13))"    );

/*********************************************************************/
/*                                                                   */
/* ORG                                                               */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO ORG VALUES( "
" 10,'Head Office', 160,'Corporate','New York')"   );

execi(
"INSERT INTO ORG VALUES( "
" 15,'New England', 50, 'Eastern','Boston')"   );

execi(
"INSERT INTO ORG VALUES( "
" 20, 'Mid Atlantic', 10, 'Eastern', 'Washington')"   );

execi(
"INSERT INTO ORG VALUES( "
" 38, 'South Atlantic', 30, 'Eastern', 'Atlanta')"   );

execi(
"INSERT INTO ORG VALUES( "
" 42, 'Great Lakes', 100, 'Midwest', 'Chicago')"   );

execi(
"INSERT INTO ORG VALUES( "
" 51, 'Plains', 140, 'Midwest', 'Dallas')"   );

execi(
"INSERT INTO ORG VALUES( "
" 66, 'Pacific', 270, 'Western', 'San Francisco')"   );

execi(
"INSERT INTO ORG VALUES( "
" 84, 'Mountain', 290, 'Western', 'Denver')"   ); 

/*********************************************************************/
/*                                                                   */
/* STAFF                                                             */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE STAFF \
             (ID  SMALLINT NOT NULL, \
              NAME VARCHAR(9), \
              DEPT SMALLINT, \
              JOB CHAR(5), \
              YEARS SMALLINT, \
              SALARY DECIMAL(7,2), \
              COMM DECIMAL(7,2))"       );

/*********************************************************************/
/*                                                                   */
/* STAFF                                                             */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO STAFF VALUES( \
10, 'Sanders', 20, 'Mgr', 7, 18357.50, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
20, 'Pernal', 20, 'Sales', 8, 18171.25, 612.45)"   );

execi(
"INSERT INTO STAFF VALUES( \
30, 'Marenghi', 38, 'Mgr', 5, 17506.75, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
40, 'O''Brien', 38, 'Sales', 6, 18006.00, 846.55)"   );

execi(
"INSERT INTO STAFF VALUES( \
50, 'Hanes', 15, 'Mgr', 10, 20659.80, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
60, 'Quigley', 38, 'Sales', NULL, 16808.30, 650.25)"   );

execi(
"INSERT INTO STAFF VALUES( \
70, 'Rothman', 15, 'Sales', 7, 16502.83, 1152.00)"   );

execi(
"INSERT INTO STAFF VALUES( \
80, 'James', 20, 'Clerk', NULL, 13504.60, 128.20)"   );

execi(
"INSERT INTO STAFF VALUES( \
90, 'Koonitz', 42, 'Sales', 6, 18001.75, 1386.70)"   );

execi(
"INSERT INTO STAFF VALUES( \
100, 'Plotz', 42, 'Mgr', 7, 18352.80, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
110, 'Ngan', 15, 'Clerk', 5, 12508.20, 206.60)"   );

execi(
"INSERT INTO STAFF VALUES( \
120, 'Naughton', 38, 'Clerk', NULL, 12954.75, 180.00)"   );

execi(
"INSERT INTO STAFF VALUES( \
130, 'Yamaguchi', 42, 'Clerk', 6, 10505.90, 75.60)"   );

execi(
"INSERT INTO STAFF VALUES( \
140, 'Fraye', 51, 'Mgr', 6, 21150.00, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
150, 'Williams', 51, 'Sales', 6, 19456.50, 637.65)"   );

execi(
"INSERT INTO STAFF VALUES( \
160, 'Molinare', 10, 'Mgr', 7, 22959.20, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
170, 'Kermisch', 15, 'Clerk', 4, 12258.50, 110.10)"   );

execi(
"INSERT INTO STAFF VALUES( \
180, 'Abrahams', 38, 'Clerk', 3, 12009.75,236.50)"   );

execi(
"INSERT INTO STAFF VALUES( \
190, 'Sneider', 20, 'Clerk', 8, 14252.75, 126.50)"   );

execi(
"INSERT INTO STAFF VALUES( \
200, 'Scoutten', 42, 'Clerk', NULL, 11508.60, 84.20)"   );

execi(
"INSERT INTO STAFF VALUES( \
210, 'Lu', 10, 'Mgr', 10, 20010.00, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
220, 'Smith', 51, 'Sales', 7, 17654.50, 992.80)"   );

execi(
"INSERT INTO STAFF VALUES( \
230, 'Lundquist', 51, 'Clerk', 3, 13369.80, 189.65)"   );

execi(
"INSERT INTO STAFF VALUES( \
240, 'Daniels', 10, 'Mgr', 5, 19260.25, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
250, 'Wheeler', 51, 'Clerk', 6, 14460.00, 513.30)"   );

execi(
"INSERT INTO STAFF VALUES( \
260, 'Jones', 10, 'Mgr', 12, 21234.00, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
270, 'Lea', 66, 'Mgr', 9, 18555.50, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
280, 'Wilson', 66, 'Sales', 9, 18674.50, 811.50)"   );

execi(
"INSERT INTO STAFF VALUES( \
290, 'Quill', 84, 'Mgr', 10, 19818.00, NULL)"   );

execi(
"INSERT INTO STAFF VALUES( \
300, 'Davis', 84, 'Sales', 5, 15454.50, 806.10)"   );

execi(
"INSERT INTO STAFF VALUES( \
310, 'Graham', 66, 'Sales', 13, 21000.00, 200.30)"   );

execi(
"INSERT INTO STAFF VALUES( \
320, 'Gonzales', 66, 'Sales', 4, 16858.20, 844.00)"   );

execi(
"INSERT INTO STAFF VALUES( \
330, 'Burke', 66, 'Clerk', 1, 10988.00, 55.50)"   );

execi(
"INSERT INTO STAFF VALUES( \
340, 'Edwards', 84, 'Sales', 7, 17844.00, 1285.00)"   );

execi(
"INSERT INTO STAFF VALUES( \
350, 'Gafney', 84, 'Clerk', 5, 13030.50, 188.00)"   );

/*********************************************************************/
/*                                                                   */
/* DEPARTMENT                                                        */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE DEPARTMENT ( \
              DEPTNO CHAR(3) NOT NULL, \
              DEPTNAME VARCHAR(36) NOT NULL, \
              MGRNO CHAR(6), \
              ADMRDEPT CHAR(3) NOT NULL, \
              LOCATION CHAR(16), \
              PRIMARY KEY (DEPTNO))"    );

execi(
"ALTER  TABLE DEPARTMENT \
              ADD FOREIGN KEY  ROD (ADMRDEPT) \
              REFERENCES DEPARTMENT \
              ON DELETE CASCADE"        );

execi(
"CREATE INDEX XDEPT2 \
              ON DEPARTMENT (MGRNO)"    );

execi(
"CREATE INDEX XDEPT3 \
              ON DEPARTMENT (ADMRDEPT)" );

execi(
"CREATE ALIAS DEPT FOR DEPARTMENT"      );

/*********************************************************************/
/*                                                                   */
/* DEPARTMENT                                                        */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'A00' ,'SPIFFY COMPUTER SERVICE DIV.' ,      '000010', 'A00', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'B01', 'PLANNING',                           '000020', 'A00', NULL)"   );
execi(
"INSERT INTO  DEPARTMENT VALUES( \
'C01', 'INFORMATION CENTER',                 '000030', 'A00', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'D01', 'DEVELOPMENT CENTER',                 NULL,     'A00',NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'D11', 'MANUFACTURING SYSTEMS',              '000060', 'D01', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'D21', 'ADMINISTRATION SYSTEMS',             '000070', 'D01', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'E01', 'SUPPORT SERVICES',                   '000050', 'A00', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'E11', 'OPERATIONS',                         '000090', 'E01', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'E21', 'SOFTWARE SUPPORT','000100', 'E01', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'F22', 'BRANCH OFFICE F2',NULL, 'E01', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'G22', 'BRANCH OFFICE G2',NULL, 'E01', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'H22', 'BRANCH OFFICE H2',NULL, 'E01', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'I22', 'BRANCH OFFICE I2',NULL, 'E01', NULL)"   );

execi(
"INSERT INTO  DEPARTMENT VALUES( \
'J22', 'BRANCH OFFICE J2',NULL, 'E01', NULL)"   );

/*********************************************************************/
/*                                                                   */
/* EMPLOYEE                                                          */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE EMPLOYEE ( \
              EMPNO CHAR(6) NOT NULL, \
              FIRSTNME VARCHAR(12) NOT NULL, \
              MIDINIT CHAR(1) NOT NULL, \
              LASTNAME VARCHAR(15) NOT NULL, \
              WORKDEPT CHAR(3), \
              PHONENO CHAR(4), \
              HIREDATE DATE, \
              JOB CHAR(8), \
              EDLEVEL SMALLINT NOT NULL, \
              SEX CHAR(1), \
              BIRTHDATE DATE, \
              SALARY DECIMAL(9,2), \
              BONUS DECIMAL(9,2),COMM DECIMAL(9,2), \
              PRIMARY KEY (EMPNO))"     );

execi(
"ALTER TABLE EMPLOYEE \
              ADD FOREIGN KEY  RED (WORKDEPT) \
              REFERENCES DEPARTMENT \
              ON DELETE SET NULL"       );

execi(
"ALTER TABLE EMPLOYEE \
             ADD CONSTRAINT NUMBER \
             CHECK (PHONENO >= '0000' AND PHONENO <= '9999')" );

execi(
"CREATE INDEX XEMP2 \
             ON EMPLOYEE (WORKDEPT)"    );

execi(
"CREATE ALIAS EMP FOR EMPLOYEE"         );

/*********************************************************************/
/*                                                                   */
/* EMPLOYEE                                                          */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000010','CHRISTINE',     'I',  'HAAS',           'A00',  '3978', \
 '1965-01-01','PRES' ,       18 , 'F', '1933-08-24',  52750.00,   \
1000,4220)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000020','MICHAEL',       'L',  'THOMPSON',       'B01',  '3476', \
'1973-10-10','MANAGER',    18 , 'M', '1948-02-02',  41250.00,     \
800,3300)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000030','SALLY',         'A',  'KWAN',           'C01',  '4738', \
'1975-04-05', 'MANAGER',   20, 'F', '1941-05-11',  38250.00,      \
800,3060)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000050','JOHN',          'B','GEYER',          'E01',  '6789',   \
 '1949-08-17','MANAGER',      16,  'M', '1925-09-15',  40175.00,  \
800,3214)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000060','IRVING',        'F',  'STERN',          'D11',  '6423', \
 '1973-09-14','MANAGER',     16,  'M', '1945-07-07',  32250.00,   \
500,2580)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000070','EVA',           'D',  'PULASKI',        'D21',  '7831', \
 '1980-09-30','MANAGER',     16,  'F', '1953-05-26',  36170.00,   \
700,2893)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000090','EILEEN',        'W',  'HENDERSON',      'E11',  '5498', \
 '1970-08-15','MANAGER',   16,  'F', '1941-05-15',  29750.00,     \
600,2380)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000100','THEODORE',      'Q',  'SPENSER',        'E21',  '0972', \
 '1980-06-19','MANAGER',     14 , 'M', '1956-12-18',  26150.00,   \
500,2092)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000110','VICENZO',       'G',  'LUCCHESSI',      'A00',  '3490', \
 '1958-05-16','SALESREP',    19,  'M', '1929-11-05',  46500.00,   \
900,3720)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000120','SEAN',        ' ',    'O''CONNELL',      'A00',  '2167',\
 '1963-12-05','CLERK',       14,  'M', '1942-10-18',  29250.00,   \
600,2340)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000130','DELORES',       'M',  'QUINTANA',       'C01',  '4578', \
 '1971-07-28','ANALYST',     16,  'F', '1925-09-15',  23800.00,   \
500,1904)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000140','HEATHER',       'A',  'NICHOLLS',       'C01',  '1793', \
 '1976-12-15','ANALYST',     18,  'F', '1946-01-19',  28420.00,   \
600,2274)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000150','BRUCE',        ' ',   'ADAMSON',        'D11',  '4510', \
 '1972-02-12','DESIGNER',    16,  'M', '1947-05-17',  25280.00,   \
500,2022)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000160','ELIZABETH',     'R',  'PIANKA',         'D11',  '3782', \
 '1977-10-11','DESIGNER',    17,  'F', '1955-04-12',  22250.00,   \
400,1780)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000170','MASATOSHI',     'J',  'YOSHIMURA',      'D11',  '2890', \
 '1978-09-15','DESIGNER',    16,  'M', '1951-01-05',  24680.00,   \
500,1974)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000180','MARILYN',       'S',  'SCOUTTEN',       'D11',  '1682', \
'1973-07-07','DESIGNER',    17,  'F', '1949-02-21',  21340.00,    \
500,1707)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000190','JAMES',         'H',  'WALKER',         'D11',  '2986', \
'1974-07-26','DESIGNER',    16,  'M', '1952-06-25',  20450.00,    \
400,1636)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000200','DAVID',        ' ',   'BROWN',          'D11',  '4501', \
'1966-03-03','DESIGNER',    16,  'M', '1941-05-29',  27740.00,    \
600,2217)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000210','WILLIAM',       'T',  'JONES',          'D11',  '0942', \
'1979-04-11','DESIGNER',    17,  'M', '1953-02-23',  18270.00,    \
400,1462)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000220','JENNIFER',      'K',  'LUTZ',           'D11',  '0672', \
'1968-08-29','DESIGNER',    18,  'F', '1948-03-19',  29840.00,    \
600,2387)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000230','JAMES',         'J',  'JEFFERSON',      'D21',  '2094', \
'1966-11-21','CLERK',       14,  'M', '1935-05-30',  22180.00,    \
400,1774)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000240', 'SALVATORE',     'M',  'MARINO',         'D21',  '3780',\
'1979-12-05','CLERK',       17,  'M', '1954-03-31',  28760.00,    \
600,2301)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000250', 'DANIEL',        'S',  'SMITH',          'D21',  '0961',\
'1969-10-30','CLERK',       15,  'M', '1939-11-12',  19180.00,    \
400,1534)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000260', 'SYBIL',         'P',  'JOHNSON',        'D21',  '8953',\
'1975-09-11','CLERK',       16,  'F', '1936-10-05',  17250.00,    \
300,1380)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000270', 'MARIA',         'L',  'PEREZ',          'D21',  '9001',\
'1980-09-30','CLERK',       15,  'F', '1953-05-26',  27380.00,    \
500,2190)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000280', 'ETHEL',         'R',  'SCHNEIDER',      'E11',  '8997',\
'1967-03-24','OPERATOR',    17,  'F', '1936-03-28',  26250.00,    \
500,2100)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000290', 'JOHN',          'R',  'PARKER',         'E11',  '4502',\
'1980-05-30','OPERATOR',    12,  'M', '1946-07-09',  15340.00,    \
300,1227)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000300', 'PHILIP',        'X',  'SMITH',          'E11',  '2095',\
'1972-06-19','OPERATOR',    14,  'M', '1936-10-27',  17750.00,    \
400,1420)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000310', 'MAUDE',         'F',  'SETRIGHT',       'E11',  '3332',\
'1964-09-12','OPERATOR',    12,  'F', '1931-04-21',  15900.00,    \
300,1272)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000320', 'RAMLAL',        'V',  'MEHTA',          'E21',  '9990',\
'1965-07-07','FILEREP',     16,  'M', '1932-08-11',  19950.00,    \
400,1596)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000330', 'WING',          ' ',  'LEE',            'E21',  '2103',\
'1976-02-23','FILEREP',     14,  'M', '1941-07-18',  25370.00,    \
500,2030)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'000340', 'JASON',         'R',  'GOUNOT',      'E21',  '5698',   \
'1947-05-05','FILEREP',    16,  'M', '1926-05-17',  23840.00,     \
500,1907)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200010', 'DIAN',         'J',  'HEMMINGER',      'A00',  '3978', \
'1965-01-01','SALESREP',    18,  'F', '1933-08-14',  46500.00,    \
1000,4220)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200120', 'GREG',         '',  'ORLANDO',      'A00',  '2167',    \
'1972-05-05','CLERK',    14,  'M', '1942-10-18',  29250.00,       \
600,2340)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200140', 'KIM','N',  'NATZ',      'C01',  '1793',                \
'1976-12-15','ANALYST',    18,  'F', '1946-01-19',  28420.00,     \
600,2274)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200170', 'KIYOSHI',         '',  'YAMAMOTO',      'D11',  '2890',\
'1978-09-15','DESIGNER',    16,  'M', '1951-01-05',  24680.00,    \
500,1974)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200220', 'REBA',         'K',  'JOHN',      'D11',  '0672',      \
'1968-08-29','DESIGNER',    18,  'F', '1948-03-19',  29840.00,    \
600,2387)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200240', 'ROBERT',         'M',  'MONTEVERDE',      'D21',  '3780', \
'1979-12-05','CLERK',    17,  'M', '1954-03-31',  28760.00,          \
600,2301)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200280', 'EILEEN',         'R',  'SCHWARTZ',      'E11',  '8997',\
'1967-03-24','OPERATOR',    17,  'F', '1936-03-28',  26250.00,    \
500,2100)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200310', 'MICHELLE',         'F',  'SPRINGER',      'E11',  '3332', \
'1964-09-12','OPERATOR',    12,  'F', '1931-04-21',  15900.00,       \
300,1272)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200330', 'HELENA',         '',  'WONG',      'E21',  '2103',     \
'1976-02-23','FIELDREP',    14,  'F', '1941-07-18',  25370.00,    \
500,2030)"   );

execi(
"INSERT INTO EMPLOYEE VALUES( \
'200340', 'ROY',         'R',  'ALONZO',      'E21',  '5698',     \
'1947-05-05','FIELDREP',    16,  'M', '1926-05-17',  23840.00,    \
500,1907)"   );

execi(
"ALTER  TABLE DEPARTMENT \
              ADD FOREIGN KEY  RDE (MGRNO) \
              REFERENCES EMPLOYEE \
              ON DELETE SET NULL"       );

/*********************************************************************/
/*                                                                   */
/* PROJECT                                                           */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE PROJECT \
             (PROJNO CHAR(6) NOT NULL, \
              PROJNAME VARCHAR(24) NOT NULL DEFAULT '', \
              DEPTNO CHAR(3) NOT NULL, \
             RESPEMP CHAR(6) NOT NULL, \
              PRSTAFF DECIMAL(5,2), \
              PRSTDATE DATE, \
              PRENDATE DATE, \
              MAJPROJ CHAR(6), \
              PRIMARY KEY (PROJNO))"    );

execi(
"ALTER TABLE PROJECT \
              ADD FOREIGN KEY (DEPTNO) \
              REFERENCES DEPARTMENT \
              ON DELETE RESTRICT"       );

execi(
"ALTER TABLE PROJECT \
              ADD FOREIGN KEY (RESPEMP) \
              REFERENCES EMPLOYEE \
              ON DELETE RESTRICT"       );

execi(
"ALTER  TABLE PROJECT \
              ADD FOREIGN KEY  RPP (MAJPROJ) \
              REFERENCES PROJECT \
              ON DELETE CASCADE"        );

execi(
"CREATE INDEX XPROJ2 \
              ON PROJECT (RESPEMP)"     );

execi(
"CREATE ALIAS PROJ FOR PROJECT"         );

/*********************************************************************/
/*                                                                   */
/* PROJECT                                                           */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO PROJECT VALUES( \
'AD3100', 'ADMIN SERVICES',             'D01', '000010', \
  6.50,  '1982-01-01', '1983-02-01',NULL)"   );

execi(
"INSERT INTO PROJECT VALUES( \
'AD3110', 'GENERAL ADMIN SYSTEMS',               'D21', '000070', \
  6.00,  '1982-01-01', '1983-02-01','AD3100')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'AD3111', 'PAYROLL PROGRAMMING',                 'D21', '000230', \
  2.00,  '1982-01-01', '1983-02-01','AD3110')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'AD3112', 'PERSONNEL PROGRAMMING',               'D21', '000250', \
  1.00,  '1982-01-01', '1983-02-01','AD3110')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'AD3113', 'ACCOUNT PROGRAMMING',                 'D21', '000270', \
  2.00,  '1982-01-01', '1983-02-01','AD3110')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'IF1000', 'QUERY SERVICES',                      'C01', '000030', \
  2.00,  '1982-01-01', '1983-02-01',NULL)"   );

execi(
"INSERT INTO PROJECT VALUES( \
'IF2000', 'USER EDUCATION',                      'C01', '000030', \
  1.00,  '1982-01-01', '1983-02-01',NULL    )"   );

execi(
"INSERT INTO PROJECT VALUES( \
'MA2100', 'WELD LINE AUTOMATION',                'D01', '000010', \
 12.00,  '1982-01-01', '1983-02-01',NULL    )"   );

execi(
"INSERT INTO PROJECT VALUES( \
'MA2110', 'W L PROGRAMMING',                     'D11', '000060', \
  9.00,  '1982-01-01', '1983-02-01','MA2100')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'MA2111', 'W L PROGRAM DESIGN',                  'D11', '000220', \
  2.00,  '1982-01-01', '1982-12-01','MA2110')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'MA2112', 'W L ROBOT DESIGN',                    'D11', '000150', \
  3.00,  '1982-01-01', '1982-12-01','MA2110')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'MA2113', 'W L PROD CONT PROGS',                 'D11', '000160', \
  3.00,  '1982-02-15', '1982-12-01','MA2110')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'OP1000', 'OPERATION SUPPORT',                 'E01', '000050', \
  6.00,  '1982-01-01', '1983-02-01',NULL    )"   );

execi(
"INSERT INTO PROJECT VALUES( \
'OP1010', 'OPERATION',                         'E11', '000090', \
  5.00,  '1982-01-01', '1983-02-01','OP1000')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'OP2000', 'GEN SYSTEMS SERVICES',              'E01', '000050', \
  5.00,  '1982-01-01', '1983-02-01',NULL    )"   );

execi(
"INSERT INTO PROJECT VALUES( \
'OP2010', 'SYSTEMS SUPPORT',                   'E21', '000100', \
  4.00,  '1982-01-01', '1983-02-01','OP2000')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'OP2011', 'SCP SYSTEMS SUPPORT',               'E21', '000320', \
  1.00,  '1982-01-01', '1983-02-01','OP2010')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'OP2012', 'APPLICATIONS SUPPORT',              'E21', '000330', \
  1.00,  '1982-01-01', '1983-02-01','OP2010')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'OP2013', 'DB/DC SUPPORT',                     'E21', '000340', \
  1.00,  '1982-01-01', '1983-02-01','OP2010')"   );

execi(
"INSERT INTO PROJECT VALUES( \
'PL2100', 'WELD LINE PLANNING',                'B01', '000020', \
  1.00,  '1982-01-01', '1982-09-15','MA2100')"   );

/*********************************************************************/
/*                                                                   */
/* PROJACT                                                           */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE PROJACT \
             (PROJNO CHAR(6) NOT NULL, \
              ACTNO SMALLINT NOT NULL, \
              ACSTAFF DECIMAL(5,2), \
              ACSTDATE DATE NOT NULL, \
              ACENDATE DATE , \
              PRIMARY KEY (PROJNO, ACTNO, ACSTDATE))"  );

execi(
"ALTER TABLE PROJACT \
              ADD FOREIGN KEY RPAP (PROJNO) \
              REFERENCES PROJECT \
              ON DELETE RESTRICT"       );


/*********************************************************************/
/*                                                                   */
/* PROJACT                                                           */
/*                                                                   */
/*********************************************************************/

execi(
 "INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3100',    10, '1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3110',    10,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111',    60,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111',    60,'1982-03-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111',    70,'1982-03-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111',    80,'1982-04-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111',   180,'1982-10-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111',    70,'1982-02-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111',    80,'1982-09-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112',    60,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112',    60,'1982-02-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112',    60,'1983-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112',    70,'1982-02-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112',    70,'1982-03-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112',    70,'1982-08-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112',    80,'1982-08-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
 'AD3112',    80,'1982-10-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
 'AD3112',   180,'1982-08-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
 'AD3113',    70,'1982-06-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
 'AD3113',    70,'1982-07-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',    80,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
 'AD3113',    80,'1982-03-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',   180,'1982-03-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',   180,'1982-04-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',   180,'1982-06-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',    60,'1982-03-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',    60,'1982-04-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',    60,'1982-09-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',    70,'1982-09-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113',    70,'1982-10-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF1000',    10,'1982-06-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF1000',   90,'1982-10-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF1000',   100,'1982-10-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000',    10,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000',   100,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000',   100,'1982-03-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000',   110,'1982-03-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000',  110,'1982-10-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2100',    10,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2100',    20,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2110',    10,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2111',    50,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2111',    60,'1982-06-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2111',    40,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112',    60,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112',   180,'1982-07-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112',    70,'1982-06-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112',    70,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112',    80,'1982-10-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113',    60,'1982-07-15')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113',    80,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113',    70,'1982-04-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113',    80,'1982-10-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113',   180,'1982-10-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP1000',    10,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP1010',    10,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP1010',   130,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2010',    10,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2011',   140,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2011',150,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2012',   140,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2012',   160,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2013',   140,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
 'OP2013',   170,'1982-01-01')"   );

execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'PL2100',    30,'1982-01-01')"   );

/*********************************************************************/
/*                                                                   */
/* EMPPROJACT (EMP_ACT is an ALIAS)                                  */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE EMPPROJACT \
             (EMPNO CHAR(6) NOT NULL, \
              PROJNO CHAR(6) NOT NULL, \
              ACTNO SMALLINT NOT NULL, \
              EMPTIME DEC(5,2), \
              EMSTDATE DATE, \
              EMENDATE DATE)"           );

execi(
"ALTER TABLE EMPPROJACT \
              ADD FOREIGN KEY REPAPA (PROJNO, ACTNO, EMSTDATE) \
              REFERENCES PROJACT \
              ON DELETE RESTRICT"       );

execi(
"CREATE ALIAS EMPACT FOR EMPPROJACT"    );

execi(
"CREATE ALIAS EMP_ACT FOR EMPPROJACT"   );

/*********************************************************************/
/*                                                                   */
/* EMP_ACT                                                           */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO EMP_ACT VALUES( \
'000010', 'AD3100',    10, 0.5,'1982-01-01', '1982-07-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000070', 'AD3110',    10, 1.0,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111',    60, 1.0,'1982-01-01', '1982-03-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111',    60, 0.5,'1982-03-15', '1982-04-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111',    70, 0.5,'1982-03-15', '1982-10-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111',    80, 0.5,'1982-04-15', '1982-10-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111',   180, 0.5,'1982-10-15', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000240', 'AD3111',    70, 1.0,'1982-02-15', '1982-09-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000240', 'AD3111',    80, 1.0,'1982-09-15', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',    60, 1.0,'1982-01-01', '1982-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',    60, 0.5,'1982-02-01', '1982-03-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',    60, 1.0,'1983-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',    70, 0.5,'1982-02-01', '1982-03-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',    70, 1.0,'1982-03-15', '1982-08-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',    70,0.25,'1982-08-15', '1982-10-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',    80,0.25,'1982-08-15', '1982-10-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',    80,0.50,'1982-10-15', '1982-12-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112',   180,0.50,'1982-08-15', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113',    70,0.50,'1982-06-15', '1982-07-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113',    70,1.00,'1982-07-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113',    80,1.00,'1982-01-01', '1982-03-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113',    80,0.50,'1982-03-01', '1982-04-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113',   180,0.50,'1982-03-01', '1982-04-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113',   180,1.00,'1982-04-15', '1982-06-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113',   180,1.00,'1982-06-01', '1982-07-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113',    60,0.50,'1982-03-01', '1982-04-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113',    60,1.00,'1982-04-01', '1982-09-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113',    60,0.25,'1982-09-01', '1982-10-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113',    70,0.75,'1982-09-01', '1982-10-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113',    70,1.00,'1982-10-15', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113',    80,1.00,'1982-01-01', '1982-03-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113',    80,0.50,'1982-03-01', '1982-04-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000030', 'IF1000',    10,0.50,'1982-06-01', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000130', 'IF1000',    90,1.00,'1982-10-01', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000130', 'IF1000',   100,0.50,'1982-10-01', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF1000',    90,0.50,'1982-10-01', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000030', 'IF2000',10,0.50,'1982-01-01', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF2000',   100,1.00,'1982-01-01', '1982-03-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF2000',   100,0.50,'1982-03-01', '1982-07-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF2000',   110,0.50,'1982-03-01', '1982-07-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF2000',   110,0.50,'1982-10-01', '1983-01-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000010', 'MA2100',    10,0.50,'1982-01-01', '1982-11-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000110', 'MA2100',    20,1.00,'1982-01-01', '1983-03-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000010', 'MA2110',    10,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000200', 'MA2111',    50,1.00,'1982-01-01', '1982-06-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000200', 'MA2111',    60,1.00,'1982-06-15', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000220', 'MA2111',    40,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000150', 'MA2112',    60,1.00,'1982-01-01', '1982-07-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000150', 'MA2112',   180,1.00,'1982-07-15', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000170', 'MA2112',    60,1.00,'1982-01-01', '1983-06-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000170', 'MA2112',    70,1.00,'1982-06-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000190', 'MA2112',    70,1.00,'1982-01-01', '1982-10-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000190', 'MA2112',    80,1.00,'1982-10-01', '1983-10-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000160', 'MA2113',    60,1.00,'1982-07-15', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000170', 'MA2113',    80,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000180', 'MA2113',    70,1.00,'1982-04-01', '1982-06-15')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000210', 'MA2113',    80,0.50,'1982-10-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000210', 'MA2113',   180,0.50,'1982-10-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000050', 'OP1000',    10,0.25,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000090', 'OP1010',    10,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000280', 'OP1010',   130,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000290', 'OP1010',   130,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000300', 'OP1010',   130,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000310', 'OP1010',   130,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000050', 'OP2010',    10,0.75,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000100', 'OP2010',    10,1.00,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000320', 'OP2011',   140,0.75,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000320', 'OP2011',   150,0.25,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000330', 'OP2012',   140,0.25,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000330', 'OP2012',   160,0.75,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000340', 'OP2013',   140,0.50,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000340', 'OP2013',   170,0.50,'1982-01-01', '1983-02-01')"   );

execi(
"INSERT INTO EMP_ACT VALUES( \
'000020', 'PL2100',30,1.00,'1982-01-01', '1982-09-15')"   );

/*********************************************************************/
/*                                                                   */
/* ACT                                                               */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE ACT ( \
              ACTNO SMALLINT NOT NULL, \
              ACTKWD CHAR(6) NOT NULL, \
              ACTDESC VARCHAR(20) NOT NULL, \
              PRIMARY KEY (ACTNO) )"    );

execi(
"CREATE UNIQUE INDEX XACT2 \
              ON ACT (ACTKWD)"          );

/*********************************************************************/
/*                                                                   */
/* ACT                                                               */
/*                                                                   */
/*********************************************************************/
execi(
"INSERT INTO ACT VALUES( \
10,'MANAGE','MANAGE/ADVISE')"   );

execi(
"INSERT INTO ACT VALUES( \
20,'ECOST','ESTIMATE COST')"   );

execi(
"INSERT INTO ACT VALUES( \
30,'DEFINE','DEFINE SPECS')"   );

execi(
"INSERT INTO ACT VALUES( \
40,'LEADPR','LEAD PROGRAM/DESIGN')"   );

execi(
"INSERT INTO ACT VALUES( \
50,'SPECS','WRITE SPECS')"   );

execi(
"INSERT INTO ACT VALUES( \
60,'LOGIC','DESCRIBE LOGIC')"   );

execi(
"INSERT INTO ACT VALUES( \
70,'CODE','CODE PROGRAMS')"   );

execi(
"INSERT INTO ACT VALUES( \
80,'TEST','TEST PROGRAMS')"   );

execi(
"INSERT INTO ACT VALUES( \
90,'ADMQS','ADM QUERY SYSTEM')"   );

execi(
"INSERT INTO ACT VALUES( \
100,'TEACH','TEACH CLASSES')"   );

execi(
"INSERT INTO ACT VALUES( \
110,'COURSE','DEVELOP COURSES')"   );

execi(
"INSERT INTO ACT VALUES( \
120,'STAFF','PERS AND STAFFING')"   );

execi(
"INSERT INTO ACT VALUES( \
130,'OPERAT','OPER COMPUTER SYS')"   );

execi(
"INSERT INTO ACT VALUES( \
140,'MAINT','MAINT SOFTWARE SYS')"   );

execi(
"INSERT INTO ACT VALUES( \
150,'ADMSYS','ADM OPERATING SYS')"   );

execi(
"INSERT INTO ACT VALUES( \
160,'ADMDB','ADM DATA BASES')"   );

execi(
"INSERT INTO ACT VALUES( \
170,'ADMDC','ADM DATA COMM')"   );

execi(
"INSERT INTO ACT VALUES( \
180,'DOC','DOCUMENT')"   );

execi(
"ALTER TABLE PROJACT \
              ADD FOREIGN KEY RPAA (ACTNO) \
              REFERENCES ACT \
              ON DELETE RESTRICT"       );

/*********************************************************************/
/*                                                                   */
/* EMP_PHOTO                                                         */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE EMP_PHOTO ( \
              EMPNO CHAR(6) NOT NULL, \
              PHOTO_FORMAT VARCHAR(10) NOT NULL, \
              PICTURE BLOB(100K), \
              PRIMARY KEY (EMPNO,PHOTO_FORMAT))"  );

execi(
"ALTER TABLE EMP_PHOTO \
              ADD FOREIGN KEY (EMPNO) \
             REFERENCES EMPLOYEE \
              ON DELETE RESTRICT"       );

/*********************************************************************/
/*                                                                   */
/* EMP_PHOTO                                                         */
/*                                                                   */
/*********************************************************************/
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000130', 'bitmap', null)"   );

execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000130','gif', null)"   );

execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000140','bitmap', null)"   );

execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000140','gif', null)"   );

execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000150','bitmap', null)"   );

execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000150','gif', null)"   );

execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000190','bitmap', null)"   );

execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000190','gif', null)"   );

/*********************************************************************/
/*                                                                   */
/* EMP_RESUME                                                        */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE EMP_RESUME ( \
              EMPNO CHAR(6) NOT NULL, \
              RESUME_FORMAT VARCHAR(10) NOT NULL, \
              RESUME CLOB(5K), \
              PRIMARY KEY (EMPNO,RESUME_FORMAT))"  );

execi(
"ALTER TABLE EMP_RESUME \
              ADD FOREIGN KEY (EMPNO) \
              REFERENCES EMPLOYEE \
              ON DELETE RESTRICT"       );

/*********************************************************************/
/*                                                                   */
/* EMP_RESUME                                                        */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO EMP_RESUME VALUES( \
'000130','ascii', null)"   );

execi(
"INSERT INTO EMP_RESUME VALUES( \
'000130','html', null)"   );

execi(
"INSERT INTO EMP_RESUME VALUES( \
'000140','ascii', null)"   );

execi(
"INSERT INTO EMP_RESUME VALUES( \
'000140','html', null)"   );

execi(
"INSERT INTO EMP_RESUME VALUES( \
'000150','ascii', null)"   );

execi(
"INSERT INTO EMP_RESUME VALUES( \
'000150','html', null)"   );

execi(
"INSERT INTO EMP_RESUME VALUES( \
'000190','ascii', null)"   );

execi(
"INSERT INTO EMP_RESUME VALUES( \
'000190','html', null)"   );
/*********************************************************************/
/*                                                                   */
/* SALES                                                             */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE SALES \
             (SALES_DATE DATE, \
              SALES_PERSON VARCHAR(15), \
              REGION VARCHAR(15), \
              SALES INTEGER)"           );

/*********************************************************************/
/*                                                                   */
/* SALES                                                             */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'LUCCHESSI','Ontario-South', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'LEE','Ontario-South', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'LEE','Quebec', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'LEE','Manitoba', 2)"   );

execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'GOUNOT','Quebec', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LUCCHESSI','Ontario-South', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LUCCHESSI','Quebec', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LEE','Ontario-South', 2)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LEE','Ontario-North', 2)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LEE','Quebec', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LEE','Manitoba', 5)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'GOUNOT','Ontario-South', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'GOUNOT','Quebec', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'GOUNOT','Manitoba', 7)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LUCCHESSI','Ontario-South', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LUCCHESSI','Quebec', 2)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LUCCHESSI','Manitoba', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LEE','Ontario-South', 7)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LEE','Ontario-North', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LEE','Quebec', 7)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LEE','Manitoba', 4)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'GOUNOT','Ontario-South', 2)"   );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'GOUNOT','Quebec', 18)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'GOUNOT','Manitoba', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LUCCHESSI','Manitoba', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LEE','Ontario-South', 14)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LEE','Ontario-North', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LEE','Quebec', 7)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LEE','Manitoba', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'GOUNOT','Ontario-South', 2)"   );

execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'GOUNOT','Quebec', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LUCCHESSI','Ontario-South', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LUCCHESSI','Manitoba', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LEE','Ontario-South', 8)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LEE','Ontario-North',NULL)"   );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LEE','Quebec', 8)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LEE','Manitoba', 9)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'GOUNOT','Ontario-South', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'GOUNOT','Ontario-North', 1)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'GOUNOT','Quebec', 3)"   );

execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'GOUNOT','Manitoba', 7)"   );

/*********************************************************************/
/*                                                                   */
/* CL_SCHED                                                          */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE CL_SCHED ( \
              CLASS_CODE CHAR(7), \
              ""DAY"" SMALLINT, \
              STARTING TIME, \
              ENDING TIME)"             );

/*********************************************************************/
/*                                                                   */
/* CL_SCHED                                                          */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO  CL_SCHED VALUES( \
'042:BF' ,4,'12:10:00', '14:00:00')"    );

execi(
"INSERT INTO  CL_SCHED VALUES( \
'553:MJA' ,1,'10:30:00', '11:00:00')"   );

execi(
"INSERT INTO  CL_SCHED VALUES( \
'543:CWM' ,3,'09:10:00', '10:30:00')"   );

execi(
"INSERT INTO  CL_SCHED VALUES( \
'778:RES' ,2,'12:10:00', '14:00:00')"   );

execi(
"INSERT INTO  CL_SCHED VALUES( \
'044:HD'  ,3,'17:12:30', '18:00:00')"   );

/*********************************************************************/
/*                                                                   */
/* IN_TRAY                                                           */
/*                                                                   */
/*********************************************************************/
execi(
"CREATE TABLE IN_TRAY \
             (RECEIVED TIMESTAMP, \
              SOURCE CHAR(8), \
              SUBJECT CHAR(64), \
              NOTE_TEXT VARCHAR(3000))" );

/*********************************************************************/
/*                                                                   */
/* IN_TRAY                                                           */
/*                                                                   */
/*********************************************************************/

execi(
"INSERT INTO  IN_TRAY VALUES( \
'1988-12-25-17.12.30.000000' ,'BADAMSON', \
'FWD: Fantastic year! 4th Quarter Bonus.', \
'To: JWALKER  \
Cc: QUINTANA, NICHOLLS  \
  \
Jim,  \
Looks like our hard work has paid off. I have some good beer in the \
fridge if you want to come over to celebrate a bit. Delores and \
Heather, are you interested as well?  \
  \
Bruce \
  \
<Forwarding from ISTERN>  \
Subject: FWD: Fantastic year! 4th Quarter Bonus.  \
To: Dept_D11 \
  \
Congratulations on a job well done. Enjoy this year''s bonus. \
  \
Irv  \
  \
<Forwarding from CHAAS>  \
Subject: Fantastic year! 4th Quarter Bonus.  \
To: All_Managers  \
  \
Our 4th quarter results are in. We pulled together as a team and \
exceeded our plan! I am pleased to announce a bonus this year of \
18%. Enjoy the holidays.  \
  \
Christine Haas')"   );

execi(
"INSERT INTO  IN_TRAY VALUES( \
'1988-12-23-08.53.58.000000' ,'ISTERN', \
'FWD: Fantastic year! 4th Quarter Bonus.', \
'To: Dept_D11  \
  \
Congratulations on a job well done. Enjoy this year''s bonus.  \
  \
Irv  \
  \
<Forwarding from CHAAS>  \
Subject: Fantastic year! 4th Quarter Bonus.  \
To: All_Managers  \
  \
Our 4th quarter results are in. We pulled together as a team and \
exceeded our plan! I am pleased to announce a bonus this year of \
18%. Enjoy the holidays.  \
  \
Christine Haas')"   );

execi(
"INSERT INTO  IN_TRAY VALUES( \
'1988-12-22-14.07.21.136421' ,'CHAAS', \
'Fantastic year! 4th Quarter Bonus.', \
'To: All_Managers  \
  \
Our 4th quarter results are in. We pulled together as a team and \
exceeded our plan! I am pleased to announce a bonus this year of \
18%. Enjoy the holidays.  \
  \
Christine Haas')"   );

/*********************************************************************/
/*********************************************************************/
/*                                                                   */
/* Create views on the sample tables                                 */
/*                                                                   */
/*********************************************************************/
/*********************************************************************/

execi(
"CREATE VIEW VDEPT \
     AS SELECT ALL DEPTNO, \
  DEPTNAME, \
  MGRNO, \
  ADMRDEPT \
FROM DEPT"                            );

execi(
"CREATE VIEW VHDEPT \
   AS SELECT ALL DEPTNO , \
  DEPTNAME, \
  MGRNO , \
  ADMRDEPT, \
  LOCATION \
  FROM DEPT"                            );

execi(
"CREATE VIEW VEMP \
   AS SELECT ALL EMPNO , \
  FIRSTNME, \
  MIDINIT , \
  LASTNAME, \
  WORKDEPT \
  FROM EMP"                             );

execi(
"CREATE VIEW VPROJ \
   AS SELECT ALL \
PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, \
PRSTDATE, PRENDATE, MAJPROJ \
FROM PROJ"                              );

execi(
"CREATE VIEW VACT \
AS SELECT ALL ACTNO , \
  ACTKWD , \
  ACTDESC \
FROM ACT"                               );

execi(
"CREATE VIEW VPROJACT \
   AS SELECT ALL \
PROJNO,ACTNO, ACSTAFF, ACSTDATE, ACENDATE \
FROM PROJACT"                           );

execi(
"CREATE VIEW VEMPPROJACT \
AS SELECT ALL \
EMPNO, PROJNO, ACTNO, EMPTIME, EMSTDATE, EMENDATE \
FROM EMPPROJACT"                        );

execi(
"CREATE VIEW VDEPMG1 \
(DEPTNO, DEPTNAME, MGRNO, FIRSTNME, MIDINIT, \
  LASTNAME, ADMRDEPT) \
AS SELECT ALL \
DEPTNO, DEPTNAME, EMPNO, FIRSTNME, MIDINIT, \
  LASTNAME, ADMRDEPT \
FROM DEPT LEFT OUTER  JOIN EMP \
ON MGRNO = EMPNO"                       );

execi(
"CREATE VIEW VEMPDPT1 \
(DEPTNO, DEPTNAME, EMPNO, FRSTINIT, MIDINIT, \
  LASTNAME, WORKDEPT) \
AS SELECT ALL \
DEPTNO, DEPTNAME, EMPNO, SUBSTR(FIRSTNME, 1, 1), MIDINIT, \
  LASTNAME, WORKDEPT \
FROM DEPT  RIGHT OUTER JOIN EMP \
ON WORKDEPT = DEPTNO"                   );

execi(
"CREATE VIEW VASTRDE1 \
  (DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2, \
  DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN) \
AS SELECT ALL \
  D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT, \
  D1.LASTNAME, '1', \
  D2.DEPTNO,D2.DEPTNAME,D2.MGRNO,D2.FIRSTNME,D2.MIDINIT, \
  D2.LASTNAME \
FROM VDEPMG1 D1, VDEPMG1 D2 \
WHERE D1.DEPTNO = D2.ADMRDEPT"          );

execi(
"CREATE VIEW VASTRDE2 \
  (DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2, \
  DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN) \
AS SELECT ALL \
  D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT, \
  D1.LASTNAME,'2', \
  D1.DEPTNO,D1.DEPTNAME,E2.EMPNO,E2.FIRSTNME,E2.MIDINIT, \
  E2.LASTNAME \
FROM VDEPMG1 D1, EMP E2 \
WHERE D1.DEPTNO = E2.WORKDEPT"          );

execi(
"CREATE VIEW VPROJRE1 \
  (PROJNO,PROJNAME,PROJDEP,RESPEMP,FIRSTNME,MIDINIT, \
  LASTNAME,MAJPROJ) \
AS SELECT ALL \
  PROJNO,PROJNAME,DEPTNO,EMPNO,FIRSTNME,MIDINIT, \
  LASTNAME,MAJPROJ \
FROM PROJ, EMP \
WHERE RESPEMP = EMPNO"                  );

execi(
"CREATE VIEW VPSTRDE1 \
  (PROJ1NO,PROJ1NAME,RESP1NO,RESP1FN,RESP1MI,RESP1LN, \
  PROJ2NO,PROJ2NAME,RESP2NO,RESP2FN,RESP2MI,RESP2LN) \
AS SELECT ALL \
  P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT, \
  P1.LASTNAME, \
  P2.PROJNO,P2.PROJNAME,P2.RESPEMP,P2.FIRSTNME,P2.MIDINIT, \
  P2.LASTNAME \
FROM VPROJRE1 P1, \
  VPROJRE1 P2 \
WHERE P1.PROJNO = P2.MAJPROJ"           );

execi(
"CREATE VIEW VPSTRDE2 \
  (PROJ1NO,PROJ1NAME,RESP1NO,RESP1FN,RESP1MI,RESP1LN, \
  PROJ2NO,PROJ2NAME,RESP2NO,RESP2FN,RESP2MI,RESP2LN) \
AS SELECT ALL \
  P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT, \
  P1.LASTNAME, \
  P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT, \
  P1.LASTNAME \
FROM VPROJRE1 P1 \
WHERE NOT EXISTS \
(SELECT * FROM VPROJRE1 P2 \
WHERE P1.PROJNO = P2.MAJPROJ)"          );

execi(
"CREATE VIEW VFORPLA \
  (PROJNO,PROJNAME,RESPEMP,PROJDEP,FRSTINIT,MIDINIT,LASTNAME) \
AS SELECT ALL \
F1.PROJNO,PROJNAME,RESPEMP,PROJDEP, SUBSTR(FIRSTNME, 1, 1), \
  MIDINIT, LASTNAME \
FROM VPROJRE1 F1 LEFT OUTER JOIN EMPPROJACT F2 \
ON F1.PROJNO = F2.PROJNO"               );

execi(
"CREATE VIEW VSTAFAC1(PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT, \
     LASTNAME, EMPTIME,STDATE,ENDATE, TYPE) \
AS SELECT ALL \
PA.PROJNO, PA.ACTNO, AC.ACTDESC,' ', ' ', ' ', ' ', \
  PA.ACSTAFF, PA.ACSTDATE, \
  PA.ACENDATE,'1' \
FROM PROJACT PA, ACT AC  \
WHERE PA.ACTNO = AC.ACTNO"              );

execi(
"CREATE VIEW VSTAFAC2 \
(PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT, LASTNAME, \
EMPTIME,STDATE, ENDATE, TYPE) \
AS SELECT ALL \
EP.PROJNO, EP.ACTNO, AC.ACTDESC, EP.EMPNO,EM.FIRSTNME, \
EM.MIDINIT, EM.LASTNAME, EP.EMPTIME, EP.EMSTDATE, \
  EP.EMENDATE,'2' \
FROM EMPPROJACT EP, ACT AC, EMP EM \
WHERE EP.ACTNO = AC.ACTNO AND EP.EMPNO = EM.EMPNO"  );

execi(
"CREATE VIEW VPHONE \
  (LASTNAME, \
  FIRSTNAME, \
  MIDDLEINITIAL, \
  PHONENUMBER, \
  EMPLOYEENUMBER, \
  DEPTNUMBER, \
  DEPTNAME) \
AS SELECT ALL LASTNAME, \
  FIRSTNME, \
  MIDINIT , \
  VALUE(PHONENO,' '), \
  EMPNO, \
  DEPTNO, \
  DEPTNAME \
FROM EMP, DEPT \
WHERE WORKDEPT = DEPTNO"                );

execi(
"CREATE VIEW VEMPLP \
  (EMPLOYEENUMBER, \
  PHONENUMBER) \
AS SELECT ALL EMPNO , \
  PHONENO \
FROM EMP"                               );

  /* Disconnect Database */
  rc = DbDisconnect();  

  /* attach to a local or remote instance */
  rc = InstanceAttach(nodeName, user, pswd);
  if (rc != 0)
  {
    return rc;
  }

  /* The next function will disconnect all the applications from all */
  /* the databases located in the instance you are attached to. */
  /* Uncomment the next function if this is acceptable. */

  /* rc = AllApplicationsConnectedToAllDatabasesForceOff(); */

  /* detach from the local or remote instance */
  rc = InstanceDetach(nodeName);
  if (rc != 0)
  {
    return rc;
  }

  return rc;
} /* end main */

int DbConnect(char dbAlias[], char user[], char pswd[])
{
  struct sqlca sqlca;

  printf("\n-----------------------------------------------------------");
  printf("\nUSE THE SQL STATEMENTS:\n");
  printf("  CONNECT TO\n");
  printf("TO CONNECT TO A DATABASE.\n");

  /* connect to a database */
  printf("\n  Execute the statement\n");
  printf("    CONNECT TO %s\n", dbAlias);
  if (strlen(user) > 0)
  {
    EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
    EMB_SQL_CHECK("database -- connect with userid and password");
  }
  else
  {

    EXEC SQL CONNECT TO :dbAlias;
    EMB_SQL_CHECK("Database -- Connect");
  }

  return 0;
} /* DbConnect */

int DbRestart(char dbAlias[], char user[], char pswd[])
{
  struct sqlca sqlca;
  struct db2RestartDbStruct dbRestartParam;

 printf("\n-----------------------------------------------------------");
  printf("\nUSE THE DB2 APIs:\n");
  printf("  db2DatabaseRestart -- RESTART DATABASE\n");
  printf("TO RESTART A DATABASE.\n");

  /* restart a database */
  dbRestartParam.piDatabaseName = dbAlias;
  dbRestartParam.piUserId = user;
  dbRestartParam.piPassword = pswd;
  dbRestartParam.piTablespaceNames = NULL;
  printf("\n  Restart a database.\n");
  printf("    database alias: %s\n", dbAlias);

  /* restart database */
  db2DatabaseRestart(db2Version710, &dbRestartParam, &sqlca);
  DB2_API_CHECK("Database -- Restart");

  return 0;
} /* DbRestart */

int DbDisconnect(void)
{
  struct sqlca sqlca;

  printf("\n-----------------------------------------------------------");
  printf("\nUSE THE SQL STATEMENTS:\n");
  printf("  CONNECT RESET");
  printf("\nTO DISCONNECT FROM THE CURRENT DATABASE.\n");

  /* disconnect from the database */
  printf("\n  Execute the statement\n");
  printf("    CONNECT RESET\n");

  EXEC SQL CONNECT RESET;
  EMB_SQL_CHECK("Database -- Disconnect");

  return 0;
} /* DbDisconnect */

int AllApplicationsConnectedToAllDatabasesForceOff(void)
{
  struct sqlca sqlca;

  printf("\n-----------------------------------------------------------");
  printf("\nUSE THE DB2 APIs:\n");
  printf("  sqlefrce -- FORCE APPLICATION\n");
  printf("TO FORCE OFF ALL THE APPLICATIONS CONNECTED TO ALL DATABASES.\n");

  /* force off all the appl. connected to all databases */
  printf("\n  Force off all applications connected to all databases.\n");

  /* force application */
  sqlefrce(SQL_ALL_USERS, NULL, SQL_ASYNCH, &sqlca);
  DB2_API_CHECK("DBM Config. -- Set");

  return 0;
} /* AllApplicationsConnectedToAllDatabasesForceOff */

/*********************************************************************/
/* Function:     execi                                               */
/* Description:  Execute an SQL statement                            */
/*********************************************************************/
int execi (char s[1000]) {

strcpy(sqlstmt.stmt, s);            /* Copy the string to the        */
                                    /* varying length structure      */
sqlstmt.len = strlen(sqlstmt.stmt);
                                    /* Copy the length to the        */
                                    /* varying length structure      */
                                    /* Note that this assumes        */
                                    /* that we will not need a       */
                                    /* hex 00 in the string in       */
                                    /* this program                  */
EXEC SQL
   EXECUTE IMMEDIATE :sqlstmt;
                                    /* Execute the statement         */
EMB_SQL_CHECK("SQL Check");

EXEC SQL COMMIT;

EMB_SQL_CHECK("COMMIT");

printf("\nRunning execi: %s", s);

return 0;                             /* Return to the mainline        */

}


int PackageCreate(char *sourceFileBaseName)
{
  struct sqlca sqlca;

  char bndFileName[50 + 1 + 3 + 1];
  char msgFileName[20 + 1 + 3 + 1];

  struct sqlopt *pBindOptions;
  struct sqloptions *optionsArray;

  printf("\n-----------------------------------------------------------");
  printf("\nUSE THE DB2 API:\n");
  printf("  sqlabndx -- BIND\n");
  printf("TO CREATE A PACKAGE.\n");

  /* create the package */
  strcpy(bndFileName, sourceFileBaseName);
  strcat(bndFileName, ".bnd");
 /* strcpy(msgFileName, sourceFileBaseName);*/
 
  strcpy(msgFileName, "dbsample");
 

 /*  msgFileName[6] = '\0';*/ /* truncate to 6 characters */
  strcat(msgFileName, "_b.msg"); /* _b = bind */

  printf("\n  Create the package.\n");
  printf("    bnd file name    : %s\n", bndFileName);
  printf("    package name     : %s\n", sourceFileBaseName);
  printf("    message file name: %s\n", msgFileName);

  pBindOptions = (struct sqlopt *)malloc(sizeof(struct sqlopt) +
                 (2 - 1) * sizeof(struct sqloptions));
  /* for 2 options one more struct sqloptions is needed because */
  /* struct sqlopt already contains one */

  pBindOptions->header.allocated = 2; /* number of options */
  pBindOptions->header.used = 2;

  optionsArray = pBindOptions->option;
  optionsArray[0].type = SQL_BLOCK_OPT;
  optionsArray[0].val = SQL_BL_ALL; /* cursor blocking = block all */
  optionsArray[1].type = SQL_ISO_OPT;
  optionsArray[1].val = SQL_CURSOR_STAB; /* isol. level = cursor stab. */

  /* invoke the bind utility */
  sqlabndx(bndFileName, msgFileName,NULL, &sqlca);
 /*sqlabndx(bndFileName, msgFileName, pBindOptions, &sqlca);*/

 DB2_API_CHECK("Package -- Create");


  /* free the memory allocated */
  free(pBindOptions);
 return 0;
} /* PackageCreate */

int DbConnectCheck(char dbAlias[], char user[], char pswd[])
{
  struct sqlca sqlca;

  /*Check if the database exist*/

  /* connect to a database */
  if (strlen(user) > 0)
  {
    EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
  }
  else
  {

    EXEC SQL CONNECT TO :dbAlias;
    if(sqlca.sqlcode < 0)
    {
      return 1;
    } 
  }

  return 0;
}