clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591678 fiddles created (45711 in the last week).

select version();
version()
8.0.25
 hidden batch(es)


CREATE TABLE `admins` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 hidden batch(es)


CREATE TABLE `places` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `position` point NOT NULL COMMENT 'Coordinates of the city.', `admin_id` bigint(20) DEFAULT NULL, `country_id` bigint(20) DEFAULT NULL, `population` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `admin_id` (`admin_id`), KEY `country_id` (`country_id`), SPATIAL KEY `position` (`position`), CONSTRAINT `places_admin_id_fk` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `places_countries_id_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Failed to open the referenced table 'countries'
 hidden batch(es)


CREATE TABLE `place_names` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `place_id` bigint(20) NOT NULL COMMENT 'ID of place in table places.', `name` char(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the place in the given language.', `language_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `place_names_place_id_language_uindex` (`place_id`,`language_id`), KEY `new_place_names_language_id_fk` (`language_id`), KEY `place_id` (`place_id`), KEY `place_name` (`name`), FULLTEXT KEY `place_name_fulltext` (`name`), CONSTRAINT `new_place_names_language_id_fk` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `new_place_names_places_id_fk` FOREIGN KEY (`place_id`) REFERENCES `places` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Failed to open the referenced table 'languages'
 hidden batch(es)


