inserter.sql

-- select concat_ws(',',
--                  id,
--                  concat('''', name, ''''),
--                  concat('''', countrycode, ''''),
--                  concat('''', district, ''''),
--                  population)
-- from city;

create table city
(
    id          int         NULL,
    name        varchar(19) NULL,
    countrycode varchar(3)  NULL,
    district    varchar(19) NULL,
    population  int         NULL
);

-- select concat_ws(',',
--                  concat('''', code, ''''),
--                  concat('''', name, ''''),
--                  concat('''', continent, ''''),
--                  concat('''', region, ''''),
--                  concat('''', surfacearea, ''''),
--                  concat('''', indepyear, ''''),
--                  population,
--                  concat('''', lifeexpectancy, ''''),
--                  gnp)
-- from country;

create table country
(
    code           varchar(3)     NULL,
    name           varchar(44)    NULL,
    continent      varchar(13)    NULL,
    region         varchar(25)    NULL,
    surfacearea    decimal(10, 0) NULL,
    indepyear      varchar(4)     NULL,
    population     int            NULL,
    lifeexpectancy varchar(4)     NULL,
    gnp            decimal(10, 0) NULL
);

insert into city(id, name, countrycode, district, population)
values (4, 'mazar-e-sharif', 'afg', 'balkh', 127800),
       (11, 'groningen', 'nld', 'groningen', 172701),
       (80, 'merlo', 'arg', 'buenos aires', 463846),
       (89, 'san isidro', 'arg', 'buenos aires', 306341),
       (91, 'malvinas argentinas', 'arg', 'buenos aires', 290335),
       (124, 'san rafael', 'arg', 'mendoza', 94651),
       (128, 'vanadzor', 'arm', 'lori', 172700),
       (141, 'geelong', 'aus', 'victoria', 125382),
       (142, 'townsville', 'aus', 'queensland', 109914),
       (143, 'cairns', 'aus', 'queensland', 92273),
       (146, 'sumqayit', 'aze', 'sumqayit', 283000),
       (150, 'dhaka', 'bgd', 'dhaka', 3612850),
       (151, 'chittagong', 'bgd', 'chittagong', 1392860),
       (162, 'dinajpur', 'bgd', 'rajshahi', 127815),
       (166, 'tangail', 'bgd', 'dhaka', 106004),
       (168, 'pabna', 'bgd', 'rajshahi', 103277),
       (304, 'petrolina', 'bra', 'pernambuco', 210540),
       (321, 'rio grande', 'bra', 'rio grande do sul', 182222),
       (379, 'palmas', 'bra', 'tocantins', 121919),
       (431, 'angra dos reis', 'bra', 'rio de janeiro', 96864),
       (473, 'stoke-on-trent', 'gbr', 'england', 252000),
       (478, 'aberdeen', 'gbr', 'scotland', 213070),
       (479, 'northampton', 'gbr', 'england', 196000),
       (484, 'luton', 'gbr', 'england', 183000),
       (510, 'slough', 'gbr', 'england', 112000),
       (521, 'colchester', 'gbr', 'england', 96063),
       (526, 'birkenhead', 'gbr', 'england', 93087),
       (532, 'maidstone', 'gbr', 'england', 90878),
       (540, 'plovdiv', 'bgr', 'plovdiv', 342584),
       (575, 'punta arenas', 'chl', 'magallanes', 125631),
       (628, 'qina', 'egy', 'qina', 171275),
       (633, 'warraq al-arab', 'egy', 'giza', 127108),
       (657, 'zaragoza', 'esp', 'aragonia', 603367),
       (665, 'vigo', 'esp', 'galicia', 283670),
       (673, 'badalona', 'esp', 'katalonia', 209635),
       (686, 'terrassa', 'esp', 'katalonia', 168695),
       (720, 'kempton park', 'zaf', 'gauteng', 442633),
       (721, 'alberton', 'zaf', 'gauteng', 410102),
       (732, 'klerksdorp', 'zaf', 'north west', 261911),
       (738, 'uitenhage', 'zaf', 'eastern cape', 192120),
       (743, 'brakpan', 'zaf', 'gauteng', 171363),
       (784, 'iloilo', 'phl', 'western visayas', 365820),
       (789, 'iligan', 'phl', 'central mindanao', 285061),
       (805, 'san pablo', 'phl', 'southern tagalog', 207927),
       (811, 'binangonan', 'phl', 'southern tagalog', 187691),
       (820, 'legazpi', 'phl', 'bicol', 157010),
       (858, 'hagonoy', 'phl', 'central luzon', 111425),
       (865, 'san jose', 'phl', 'central luzon', 108254),
       (886, 'bislig', 'phl', 'caraga', 97860),
       (887, 'talavera', 'phl', 'central luzon', 97329),
       (892, 'capas', 'phl', 'central luzon', 95219),
       (902, 'libreville', 'gab', 'estuaire', 419000),
       (906, 'kutaisi', 'geo', 'imereti', 240900),
       (909, 'sohumi', 'geo', 'abhasia [aphazeti]', 111700),
       (931, 'delmas', 'hti', 'ouest', 240429),
       (939, 'jakarta', 'idn', 'jakarta raya', 9604900),
       (941, 'bandung', 'idn', 'west java', 2429000),
       (967, 'ciputat', 'idn', 'west java', 270800),
       (981, 'karawang', 'idn', 'west java', 145000),
       (997, 'cianjur', 'idn', 'west java', 114300),
       (1002, 'citeureup', 'idn', 'west java', 105100),
       (1004, 'klaten', 'idn', 'central java', 103300),
       (1021, 'sawangan', 'idn', 'west java', 91100),
       (1032, 'nagpur', 'ind', 'maharashtra', 1624752),
       (1045, 'patna', 'ind', 'bihar', 917243),
       (1046, 'srinagar', 'ind', 'jammu and kashmir', 892506),
       (1060, 'hubli-dharwad', 'ind', 'karnataka', 648298),
       (1063, 'bareilly', 'ind', 'uttar pradesh', 587211),
       (1092, 'bhilai', 'ind', 'chhatisgarh', 386159),
       (1099, 'jamnagar', 'ind', 'gujarat', 341637),
       (1119, 'davangere', 'ind', 'karnataka', 266082),
       (1122, 'bellary', 'ind', 'karnataka', 245391),
       (1126, 'muzaffarpur', 'ind', 'bihar', 241107),
       (1163, 'bally', 'ind', 'west bengali', 184474),
       (1164, 'bhilwara', 'ind', 'rajasthan', 183965),
       (1195, 'arrah (ara)', 'ind', 'bihar', 157082),
       (1201, 'cuddalore', 'ind', 'tamil nadu', 153086),
       (1203, 'dhanbad', 'ind', 'jharkhand', 151789);

insert into country(code, name, continent, region, surfacearea, indepyear, population, lifeexpectancy, gnp)
values ('abw', 'aruba', 'north america', 'caribbean', '193', 'null', 103000, '78.4', 828),
       ('afg', 'afghanistan', 'asia', 'southern and central asia', '652090', '1919', 22720000, '45.9', 5976),
       ('aia', 'anguilla', 'north america', 'caribbean', '96', 'null', 8000, '76.1', 63),
       ('and', 'andorra', 'europe', 'southern europe', '468', '1278', 78000, '83.5', 1630),
       ('ant', 'netherlands antilles', 'north america', 'caribbean', '800', 'null', 217000, '74.7', 1941),
       ('asm', 'american samoa', 'oceania', 'polynesia', '199', 'null', 68000, '75.1', 334),
       ('atg', 'antigua and barbuda', 'north america', 'caribbean', '442', '1981', 68000, '70.5', 612),
       ('aus', 'australia', 'oceania', 'australia and new zealand', '7741220', '1901', 18886000, '79.8', 351182),
       ('bdi', 'burundi', 'africa', 'eastern africa', '27834', '1962', 6695000, '46.2', 903),
       ('bgd', 'bangladesh', 'asia', 'southern and central asia', '143998', '1971', 129155000, '60.2', 32852),
       ('bgr', 'bulgaria', 'europe', 'eastern europe', '110994', '1908', 8190900, '70.9', 12178),
       ('bhr', 'bahrain', 'asia', 'middle east', '694', '1971', 617000, '73.0', 6366),
       ('bhs', 'bahamas', 'north america', 'caribbean', '13878', '1973', 307000, '71.1', 3527),
       ('bih', 'bosnia and herzegovina', 'europe', 'southern europe', '51197', '1992', 3972000, '71.5', 2841),
       ('blz', 'belize', 'north america', 'central america', '22696', '1981', 241000, '70.9', 630),
       ('bmu', 'bermuda', 'north america', 'north america', '53', 'null', 65000, '76.9', 2328),
       ('bra', 'brazil', 'south america', 'south america', '8547403', '1822', 170115000, '62.9', 776739),
       ('brb', 'barbados', 'north america', 'caribbean', '430', '1966', 270000, '73.0', 2223),
       ('brn', 'brunei', 'asia', 'southeast asia', '5765', '1984', 328000, '73.6', 11705),
       ('btn', 'bhutan', 'asia', 'southern and central asia', '47000', '1910', 2124000, '52.4', 372),
       ('bwa', 'botswana', 'africa', 'southern africa', '581730', '1966', 1622000, '39.3', 4834),
       ('can', 'canada', 'north america', 'north america', '9970610', '1867', 31147000, '79.4', 598862),
       ('cck', 'cocos (keeling) islands', 'oceania', 'australia and new zealand', '14', 'null', 600, 'null', 0),
       ('che', 'switzerland', 'europe', 'western europe', '41284', '1499', 7160400, '79.6', 264478),
       ('chl', 'chile', 'south america', 'south america', '756626', '1810', 15211000, '75.7', 72949),
       ('cmr', 'cameroon', 'africa', 'central africa', '475442', '1960', 15085000, '54.8', 9174),
       ('cog', 'congo', 'africa', 'central africa', '342000', '1960', 2943000, '47.4', 2108),
       ('cok', 'cook islands', 'oceania', 'polynesia', '236', 'null', 20000, '71.1', 100),
       ('com', 'comoros', 'africa', 'eastern africa', '1862', '1975', 578000, '60.0', 4401),
       ('cub', 'cuba', 'north america', 'caribbean', '110861', '1902', 11201000, '76.2', 17843),
       ('cxr', 'christmas island', 'oceania', 'australia and new zealand', '135', 'null', 2500, 'null', 0),
       ('cym', 'cayman islands', 'north america', 'caribbean', '264', 'null', 38000, '78.9', 1263),
       ('deu', 'germany', 'europe', 'western europe', '357022', '1955', 82164700, '77.4', 2133367),
       ('dji', 'djibouti', 'africa', 'eastern africa', '23200', '1977', 638000, '50.8', 382),
       ('dma', 'dominica', 'north america', 'caribbean', '751', '1978', 71000, '73.4', 256),
       ('dnk', 'denmark', 'europe', 'nordic countries', '43094', '800', 5330000, '76.5', 174099),
       ('ecu', 'ecuador', 'south america', 'south america', '283561', '1822', 12646000, '71.1', 19770),
       ('egy', 'egypt', 'africa', 'northern africa', '1001449', '1922', 68470000, '63.3', 82710),
       ('eri', 'eritrea', 'africa', 'eastern africa', '117600', '1993', 3850000, '55.8', 650),
       ('esh', 'western sahara', 'africa', 'northern africa', '266000', 'null', 293000, '49.8', 60),
       ('est', 'estonia', 'europe', 'baltic countries', '45227', '1991', 1439200, '69.5', 5328),
       ('fin', 'finland', 'europe', 'nordic countries', '338145', '1917', 5171300, '77.4', 121914),
       ('fji', 'fiji islands', 'oceania', 'melanesia', '18274', '1970', 817000, '67.9', 1536),
       ('flk', 'falkland islands', 'south america', 'south america', '12173', 'null', 2000, 'null', 0),
       ('fra', 'france', 'europe', 'western europe', '551500', '843', 59225700, '78.8', 1424285),
       ('fsm', 'micronesia, federated states of', 'oceania', 'micronesia', '702', '1990', 119000, '68.6', 212),
       ('gab', 'gabon', 'africa', 'central africa', '267668', '1960', 1226000, '50.1', 5493),
       ('gbr', 'united kingdom', 'europe', 'british islands', '242900', '1066', 59623400, '77.7', 1378330),
       ('gha', 'ghana', 'africa', 'western africa', '238533', '1957', 20212000, '57.4', 7137),
       ('gib', 'gibraltar', 'europe', 'southern europe', '6', 'null', 25000, '79.0', 258),
       ('glp', 'guadeloupe', 'north america', 'caribbean', '1705', 'null', 456000, '77.0', 3501),
       ('gmb', 'gambia', 'africa', 'western africa', '11295', '1965', 1305000, '53.2', 320),
       ('gnq', 'equatorial guinea', 'africa', 'central africa', '28051', '1968', 453000, '53.6', 283),
       ('grd', 'grenada', 'north america', 'caribbean', '344', '1974', 94000, '64.5', 318),
       ('gtm', 'guatemala', 'north america', 'central america', '108889', '1821', 11385000, '66.2', 19008),
       ('gum', 'guam', 'oceania', 'micronesia', '549', 'null', 168000, '77.8', 1197),
       ('guy', 'guyana', 'south america', 'south america', '214969', '1966', 861000, '64.0', 722),
       ('hkg', 'hong kong', 'asia', 'eastern asia', '1075', 'null', 6782000, '79.5', 166448),
       ('hmd', 'heard island and mcdonald islands', 'antarctica', 'antarctica', '359', 'null', 0, 'null', 0),
       ('idn', 'indonesia', 'asia', 'southeast asia', '1904569', '1945', 212107000, '68.0', 84982),
       ('ind', 'india', 'asia', 'southern and central asia', '3287263', '1947', 1013662000, '62.5', 447114),
       ('iot', 'british indian ocean territory', 'africa', 'eastern africa', '78', 'null', 0, 'null', 0),
       ('irn', 'iran', 'asia', 'southern and central asia', '1648195', '1906', 67702000, '69.7', 195746),
       ('ita', 'italy', 'europe', 'southern europe', '301316', '1861', 57680000, '79.0', 1161755),
       ('jam', 'jamaica', 'north america', 'caribbean', '10990', '1962', 2583000, '75.2', 6871),
       ('jor', 'jordan', 'asia', 'middle east', '88946', '1946', 5083000, '77.4', 7526),
       ('jpn', 'japan', 'asia', 'eastern asia', '377829', '-660', 126714000, '80.7', 3787042),
       ('kaz', 'kazakstan', 'asia', 'southern and central asia', '2724900', '1991', 16223000, '63.2', 24375),
       ('ken', 'kenya', 'africa', 'eastern africa', '580367', '1963', 30080000, '48.0', 9217),
       ('kgz', 'kyrgyzstan', 'asia', 'southern and central asia', '199900', '1991', 4699000, '63.4', 1626),
       ('kir', 'kiribati', 'oceania', 'micronesia', '726', '1979', 83000, '59.8', 41),
       ('kna', 'saint kitts and nevis', 'north america', 'caribbean', '261', '1983', 38000, '70.7', 299),
       ('kwt', 'kuwait', 'asia', 'middle east', '17818', '1961', 1972000, '76.1', 27037),
       ('lao', 'laos', 'asia', 'southeast asia', '236800', '1953', 5433000, '53.1', 1292),
       ('lbr', 'liberia', 'africa', 'western africa', '111369', '1847', 3154000, '51.0', 2012),
       ('lby', 'libyan arab jamahiriya', 'africa', 'northern africa', '1759540', '1951', 5605000, '75.5', 44806),
       ('lca', 'saint lucia', 'north america', 'caribbean', '622', '1979', 154000, '72.3', 571),
       ('lie', 'liechtenstein', 'europe', 'western europe', '160', '1806', 32300, '78.8', 1119),
       ('lka', 'sri lanka', 'asia', 'southern and central asia', '65610', '1948', 18827000, '71.8', 15706),
       ('lso', 'lesotho', 'africa', 'southern africa', '30355', '1966', 2153000, '50.8', 1061),
       ('ltu', 'lithuania', 'europe', 'baltic countries', '65301', '1991', 3698500, '69.1', 10692),
       ('lva', 'latvia', 'europe', 'baltic countries', '64589', '1991', 2424200, '68.4', 6398),
       ('mac', 'macao', 'asia', 'eastern asia', '18', 'null', 473000, '81.6', 5749),
       ('mar', 'morocco', 'africa', 'northern africa', '446550', '1956', 28351000, '69.1', 36124),
       ('mco', 'monaco', 'europe', 'western europe', '2', '1861', 34000, '78.8', 776),
       ('mda', 'moldova', 'europe', 'eastern europe', '33851', '1991', 4380000, '64.5', 1579),
       ('mdg', 'madagascar', 'africa', 'eastern africa', '587041', '1960', 15942000, '55.0', 3750),
       ('mdv', 'maldives', 'asia', 'southern and central asia', '298', '1965', 286000, '62.2', 199),
       ('mhl', 'marshall islands', 'oceania', 'micronesia', '181', '1990', 64000, '65.5', 97),
       ('mkd', 'macedonia', 'europe', 'southern europe', '25713', '1991', 2024000, '73.8', 1694),
       ('mlt', 'malta', 'europe', 'southern europe', '316', '1964', 380200, '77.9', 3512),
       ('mmr', 'myanmar', 'asia', 'southeast asia', '676578', '1948', 45611000, '54.9', 180375),
       ('mng', 'mongolia', 'asia', 'eastern asia', '1566500', '1921', 2662000, '67.3', 1043),
       ('mnp', 'northern mariana islands', 'oceania', 'micronesia', '464', 'null', 78000, '75.5', 0),
       ('msr', 'montserrat', 'north america', 'caribbean', '102', 'null', 11000, '78.0', 109),
       ('mtq', 'martinique', 'north america', 'caribbean', '1102', 'null', 395000, '78.3', 2731),
       ('mus', 'mauritius', 'africa', 'eastern africa', '2040', '1968', 1158000, '71.0', 4251),
       ('mwi', 'malawi', 'africa', 'eastern africa', '118484', '1964', 10925000, '37.6', 1687),
       ('mys', 'malaysia', 'asia', 'southeast asia', '329758', '1957', 22244000, '70.8', 69213),
       ('myt', 'mayotte', 'africa', 'eastern africa', '373', 'null', 149000, '59.5', 0),
       ('nam', 'namibia', 'africa', 'southern africa', '824292', '1990', 1726000, '42.5', 3101),
       ('ner', 'niger', 'africa', 'western africa', '1267000', '1960', 10730000, '41.3', 1706),
       ('nfk', 'norfolk island', 'oceania', 'australia and new zealand', '36', 'null', 2000, 'null', 0),
       ('nga', 'nigeria', 'africa', 'western africa', '923768', '1960', 111506000, '51.6', 65707),
       ('niu', 'niue', 'oceania', 'polynesia', '260', 'null', 2000, 'null', 0),
       ('nld', 'netherlands', 'europe', 'western europe', '41526', '1581', 15864000, '78.3', 371362),
       ('nor', 'norway', 'europe', 'nordic countries', '323877', '1905', 4478500, '78.7', 145895),
       ('npl', 'nepal', 'asia', 'southern and central asia', '147181', '1769', 23930000, '57.8', 4768),
       ('nru', 'nauru', 'oceania', 'micronesia', '21', '1968', 12000, '60.8', 197),
       ('nzl', 'new zealand', 'oceania', 'australia and new zealand', '270534', '1907', 3862000, '77.8', 54669),
       ('pak', 'pakistan', 'asia', 'southern and central asia', '796095', '1947', 156483000, '61.1', 61289),
       ('pcn', 'pitcairn', 'oceania', 'polynesia', '49', 'null', 50, 'null', 0),
       ('phl', 'philippines', 'asia', 'southeast asia', '300000', '1946', 75967000, '67.5', 65107),
       ('plw', 'palau', 'oceania', 'micronesia', '459', '1994', 19000, '68.6', 105),
       ('png', 'papua new guinea', 'oceania', 'melanesia', '462840', '1975', 4807000, '63.1', 4988),
       ('pol', 'poland', 'europe', 'eastern europe', '323250', '1918', 38653600, '73.2', 151697),
       ('pri', 'puerto rico', 'north america', 'caribbean', '8875', 'null', 3869000, '75.6', 34100),
       ('pse', 'palestine', 'asia', 'middle east', '6257', 'null', 3101000, '71.4', 4173),
       ('qat', 'qatar', 'asia', 'middle east', '11000', '1971', 599000, '72.4', 9472),
       ('rus', 'russian federation', 'europe', 'eastern europe', '17075400', '1991', 146934000, '67.2', 276608),
       ('rwa', 'rwanda', 'africa', 'eastern africa', '26338', '1962', 7733000, '39.3', 2036),
       ('sdn', 'sudan', 'africa', 'northern africa', '2505813', '1956', 29490000, '56.6', 10162),
       ('sgp', 'singapore', 'asia', 'southeast asia', '618', '1965', 3567000, '80.1', 86503),
       ('sgs', 'south georgia and the south sandwich islands', 'antarctica', 'antarctica', '3903', 'null', 0, 'null',
        0),
       ('shn', 'saint helena', 'africa', 'western africa', '314', 'null', 6000, '76.8', 0),
       ('sjm', 'svalbard and jan mayen', 'europe', 'nordic countries', '62422', 'null', 3200, 'null', 0),
       ('slb', 'solomon islands', 'oceania', 'melanesia', '28896', '1978', 444000, '71.3', 182);