---------------------------------------------------------------------------------------------- -- Licensed Materials - Property of IBM -- (C) Copyright IBM Corp. 2021 All Rights Reserved. -- US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP -- Schedule Contract with IBM Corp. ----------------------------------------------------------------------------------------------- -- Used to create the SAMPLE database (SQL Only) -- CONNECT TO SAMPLE USER DB2INST1 USING db2inst1 -- ORG CREATE TABLE ORG ( DEPTNUMB SMALLINT NOT NULL, DEPTNAME VARCHAR(14), MANAGER SMALLINT, DIVISION VARCHAR(10), LOCATION VARCHAR(13) ); INSERT INTO ORG VALUES (10,'Head Office', 160,'Corporate','New York'), (15,'New England', 50,'Eastern','Boston'), (20,'Mid Atlantic', 10,'Eastern','Washington'), (38,'South Atlantic', 30,'Eastern','Atlanta'), (42,'Great Lakes', 100,'Midwest','Chicago'), (51,'Plains', 140,'Midwest','Dallas'), (66,'Pacific', 270,'Western','San Francisco'), (84,'Mountain', 290,'Western','Denver') ; -- STAFF 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) ); INSERT INTO STAFF VALUES (10,'Sanders', 20,'Mgr', 7, 18357.50, NULL), (20,'Pernal', 20,'Sales', 8, 18171.25, 612.45), (30,'Marenghi', 38,'Mgr', 5, 17506.75, NULL), (40,'O''Brien', 38,'Sales', 6, 18006.00, 846.55), (50,'Hanes', 15,'Mgr', 10, 20659.80, NULL), (60,'Quigley', 38,'Sales', NULL, 16808.30, 650.25), (70,'Rothman', 15,'Sales', 7, 16502.83, 1152.00), (80,'James', 20,'Clerk', NULL, 13504.60, 128.20), (90,'Koonitz', 42,'Sales', 6, 18001.75, 1386.70), (100,'Plotz', 42,'Mgr', 7, 18352.80, NULL), (110,'Ngan', 15,'Clerk', 5, 12508.20, 206.60), (120,'Naughton', 38,'Clerk', NULL, 12954.75, 180.00), (130,'Yamaguchi', 42,'Clerk', 6, 10505.90, 75.60), (140,'Fraye', 51,'Mgr', 6, 21150.00, NULL), (150,'Williams', 51,'Sales', 6, 19456.50, 637.65), (160,'Molinare', 10,'Mgr', 7, 22959.20, NULL), (170,'Kermisch', 15,'Clerk', 4, 12258.50, 110.10), (180,'Abrahams', 38,'Clerk', 3, 12009.75,236.50), (190,'Sneider', 20,'Clerk', 8, 14252.75, 126.50), (200,'Scoutten', 42,'Clerk', NULL, 11508.60, 84.20), (210,'Lu', 10,'Mgr', 10, 20010.00, NULL), (220,'Smith', 51,'Sales', 7, 17654.50, 992.80), (230,'Lundquist', 51,'Clerk', 3, 13369.80, 189.65), (240,'Daniels', 10,'Mgr', 5, 19260.25, NULL), (250,'Wheeler', 51,'Clerk', 6, 14460.00, 513.30), (260,'Jones', 10,'Mgr', 12, 21234.00, NULL), (270,'Lea', 66,'Mgr', 9, 18555.50, NULL), (280,'Wilson', 66,'Sales', 9, 18674.50, 811.50), (290,'Quill', 84,'Mgr', 10, 19818.00, NULL), (300,'Davis', 84,'Sales', 5, 15454.50, 806.10), (310,'Graham', 66,'Sales', 13, 21000.00, 200.30), (320,'Gonzales', 66,'Sales', 4, 16858.20, 844.00), (330,'Burke', 66,'Clerk', 1, 10988.00, 55.50), (340,'Edwards', 84,'Sales', 7, 17844.00, 1285.00), (350,'Gafney', 84,'Clerk', 5, 13030.50, 188.00); -- DEPARTMENT 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) ); ALTER TABLE DEPARTMENT ADD FOREIGN KEY ROD (ADMRDEPT) REFERENCES DEPARTMENT ON DELETE CASCADE ; CREATE INDEX XDEPT2 ON DEPARTMENT (MGRNO); CREATE INDEX XDEPT3 ON DEPARTMENT (ADMRDEPT); CREATE ALIAS DEPT FOR DEPARTMENT; INSERT INTO DEPARTMENT VALUES ('A00' ,'SPIFFY COMPUTER SERVICE DIV.','000010','A00', NULL), ('B01','PLANNING','000020','A00', NULL), ('C01','INFORMATION CENTER','000030','A00', NULL), ('D01','DEVELOPMENT CENTER',NULL,'A00',NULL), ('D11','MANUFACTURING SYSTEMS','000060','D01', NULL), ('D21','ADMINISTRATION SYSTEMS','000070','D01', NULL), ('E01','SUPPORT SERVICES','000050','A00', NULL), ('E11','OPERATIONS','000090','E01', NULL), ('E21','SOFTWARE SUPPORT','000100','E01', NULL), ('F22','BRANCH OFFICE F2',NULL,'E01', NULL), ('G22','BRANCH OFFICE G2',NULL,'E01', NULL), ('H22','BRANCH OFFICE H2',NULL,'E01', NULL), ('I22','BRANCH OFFICE I2',NULL,'E01', NULL), ('J22','BRANCH OFFICE J2',NULL,'E01', NULL); -- EMPLOYEE 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) ); ALTER TABLE EMPLOYEE ADD FOREIGN KEY RED (WORKDEPT) REFERENCES DEPARTMENT ON DELETE SET NULL; ALTER TABLE EMPLOYEE ADD CONSTRAINT NUMBER CHECK (PHONENO >= '0000' AND PHONENO <= '9999'); CREATE INDEX XEMP2 ON EMPLOYEE (WORKDEPT); CREATE ALIAS EMP FOR EMPLOYEE; INSERT INTO EMPLOYEE VALUES ('000010','CHRISTINE','I','HAAS','A00','3978','1965-01-01','PRES' ,18 ,'F','1933-08-24', 52750.00, 1000,4220), ('000020','MICHAEL','L','THOMPSON','B01','3476','1973-10-10','MANAGER',18 ,'M','1948-02-02', 41250.00, 800,3300), ('000030','SALLY','A','KWAN','C01','4738','1975-04-05','MANAGER',20,'F','1941-05-11', 38250.00, 800,3060), ('000050','JOHN','B','GEYER','E01','6789', '1949-08-17','MANAGER',16,'M','1925-09-15', 40175.00, 800,3214), ('000060','IRVING','F','STERN','D11','6423','1973-09-14','MANAGER',16,'M','1945-07-07', 32250.00, 500,2580), ('000070','EVA','D','PULASKI','D21','7831','1980-09-30','MANAGER',16,'F','1953-05-26', 36170.00, 700,2893), ('000090','EILEEN','W','HENDERSON','E11','5498','1970-08-15','MANAGER',16,'F','1941-05-15', 29750.00, 600,2380), ('000100','THEODORE','Q','SPENSER','E21','0972','1980-06-19','MANAGER',14 ,'M','1956-12-18', 26150.00, 500,2092), ('000110','VICENZO','G','LUCCHESSI','A00','3490','1958-05-16','SALESREP',19,'M','1929-11-05', 46500.00, 900,3720), ('000120','SEAN',' ','O''CONNELL','A00','2167','1963-12-05','CLERK',14,'M','1942-10-18', 29250.00, 600,2340), ('000130','DELORES','M','QUINTANA','C01','4578','1971-07-28','ANALYST',16,'F','1925-09-15', 23800.00, 500,1904), ('000140','HEATHER','A','NICHOLLS','C01','1793','1976-12-15','ANALYST',18,'F','1946-01-19', 28420.00, 600,2274), ('000150','BRUCE',' ','ADAMSON','D11','4510','1972-02-12','DESIGNER',16,'M','1947-05-17', 25280.00, 500,2022), ('000160','ELIZABETH','R','PIANKA','D11','3782','1977-10-11','DESIGNER',17,'F','1955-04-12', 22250.00, 400,1780), ('000170','MASATOSHI','J','YOSHIMURA','D11','2890','1978-09-15','DESIGNER',16,'M','1951-01-05', 24680.00, 500,1974), ('000180','MARILYN','S','SCOUTTEN','D11','1682','1973-07-07','DESIGNER',17,'F','1949-02-21', 21340.00, 500,1707), ('000190','JAMES','H','WALKER','D11','2986','1974-07-26','DESIGNER',16,'M','1952-06-25', 20450.00, 400,1636), ('000200','DAVID',' ','BROWN','D11','4501','1966-03-03','DESIGNER',16,'M','1941-05-29', 27740.00, 600,2217), ('000210','WILLIAM','T','JONES','D11','0942','1979-04-11','DESIGNER',17,'M','1953-02-23', 18270.00, 400,1462), ('000220','JENNIFER','K','LUTZ','D11','0672','1968-08-29','DESIGNER',18,'F','1948-03-19', 29840.00, 600,2387), ('000230','JAMES','J','JEFFERSON','D21','2094','1966-11-21','CLERK',14,'M','1935-05-30', 22180.00, 400,1774), ('000240','SALVATORE','M','MARINO','D21','3780','1979-12-05','CLERK',17,'M','1954-03-31', 28760.00, 600,2301), ('000250','DANIEL','S','SMITH','D21','0961','1969-10-30','CLERK',15,'M','1939-11-12', 19180.00, 400,1534), ('000260','SYBIL','P','JOHNSON','D21','8953','1975-09-11','CLERK',16,'F','1936-10-05', 17250.00, 300,1380), ('000270','MARIA','L','PEREZ','D21','9001','1980-09-30','CLERK',15,'F','1953-05-26', 27380.00, 500,2190), ('000280','ETHEL','R','SCHNEIDER','E11','8997','1967-03-24','OPERATOR',17,'F','1936-03-28', 26250.00, 500,2100), ('000290','JOHN','R','PARKER','E11','4502','1980-05-30','OPERATOR',12,'M','1946-07-09', 15340.00, 300,1227), ('000300','PHILIP','X','SMITH','E11','2095','1972-06-19','OPERATOR',14,'M','1936-10-27', 17750.00, 400,1420), ('000310','MAUDE','F','SETRIGHT','E11','3332','1964-09-12','OPERATOR',12,'F','1931-04-21', 15900.00, 300,1272), ('000320','RAMLAL','V','MEHTA','E21','9990','1965-07-07','FILEREP',16,'M','1932-08-11', 19950.00, 400,1596), ('000330','WING',' ','LEE','E21','2103','1976-02-23','FILEREP',14,'M','1941-07-18', 25370.00, 500,2030), ('000340','JASON','R','GOUNOT','E21','5698','1947-05-05','FILEREP',16,'M','1926-05-17', 23840.00, 500,1907), ('200010','DIAN','J','HEMMINGER','A00','3978','1965-01-01','SALESREP',18,'F','1933-08-14', 46500.00, 1000,4220), ('200120','GREG','','ORLANDO','A00','2167', '1972-05-05','CLERK',14,'M','1942-10-18', 29250.00, 600,2340), ('200140','KIM','N','NATZ','C01','1793','1976-12-15','ANALYST',18,'F','1946-01-19', 28420.00, 600,2274), ('200170','KIYOSHI','','YAMAMOTO','D11','2890','1978-09-15','DESIGNER',16,'M','1951-01-05', 24680.00, 500,1974), ('200220','REBA','K','JOHN','D11','0672','1968-08-29','DESIGNER',18,'F','1948-03-19', 29840.00, 600,2387), ('200240','ROBERT','M','MONTEVERDE','D21','3780','1979-12-05','CLERK',17,'M','1954-03-31', 28760.00,600,2301), ('200280','EILEEN','R','SCHWARTZ','E11','8997','1967-03-24','OPERATOR',17,'F','1936-03-28', 26250.00,500,2100), ('200310','MICHELLE','F','SPRINGER','E11','3332','1964-09-12','OPERATOR',12,'F','1931-04-21', 15900.00,300,1272), ('200330','HELENA','','WONG','E21','2103','1976-02-23','FIELDREP',14,'F','1941-07-18', 25370.00, 500,2030), ('200340','ROY','R','ALONZO','E21','5698','1947-05-05','FIELDREP',16,'M','1926-05-17', 23840.00, 500,1907); ALTER TABLE DEPARTMENT ADD FOREIGN KEY RDE (MGRNO) REFERENCES EMPLOYEE ON DELETE SET NULL; -- PROJECT 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) ); ALTER TABLE PROJECT ADD FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT ON DELETE RESTRICT; ALTER TABLE PROJECT ADD FOREIGN KEY (RESPEMP) REFERENCES EMPLOYEE ON DELETE RESTRICT; ALTER TABLE PROJECT ADD FOREIGN KEY RPP (MAJPROJ) REFERENCES PROJECT ON DELETE CASCADE; CREATE INDEX XPROJ2 ON PROJECT (RESPEMP); CREATE ALIAS PROJ FOR PROJECT; INSERT INTO PROJECT VALUES ('AD3100','ADMIN SERVICES','D01','000010', 6.50,'1982-01-01','1983-02-01',NULL), ('AD3110','GENERAL ADMIN SYSTEMS','D21','000070', 6.00,'1982-01-01','1983-02-01','AD3100'), ('AD3111','PAYROLL PROGRAMMING','D21','000230', 2.00,'1982-01-01','1983-02-01','AD3110'), ('AD3112','PERSONNEL PROGRAMMING','D21','000250', 1.00,'1982-01-01','1983-02-01','AD3110'), ('AD3113','ACCOUNT PROGRAMMING','D21','000270', 2.00,'1982-01-01','1983-02-01','AD3110'), ('IF1000','QUERY SERVICES','C01','000030', 2.00,'1982-01-01','1983-02-01',NULL), ('IF2000','USER EDUCATION','C01','000030', 1.00,'1982-01-01','1983-02-01',NULL), ('MA2100','WELD LINE AUTOMATION','D01','000010', 12.00,'1982-01-01','1983-02-01',NULL), ('MA2110','W L PROGRAMMING','D11','000060', 9.00,'1982-01-01','1983-02-01','MA2100'), ('MA2111','W L PROGRAM DESIGN','D11','000220', 2.00,'1982-01-01','1982-12-01','MA2110'), ('MA2112','W L ROBOT DESIGN','D11','000150', 3.00,'1982-01-01','1982-12-01','MA2110'), ('MA2113','W L PROD CONT PROGS','D11','000160', 3.00,'1982-02-15','1982-12-01','MA2110'), ('OP1000','OPERATION SUPPORT','E01','000050', 6.00,'1982-01-01','1983-02-01',NULL), ('OP1010','OPERATION','E11','000090', 5.00,'1982-01-01','1983-02-01','OP1000'), ('OP2000','GEN SYSTEMS SERVICES','E01','000050', 5.00,'1982-01-01','1983-02-01',NULL), ('OP2010','SYSTEMS SUPPORT','E21','000100', 4.00,'1982-01-01','1983-02-01','OP2000'), ('OP2011','SCP SYSTEMS SUPPORT','E21','000320', 1.00,'1982-01-01','1983-02-01','OP2010'), ('OP2012','APPLICATIONS SUPPORT','E21','000330', 1.00,'1982-01-01','1983-02-01','OP2010'), ('OP2013','DB/DC SUPPORT','E21','000340', 1.00,'1982-01-01','1983-02-01','OP2010'), ('PL2100','WELD LINE PLANNING','B01','000020', 1.00,'1982-01-01','1982-09-15','MA2100'); -- PROJACT 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) ); ALTER TABLE PROJACT ADD FOREIGN KEY RPAP (PROJNO) REFERENCES PROJECT ON DELETE RESTRICT; INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES ('AD3100', 10,'1982-01-01'), ('AD3110', 10,'1982-01-01'), ('AD3111', 60,'1982-01-01'), ('AD3111', 60,'1982-03-15'), ('AD3111', 70,'1982-03-15'), ('AD3111', 80,'1982-04-15'), ('AD3111', 180,'1982-10-15'), ('AD3111', 70,'1982-02-15'), ('AD3111', 80,'1982-09-15'), ('AD3112', 60,'1982-01-01'), ('AD3112', 60,'1982-02-01'), ('AD3112', 60,'1983-01-01'), ('AD3112', 70,'1982-02-01'), ('AD3112', 70,'1982-03-15'), ('AD3112', 70,'1982-08-15'), ('AD3112', 80,'1982-08-15'), ('AD3112', 80,'1982-10-15'), ('AD3112', 180,'1982-08-15'), ('AD3113', 70,'1982-06-15'), ('AD3113', 70,'1982-07-01'), ('AD3113', 80,'1982-01-01'), ('AD3113', 80,'1982-03-01'), ('AD3113', 180,'1982-03-01'), ('AD3113', 180,'1982-04-15'), ('AD3113', 180,'1982-06-01'), ('AD3113', 60,'1982-03-01'), ('AD3113', 60,'1982-04-01'), ('AD3113', 60,'1982-09-01'), ('AD3113', 70,'1982-09-01'), ('AD3113', 70,'1982-10-15'), ('IF1000', 10,'1982-06-01'), ('IF1000', 90,'1982-10-01'), ('IF1000', 100,'1982-10-01'), ('IF2000', 10,'1982-01-01'), ('IF2000', 100,'1982-01-01'), ('IF2000', 100,'1982-03-01'), ('IF2000', 110,'1982-03-01'), ('IF2000', 110,'1982-10-01'), ('MA2100', 10,'1982-01-01'), ('MA2100', 20,'1982-01-01'), ('MA2110', 10,'1982-01-01'), ('MA2111', 50,'1982-01-01'), ('MA2111', 60,'1982-06-15'), ('MA2111', 40,'1982-01-01'), ('MA2112', 60,'1982-01-01'), ('MA2112', 180,'1982-07-15'), ('MA2112', 70,'1982-06-01'), ('MA2112', 70,'1982-01-01'), ('MA2112', 80,'1982-10-01'), ('MA2113', 60,'1982-07-15'), ('MA2113', 80,'1982-01-01'), ('MA2113', 70,'1982-04-01'), ('MA2113', 80,'1982-10-01'), ('MA2113', 180,'1982-10-01'), ('OP1000', 10,'1982-01-01'), ('OP1010', 10,'1982-01-01'), ('OP1010', 130,'1982-01-01'), ('OP2010', 10,'1982-01-01'), ('OP2011', 140,'1982-01-01'), ('OP2011', 150,'1982-01-01'), ('OP2012', 140,'1982-01-01'), ('OP2012', 160,'1982-01-01'), ('OP2013', 140,'1982-01-01'), ('OP2013', 170,'1982-01-01'), ('PL2100', 30,'1982-01-01'); -- EMPPROJACT (EMP_ACT is an ALIAS) 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 ); ALTER TABLE EMPPROJACT ADD FOREIGN KEY REPAPA (PROJNO, ACTNO, EMSTDATE) REFERENCES PROJACT ON DELETE RESTRICT; CREATE ALIAS EMPACT FOR EMPPROJACT; CREATE ALIAS EMP_ACT FOR EMPPROJACT; INSERT INTO EMP_ACT VALUES ('000010','AD3100', 10, 0.5,'1982-01-01','1982-07-01'), ('000070','AD3110', 10, 1.0,'1982-01-01','1983-02-01'), ('000230','AD3111', 60, 1.0,'1982-01-01','1982-03-15'), ('000230','AD3111', 60, 0.5,'1982-03-15','1982-04-15'), ('000230','AD3111', 70, 0.5,'1982-03-15','1982-10-15'), ('000230','AD3111', 80, 0.5,'1982-04-15','1982-10-15'), ('000230','AD3111', 180, 0.5,'1982-10-15','1983-01-01'), ('000240','AD3111', 70, 1.0,'1982-02-15','1982-09-15'), ('000240','AD3111', 80, 1.0,'1982-09-15','1983-01-01'), ('000250','AD3112', 60, 1.0,'1982-01-01','1982-02-01'), ('000250','AD3112', 60, 0.5,'1982-02-01','1982-03-15'), ('000250','AD3112', 60, 1.0,'1983-01-01','1983-02-01'), ('000250','AD3112', 70, 0.5,'1982-02-01','1982-03-15'), ('000250','AD3112', 70, 1.0,'1982-03-15','1982-08-15'), ('000250','AD3112', 70,0.25,'1982-08-15','1982-10-15'), ('000250','AD3112', 80,0.25,'1982-08-15','1982-10-15'), ('000250','AD3112', 80,0.50,'1982-10-15','1982-12-01'), ('000250','AD3112', 180,0.50,'1982-08-15','1983-01-01'), ('000260','AD3113', 70,0.50,'1982-06-15','1982-07-01'), ('000260','AD3113', 70,1.00,'1982-07-01','1983-02-01'), ('000260','AD3113', 80,1.00,'1982-01-01','1982-03-01'), ('000260','AD3113', 80,0.50,'1982-03-01','1982-04-15'), ('000260','AD3113', 180,0.50,'1982-03-01','1982-04-15'), ('000260','AD3113', 180,1.00,'1982-04-15','1982-06-01'), ('000260','AD3113', 180,1.00,'1982-06-01','1982-07-01'), ('000270','AD3113', 60,0.50,'1982-03-01','1982-04-01'), ('000270','AD3113', 60,1.00,'1982-04-01','1982-09-01'), ('000270','AD3113', 60,0.25,'1982-09-01','1982-10-15'), ('000270','AD3113', 70,0.75,'1982-09-01','1982-10-15'), ('000270','AD3113', 70,1.00,'1982-10-15','1983-02-01'), ('000270','AD3113', 80,1.00,'1982-01-01','1982-03-01'), ('000270','AD3113', 80,0.50,'1982-03-01','1982-04-01'), ('000030','IF1000', 10,0.50,'1982-06-01','1983-01-01'), ('000130','IF1000', 90,1.00,'1982-10-01','1983-01-01'), ('000130','IF1000', 100,0.50,'1982-10-01','1983-01-01'), ('000140','IF1000', 90,0.50,'1982-10-01','1983-01-01'), ('000030','IF2000', 10,0.50,'1982-01-01','1983-01-01'), ('000140','IF2000', 100,1.00,'1982-01-01','1982-03-01'), ('000140','IF2000', 100,0.50,'1982-03-01','1982-07-01'), ('000140','IF2000', 110,0.50,'1982-03-01','1982-07-01'), ('000140','IF2000', 110,0.50,'1982-10-01','1983-01-01'), ('000010','MA2100', 10,0.50,'1982-01-01','1982-11-01'), ('000110','MA2100', 20,1.00,'1982-01-01','1983-03-01'), ('000010','MA2110', 10,1.00,'1982-01-01','1983-02-01'), ('000200','MA2111', 50,1.00,'1982-01-01','1982-06-15'), ('000200','MA2111', 60,1.00,'1982-06-15','1983-02-01'), ('000220','MA2111', 40,1.00,'1982-01-01','1983-02-01'), ('000150','MA2112', 60,1.00,'1982-01-01','1982-07-15'), ('000150','MA2112', 180,1.00,'1982-07-15','1983-02-01'), ('000170','MA2112', 60,1.00,'1982-01-01','1983-06-01'), ('000170','MA2112', 70,1.00,'1982-06-01','1983-02-01'), ('000190','MA2112', 70,1.00,'1982-01-01','1982-10-01'), ('000190','MA2112', 80,1.00,'1982-10-01','1983-10-01'), ('000160','MA2113', 60,1.00,'1982-07-15','1983-02-01'), ('000170','MA2113', 80,1.00,'1982-01-01','1983-02-01'), ('000180','MA2113', 70,1.00,'1982-04-01','1982-06-15'), ('000210','MA2113', 80,0.50,'1982-10-01','1983-02-01'), ('000210','MA2113', 180,0.50,'1982-10-01','1983-02-01'), ('000050','OP1000', 10,0.25,'1982-01-01','1983-02-01'), ('000090','OP1010', 10,1.00,'1982-01-01','1983-02-01'), ('000280','OP1010', 130,1.00,'1982-01-01','1983-02-01'), ('000290','OP1010', 130,1.00,'1982-01-01','1983-02-01'), ('000300','OP1010', 130,1.00,'1982-01-01','1983-02-01'), ('000310','OP1010', 130,1.00,'1982-01-01','1983-02-01'), ('000050','OP2010', 10,0.75,'1982-01-01','1983-02-01'), ('000100','OP2010', 10,1.00,'1982-01-01','1983-02-01'), ('000320','OP2011', 140,0.75,'1982-01-01','1983-02-01'), ('000320','OP2011', 150,0.25,'1982-01-01','1983-02-01'), ('000330','OP2012', 140,0.25,'1982-01-01','1983-02-01'), ('000330','OP2012', 160,0.75,'1982-01-01','1983-02-01'), ('000340','OP2013', 140,0.50,'1982-01-01','1983-02-01'), ('000340','OP2013', 170,0.50,'1982-01-01','1983-02-01'), ('000020','PL2100', 30,1.00,'1982-01-01','1982-09-15'); -- ACT CREATE TABLE ACT ( ACTNO SMALLINT NOT NULL, ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, PRIMARY KEY (ACTNO) ); CREATE UNIQUE INDEX XACT2 ON ACT (ACTKWD); INSERT INTO ACT VALUES (10,'MANAGE','MANAGE/ADVISE'), (20,'ECOST','ESTIMATE COST'), (30,'DEFINE','DEFINE SPECS'), (40,'LEADPR','LEAD PROGRAM/DESIGN'), (50,'SPECS','WRITE SPECS'), (60,'LOGIC','DESCRIBE LOGIC'), (70,'CODE','CODE PROGRAMS'), (80,'TEST','TEST PROGRAMS'), (90,'ADMQS','ADM QUERY SYSTEM'), (100,'TEACH','TEACH CLASSES'), (110,'COURSE','DEVELOP COURSES'), (120,'STAFF','PERS AND STAFFING'), (130,'OPERAT','OPER COMPUTER SYS'), (140,'MAINT','MAINT SOFTWARE SYS'), (150,'ADMSYS','ADM OPERATING SYS'), (160,'ADMDB','ADM DATA BASES'), (170,'ADMDC','ADM DATA COMM'), (180,'DOC','DOCUMENT'); ALTER TABLE PROJACT ADD FOREIGN KEY RPAA (ACTNO) REFERENCES ACT ON DELETE RESTRICT; -- EMP_PHOTO CREATE TABLE EMP_PHOTO ( EMPNO CHAR(6) NOT NULL, PHOTO_FORMAT VARCHAR(10) NOT NULL, PICTURE BLOB(100K), PRIMARY KEY (EMPNO,PHOTO_FORMAT) ); ALTER TABLE EMP_PHOTO ADD FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE ON DELETE RESTRICT; INSERT INTO EMP_PHOTO VALUES ('000130','bitmap', NULL), ('000130','gif', null), ('000140','bitmap', null), ('000140','gif', null), ('000150','bitmap', null), ('000150','gif', null), ('000190','bitmap', null), ('000190','gif', null); -- EMP_RESUME CREATE TABLE EMP_RESUME ( EMPNO CHAR(6) NOT NULL, RESUME_FORMAT VARCHAR(10) NOT NULL, RESUME CLOB(5K), PRIMARY KEY (EMPNO,RESUME_FORMAT) ); ALTER TABLE EMP_RESUME ADD FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE ON DELETE RESTRICT; INSERT INTO EMP_RESUME VALUES ('000130','ascii', null), ('000130','html', null), ('000140','ascii', null), ('000140','html', null), ('000150','ascii', null), ('000150','html', null), ('000190','ascii', null), ('000190','html', null); -- SALES CREATE TABLE SALES ( SALES_DATE DATE, SALES_PERSON VARCHAR(15), REGION VARCHAR(15), SALES INTEGER ); INSERT INTO SALES VALUES ('12/31/1995','LUCCHESSI','Ontario-South', 1), ('12/31/1995','LEE','Ontario-South', 3), ('12/31/1995','LEE','Quebec', 1), ('12/31/1995','LEE','Manitoba', 2), ('12/31/1995','GOUNOT','Quebec', 1), ('03/29/1996','LUCCHESSI','Ontario-South', 3), ('03/29/1996','LUCCHESSI','Quebec', 1), ('03/29/1996','LEE','Ontario-South', 2), ('03/29/1996','LEE','Ontario-North', 2), ('03/29/1996','LEE','Quebec', 3), ('03/29/1996','LEE','Manitoba', 5), ('03/29/1996','GOUNOT','Ontario-South', 3), ('03/29/1996','GOUNOT','Quebec', 1), ('03/29/1996','GOUNOT','Manitoba', 7), ('03/30/1996','LUCCHESSI','Ontario-South', 1), ('03/30/1996','LUCCHESSI','Quebec', 2), ('03/30/1996','LUCCHESSI','Manitoba', 1), ('03/30/1996','LEE','Ontario-South', 7), ('03/30/1996','LEE','Ontario-North', 3), ('03/30/1996','LEE','Quebec', 7), ('03/30/1996','LEE','Manitoba', 4), ('03/30/1996','GOUNOT','Ontario-South', 2), ('03/30/1996','GOUNOT','Quebec', 18), ('03/30/1996','GOUNOT','Manitoba', 1), ('03/31/1996','LUCCHESSI','Manitoba', 1), ('03/31/1996','LEE','Ontario-South', 14), ('03/31/1996','LEE','Ontario-North', 3), ('03/31/1996','LEE','Quebec', 7), ('03/31/1996','LEE','Manitoba', 3), ('03/31/1996','GOUNOT','Ontario-South', 2), ('03/31/1996','GOUNOT','Quebec', 1), ('04/01/1996','LUCCHESSI','Ontario-South', 3), ('04/01/1996','LUCCHESSI','Manitoba', 1), ('04/01/1996','LEE','Ontario-South', 8), ('04/01/1996','LEE','Ontario-North',NULL), ('04/01/1996','LEE','Quebec', 8), ('04/01/1996','LEE','Manitoba', 9), ('04/01/1996','GOUNOT','Ontario-South', 3), ('04/01/1996','GOUNOT','Ontario-North', 1), ('04/01/1996','GOUNOT','Quebec', 3), ('04/01/1996','GOUNOT','Manitoba', 7); -- CL_SCHED CREATE TABLE CL_SCHED ( CLASS_CODE CHAR(7), "DAY" SMALLINT, STARTING TIME, ENDING TIME ); INSERT INTO CL_SCHED VALUES ('042:BF' ,4,'12:10:00','14:00:00'), ('553:MJA' ,1,'10:30:00','11:00:00'), ('543:CWM' ,3,'09:10:00','10:30:00'), ('778:RES' ,2,'12:10:00','14:00:00'), ('044:HD' ,3,'17:12:30','18:00:00'); -- IN_TRAY CREATE TABLE IN_TRAY ( RECEIVED TIMESTAMP, SOURCE CHAR(8), SUBJECT CHAR(64), NOTE_TEXT VARCHAR(3000) ); 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 thefridge if you want to come over to celebrate a bit. Delores andHeather, are you interested as well? Bruce Subject: FWD: Fantastic year! 4th Quarter Bonus. To: Dept_D11 Congratulations on a job well done. Enjoy this year''s bonus. Irv Subject: Fantastic year! 4th Quarter Bonus. To: All_Managers Our 4th quarter results are in. We pulled together as a team andexceeded our plan! I am pleased to announce a bonus this year of 18%. Enjoy the holidays. Christine Haas'), ('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 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'), ('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 andexceeded our plan! I am pleased to announce a bonus this year of 18%. Enjoy the holidays. Christine Haas'); -- View Creation CREATE VIEW VDEPT AS SELECT ALL DEPTNO, DEPTNAME, MGRNO, ADMRDEPT FROM DEPT; CREATE VIEW VHDEPT AS SELECT ALL DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION FROM DEPT; CREATE VIEW VEMP AS SELECT ALL EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT FROM EMP; CREATE VIEW VPROJ AS SELECT ALL PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, MAJPROJ FROM PROJ; CREATE VIEW VACT AS SELECT ALL ACTNO, ACTKWD, ACTDESC FROM ACT; CREATE VIEW VPROJACT AS SELECT ALL PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE FROM PROJACT; CREATE VIEW VEMPPROJACT AS SELECT ALL EMPNO, PROJNO, ACTNO, EMPTIME, EMSTDATE, EMENDATE FROM EMPPROJACT; 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; 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; 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; 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; 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; 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; 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); 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; 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; 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; 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; CREATE VIEW VEMPLP (EMPLOYEENUMBER, PHONENUMBER) AS SELECT ALL EMPNO, PHONENO FROM EMP;