1CREATE TABLE employee ( 2serialno CHAR(5) NOT NULL, 3name VARCHAR(12) NOT NULL FLAG=6, 4sex TINYINT(1) NOT NULL, 5title VARCHAR(15) NOT NULL FLAG=20, 6manager CHAR(5) DEFAULT NULL, 7department CHAR(4) NOT NULL FLAG=41, 8secretary CHAR(5) DEFAULT NULL FLAG=46, 9salary DOUBLE(8,2) NOT NULL FLAG=52 10) ENGINE=connect TABLE_TYPE=fix FILE_NAME='employee.dat' ENDING=1; 11SELECT * FROM employee; 12serialno name sex title manager department secretary salary 1374200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00 1402345 SMITH 1 ENGINEER 31416 2452 11111 9000.00 1578943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00 1607654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00 1745678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00 1834567 BIGHEAD 1 SCIENTIST 31416 2452 11111 8000.00 1977777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00 2074234 WALTER 1 ENGINEER 70012 0318 24888 7400.00 2156789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00 2273452 TONGHO 1 ENGINEER 70012 0318 24888 6800.00 2322222 SHORTSIGHT 2 SECRETARY 87777 0021 NULL 5500.00 2455555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50 2527845 HONEY 2 SECRETARY 70012 0318 24888 4900.00 2698765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00 2711111 CHERRY 2 SECRETARY 31416 2452 NULL 4500.00 2833333 MONAPENNY 2 SECRETARY 07654 0319 NULL 3800.00 2912345 KITTY 2 TYPIST 40567 0319 NULL 3000.45 3024888 PLUMHEAD 2 TYPIST 27845 0318 NULL 2800.00 3187777 STRONG 1 DIRECTOR NULL 0021 22222 23000.00 3276543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00 3370012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00 3440567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00 3531416 ORELLY 1 ENGINEER 87777 2452 11111 13400.00 3636666 BIGHORN 1 SCIENTIST 31416 2452 11111 11000.00 3700137 BROWNY 1 ENGINEER 40567 0319 12345 10500.00 3873111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00 3900023 MARTIN 1 ENGINEER 40567 0319 12345 10000.00 40CREATE 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';; 41SELECT * FROM occurs; 42name sex title department salary id_of id 43BANCROFT 2 SALESMAN 0318 9600.00 serialno 74200 44BANCROFT 2 SALESMAN 0318 9600.00 manager 70012 45BANCROFT 2 SALESMAN 0318 9600.00 secretary 24888 46SMITH 1 ENGINEER 2452 9000.00 serialno 02345 47SMITH 1 ENGINEER 2452 9000.00 manager 31416 48SMITH 1 ENGINEER 2452 9000.00 secretary 11111 49MERCHANT 1 SALESMAN 0318 8700.00 serialno 78943 50MERCHANT 1 SALESMAN 0318 8700.00 manager 70012 51MERCHANT 1 SALESMAN 0318 8700.00 secretary 24888 52FUNNIGUY 1 ADMINISTRATOR 0319 8500.00 serialno 07654 53FUNNIGUY 1 ADMINISTRATOR 0319 8500.00 manager 40567 54FUNNIGUY 1 ADMINISTRATOR 0319 8500.00 secretary 33333 55BUGHAPPY 1 PROGRAMMER 0319 8500.00 serialno 45678 56BUGHAPPY 1 PROGRAMMER 0319 8500.00 manager 40567 57BUGHAPPY 1 PROGRAMMER 0319 8500.00 secretary 12345 58BIGHEAD 1 SCIENTIST 2452 8000.00 serialno 34567 59BIGHEAD 1 SCIENTIST 2452 8000.00 manager 31416 60BIGHEAD 1 SCIENTIST 2452 8000.00 secretary 11111 61SHRINKY 2 ADMINISTRATOR 0318 7500.00 serialno 77777 62SHRINKY 2 ADMINISTRATOR 0318 7500.00 manager 70012 63SHRINKY 2 ADMINISTRATOR 0318 7500.00 secretary 27845 64WALTER 1 ENGINEER 0318 7400.00 serialno 74234 65WALTER 1 ENGINEER 0318 7400.00 manager 70012 66WALTER 1 ENGINEER 0318 7400.00 secretary 24888 67FODDERMAN 1 SALESMAN 0319 7000.00 serialno 56789 68FODDERMAN 1 SALESMAN 0319 7000.00 manager 40567 69FODDERMAN 1 SALESMAN 0319 7000.00 secretary 12345 70TONGHO 1 ENGINEER 0318 6800.00 serialno 73452 71TONGHO 1 ENGINEER 0318 6800.00 manager 70012 72TONGHO 1 ENGINEER 0318 6800.00 secretary 24888 73SHORTSIGHT 2 SECRETARY 0021 5500.00 serialno 22222 74SHORTSIGHT 2 SECRETARY 0021 5500.00 manager 87777 75MESSIFUL 2 SECRETARY 0319 5000.50 serialno 55555 76MESSIFUL 2 SECRETARY 0319 5000.50 manager 40567 77MESSIFUL 2 SECRETARY 0319 5000.50 secretary 12345 78HONEY 2 SECRETARY 0318 4900.00 serialno 27845 79HONEY 2 SECRETARY 0318 4900.00 manager 70012 80HONEY 2 SECRETARY 0318 4900.00 secretary 24888 81GOOSEPEN 1 ADMINISTRATOR 0319 4700.00 serialno 98765 82GOOSEPEN 1 ADMINISTRATOR 0319 4700.00 manager 07654 83GOOSEPEN 1 ADMINISTRATOR 0319 4700.00 secretary 33333 84CHERRY 2 SECRETARY 2452 4500.00 serialno 11111 85CHERRY 2 SECRETARY 2452 4500.00 manager 31416 86MONAPENNY 2 SECRETARY 0319 3800.00 serialno 33333 87MONAPENNY 2 SECRETARY 0319 3800.00 manager 07654 88KITTY 2 TYPIST 0319 3000.45 serialno 12345 89KITTY 2 TYPIST 0319 3000.45 manager 40567 90PLUMHEAD 2 TYPIST 0318 2800.00 serialno 24888 91PLUMHEAD 2 TYPIST 0318 2800.00 manager 27845 92STRONG 1 DIRECTOR 0021 23000.00 serialno 87777 93STRONG 1 DIRECTOR 0021 23000.00 secretary 22222 94BULLOZER 1 SALESMAN 0319 14800.00 serialno 76543 95BULLOZER 1 SALESMAN 0319 14800.00 manager 40567 96BULLOZER 1 SALESMAN 0319 14800.00 secretary 12345 97WERTHER 1 DIRECTOR 0318 14500.00 serialno 70012 98WERTHER 1 DIRECTOR 0318 14500.00 manager 87777 99WERTHER 1 DIRECTOR 0318 14500.00 secretary 27845 100QUINN 1 DIRECTOR 0319 14000.00 serialno 40567 101QUINN 1 DIRECTOR 0319 14000.00 manager 87777 102QUINN 1 DIRECTOR 0319 14000.00 secretary 55555 103ORELLY 1 ENGINEER 2452 13400.00 serialno 31416 104ORELLY 1 ENGINEER 2452 13400.00 manager 87777 105ORELLY 1 ENGINEER 2452 13400.00 secretary 11111 106BIGHORN 1 SCIENTIST 2452 11000.00 serialno 36666 107BIGHORN 1 SCIENTIST 2452 11000.00 manager 31416 108BIGHORN 1 SCIENTIST 2452 11000.00 secretary 11111 109BROWNY 1 ENGINEER 0319 10500.00 serialno 00137 110BROWNY 1 ENGINEER 0319 10500.00 manager 40567 111BROWNY 1 ENGINEER 0319 10500.00 secretary 12345 112WHEELFOR 1 SALESMAN 0318 10030.00 serialno 73111 113WHEELFOR 1 SALESMAN 0318 10030.00 manager 70012 114WHEELFOR 1 SALESMAN 0318 10030.00 secretary 24888 115MARTIN 1 ENGINEER 0319 10000.00 serialno 00023 116MARTIN 1 ENGINEER 0319 10000.00 manager 40567 117MARTIN 1 ENGINEER 0319 10000.00 secretary 12345 118DROP TABLE occurs; 119DROP TABLE employee; 120CREATE TABLE pets ( 121name VARCHAR(12) NOT NULL, 122dog INT NOT NULL DEFAULT 0, 123cat INT NOT NULL DEFAULT 0, 124rabbit INT NOT NULL DEFAULT 0, 125bird INT NOT NULL DEFAULT 0, 126fish INT NOT NULL DEFAULT 0) ENGINE=MYISAM; 127INSERT INTO pets(name,dog) VALUES('John',2); 128INSERT INTO pets(name,cat) VALUES('Bill',1); 129INSERT INTO pets(name,dog,cat) VALUES('Mary',1,1); 130INSERT INTO pets(name,rabbit) VALUES('Lisbeth',2); 131INSERT INTO pets(name,cat,bird) VALUES('Kevin',2,6); 132INSERT INTO pets(name,dog,fish) VALUES('Donald',1,3); 133SELECT * FROM pets; 134name dog cat rabbit bird fish 135John 2 0 0 0 0 136Bill 0 1 0 0 0 137Mary 1 1 0 0 0 138Lisbeth 0 0 2 0 0 139Kevin 0 2 0 6 0 140Donald 1 0 0 0 3 141CREATE 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'; 142SELECT * FROM xpet; 143name race number 144John dog 2 145John cat 0 146John rabbit 0 147John bird 0 148John fish 0 149Bill dog 0 150Bill cat 1 151Bill rabbit 0 152Bill bird 0 153Bill fish 0 154Mary dog 1 155Mary cat 1 156Mary rabbit 0 157Mary bird 0 158Mary fish 0 159Lisbeth dog 0 160Lisbeth cat 0 161Lisbeth rabbit 2 162Lisbeth bird 0 163Lisbeth fish 0 164Kevin dog 0 165Kevin cat 2 166Kevin rabbit 0 167Kevin bird 6 168Kevin fish 0 169Donald dog 1 170Donald cat 0 171Donald rabbit 0 172Donald bird 0 173Donald fish 3 174SELECT name FROM xpet; 175name 176John 177Bill 178Mary 179Lisbeth 180Kevin 181Donald 182SELECT name FROM xpet WHERE race = 'cat' AND number = 0; 183name 184John 185Lisbeth 186Donald 187SELECT name, SUM(number) pets FROM xpet GROUP BY name; 188name pets 189Bill 1 190Donald 4 191John 2 192Kevin 8 193Lisbeth 2 194Mary 2 195ALTER TABLE xpet MODIFY number INT NOT NULL; 196SELECT * FROM xpet; 197name race number 198John dog 2 199Bill cat 1 200Mary dog 1 201Mary cat 1 202Lisbeth rabbit 2 203Kevin cat 2 204Kevin bird 6 205Donald dog 1 206Donald fish 3 207SELECT * FROM xpet WHERE number > 1; 208name race number 209John dog 2 210Lisbeth rabbit 2 211Kevin cat 2 212Kevin bird 6 213Donald fish 3 214SELECT DISTINCT name FROM xpet WHERE number > 1; 215name 216John 217Lisbeth 218Kevin 219Donald 220SELECT name FROM xpet; 221name 222John 223Bill 224Mary 225Lisbeth 226Kevin 227Donald 228SELECT name, race FROM xpet; 229name race 230John 231Bill 232Mary 233Lisbeth 234Kevin 235Donald 236SELECT name, count(*) FROM xpet GROUP BY name, LEAST(number,1); 237name count(*) 238Bill 1 239Donald 2 240John 1 241Kevin 2 242Lisbeth 1 243Mary 2 244SELECT name, number, count(*) FROM xpet GROUP BY name, number; 245name number count(*) 246Bill 1 1 247Donald 1 1 248Donald 3 1 249John 2 1 250Kevin 2 1 251Kevin 6 1 252Lisbeth 2 1 253Mary 1 2 254DROP TABLE xpet; 255DROP TABLE pets; 256