CREATE TABLE `admin_names` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `admin_id` bigint(20) NOT NULL COMMENT 'ID of admin in table admins.', `language_id` int(11) NOT NULL, `name` char(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the administration in the given language.', `abbr` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Abbreviation of the administration, if available.', PRIMARY KEY (`id`), UNIQUE KEY `admin_names_admin_id_language_uindex` (`admin_id`,`language_id`), KEY `admin_id` (`admin_id`), KEY `language_id` (`language_id`), FULLTEXT KEY `admin_name_fulltext` (`name`), FULLTEXT KEY `admin_abbr_fulltext` (`abbr`), CONSTRAINT `admin_names_admins_id_fk` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `admin_names_languages_id_fk` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Failed to open the referenced table 'languages'
 hidden batch(es)


-- Initial contents INSERT INTO geodb.languages (id, code_3) VALUES (2, 'DEU'), (1, 'ENG'), (3, 'FRA'), (6, 'ITA'), (5, 'POR'), (4, 'SPA');
INSERT command denied to user 'u_1901826740'@'localhost' for table 'languages'
 hidden batch(es)


-- Example data (for some of the records that should appear in the query as written above) INSERT INTO geodb.admins (id) VALUES (35), (133), (9980), (61), (30113), (51), (128), (129), (130), (125), (120), (121), (123), (126), (132), (1476), (1280), (1032), (5740), (1504), (40), (41), (39), (325), (10), (5576), (1924), (24), (59), (2201), (696959);
INSERT command denied to user 'u_1901826740'@'localhost' for table 'admins'
 hidden batch(es)


INSERT INTO geodb.countries (id) VALUES (4), (132), (342), (59), (1209), (856), (16), (10), (82), (24), (136);
INSERT command denied to user 'u_1901826740'@'localhost' for table 'countries'
 hidden batch(es)


INSERT INTO geodb.country_names (id, country_id, language_id, name) VALUES (8, 4, 1, 'Germany'), (242, 132, 1, 'Austria'), (548, 342, 1, 'Iran'), (134, 59, 1, 'Netherlands'), (1094, 1209, 1, 'Hungary'), (926, 856, 1, 'Romania'), (44, 16, 1, 'Italy'), (20, 10, 1, 'United Kingdom'), (152, 82, 1, 'India'), (74, 24, 1, 'France'), (254, 136, 1, 'Sweden');
INSERT command denied to user 'u_1901826740'@'localhost' for table 'country_names'
 hidden batch(es)


INSERT INTO geodb.places (id, position, admin_id, country_id, population) VALUES (88, 0xE6100000010100000013150FC771DC1D4070DE6F1CC7C14940, 35, 4, 587010), (1966, 0xE610000001010000000000000000802340D4E6535555B54740, 133, 132, 48067), (4856, 0xE610000001010000001374A9814EDB1B4014AE47E17AD44940, 35, 4, 74736), (10073, 0xE610000001010000009A999999998948407FA1F18B25BF4040, 9980, 342, 121608), (10321, 0xE610000001010000005C8FC2F5285C1540CDCCCCCCCCFC4940, 61, 59, 10000), (30643, 0xE61000000101000000A9CDA7AAAABA32406B226BC116DC4740, 30113, 1209, 11883), (39373, 0xE610000001010000005C8FC2F5285C1B40CEAACFD5568C4940, 35, 4, 64335), (39853, 0xE61000000101000000858CB7FD41062140DC5F7598BA2C4840, 51, 4, 6006), (39977, 0xE610000001010000003D4CA22EB7FF20405C8FC2F5283C4840, 51, 4, 8061), (40146, 0xE61000000101000000CDCCCCCCCC4C2840A1B3202222224840, 128, 4, 14650), (46153, 0xE610000001010000000AD7A3703D6A2740F6C9C1D4E5864940, 129, 4, 5426), (69341, 0xE610000001010000001E681C2222222B4037C0767777774940, 130, 4, 1093), (69544, 0xE610000001010000002663ECEC7ADB1F40BABCC706838B4840, 125, 4, 905), (69881, 0xE61000000101000000E15D457EB1E42A40E9949B36D0614940, 130, 4, 1551), (74577, 0xE61000000101000000DC5F7598BAFC2540D3B7923EE9234940, 128, 4, 3591), (75606, 0xE6100000010100000000000000000022408BD6172A3B044A40, 35, 4, 7720), (79421, 0xE61000000101000000DA1B7C6132F523409E5E29CB104F4B40, 120, 4, 733), (79443, 0xE610000001010000003E7958A835ED26406B2BF697DDEB4A40, 121, 4, 3090), (80042, 0xE610000001010000004267414444C42240048D434444E44840, 128, 4, 1755), (80439, 0xE61000000101000000B801B6BBBB3B2640CDCCCCCCCCCC4840, 128, 4, 2098), (84333, 0xE61000000101000000759A747777772240CDCCCCCCCC6C4A40, 123, 4, 8972), (89196, 0xE61000000101000000EBF21D64A88C21403DAB3CE9931E4840, 51, 4, 1128), (89531, 0xE610000001010000006666666666662340D159101111B14840, 51, 4, 2485), (89940, 0xE610000001010000009A99999999992240A1B3202222224B40, 120, 4, 624), (90042, 0xE610000001010000005BB4BE50D9E11E40E0ECB8DCFEF84840, 125, 4, 696), (94578, 0xE610000001010000000042CB8A46021F40AEE846C037CF4840, 125, 4, 147), (94606, 0xE61000000101000000A3369FAAAA2A1B4080D0B2A291304940, 125, 4, 190), (94615, 0xE61000000101000000612EF8204305204048E17A14AE1F4940, 125, 4, 423), (94641, 0xE6100000010100000066A831F1AC281F40E3A9D5C4B3224940, 125, 4, 197), (94836, 0xE610000001010000004782E059D1081F4005BC045BB00D4940, 125, 4, 382), (95134, 0xE61000000101000000A3369FAAAAAA1D403333333333D34A40, 123, 4, 4442), (95167, 0xE610000001010000007AF7DE174B7E1F401DF78F806FF64840, 125, 4, 718), (95544, 0xE610000001010000004782E059D1881F40C9E0EECDAB294940, 125, 4, 465), (99494, 0xE610000001010000000A196FFB830C2040D8D2315476404940, 126, 4, 8434), (99870, 0xE61000000101000000517E8025BF581D403B4DBABBBB7B4A40, 123, 4, 5287), (103271, 0xE6100000010100000070C1A0EC30552940F49A00BE79654740, 132, 132, 63), (103785, 0xE610000001010000001E681C2222E22340F36B3FA70D3C4840, 51, 4, 8686), (103867, 0xE610000001010000001E681C2222222540CDCCCCCCCC0C4A40, 123, 4, 705), (103952, 0xE61000000101000000B801B6BBBBBB2440D4E6535555154B40, 120, 4, 258), (104057, 0xE61000000101000000997CCA6903FD25407B14AE47E16A4940, 129, 4, 579), (108810, 0xE610000001010000004723469FF4892040B2D424BF58E24840, 125, 4, 999), (109447, 0xE610000001010000005C8FC2F5285C1E40525984CA0E134940, 125, 4, 520), (110365, 0xE610000001010000003333333333B32240406859D148384B40, 120, 4, 556), (123954, 0xE61000000101000000FB85C62F967C30401CC8CE6903BD4740, 1476, 132, 1291), (128696, 0xE61000000101000000941F60C92F362A406E80EDEEEE9E4740, 1280, 132, 1614), (128724, 0xE61000000101000000BDBDBA166C412B40A2E2E1388E234840, 1032, 132, 1039), (129077, 0xE6100000010100000076F90E3254663A40035E822DD8FA4740, 5740, 856, 24309), (133664, 0xE6100000010100000014F0126CC1F62D40BA4D4602BE294840, 1504, 132, 584), (134228, 0xE6100000010100000085CE828888082A40A1B3202222024840, 1280, 132, 1547), (135334, 0xE610000001010000000042CB8A46C223408E349A5713BF4740, 133, 132, 1029), (233062, 0xE61000000101000000C16ED8B628DB2240DD9733DB15D04840, 51, 4, 62), (238213, 0xE6100000010100000070036C7777771F409D26DDDDDDBD4640, 40, 16, 3430), (243102, 0xE6100000010100000085CE828888881D406E80EDEEEE8E4640, 40, 16, 2892), (243184, 0xE61000000101000000B3E616A62EB72B4048E17A14AECF4640, 41, 16, 5754), (243488, 0xE61000000101000000A9CDA7AAAA2A20403333333333B34640, 40, 16, 520), (243959, 0xE610000001010000001E681C22222221409D26DDDDDDDD4640, 40, 16, 2600), (248807, 0xE610000001010000006666666666E621403333333333934640, 39, 16, 4671), (253055, 0xE610000001010000001E681C2222A22240CDCCCCCCCC0C4740, 39, 16, 319), (281738, 0xE610000001010000008B6CE7FBA9D1254052B81E85EB094740, 325, 16, 1562), (283011, 0xE61000000101000000FF8396158D8403C022122F96FC5A4940, 10, 10, 20135), (403030, 0xE61000000101000000BA7BF042894C3140122DB1E68BCC4740, 5576, 1209, 675), (439462, 0xE61000000101000000F6285C8FC2C55240D7A3703D0AB74040, 1924, 82, 199429), (696911, 0xE6100000010100000032582F8EE338184065D80A951D864840, 24, 24, 169), (697194, 0xE610000001010000005A1B8636D069FD3F2DE9D2063A7D4640, 24, 24, 1135), (707266, 0xE61000000101000000BF8919C850D9F1BF7D723075B99D4740, 24, 24, 1182), (884374, 0xE61000000101000000AE47E17A14AE124048E17A14AEE74940, 59, 59, 118654), (1160830, 0xE61000000101000000B6A0D1FF6D012C40E28FA2CE5C7E4C40, 2201, 136, 129), (1306022, 0xE61000000101000000AE2A124B7EF11D403333333333434840, 24, 24, 2633), (1311153, 0xE61000000101000000237C436F5E4DF13FA2E2E1388E1B4740, 24, 24, 1638), (1315478, 0xE610000001010000008103D9396DA0E93F65D80A951D364840, 696959, 24, 386), (1320476, 0xE610000001010000000042CB8A46020E4058A3E4F607B14640, 24, 24, 645), (1321355, 0xE610000001010000005B552496FCA216404782E059D1284740, 24, 24, 1854), (1325957, 0xE6100000010100000052B81E85EBD10A40EC51B81E855B4740, 24, 24, 1448), (1330302, 0xE61000000101000000993AFFDEBC5A1B40EAC35C4D3CCB4740, 24, 24, 774), (1331044, 0xE610000001010000006376C0BC9A78064070DE6F1CC7C94640, 24, 24, 1280), (1331360, 0xE6100000010100000089E2A9D5C4B3DABF81FF73B9FDF14740, 24, 24, 352), (1335169, 0xE61000000101000000D7E53BC850D90B400AD7A3703DF24640, 24, 24, 701), (1335243, 0xE61000000101000000D471FF08F8660D4085EB51B81EFD4840, 24, 24, 379), (1335253, 0xE61000000101000000652CC8063AED0C406B226BC116B44640, 24, 24, 167), (1345066, 0xE61000000101000000610284FD4186FE3F59D2A50D748A4640, 24, 24, 638);
INSERT command denied to user 'u_1901826740'@'localhost' for table 'places'
 hidden batch(es)