| CREATE TABLE trafodion.persnl.employee |
| ( empnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL |
| , first_name CHARACTER (15) DEFAULT ' ' NOT NULL |
| , last_name CHARACTER (20) DEFAULT ' ' NOT NULL |
| , deptnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL |
| , jobcode NUMERIC (4) UNSIGNED DEFAULT NULL |
| , salary NUMERIC (8, 2) UNSIGNED DEFAULT NULL |
| , PRIMARY KEY ( empnum ) |
| ) ; |
| |
| ALTER TABLE trafodion.persnl.employee |
| ADD CONSTRAINT empnum_constrnt CHECK ( empnum BETWEEN 0001 AND 9999 ) |
| ; |
| |
| CREATE INDEX xempname ON employee |
| ( last_name |
| , first_name |
| ) ; |
| |
| CREATE INDEX xempdept ON employee |
| ( deptnum |
| ) ; |
| |
| CREATE VIEW trafodion.persnl.emplist AS |
| SELECT |
| empnum |
| , first_name |
| , last_name |
| , deptnum |
| , jobcode |
| FROM employee |
| ; |
| |
| INSERT INTO trafodion.persnl.employee VALUES |
| ( 1, 'ROGER', 'GREEN', 9000, 100, 175500.00 ) |
| , ( 23, 'JERRY', 'HOWARD', 1000, 100, 137000.10 ) |
| , ( 29, 'JANE', 'RAYMOND', 3000, 100, 136000.00 ) |
| , ( 32, 'THOMAS', 'RUDLOFF', 2000, 100, 138000.40 ) |
| , ( 39, 'KLAUS ', 'SAFFERT', 3200, 100, 75000.00 ) |
| , ( 43, 'PAUL', 'WINTER', 3100, 100, 90000.00 ) |
| , ( 65, 'RACHEL', 'MCKAY', 4000, 100, 118000.00 ) |
| , ( 72, 'GLENN', 'THOMAS', 3300, 100, 80000.00 ) |
| , ( 75, 'TIM', 'WALKER', 3000, 300, 32000.00 ) |
| , ( 87, 'ERIC', 'BROWN', 4000, 400, 89000.00 ) |
| , ( 89, 'PETER', 'SMITH', 3300, 300, 37000.40 ) |
| , ( 93, 'DONALD', 'TAYLOR', 3100, 300, 33000.00 ) |
| , ( 104, 'DAVID', 'STRAND', 4000, 400, 69000.00 ) |
| , ( 109, 'STEVE', 'COOK', 4000, 400, 68000.00 ) |
| , ( 111, 'SHERRIE', 'WONG', 3500, 100, 70000.00 ) |
| , ( 178, 'JOHN', 'CHOU', 3500, 900, 28000.00 ) |
| , ( 180, 'MANFRED', 'CONRAD', 4000, 450, 32000.00 ) |
| , ( 201, 'JIM', 'HERMAN', 3000, 300, 19000.00 ) |
| , ( 202, 'LARRY', 'CLARK', 1000, 500, 25000.75 ) |
| , ( 203, 'KATHRYN', 'HALL', 4000, 400, 96000.00 ) |
| , ( 205, 'GINNY', 'FOSTER', 3300, 900, 30000.00 ) |
| , ( 206, 'DAVE', 'FISHER', 3200, 900, 25000.00 ) |
| , ( 207, 'MARK', 'FOLEY', 4000, 420, 33000.00 ) |
| , ( 208, 'SUE', 'CRAMER', 1000, 900, 19000.00 ) |
| , ( 209, 'SUSAN', 'CHAPMAN', 1500, 900, 17000.00 ) |
| , ( 210, 'RICHARD', 'BARTON', 1000, 500, 29000.00 ) |
| , ( 211, 'JIMMY', 'SCHNEIDER', 1500, 600, 26000.00 ) |
| , ( 212, 'JONATHAN', 'MITCHELL', 1500, 600, 32000.00 ) |
| , ( 213, 'ROBERT', 'WHITE', 1500, 100, 90000.00 ) |
| , ( 214, 'JULIA', 'KELLY', 1000, 500, 50000.00 ) |
| , ( 215, 'WALTER', 'LANCASTER', 4000, 450, 33000.50 ) |
| , ( 216, 'JOHN', 'JONES', 4000, 450, 40000.00 ) |
| , ( 217, 'MARLENE', 'BONNY', 4000, 900, 24000.90 ) |
| , ( 218, 'GEORGE', 'FRENCHMAN', 4000, 420, 36000.00 ) |
| , ( 219, 'DAVID', 'TERRY', 2000, 250, 27000.12 ) |
| , ( 220, 'JOHN', 'HUGHES', 3200, 300, 33000.10 ) |
| , ( 221, 'OTTO', 'SCHNABL', 3200, 300, 33000.00 ) |
| , ( 222, 'MARTIN', 'SCHAEFFER', 3200, 300, 31000.00 ) |
| , ( 223, 'HERBERT', 'KARAJAN', 3200, 300, 29000.00 ) |
| , ( 224, 'MARIA', 'JOSEF', 4000, 420, 18000.10 ) |
| , ( 225, 'KARL', 'HELMSTED', 4000, 450, 32000.00 ) |
| , ( 226, 'HEIDI', 'WEIGL', 3200, 300, 22000.00 ) |
| , ( 227, 'XAVIER', 'SEDLEMEYER', 3300, 300, 30000.00 ) |
| , ( 228, 'PETE', 'WELLINGTON', 3100, 300, 32000.20 ) |
| , ( 229, 'GEORGE', 'STRICKER', 3100, 300, 32222.00 ) |
| , ( 230, 'ROCKY', 'LEWIS', 2000, 200, 24000.00 ) |
| , ( 231, 'HERB', 'ALBERT', 3300, 300, 33000.00 ) |
| , ( 232, 'THOMAS', 'SPINNER', 4000, 450, 45000.00 ) |
| , ( 233, 'TED', 'MCDONALD', 2000, 250, 29000.00 ) |
| , ( 234, 'MARY', 'MILLER', 2500, 100, 56000.00 ) |
| , ( 235, 'MIRIAM', 'KING', 2500, 900, 18000.00 ) |
| , ( 321, 'BILL', 'WINN', 2000, 900, 32000.00 ) |
| , ( 337, 'DINAH', 'CLARK', 9000, 900, 37000.00 ) |
| , ( 343, 'ALAN', 'TERRY', 3000, 900, 39500.00 ) |
| , ( 557, 'BEN', 'HENDERSON', 4000, 400, 65000.00 ) |
| , ( 568, 'JESSICA', 'CRINER', 3500, 300, 39500.00 ) |
| , ( 990, 'THOMAS', 'STIBBS', 3500, NULL, NULL ) |
| , ( 991, 'WAYNE', 'O''NEIL', 3500, NULL, NULL ) |
| , ( 992, 'BARRY', 'KINNEY', 3500, NULL, NULL ) |
| , ( 993, 'PAUL', 'BUSKETT', 3100, NULL, NULL ) |
| , ( 994, 'EMMY', 'BUSKETT', 3100, NULL, NULL ) |
| , ( 995, 'WALT', 'FARLEY', 3100, NULL, NULL ) |
| ; |
| |
| UPDATE STATISTICS FOR TABLE trafodion.persnl.employee ON EVERY COLUMN ; |
