EVOLUTION-MANAGER

Edit File: occur.result

CREATE TABLE employee ( serialno CHAR(5) NOT NULL, name VARCHAR(12) NOT NULL FLAG=6, sex TINYINT(1) NOT NULL, title VARCHAR(15) NOT NULL FLAG=20, manager CHAR(5) DEFAULT NULL, department CHAR(4) NOT NULL FLAG=41, secretary CHAR(5) DEFAULT NULL FLAG=46, salary DOUBLE(8,2) NOT NULL FLAG=52 ) ENGINE=connect TABLE_TYPE=fix FILE_NAME='employee.dat' ENDING=1; SELECT * FROM employee; serialno name sex title manager department secretary salary 74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00 02345 SMITH 1 ENGINEER 31416 2452 11111 9000.00 78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00 07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00 45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00 34567 BIGHEAD 1 SCIENTIST 31416 2452 11111 8000.00 77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00 74234 WALTER 1 ENGINEER 70012 0318 24888 7400.00 56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00 73452 TONGHO 1 ENGINEER 70012 0318 24888 6800.00 22222 SHORTSIGHT 2 SECRETARY 87777 0021 NULL 5500.00 55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50 27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00 98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00 11111 CHERRY 2 SECRETARY 31416 2452 NULL 4500.00 33333 MONAPENNY 2 SECRETARY 07654 0319 NULL 3800.00 12345 KITTY 2 TYPIST 40567 0319 NULL 3000.45 24888 PLUMHEAD 2 TYPIST 27845 0318 NULL 2800.00 87777 STRONG 1 DIRECTOR NULL 0021 22222 23000.00 76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00 70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00 40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00 31416 ORELLY 1 ENGINEER 87777 2452 11111 13400.00 36666 BIGHORN 1 SCIENTIST 31416 2452 11111 11000.00 00137 BROWNY 1 ENGINEER 40567 0319 12345 10500.00 73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00 00023 MARTIN 1 ENGINEER 40567 0319 12345 10000.00 CREATE TABLE occurs (name CHAR(12), sex CHAR(1), title CHAR(15), department CHAR(4), salary DOUBLE(8,2), id_of CHAR(12), id CHAR(5) NOT NULL) ENGINE=CONNECT TABLE_TYPE=OCCUR TABNAME=employee OPTION_LIST='OccurCol=ID,RankCol=ID_OF,Colist=serialno;manager;secretary,port=PORT';; SELECT * FROM occurs; name sex title department salary id_of id BANCROFT 2 SALESMAN 0318 9600.00 serialno 74200 BANCROFT 2 SALESMAN 0318 9600.00 manager 70012 BANCROFT 2 SALESMAN 0318 9600.00 secretary 24888 SMITH 1 ENGINEER 2452 9000.00 serialno 02345 SMITH 1 ENGINEER 2452 9000.00 manager 31416 SMITH 1 ENGINEER 2452 9000.00 secretary 11111 MERCHANT 1 SALESMAN 0318 8700.00 serialno 78943 MERCHANT 1 SALESMAN 0318 8700.00 manager 70012 MERCHANT 1 SALESMAN 0318 8700.00 secretary 24888 FUNNIGUY 1 ADMINISTRATOR 0319 8500.00 serialno 07654 FUNNIGUY 1 ADMINISTRATOR 0319 8500.00 manager 40567 FUNNIGUY 1 ADMINISTRATOR 0319 8500.00 secretary 33333 BUGHAPPY 1 PROGRAMMER 0319 8500.00 serialno 45678 BUGHAPPY 1 PROGRAMMER 0319 8500.00 manager 40567 BUGHAPPY 1 PROGRAMMER 0319 8500.00 secretary 12345 BIGHEAD 1 SCIENTIST 2452 8000.00 serialno 34567 BIGHEAD 1 SCIENTIST 2452 8000.00 manager 31416 BIGHEAD 1 SCIENTIST 2452 8000.00 secretary 11111 SHRINKY 2 ADMINISTRATOR 0318 7500.00 serialno 77777 SHRINKY 2 ADMINISTRATOR 0318 7500.00 manager 70012 SHRINKY 2 ADMINISTRATOR 0318 7500.00 secretary 27845 WALTER 1 ENGINEER 0318 7400.00 serialno 74234 WALTER 1 ENGINEER 0318 7400.00 manager 70012 WALTER 1 ENGINEER 0318 7400.00 secretary 24888 FODDERMAN 1 SALESMAN 0319 7000.00 serialno 56789 FODDERMAN 1 SALESMAN 0319 7000.00 manager 40567 FODDERMAN 1 SALESMAN 0319 7000.00 secretary 12345 TONGHO 1 ENGINEER 0318 6800.00 serialno 73452 TONGHO 1 ENGINEER 0318 6800.00 manager 70012 TONGHO 1 ENGINEER 0318 6800.00 secretary 24888 SHORTSIGHT 2 SECRETARY 0021 5500.00 serialno 22222 SHORTSIGHT 2 SECRETARY 0021 5500.00 manager 87777 MESSIFUL 2 SECRETARY 0319 5000.50 serialno 55555 MESSIFUL 2 SECRETARY 0319 5000.50 manager 40567 MESSIFUL 2 SECRETARY 0319 5000.50 secretary 12345 HONEY 2 SECRETARY 0318 4900.00 serialno 27845 HONEY 2 SECRETARY 0318 4900.00 manager 70012 HONEY 2 SECRETARY 0318 4900.00 secretary 24888 GOOSEPEN 1 ADMINISTRATOR 0319 4700.00 serialno 98765 GOOSEPEN 1 ADMINISTRATOR 0319 4700.00 manager 07654 GOOSEPEN 1 ADMINISTRATOR 0319 4700.00 secretary 33333 CHERRY 2 SECRETARY 2452 4500.00 serialno 11111 CHERRY 2 SECRETARY 2452 4500.00 manager 31416 MONAPENNY 2 SECRETARY 0319 3800.00 serialno 33333 MONAPENNY 2 SECRETARY 0319 3800.00 manager 07654 KITTY 2 TYPIST 0319 3000.45 serialno 12345 KITTY 2 TYPIST 0319 3000.45 manager 40567 PLUMHEAD 2 TYPIST 0318 2800.00 serialno 24888 PLUMHEAD 2 TYPIST 0318 2800.00 manager 27845 STRONG 1 DIRECTOR 0021 23000.00 serialno 87777 STRONG 1 DIRECTOR 0021 23000.00 secretary 22222 BULLOZER 1 SALESMAN 0319 14800.00 serialno 76543 BULLOZER 1 SALESMAN 0319 14800.00 manager 40567 BULLOZER 1 SALESMAN 0319 14800.00 secretary 12345 WERTHER 1 DIRECTOR 0318 14500.00 serialno 70012 WERTHER 1 DIRECTOR 0318 14500.00 manager 87777 WERTHER 1 DIRECTOR 0318 14500.00 secretary 27845 QUINN 1 DIRECTOR 0319 14000.00 serialno 40567 QUINN 1 DIRECTOR 0319 14000.00 manager 87777 QUINN 1 DIRECTOR 0319 14000.00 secretary 55555 ORELLY 1 ENGINEER 2452 13400.00 serialno 31416 ORELLY 1 ENGINEER 2452 13400.00 manager 87777 ORELLY 1 ENGINEER 2452 13400.00 secretary 11111 BIGHORN 1 SCIENTIST 2452 11000.00 serialno 36666 BIGHORN 1 SCIENTIST 2452 11000.00 manager 31416 BIGHORN 1 SCIENTIST 2452 11000.00 secretary 11111 BROWNY 1 ENGINEER 0319 10500.00 serialno 00137 BROWNY 1 ENGINEER 0319 10500.00 manager 40567 BROWNY 1 ENGINEER 0319 10500.00 secretary 12345 WHEELFOR 1 SALESMAN 0318 10030.00 serialno 73111 WHEELFOR 1 SALESMAN 0318 10030.00 manager 70012 WHEELFOR 1 SALESMAN 0318 10030.00 secretary 24888 MARTIN 1 ENGINEER 0319 10000.00 serialno 00023 MARTIN 1 ENGINEER 0319 10000.00 manager 40567 MARTIN 1 ENGINEER 0319 10000.00 secretary 12345 DROP TABLE occurs; DROP TABLE employee; CREATE TABLE pets ( name VARCHAR(12) NOT NULL, dog INT NOT NULL DEFAULT 0, cat INT NOT NULL DEFAULT 0, rabbit INT NOT NULL DEFAULT 0, bird INT NOT NULL DEFAULT 0, fish INT NOT NULL DEFAULT 0) ENGINE=MYISAM; INSERT INTO pets(name,dog) VALUES('John',2); INSERT INTO pets(name,cat) VALUES('Bill',1); INSERT INTO pets(name,dog,cat) VALUES('Mary',1,1); INSERT INTO pets(name,rabbit) VALUES('Lisbeth',2); INSERT INTO pets(name,cat,bird) VALUES('Kevin',2,6); INSERT INTO pets(name,dog,fish) VALUES('Donald',1,3); SELECT * FROM pets; name dog cat rabbit bird fish John 2 0 0 0 0 Bill 0 1 0 0 0 Mary 1 1 0 0 0 Lisbeth 0 0 2 0 0 Kevin 0 2 0 6 0 Donald 1 0 0 0 3 CREATE TABLE xpet (name VARCHAR(12) NOT NULL, race CHAR(6) NOT NULL, number INT) ENGINE=CONNECT TABLE_TYPE=OCCUR TABNAME=pets OPTION_LIST='OccurCol=number,RankCol=race,Colist=dog;cat;rabbit;bird;fish,port=PORT'; SELECT * FROM xpet; name race number John dog 2 John cat 0 John rabbit 0 John bird 0 John fish 0 Bill dog 0 Bill cat 1 Bill rabbit 0 Bill bird 0 Bill fish 0 Mary dog 1 Mary cat 1 Mary rabbit 0 Mary bird 0 Mary fish 0 Lisbeth dog 0 Lisbeth cat 0 Lisbeth rabbit 2 Lisbeth bird 0 Lisbeth fish 0 Kevin dog 0 Kevin cat 2 Kevin rabbit 0 Kevin bird 6 Kevin fish 0 Donald dog 1 Donald cat 0 Donald rabbit 0 Donald bird 0 Donald fish 3 SELECT name FROM xpet; name John Bill Mary Lisbeth Kevin Donald SELECT name FROM xpet WHERE race = 'cat' AND number = 0; name John Lisbeth Donald SELECT name, SUM(number) pets FROM xpet GROUP BY name; name pets Bill 1 Donald 4 John 2 Kevin 8 Lisbeth 2 Mary 2 ALTER TABLE xpet MODIFY number INT NOT NULL; SELECT * FROM xpet; name race number John dog 2 Bill cat 1 Mary dog 1 Mary cat 1 Lisbeth rabbit 2 Kevin cat 2 Kevin bird 6 Donald dog 1 Donald fish 3 SELECT * FROM xpet WHERE number > 1; name race number John dog 2 Lisbeth rabbit 2 Kevin cat 2 Kevin bird 6 Donald fish 3 SELECT DISTINCT name FROM xpet WHERE number > 1; name John Lisbeth Kevin Donald SELECT name FROM xpet; name John Bill Mary Lisbeth Kevin Donald SELECT name, race FROM xpet; name race John Bill Mary Lisbeth Kevin Donald SELECT name, count(*) FROM xpet GROUP BY name, LEAST(number,1); name count(*) Bill 1 Donald 2 John 1 Kevin 2 Lisbeth 1 Mary 2 SELECT name, number, count(*) FROM xpet GROUP BY name, number; name number count(*) Bill 1 1 Donald 1 1 Donald 3 1 John 2 1 Kevin 2 1 Kevin 6 1 Lisbeth 2 1 Mary 1 2 DROP TABLE xpet; DROP TABLE pets;