1# 2# Testing indexing 3# 4CREATE TABLE t1 5( 6matricule INT(4) KEY NOT NULL field_format='Z', 7nom VARCHAR(16) NOT NULL, 8prenom VARCHAR(20) NOT NULL, 9sexe SMALLINT(1) NOT NULL COMMENT 'sexe 1:M 2:F', 10aanais INT(4) NOT NULL, 11mmnais INT(2) NOT NULL, 12ddentree DATE NOT NULL date_format='YYYYMM', 13ddnom DATE NOT NULL date_format='YYYYMM', 14brut INT(5) NOT NULL, 15net DOUBLE(8,2) NOT NULL, 16service INT(2) NOT NULL, 17sitmat CHAR(1) NOT NULL, 18formation CHAR(5) NOT NULL, 19INDEX NP(nom,prenom) 20) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='emp.txt' ENDING=2; 21SELECT * FROM t1 LIMIT 10; 22matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation 235745 ESCOURCHE BENEDICTE 2 1935 7 1962-12-01 1994-05-01 18345 14275.50 0 M TECHN 249692 VICENTE LAURENCE 2 1941 8 1967-10-01 1989-01-01 16212 13032.80 0 M ANGL 259146 NICOLAS ROGER 1 1941 6 1964-07-01 1995-02-01 34173 25098.65 0 M SANS 262985 TESSEREAU MARIE HELENE 2 1941 9 1967-01-01 1990-01-01 19323 14933.78 0 V SANS 273368 MOGADOR ALAIN 1 1941 1 1961-09-01 1993-11-01 43303 31420.55 0 C SANS 287394 CHAUSSEE ERIC DENIS 1 1944 9 1965-11-01 1983-12-01 32002 23583.86 0 M ANGL 294655 MAILLOT GEORGES 1 1945 5 1970-09-01 1986-12-01 24700 18541.64 0 C ANGL 302825 CAMILLE NADINE 2 1956 9 1994-01-01 1993-01-01 19494 15050.45 0 M SANS 311460 BRUYERES JEAN MARC 1 1958 8 1984-08-01 1988-05-01 20902 15980.07 0 M SANS 324974 LONES GERARD 1 1959 10 1979-01-01 1994-12-01 16081 12916.70 0 M SANS 33SELECT SUM(brut) from t1; 34SUM(brut) 3564319029 36# 37# Testing file mapping 38# 39ALTER TABLE t1 MAPPED=yes; 40SELECT * FROM t1 LIMIT 10; 41matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation 425745 ESCOURCHE BENEDICTE 2 1935 7 1962-12-01 1994-05-01 18345 14275.50 0 M TECHN 439692 VICENTE LAURENCE 2 1941 8 1967-10-01 1989-01-01 16212 13032.80 0 M ANGL 449146 NICOLAS ROGER 1 1941 6 1964-07-01 1995-02-01 34173 25098.65 0 M SANS 452985 TESSEREAU MARIE HELENE 2 1941 9 1967-01-01 1990-01-01 19323 14933.78 0 V SANS 463368 MOGADOR ALAIN 1 1941 1 1961-09-01 1993-11-01 43303 31420.55 0 C SANS 477394 CHAUSSEE ERIC DENIS 1 1944 9 1965-11-01 1983-12-01 32002 23583.86 0 M ANGL 484655 MAILLOT GEORGES 1 1945 5 1970-09-01 1986-12-01 24700 18541.64 0 C ANGL 492825 CAMILLE NADINE 2 1956 9 1994-01-01 1993-01-01 19494 15050.45 0 M SANS 501460 BRUYERES JEAN MARC 1 1958 8 1984-08-01 1988-05-01 20902 15980.07 0 M SANS 514974 LONES GERARD 1 1959 10 1979-01-01 1994-12-01 16081 12916.70 0 M SANS 52SELECT SUM(brut) FROM t1; 53SUM(brut) 5464319029 55# 56# Test the indexes (made when creating the table) 57# 58SELECT * FROM t1 WHERE matricule = '0091'; 59matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation 6091 THIVERNAL DIDIER JEAN 1 1951 10 1980-05-01 1991-10-01 14715 12024.71 1 M SANS 61SELECT * FROM t1 WHERE nom = 'FOCH'; 62matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation 631977 FOCH BERNADETTE 2 1958 3 1992-02-01 1991-02-01 8656 8145.03 1 . SANS 645707 FOCH DENIS 1 1977 7 1996-07-01 1995-07-01 7803 7679.36 15 C COMPT 652552 FOCH FRANCK 1 1962 12 1986-06-01 1990-11-01 12882 10745.81 13 M SANS 662634 FOCH JOCELYNE 2 1953 3 1996-01-01 1995-01-01 12499 10473.09 41 M INFOR 675765 FOCH ROBERT 1 1957 1 1981-03-01 1993-03-01 16081 12916.32 52 M ALLEM 684080 FOCH SERGE 1 1959 3 1981-03-01 1981-05-01 11131 9658.24 5 M SANS 69SELECT * FROM t1 WHERE nom = 'FOCH' and prenom = 'DENIS'; 70matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation 715707 FOCH DENIS 1 1977 7 1996-07-01 1995-07-01 7803 7679.36 15 C COMPT 72# 73# Testing UPDATE 74# 75UPDATE t1 SET aanais = aanais + 16; 76UPDATE t1 SET ddentree = adddate(ddentree, interval 16 year); 77UPDATE t1 SET ddnom = adddate(ddnom, interval 16 year); 78SELECT * FROM t1 WHERE nom = 'FOCH'; 79matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation 801977 FOCH BERNADETTE 2 1974 3 2008-02-01 2007-02-01 8656 8145.03 1 . SANS 815707 FOCH DENIS 1 1993 7 2012-07-01 2011-07-01 7803 7679.36 15 C COMPT 822552 FOCH FRANCK 1 1978 12 2002-06-01 2006-11-01 12882 10745.81 13 M SANS 832634 FOCH JOCELYNE 2 1969 3 2012-01-01 2011-01-01 12499 10473.09 41 M INFOR 845765 FOCH ROBERT 1 1973 1 1997-03-01 2009-03-01 16081 12916.32 52 M ALLEM 854080 FOCH SERGE 1 1975 3 1997-03-01 1997-05-01 11131 9658.24 5 M SANS 86# 87# Testing JOIN 88# 89create table t2 90( 91sexe INT(1) KEY, 92genre CHAR(8) NOT NULL 93) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='sexe.csv' SEP_CHAR=';' ENDING=2; 94SELECT * FROM t2; 95sexe genre 960 Inconnu 971 Masculin 982 Feminin 99SELECT nom, prenom, genre FROM t1 NATURAL JOIN t2 LIMIT 10; 100nom prenom genre 101ESCOURCHE BENEDICTE Feminin 102VICENTE LAURENCE Feminin 103NICOLAS ROGER Masculin 104TESSEREAU MARIE HELENE Feminin 105MOGADOR ALAIN Masculin 106CHAUSSEE ERIC DENIS Masculin 107MAILLOT GEORGES Masculin 108CAMILLE NADINE Feminin 109BRUYERES JEAN MARC Masculin 110LONES GERARD Masculin 111# 112# Another table 113# 114CREATE TABLE t3 ( 115sitmat CHAR(1) KEY, 116situation CHAR(12) NOT NULL 117) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='sitmat.csv' SEP_CHAR=';' ENDING=2; 118SELECT * FROM t3; 119sitmat situation 120. Inconnu 121C Celibataire 122D Divorce 123L Union libre 124M Marie 125S Separe 126V Veuf 127SELECT nom, prenom, genre, situation FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 WHERE nom = 'FOCH'; 128nom prenom genre situation 129FOCH BERNADETTE Feminin Inconnu 130FOCH DENIS Masculin Celibataire 131FOCH FRANCK Masculin Marie 132FOCH JOCELYNE Feminin Marie 133FOCH ROBERT Masculin Marie 134FOCH SERGE Masculin Marie 135# 136# Testing DELETE 137# 138DELETE FROM t1; 139DROP TABLE t1; 140DROP TABLE t2; 141DROP TABLE t3; 142