/*
cr_in_zmk-lehrer2.sql
erzeugt und strukturiert eine Tabelle zmk-lehrer2 mit der u. a. Struktur.

Die Daten stammen aus der Lehrer-DIF-Exportdatei des Stundenplanprogramms Untis
und wurden in Excel als Externe Daten (Excel-Menü Daten) importiert
und in einer neuen Spalte zu je einer SQL-Zeile zusammengefügt.
Beispiel: In A1-E1 stehen die Daten ACK, Dr. van Ackern, R, L, des ersten Datensatzes, ZEICHEN(34) erzeugt ein Anführungszeichen.
Die Zellenformel
="("&ZEICHEN(34)&A1&ZEICHEN(34)&","&ZEICHEN(34)&B1&ZEICHEN(34)&","&ZEICHEN(34)&C1&ZEICHEN(34)&","&ZEICHEN(34)&D1&ZEICHEN(34)&","&ZEICHEN(34)&E1&ZEICHEN(34)&"),"
ergibt dann die Zeile
("ACK","Dr. van Ackern","R","L",""),
Diese Formel durch Ziehen auf alle Zeilen erweitern, die so erzeugten Zellen ALS WERTE in eine weitere Spalte kopieren,
alle Zellen dieser Spalte kopieren und in einem Texteditor einfügen.
Die SQL-Anweisungen dann ergänzen und als sql-Skript abspeichern.

Hinweis: In Excel führt das Speichern Unter... bei den Dateitypen txt und csv nicht zum gewünschten Ergebnis, 
deshalb sollten die Zellinhalte kopiert und in einen Editor eingefügt werden!

(c) November 2005  M. Ziemke, Köln
*/

CREATE TABLE IF NOT EXISTS `zmk-lehrer2` (
`lid` INT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`kuerzel` VARCHAR( 5 ) NOT NULL ,
`langname` VARCHAR( 25 )  DEFAULT '',
`gebdatum` DATE,
`fach1` int(3) DEFAULT 0,
`fach2` int(3) DEFAULT 0,
`fach3` int(3) DEFAULT 0,
`fach1s` CHAR(2) DEFAULT '',
`fach2s` CHAR(2)  DEFAULT '',
`fach3s` CHAR(2)  DEFAULT '',
PRIMARY KEY ( `lid` ) ,
UNIQUE INDEX ( `kuerzel` )
) TYPE = MYISAM COMMENT = 'ZMK: Lehrerdaten-Tabelle mit Fakultas' ;

INSERT IGNORE INTO `zmk-lehrer2` (kuerzel, langname, fach1s, fach2s, fach3s)
  VALUES
