CREATE DATABASE allokep
DEFAULT CHARACTER SET utf8
COLLATE utf8_hungarian_ci;

USE allokep;

CREATE TABLE megye (
  kod INT(2) PRIMARY KEY,
  nev VARCHAR(30),
  letszam INT(7)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_hungarian_ci;

CREATE TABLE allapot (
  kod INT(1) NOT NULL PRIMARY KEY,
  nev VARCHAR(30)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_hungarian_ci;

CREATE TABLE aerob (
  azon INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  mkod INT(2) NOT NULL,
  nem TINYINT,
  allkod INT(1),
  letszam INT(7),
  CONSTRAINT aerob_megye FOREIGN KEY(mkod) REFERENCES megye(kod),
  CONSTRAINT aerob_allapot FOREIGN KEY(allkod) REFERENCES allapot(kod)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_hungarian_ci;

INSERT INTO megye VALUES
(1,"Győr-Moson-Sopron",56762),
(2,"Komárom-Esztergom",34932),
(3,"Vas",28640),
(4,"Veszprém",39670),
(5,"Zala",30155),
(6,"Somogy",35028),
(7,"Baranya",41661),
(8,"Tolna",25582),
(9,"Fejér",48895),
(10,"Budapest",220520),
(11,"Pest",133556),
(12,"Bács-Kiskun",60865),
(13,"Nógrád",20692),
(14,"Heves",37746),
(15,"Jász-Nagykun-Szolnok",46511),
(16,"Csongrád-Csanád",48522),
(17,"Békés",39919),
(18,"Hajdú-Bihar",67853),
(19,"Szabolcs-Szatmár-Bereg",73108),
(20,"Borsod-Abaúj-Zemplén",85505);

INSERT INTO allapot VALUES
(1,"egészséges"),
(2,"fejlesztést igényel"),
(3,"fokozott fejlesztést igényel");

INSERT INTO aerob (azon, mkod, nem, allkod, letszam) VALUES
(1,1,0,1,8701),
(2,1,0,2,2881),
(3,1,0,3,2684),
(4,1,1,1,10518),
(5,1,1,2,2100),
(6,1,1,3,3186),
(7,2,0,1,5109),
(8,2,0,2,2081),
(9,2,0,3,2156),
(10,2,1,1,6073),
(11,2,1,2,1451),
(12,2,1,3,2305),
(13,3,0,1,4246),
(14,3,0,2,1573),
(15,3,0,3,1433),
(16,3,1,1,5084),
(17,3,1,2,1166),
(18,3,1,3,1577),
(19,4,0,1,5273),
(20,4,0,2,2458),
(21,4,0,3,2890),
(22,4,1,1,6513),
(23,4,1,2,1731),
(24,4,1,3,2861),
(25,5,0,1,4318),
(26,5,0,2,1820),
(27,5,0,3,1734),
(28,5,1,1,5253),
(29,5,1,2,1129),
(30,5,1,3,2028),
(31,6,0,1,4640),
(32,6,0,2,2220),
(33,6,0,3,2655),
(34,6,1,1,6037),
(35,6,1,2,1507),
(36,6,1,3,2502),
(37,7,0,1,5506),
(38,7,0,2,2407),
(39,7,0,3,2592),
(40,7,1,1,6880),
(41,7,1,2,1731),
(42,7,1,3,2808),
(43,8,0,1,3542),
(44,8,0,2,1557),
(45,8,0,3,1620),
(46,8,1,1,4438),
(47,8,1,2,1040),
(48,8,1,3,1799),
(49,9,0,1,6284),
(50,9,0,2,2664),
(51,9,0,3,3119),
(52,9,1,1,7460),
(53,9,1,2,1695),
(54,9,1,3,3751),
(55,10,0,1,28042),
(56,10,0,2,10237),
(57,10,0,3,12498),
(58,10,1,1,34544),
(59,10,1,2,7264),
(60,10,1,3,13511),
(61,11,0,1,17714),
(62,11,0,2,6958),
(63,11,0,3,6454),
(64,11,1,1,20552),
(65,11,1,2,4648),
(66,11,1,3,7350),
(67,12,0,1,8119),
(68,12,0,2,3623),
(69,12,0,3,4158),
(70,12,1,1,9499),
(71,12,1,2,2376),
(72,12,1,3,4510),
(73,13,0,1,2253),
(74,13,0,2,1429),
(75,13,0,3,1654),
(76,13,1,1,3000),
(77,13,1,2,879),
(78,13,1,3,1647),
(79,14,0,1,4522),
(80,14,0,2,2266),
(81,14,0,3,2905),
(82,14,1,1,5746),
(83,14,1,2,1563),
(84,14,1,3,3280),
(85,15,0,1,5642),
(86,15,0,2,2952),
(87,15,0,3,3441),
(88,15,1,1,7148),
(89,15,1,2,2004),
(90,15,1,3,3599),
(91,16,0,1,6596),
(92,16,0,2,2847),
(93,16,0,3,2973),
(94,16,1,1,8508),
(95,16,1,2,1961),
(96,16,1,3,3402),
(97,17,0,1,5099),
(98,17,0,2,2623),
(99,17,0,3,2744),
(100,17,1,1,6501),
(101,17,1,2,1695),
(102,17,1,3,3020),
(103,18,0,1,9160),
(104,18,0,2,4484),
(105,18,0,3,4739),
(106,18,1,1,11295),
(107,18,1,2,2990),
(108,18,1,3,4971),
(109,19,0,1,8470),
(110,19,0,2,5206),
(111,19,0,3,5446),
(112,19,1,1,11492),
(113,19,1,2,3203),
(114,19,1,3,5303),
(115,20,0,1,9148),
(116,20,0,2,5459),
(117,20,0,3,6734),
(118,20,1,1,12669),
(119,20,1,2,3478),
(120,20,1,3,6722);
