| CREATE TABLE trafodion.persnl.dept |
| ( deptnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL |
| , deptname CHARACTER (12) NO DEFAULT NOT NULL |
| , manager NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL |
| , rptdept NUMERIC (4) UNSIGNED DEFAULT 0 NOT NULL |
| , location VARCHAR (18) DEFAULT ' ' NOT NULL |
| , PRIMARY KEY ( deptnum ) |
| ) ; |
| |
| CREATE INDEX xdeptmgr ON dept |
| ( manager |
| ) ; |
| |
| CREATE INDEX xdeptrpt ON dept |
| ( rptdept |
| ) ; |
| |
| ALTER TABLE trafodion.persnl.dept |
| ADD CONSTRAINT mgrnum_constrnt |
| CHECK (manager BETWEEN 0000 AND 9999) |
| ; |
| |
| ALTER TABLE trafodion.persnl.dept |
| ADD CONSTRAINT deptnum_constrnt |
| CHECK ( deptnum IN |
| ( 1000 |
| , 1500 |
| , 2000 |
| , 2500 |
| , 3000 |
| , 3100 |
| , 3200 |
| , 3300 |
| , 3500 |
| , 4000 |
| , 4100 |
| , 9000 |
| ) |
| ) |
| ; |
| 3 |
| CREATE VIEW trafodion.persnl.mgrlist |
| ( first_name |
| , last_name |
| , department |
| ) |
| AS SELECT |
| first_name |
| , last_name |
| , deptname |
| FROM dept, employee |
| WHERE dept.manager = employee.empnum |
| ; |
| |
| INSERT INTO trafodion.persnl.dept VALUES |
| ( 1000, 'FINANCE', 23, 9000, 'CHICAGO' ) |
| , ( 1500, 'PERSONNEL', 213, 1000, 'CHICAGO' ) |
| , ( 2000, 'INVENTORY', 32, 9000, 'LOS ANGELES' ) |
| , ( 2500, 'SHIPPING', 234, 2000, 'PHOENIX' ) |
| , ( 3000, 'MARKETING', 29, 9000, 'NEW YORK' ) |
| , ( 3100, 'CANADA SALES', 43, 3000, 'TORONTO' ) |
| , ( 3200, 'GERMNY SALES', 39, 3000, 'FRANKFURT' ) |
| , ( 3300, 'ENGLND SALES', 72, 3000, 'LONDON' ) |
| , ( 3500, 'ASIA SALES', 111, 3000, 'HONG KONG' ) |
| , ( 4000, 'RESEARCH', 65, 9000, 'NEW YORK' ) |
| , ( 4100, 'PLANNING', 87, 4000, 'NEW YORK' ) |
| , ( 9000, 'xxCORPORATE', 1, 9000, 'CHICAGO' ) |
| ; |
| |
| UPDATE STATISTICS FOR TABLE trafodion.persnl.dept ON EVERY COLUMN ; |