DROP TABLE IF EXISTS `kunde`;
CREATE TABLE `kunde` (
  `kunde_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `ort_postleitzahl` varchar(5) NOT NULL,
  `kontostand_giro` decimal(10,2) NOT NULL,
  `kredit` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`kunde_id`),
  KEY `fk_kunde_ort` (`ort_postleitzahl`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `kunde` VALUES (1,'John','79111',182.00,-430320.22),(2,'Herbert','79312',10291.32,-10000.00),(3,'Sabina','79312',-253.21,-3205.32),(4,'Mary','79111',-832.01,NULL),(5,'Heinrich','79111',15302.85,0.00),(6,'Usal','80995',23012.21,NULL),(7,'Johannes','80995',159.31,0.00),(8,'Carla','79312',503.06,-15302.68),(9,'Ludowika','79111',25201.07,-82213.99),(10,'Niemand','99999',-5021.30,-3024.21);

DROP TABLE IF EXISTS `ort`;
CREATE TABLE `ort` (
  `postleitzahl` varchar(5) NOT NULL,
  `name` varchar(255) NOT NULL,
  `einwohnerzahl` int(11) DEFAULT NULL,
  `anzahl_telefonleitungen` int(11) DEFAULT NULL,
  PRIMARY KEY (`postleitzahl`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `orte` VALUES ('20095','Hamburg',2000000,1004),('79111','Freiburg',280000,195),('79312','Emmendingen',40000,12),('80995','München',1000000,385);