("ACK","Dr. van Ackern","R","L",""),
("AR","Armata","D","PA",""),
("BAAK","Baake","PA","D","IF"),
("BAR","Barry","D","R","PK"),
("BAUM","Baumann","GE","",""),
("BEER","Beer","M","PH",""),
("BF","Becker-Fulda","PS","F","MU"),
("BEN","Benedetti","ER","",""),
("BK","Becker","E","EK",""),
("BL","Bocklet","D","E",""),
("BM","Brockmann","PA","SW","LI"),
("BND","Bernards","SP","F","LI"),
("BOE","Böcker","SP","PA","L"),
("BOES","Bösebeck","SP","",""),
("BRS","Brüssing","D","PA","ER"),
("BUET","Bützler","KR","D",""),
("BSCH","Büsch","D","R",""),
("BUSE","Buse","E","BI",""),
("CRE","Cremer","E","L",""),
("CS","Carmesin","SP","EK",""),
("DIT","Ditscheid","E","KR",""),
("DOER","Dörmann","SP","EK",""),
("DON","Donius","GE","E","PK"),
("DOP","Dopke","PH","M",""),
("DRE","Dreesmann","BI","GE",""),
("ECK","Ecker","SP","PK","E"),
("ESM","Esmaeili Fathabadi","D","F","KR"),
("FD","Fulda","R","F","D"),
("FIRL","Firl","D","E",""),
("GAR","Gardenier","E","I",""),
("GAT","Gatermann","SP","GE",""),
("GER","Gerber","M","PH",""),
("GHS","Gerhardus","KU","GE",""),
("GIE","Dr. Giesel","L","ER",""),
("GLAE","Glaesemer","D","GE",""),
("HAL","Halbach","M","PL",""),
("HAN","Handler","RK","SW",""),
("HEGG","Heggemann","BI","SP",""),
("HENK","Henke-Imgrund","D","KR",""),
("HW","Holtwick","E","F",""),
("ISER","Isermann","D","PL",""),
("JUN","Jungbluth","TX","F",""),
("KAL","Dr. Kalcher","D","PL","LI"),
("KERB","Kerber","KU","SP",""),
("KF","Kolf","M","EK",""),
("KG","König","CH","M","PH"),
("KIR","Kirschey","PA","SP","M"),
("KNE","Knechtges","EK","SW",""),
("KOES","Köser","BI","CH",""),
("KOP","Koppe","E","EK",""),
("KRA","Kraatz","CH","C1",""),
("KRAM","Kramer","E","BI",""),
("KRU","Krumm","E","F","SP"),
("KUEP","Küpper","S","",""),
("KS","Kraus","M","PS",""),
("LA","Lathe","M","PH",""),
("LIE","Liedtke","GE","L",""),
("LNG","Liening","BI","CH",""),
("LOM","Lommerzheim","CH","ER",""),
("LUEP","Lüpke","S","F",""),
("LSTR","Lungstraß","D","PL",""),
("LZ","Lenz","MU","ER",""),
("MAU","Maubach","SP","M",""),
("MAZ","Mazalla","BI","PA",""),
("MLK","Mahlke","E","BI",""),
("MORS","Morschbach","M","SP",""),
("MS","Michels","SW","WW",""),
("MUN","Mundt","SP","EK",""),
("MZ","Manz","M","EK","SP"),
("NE","Neschat","E","PA",""),
("NEU","Neugebauer","KU","SW",""),
("NEUM","Dr. Neumann","CH","",""),
("NOCH","Noch","M","SW",""),
("OELL","Oellers","RK","",""),
("OERT","Oertel","BI","SP",""),
("ON","Onnen-Saelens","KU","",""),
("PFL","Pflieger","CH","BI",""),
("PIRI","Pirincci","TC","CH",""),
("PP","Piepenbrink","I","F",""),
("REIS","Dr. Reisinger","M","PL","IF"),
("RI","Rickert","M","IF",""),
("ROS","Rosenfelder","D","SW","LI"),
("SCHE","Schein","E","GE",""),
("SCHR","Schramm","E","MU","PA"),
("SEYM","Dr. Seym-Born","BI","CH",""),
("SMO","Smolorz","S","D",""),
("SNB","Schnurrbusch","E","EK",""),
("SNEI","Schneider","SP","",""),
("SNW","Schneider-Wellems","D","GE",""),
("SOED","Soeder","M","PH",""),
("STEI","Steins","E","SP",""),
("STIL","Stiller","M","SP",""),
("STK","Stark","D","GE",""),
("STRE","Streer","E","GE",""),
("STRI","Strick","M","PH","IF"),
("STW","Steinbach-Werner","D","SP",""),
("SYB","Syberberg","S","F",""),
("TAP","Tappen","I","F",""),
("TF","Dr. Tiefelstorf","WW","SW",""),
("TY","Tordy","D","PA",""),
("URB","Urbschat","BI","CH",""),
("VED","Vedder","F","ER","MU"),
("WAG","Dr. Wagner","PH","M",""),
("WARN","Warnat","SP","",""),
("WEG","Wegener","GE","PH",""),
("WEL","Wellmann","SP","",""),
("WEST","Westphal-Hamdoun","E","F","SP"),
("WET","Wetter","KU","",""),
("WF","Wolf","KU","",""),
("WIL","Willems","GE","EK",""),
("WLD","Wild","MU","D",""),
("WIM","Dr. Wimmert","L","SP",""),
("WIN1","Herr Wintersohl","KR","D",""),
("WIN2","Frau Wintersohl","BI","M",""),
("WRZ","Wirtz","E","GE",""),
("WUEL","Wüllner","M","EK","IF"),
("ZMK","Ziemke","M","KU","IF"),
("ANST","StRef.in Anschütz","F","R",""),
("BECK","StRef.in Becker","E","ER",""),
("BRAI","StRef.in Braidi","F","I",""),
("BUER","StRef. Bürger","PS","SW",""),
("BUHT","StRef. Buhtz","SP","E",""),
("KOST","StRef.in Kösters","D","I",""),
("MATS","StRef.in Matschinsky","EK","L",""),
("MUEL","StRef.in Müller","D","KR",""),
("MLBG","StRef. Mühlenberg","PL","D","SW"),
("REST","StRef.in Rest","KU","BI",""),
("SMLT","StRef.in Dr. Schmelter","EK","SW",""),
("TESK","StRef. Teschke","TC","M",""),
("ALBR","StRef Albrecht","M","PH",""),
("BUTR","StRef.in Büter","D","SP",""),
("GABL","StRef Gabelin","M","MU",""),
("PAUR","StRef Pauer","E","SP",""),
("OLET","StRef Oletz","M","TC",""),
("SIRN","StRef Schieren","E","SW","PK"),
("SNKE","StRef.in Schünke","PS","SW","D"),
("ULKR","StRef.in Ullrich-Kreisel","D","I",""),
("XX","Dummylehrer","","",""),
("XXX","Dummylehrer","","",""),
("TSV","Straschewski","","","") ;