Skip to content

Instantly share code, notes, and snippets.

@scoumbourdis
Last active March 12, 2021 04:40
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save scoumbourdis/bc72fce984fedb2d57e4256bc438c4ae to your computer and use it in GitHub Desktop.
-- Please have in mind that as those data are only for testing purposes
-- we did add the full list of cities only for Greece and United Kingdom.
-- Other than that they are few cities for France and Italy in case you would
-- like to test it.
--
-- phpMyAdmin SQL Dump
-- version 4.8.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Server version: 5.7.23-0ubuntu0.16.04.1
-- PHP Version: 7.2.5-1+ubuntu16.04.1+deb.sury.org+1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `examples_database`
--
-- --------------------------------------------------------
--
-- Table structure for table `cities`
--
CREATE TABLE `cities` (
`id` mediumint(8) NOT NULL,
`name` varchar(250) NOT NULL,
`country` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `cities`
--
INSERT INTO `cities` (`id`, `name`, `country`) VALUES
(1, 'Bath', 76),
(2, 'Birmingham', 76),
(3, 'Bradford', 76),
(4, 'Brighton & Hove', 76),
(5, 'Bristol', 76),
(6, 'Cambridge', 76),
(7, 'Canterbury', 76),
(8, 'Carlisle', 76),
(9, 'Chelmsford', 76),
(10, 'Chester', 76),
(11, 'Chichester', 76),
(12, 'Coventry', 76),
(13, 'Derby', 76),
(14, 'Durham', 76),
(15, 'Ely', 76),
(16, 'Exeter', 76),
(17, 'Gloucester', 76),
(18, 'Hereford', 76),
(19, 'Kingston upon Hull', 76),
(20, 'Lancaster', 76),
(21, 'Leeds', 76),
(22, 'Leicester', 76),
(23, 'Lichfield', 76),
(24, 'Lincoln', 76),
(25, 'Liverpool', 76),
(26, 'London', 76),
(27, 'Manchester', 76),
(28, 'Newcastle upon Tyne', 76),
(29, 'Norwich', 76),
(30, 'Nottingham', 76),
(31, 'Oxford', 76),
(32, 'Peterborough', 76),
(33, 'Plymouth', 76),
(34, 'Portsmouth', 76),
(35, 'Preston', 76),
(36, 'Ripon', 76),
(37, 'Salford', 76),
(38, 'Salisbury', 76),
(39, 'Sheffield', 76),
(40, 'Southampton', 76),
(41, 'St Albans', 76),
(42, 'Stoke-on-Trent', 76),
(43, 'Sunderland', 76),
(44, 'Truro', 76),
(45, 'Wakefield', 76),
(46, 'Wells', 76),
(47, 'Westminster', 76),
(48, 'Winchester', 76),
(49, 'Wolverhampton', 76),
(50, 'Worcester', 76),
(51, 'York', 76),
(52, 'Aberdeen', 76),
(53, 'Dundee', 76),
(54, 'Edinburgh', 76),
(55, 'Glasgow', 76),
(56, 'Inverness', 76),
(57, 'Perth', 76),
(58, 'Stirling', 76),
(59, 'Bangor', 76),
(60, 'Cardiff', 76),
(61, 'Newport', 76),
(62, 'St Asaph', 76),
(63, 'St David\'s', 76),
(64, 'Swansea', 76),
(65, 'Armagh', 76),
(66, 'Belfast', 76),
(67, 'Londonderry', 76),
(68, 'Lisburn', 76),
(69, 'Newry', 76),
(70, 'Athens', 88),
(71, 'Thessaloniki', 88),
(72, 'Piraeus', 88),
(73, 'Patras', 88),
(74, 'Heraklion', 88),
(75, 'Peristeri', 88),
(76, 'Larissa', 88),
(77, 'Kallithea', 88),
(78, 'Nikaia', 88),
(79, 'Kalamaria', 88),
(80, 'Glyfada', 88),
(81, 'Acharnes', 88),
(82, 'Volos', 88),
(83, 'Ilio', 88),
(84, 'Keratsini', 88),
(85, 'Ilioupoli', 88),
(86, 'Nea Smyrni', 88),
(87, 'Chalandri', 88),
(88, 'Marousi', 88),
(89, 'Zografou', 88),
(90, 'Egaleo', 88),
(91, 'Agios Dimitrios', 88),
(92, 'Korydallos', 88),
(93, 'Evosmos', 88),
(94, 'Nea Ionia', 88),
(95, 'Palaio Faliro', 88),
(96, 'Ioannina', 88),
(97, 'Agia Paraskevi', 88),
(98, 'Vyronas', 88),
(99, 'Kavala', 88),
(100, 'Galatsi', 88),
(101, 'Rhodes', 88),
(102, 'Serres', 88),
(103, 'Alexandroupoli', 88),
(104, 'Chania', 88),
(105, 'Chalcis', 88),
(106, 'Petroupoli', 88),
(107, 'Katerini', 88),
(108, 'Kalamata', 88),
(109, 'Trikala', 88),
(110, 'Xanthi', 88),
(111, 'Lamia', 88),
(112, 'Irakleio', 88),
(113, 'Komotini', 88),
(114, 'Kifissia', 88),
(115, 'Sykies', 88),
(116, 'Veria', 88),
(117, 'Chaidari', 88),
(118, 'Drama', 88),
(119, 'Agrinio', 88),
(120, 'Stavroupoli', 88),
(121, 'Alimos', 88),
(122, 'Polichni', 88),
(123, 'Ampelokipoi', 88),
(124, 'Kozani', 88),
(125, 'Aspropyrgos', 88),
(126, 'Agioi Anargyroi', 88),
(127, 'Argyroupoli', 88),
(128, 'Karditsa', 88),
(129, 'Chios', 88),
(130, 'Nea Ionia', 88),
(131, 'Vrilissia', 88),
(132, 'Agia Varvara', 88),
(133, 'Corinth', 88),
(134, 'Cholargos', 88),
(135, 'Voula', 88),
(136, 'Rethymno', 88),
(137, 'Ptolemaida', 88),
(138, 'Neapoli', 88),
(139, 'Metamorfosi', 88),
(140, 'Ano Liosia', 88),
(141, 'Mytilene', 88),
(142, 'Giannitsa', 88),
(143, 'Eleusina', 88),
(144, 'Salamis', 88),
(145, 'Tripoli', 88),
(146, 'Perama', 88),
(147, 'Corfu', 88),
(148, 'Kaisariani', 88),
(149, 'Argos', 88),
(150, 'Kamatero', 88),
(151, 'Eleftherio-Kordelio', 88),
(152, 'Megara', 88),
(153, 'Melissia', 88),
(154, 'Pylaia', 88),
(155, 'Moschato', 88),
(156, 'Nea Filadelfeia', 88),
(157, 'Dafni', 88),
(158, 'Artemis', 88),
(159, 'Thebes', 88),
(160, 'Pyrgos', 88),
(161, 'Kilkis', 88),
(162, 'Livadeia', 88),
(163, 'Pefki', 88),
(164, 'Aigio', 88),
(165, 'Amaliada', 88),
(166, 'Kos', 88),
(167, 'Naousa', 88),
(168, 'Arta', 88),
(169, 'Edessa', 88),
(170, 'Ellinikon', 88),
(171, 'Gerakas', 88),
(172, 'Koropi', 88),
(173, 'Preveza', 88),
(174, 'Panorama', 88),
(175, 'Peraia', 88),
(176, 'Oraiokastro', 88),
(177, 'Nea Erythraia', 88),
(178, 'Orestiada', 88),
(179, 'Thermi', 88),
(180, 'Kalyvia Thorikou', 88),
(181, 'Pallini', 88),
(182, 'Menemeni', 88),
(183, 'Sparta', 88),
(184, 'Agios Ioannis Rentis', 88),
(185, 'Florina', 88),
(186, 'Nea Makri', 88),
(187, 'Paiania', 88),
(188, 'Paris', 74),
(189, 'Marseille', 74),
(190, 'Lyon', 74),
(191, 'Toulouse', 74),
(192, 'Oslo', 166),
(193, 'Bergen', 166),
(194, 'Stavanger / Sandnes', 166),
(195, 'Trondheim', 166),
(196, 'Rome', 109),
(197, 'Milan', 109),
(198, 'Naples', 109),
(199, 'Turin', 109),
(200, 'Amsterdam', 165),
(201, 'Rotterdam', 165),
(202, 'The Hague', 165),
(203, 'Utrecht', 165),
(204, 'Ghent', 21),
(205, 'Antwerp', 21);
-- --------------------------------------------------------
--
-- Table structure for table `continents`
--
CREATE TABLE `continents` (
`code` char(2) NOT NULL COMMENT 'Continent code',
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
--
-- Dumping data for table `continents`
--
INSERT INTO `continents` (`code`, `name`) VALUES
('AF', 'Africa'),
('AN', 'Antarctica'),
('AS', 'Asia'),
('EU', 'Europe'),
('NA', 'North America'),
('OC', 'Oceania'),
('SA', 'South America');
-- --------------------------------------------------------
--
-- Table structure for table `countries`
--
CREATE TABLE `countries` (
`id` int(11) NOT NULL,
`code` char(2) NOT NULL COMMENT 'Two-letter country code (ISO 3166-1 alpha-2)',
`name` varchar(64) NOT NULL COMMENT 'English country name',
`full_name` varchar(128) NOT NULL COMMENT 'Full English country name',
`iso3` char(3) NOT NULL COMMENT 'Three-letter country code (ISO 3166-1 alpha-3)',
`number` smallint(3) UNSIGNED ZEROFILL NOT NULL COMMENT 'Three-digit country number (ISO 3166-1 numeric)',
`continent_code` char(2) NOT NULL,
`display_order` int(3) NOT NULL DEFAULT '900'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
--
-- Dumping data for table `countries`
--
INSERT INTO `countries` (`id`, `code`, `name`, `full_name`, `iso3`, `number`, `continent_code`, `display_order`) VALUES
(1, 'AD', 'Andorra', 'Principality of Andorra', 'AND', 020, 'EU', 7),
(2, 'AE', 'United Arab Emirates', 'United Arab Emirates', 'ARE', 784, 'AS', 232),
(3, 'AF', 'Afghanistan', 'Islamic Republic of Afghanistan', 'AFG', 004, 'AS', 3),
(4, 'AG', 'Antigua and Barbuda', 'Antigua and Barbuda', 'ATG', 028, 'NA', 11),
(5, 'AI', 'Anguilla', 'Anguilla', 'AIA', 660, 'NA', 9),
(6, 'AL', 'Albania', 'Republic of Albania', 'ALB', 008, 'EU', 4),
(7, 'AM', 'Armenia', 'Republic of Armenia', 'ARM', 051, 'AS', 13),
(8, 'AN', 'Netherlands Antilles', 'Netherlands Antilles', 'ANT', 530, 'NA', 157),
(9, 'AO', 'Angola', 'Republic of Angola', 'AGO', 024, 'AF', 8),
(10, 'AQ', 'Antarctica', 'Antarctica (the territory South of 60 deg S)', 'ATA', 010, 'AN', 10),
(11, 'AR', 'Argentina', 'Argentine Republic', 'ARG', 032, 'SA', 12),
(12, 'AS', 'American Samoa', 'American Samoa', 'ASM', 016, 'OC', 6),
(13, 'AT', 'Austria', 'Republic of Austria', 'AUT', 040, 'EU', 16),
(14, 'AU', 'Australia', 'Commonwealth of Australia', 'AUS', 036, 'OC', 15),
(15, 'AW', 'Aruba', 'Aruba', 'ABW', 533, 'NA', 14),
(16, 'AX', 'Åland', 'Åland Islands', 'ALA', 248, 'EU', 246),
(17, 'AZ', 'Azerbaijan', 'Republic of Azerbaijan', 'AZE', 031, 'AS', 17),
(18, 'BA', 'Bosnia and Herzegovina', 'Bosnia and Herzegovina', 'BIH', 070, 'EU', 29),
(19, 'BB', 'Barbados', 'Barbados', 'BRB', 052, 'NA', 21),
(20, 'BD', 'Bangladesh', 'People\'s Republic of Bangladesh', 'BGD', 050, 'AS', 20),
(21, 'BE', 'Belgium', 'Kingdom of Belgium', 'BEL', 056, 'EU', 23),
(22, 'BF', 'Burkina Faso', 'Burkina Faso', 'BFA', 854, 'AF', 37),
(23, 'BG', 'Bulgaria', 'Republic of Bulgaria', 'BGR', 100, 'EU', 36),
(24, 'BH', 'Bahrain', 'Kingdom of Bahrain', 'BHR', 048, 'AS', 19),
(25, 'BI', 'Burundi', 'Republic of Burundi', 'BDI', 108, 'AF', 38),
(26, 'BJ', 'Benin', 'Republic of Benin', 'BEN', 204, 'AF', 25),
(27, 'BL', 'Saint Barthélemy', 'Saint Barthelemy', 'BLM', 652, 'NA', 185),
(28, 'BM', 'Bermuda', 'Bermuda', 'BMU', 060, 'NA', 26),
(29, 'BN', 'Brunei Darussalam', 'Brunei Darussalam', 'BRN', 096, 'AS', 35),
(30, 'BO', 'Bolivia', 'Republic of Bolivia', 'BOL', 068, 'SA', 28),
(31, 'BR', 'Brazil', 'Federative Republic of Brazil', 'BRA', 076, 'SA', 32),
(32, 'BS', 'Bahamas', 'Commonwealth of the Bahamas', 'BHS', 044, 'NA', 18),
(33, 'BT', 'Bhutan', 'Kingdom of Bhutan', 'BTN', 064, 'AS', 27),
(34, 'BV', 'Bouvet Island', 'Bouvet Island (Bouvetoya)', 'BVT', 074, 'AN', 31),
(35, 'BW', 'Botswana', 'Republic of Botswana', 'BWA', 072, 'AF', 30),
(36, 'BY', 'Belarus', 'Republic of Belarus', 'BLR', 112, 'EU', 22),
(37, 'BZ', 'Belize', 'Belize', 'BLZ', 084, 'NA', 24),
(38, 'CA', 'Canada', 'Canada', 'CAN', 124, 'NA', 2),
(39, 'CC', 'Cocos (Keeling) Islands', 'Cocos (Keeling) Islands', 'CCK', 166, 'AS', 48),
(40, 'CD', 'Congo (Kinshasa)', 'Democratic Republic of the Congo', 'COD', 180, 'AF', 51),
(41, 'CF', 'Central African Republic', 'Central African Republic', 'CAF', 140, 'AF', 43),
(42, 'CG', 'Congo (Brazzaville)', 'Republic of the Congo', 'COG', 178, 'AF', 52),
(43, 'CH', 'Switzerland', 'Swiss Confederation', 'CHE', 756, 'EU', 214),
(44, 'CI', 'Côte d\'Ivoire', 'Republic of Cote d\'Ivoire', 'CIV', 384, 'AF', 55),
(45, 'CK', 'Cook Islands', 'Cook Islands', 'COK', 184, 'OC', 53),
(46, 'CL', 'Chile', 'Republic of Chile', 'CHL', 152, 'SA', 45),
(47, 'CM', 'Cameroon', 'Republic of Cameroon', 'CMR', 120, 'AF', 40),
(48, 'CN', 'China', 'People\'s Republic of China', 'CHN', 156, 'AS', 46),
(49, 'CO', 'Colombia', 'Republic of Colombia', 'COL', 170, 'SA', 49),
(50, 'CR', 'Costa Rica', 'Republic of Costa Rica', 'CRI', 188, 'NA', 54),
(51, 'CU', 'Cuba', 'Republic of Cuba', 'CUB', 192, 'NA', 57),
(52, 'CV', 'Cape Verde', 'Republic of Cape Verde', 'CPV', 132, 'AF', 41),
(53, 'CX', 'Christmas Island', 'Christmas Island', 'CXR', 162, 'AS', 47),
(54, 'CY', 'Cyprus', 'Republic of Cyprus', 'CYP', 196, 'AS', 58),
(55, 'CZ', 'Czech Republic', 'Czech Republic', 'CZE', 203, 'EU', 59),
(56, 'DE', 'Germany', 'Federal Republic of Germany', 'DEU', 276, 'EU', 82),
(57, 'DJ', 'Djibouti', 'Republic of Djibouti', 'DJI', 262, 'AF', 61),
(58, 'DK', 'Denmark', 'Kingdom of Denmark', 'DNK', 208, 'EU', 60),
(59, 'DM', 'Dominica', 'Commonwealth of Dominica', 'DMA', 212, 'NA', 62),
(60, 'DO', 'Dominican Republic', 'Dominican Republic', 'DOM', 214, 'NA', 63),
(61, 'DZ', 'Algeria', 'People\'s Democratic Republic of Algeria', 'DZA', 012, 'AF', 5),
(62, 'EC', 'Ecuador', 'Republic of Ecuador', 'ECU', 218, 'SA', 64),
(63, 'EE', 'Estonia', 'Republic of Estonia', 'EST', 233, 'EU', 69),
(64, 'EG', 'Egypt', 'Arab Republic of Egypt', 'EGY', 818, 'AF', 65),
(65, 'EH', 'Western Sahara', 'Western Sahara', 'ESH', 732, 'AF', 242),
(66, 'ER', 'Eritrea', 'State of Eritrea', 'ERI', 232, 'AF', 68),
(67, 'ES', 'Spain', 'Kingdom of Spain', 'ESP', 724, 'EU', 207),
(68, 'ET', 'Ethiopia', 'Federal Democratic Republic of Ethiopia', 'ETH', 231, 'AF', 70),
(69, 'FI', 'Finland', 'Republic of Finland', 'FIN', 246, 'EU', 74),
(70, 'FJ', 'Fiji', 'Republic of the Fiji Islands', 'FJI', 242, 'OC', 73),
(71, 'FK', 'Falkland Islands', 'Falkland Islands (Malvinas)', 'FLK', 238, 'SA', 71),
(72, 'FM', 'Micronesia', 'Federated States of Micronesia', 'FSM', 583, 'OC', 144),
(73, 'FO', 'Faroe Islands', 'Faroe Islands', 'FRO', 234, 'EU', 72),
(74, 'FR', 'France', 'French Republic', 'FRA', 250, 'EU', 75),
(75, 'GA', 'Gabon', 'Gabonese Republic', 'GAB', 266, 'AF', 79),
(76, 'GB', 'United Kingdom', 'United Kingdom of Great Britain & Northern Ireland', 'GBR', 826, 'EU', 233),
(77, 'GD', 'Grenada', 'Grenada', 'GRD', 308, 'NA', 87),
(78, 'GE', 'Georgia', 'Georgia', 'GEO', 268, 'AS', 81),
(79, 'GF', 'French Guiana', 'French Guiana', 'GUF', 254, 'SA', 76),
(80, 'GG', 'Guernsey', 'Bailiwick of Guernsey', 'GGY', 831, 'EU', 91),
(81, 'GH', 'Ghana', 'Republic of Ghana', 'GHA', 288, 'AF', 83),
(82, 'GI', 'Gibraltar', 'Gibraltar', 'GIB', 292, 'EU', 84),
(83, 'GL', 'Greenland', 'Greenland', 'GRL', 304, 'NA', 86),
(84, 'GM', 'Gambia', 'Republic of the Gambia', 'GMB', 270, 'AF', 80),
(85, 'GN', 'Guinea', 'Republic of Guinea', 'GIN', 324, 'AF', 92),
(86, 'GP', 'Guadeloupe', 'Guadeloupe', 'GLP', 312, 'NA', 88),
(87, 'GQ', 'Equatorial Guinea', 'Republic of Equatorial Guinea', 'GNQ', 226, 'AF', 67),
(88, 'GR', 'Greece', 'Hellenic Republic Greece', 'GRC', 300, 'EU', 85),
(89, 'GS', 'South Georgia and South Sandwich Islands', 'South Georgia and the South Sandwich Islands', 'SGS', 239, 'AN', 206),
(90, 'GT', 'Guatemala', 'Republic of Guatemala', 'GTM', 320, 'NA', 90),
(91, 'GU', 'Guam', 'Guam', 'GUM', 316, 'OC', 89),
(92, 'GW', 'Guinea-Bissau', 'Republic of Guinea-Bissau', 'GNB', 624, 'AF', 93),
(93, 'GY', 'Guyana', 'Co-operative Republic of Guyana', 'GUY', 328, 'SA', 94),
(94, 'HK', 'Hong Kong', 'Hong Kong Special Administrative Region of China', 'HKG', 344, 'AS', 99),
(95, 'HM', 'Heard and McDonald Islands', 'Heard Island and McDonald Islands', 'HMD', 334, 'AN', 96),
(96, 'HN', 'Honduras', 'Republic of Honduras', 'HND', 340, 'NA', 98),
(97, 'HR', 'Croatia', 'Republic of Croatia', 'HRV', 191, 'EU', 56),
(98, 'HT', 'Haiti', 'Republic of Haiti', 'HTI', 332, 'NA', 95),
(99, 'HU', 'Hungary', 'Republic of Hungary', 'HUN', 348, 'EU', 100),
(100, 'ID', 'Indonesia', 'Republic of Indonesia', 'IDN', 360, 'AS', 103),
(101, 'IE', 'Ireland', 'Ireland', 'IRL', 372, 'EU', 106),
(102, 'IL', 'Israel', 'State of Israel', 'ISR', 376, 'AS', 108),
(103, 'IM', 'Isle of Man', 'Isle of Man', 'IMN', 833, 'EU', 107),
(104, 'IN', 'India', 'Republic of India', 'IND', 356, 'AS', 102),
(105, 'IO', 'British Indian Ocean Territory', 'British Indian Ocean Territory (Chagos Archipelago)', 'IOT', 086, 'AS', 33),
(106, 'IQ', 'Iraq', 'Republic of Iraq', 'IRQ', 368, 'AS', 105),
(107, 'IR', 'Iran', 'Islamic Republic of Iran', 'IRN', 364, 'AS', 104),
(108, 'IS', 'Iceland', 'Republic of Iceland', 'ISL', 352, 'EU', 101),
(109, 'IT', 'Italy', 'Italian Republic', 'ITA', 380, 'EU', 109),
(110, 'JE', 'Jersey', 'Bailiwick of Jersey', 'JEY', 832, 'EU', 112),
(111, 'JM', 'Jamaica', 'Jamaica', 'JAM', 388, 'NA', 110),
(112, 'JO', 'Jordan', 'Hashemite Kingdom of Jordan', 'JOR', 400, 'AS', 113),
(113, 'JP', 'Japan', 'Japan', 'JPN', 392, 'AS', 111),
(114, 'KE', 'Kenya', 'Republic of Kenya', 'KEN', 404, 'AF', 115),
(115, 'KG', 'Kyrgyzstan', 'Kyrgyz Republic', 'KGZ', 417, 'AS', 120),
(116, 'KH', 'Cambodia', 'Kingdom of Cambodia', 'KHM', 116, 'AS', 39),
(117, 'KI', 'Kiribati', 'Republic of Kiribati', 'KIR', 296, 'OC', 116),
(118, 'KM', 'Comoros', 'Union of the Comoros', 'COM', 174, 'AF', 50),
(119, 'KN', 'Saint Kitts and Nevis', 'Federation of Saint Kitts and Nevis', 'KNA', 659, 'NA', 187),
(120, 'KP', 'Korea, North', 'Democratic People\'s Republic of Korea', 'PRK', 408, 'AS', 117),
(121, 'KR', 'Korea, South', 'Republic of Korea', 'KOR', 410, 'AS', 118),
(122, 'KW', 'Kuwait', 'State of Kuwait', 'KWT', 414, 'AS', 119),
(123, 'KY', 'Cayman Islands', 'Cayman Islands', 'CYM', 136, 'NA', 42),
(124, 'KZ', 'Kazakhstan', 'Republic of Kazakhstan', 'KAZ', 398, 'AS', 114),
(125, 'LA', 'Laos', 'Lao People\'s Democratic Republic', 'LAO', 418, 'AS', 121),
(126, 'LB', 'Lebanon', 'Lebanese Republic', 'LBN', 422, 'AS', 123),
(127, 'LC', 'Saint Lucia', 'Saint Lucia', 'LCA', 662, 'NA', 188),
(128, 'LI', 'Liechtenstein', 'Principality of Liechtenstein', 'LIE', 438, 'EU', 127),
(129, 'LK', 'Sri Lanka', 'Democratic Socialist Republic of Sri Lanka', 'LKA', 144, 'AS', 208),
(130, 'LR', 'Liberia', 'Republic of Liberia', 'LBR', 430, 'AF', 125),
(131, 'LS', 'Lesotho', 'Kingdom of Lesotho', 'LSO', 426, 'AF', 124),
(132, 'LT', 'Lithuania', 'Republic of Lithuania', 'LTU', 440, 'EU', 128),
(133, 'LU', 'Luxembourg', 'Grand Duchy of Luxembourg', 'LUX', 442, 'EU', 129),
(134, 'LV', 'Latvia', 'Republic of Latvia', 'LVA', 428, 'EU', 122),
(135, 'LY', 'Libya', 'Libyan Arab Jamahiriya', 'LBY', 434, 'AF', 126),
(136, 'MA', 'Morocco', 'Kingdom of Morocco', 'MAR', 504, 'AF', 150),
(137, 'MC', 'Monaco', 'Principality of Monaco', 'MCO', 492, 'EU', 146),
(138, 'MD', 'Moldova', 'Republic of Moldova', 'MDA', 498, 'EU', 145),
(139, 'ME', 'Montenegro', 'Republic of Montenegro', 'MNE', 499, 'EU', 148),
(140, 'MF', 'Saint Martin (French part)', 'Saint Martin', 'MAF', 663, 'NA', 189),
(141, 'MG', 'Madagascar', 'Republic of Madagascar', 'MDG', 450, 'AF', 132),
(142, 'MH', 'Marshall Islands', 'Republic of the Marshall Islands', 'MHL', 584, 'OC', 138),
(143, 'MK', 'Macedonia', 'Republic of Macedonia', 'MKD', 807, 'EU', 131),
(144, 'ML', 'Mali', 'Republic of Mali', 'MLI', 466, 'AF', 136),
(145, 'MM', 'Myanmar', 'Union of Myanmar', 'MMR', 104, 'AS', 152),
(146, 'MN', 'Mongolia', 'Mongolia', 'MNG', 496, 'AS', 147),
(147, 'MO', 'Macau', 'Macao Special Administrative Region of China', 'MAC', 446, 'AS', 130),
(148, 'MP', 'Northern Mariana Islands', 'Commonwealth of the Northern Mariana Islands', 'MNP', 580, 'OC', 165),
(149, 'MQ', 'Martinique', 'Martinique', 'MTQ', 474, 'NA', 139),
(150, 'MR', 'Mauritania', 'Islamic Republic of Mauritania', 'MRT', 478, 'AF', 140),
(151, 'MS', 'Montserrat', 'Montserrat', 'MSR', 500, 'NA', 149),
(152, 'MT', 'Malta', 'Republic of Malta', 'MLT', 470, 'EU', 137),
(153, 'MU', 'Mauritius', 'Republic of Mauritius', 'MUS', 480, 'AF', 141),
(154, 'MV', 'Maldives', 'Republic of Maldives', 'MDV', 462, 'AS', 135),
(155, 'MW', 'Malawi', 'Republic of Malawi', 'MWI', 454, 'AF', 133),
(156, 'MX', 'Mexico', 'United Mexican States', 'MEX', 484, 'NA', 143),
(157, 'MY', 'Malaysia', 'Malaysia', 'MYS', 458, 'AS', 134),
(158, 'MZ', 'Mozambique', 'Republic of Mozambique', 'MOZ', 508, 'AF', 151),
(159, 'NA', 'Namibia', 'Republic of Namibia', 'NAM', 516, 'AF', 153),
(160, 'NC', 'New Caledonia', 'New Caledonia', 'NCL', 540, 'OC', 158),
(161, 'NE', 'Niger', 'Republic of Niger', 'NER', 562, 'AF', 161),
(162, 'NF', 'Norfolk Island', 'Norfolk Island', 'NFK', 574, 'OC', 164),
(163, 'NG', 'Nigeria', 'Federal Republic of Nigeria', 'NGA', 566, 'AF', 162),
(164, 'NI', 'Nicaragua', 'Republic of Nicaragua', 'NIC', 558, 'NA', 160),
(165, 'NL', 'Netherlands', 'Kingdom of the Netherlands', 'NLD', 528, 'EU', 156),
(166, 'NO', 'Norway', 'Kingdom of Norway', 'NOR', 578, 'EU', 166),
(167, 'NP', 'Nepal', 'State of Nepal', 'NPL', 524, 'AS', 155),
(168, 'NR', 'Nauru', 'Republic of Nauru', 'NRU', 520, 'OC', 154),
(169, 'NU', 'Niue', 'Niue', 'NIU', 570, 'OC', 163),
(170, 'NZ', 'New Zealand', 'New Zealand', 'NZL', 554, 'OC', 159),
(171, 'OM', 'Oman', 'Sultanate of Oman', 'OMN', 512, 'AS', 167),
(172, 'PA', 'Panama', 'Republic of Panama', 'PAN', 591, 'NA', 171),
(173, 'PE', 'Peru', 'Republic of Peru', 'PER', 604, 'SA', 174),
(174, 'PF', 'French Polynesia', 'French Polynesia', 'PYF', 258, 'OC', 77),
(175, 'PG', 'Papua New Guinea', 'Independent State of Papua New Guinea', 'PNG', 598, 'OC', 172),
(176, 'PH', 'Philippines', 'Republic of the Philippines', 'PHL', 608, 'AS', 175),
(177, 'PK', 'Pakistan', 'Islamic Republic of Pakistan', 'PAK', 586, 'AS', 168),
(178, 'PL', 'Poland', 'Republic of Poland', 'POL', 616, 'EU', 177),
(179, 'PM', 'Saint Pierre and Miquelon', 'Saint Pierre and Miquelon', 'SPM', 666, 'NA', 190),
(180, 'PN', 'Pitcairn', 'Pitcairn Islands', 'PCN', 612, 'OC', 176),
(181, 'PR', 'Puerto Rico', 'Commonwealth of Puerto Rico', 'PRI', 630, 'NA', 179),
(182, 'PS', 'Palestine', 'Occupied Palestinian Territory', 'PSE', 275, 'AS', 170),
(183, 'PT', 'Portugal', 'Portuguese Republic', 'PRT', 620, 'EU', 178),
(184, 'PW', 'Palau', 'Republic of Palau', 'PLW', 585, 'OC', 169),
(185, 'PY', 'Paraguay', 'Republic of Paraguay', 'PRY', 600, 'SA', 173),
(186, 'QA', 'Qatar', 'State of Qatar', 'QAT', 634, 'AS', 180),
(187, 'RE', 'Reunion', 'Reunion', 'REU', 638, 'AF', 181),
(188, 'RO', 'Romania', 'Romania', 'ROU', 642, 'EU', 182),
(189, 'RS', 'Serbia', 'Republic of Serbia', 'SRB', 688, 'EU', 197),
(190, 'RU', 'Russian Federation', 'Russian Federation', 'RUS', 643, 'EU', 183),
(191, 'RW', 'Rwanda', 'Republic of Rwanda', 'RWA', 646, 'AF', 184),
(192, 'SA', 'Saudi Arabia', 'Kingdom of Saudi Arabia', 'SAU', 682, 'AS', 195),
(193, 'SB', 'Solomon Islands', 'Solomon Islands', 'SLB', 090, 'OC', 203),
(194, 'SC', 'Seychelles', 'Republic of Seychelles', 'SYC', 690, 'AF', 198),
(195, 'SD', 'Sudan', 'Republic of Sudan', 'SDN', 736, 'AF', 209),
(196, 'SE', 'Sweden', 'Kingdom of Sweden', 'SWE', 752, 'EU', 213),
(197, 'SG', 'Singapore', 'Republic of Singapore', 'SGP', 702, 'AS', 200),
(198, 'SH', 'Saint Helena', 'Saint Helena', 'SHN', 654, 'AF', 186),
(199, 'SI', 'Slovenia', 'Republic of Slovenia', 'SVN', 705, 'EU', 202),
(200, 'SJ', 'Svalbard and Jan Mayen Islands', 'Svalbard & Jan Mayen Islands', 'SJM', 744, 'EU', 211),
(201, 'SK', 'Slovakia', 'Slovakia (Slovak Republic)', 'SVK', 703, 'EU', 201),
(202, 'SL', 'Sierra Leone', 'Republic of Sierra Leone', 'SLE', 694, 'AF', 199),
(203, 'SM', 'San Marino', 'Republic of San Marino', 'SMR', 674, 'EU', 193),
(204, 'SN', 'Senegal', 'Republic of Senegal', 'SEN', 686, 'AF', 196),
(205, 'SO', 'Somalia', 'Somali Republic', 'SOM', 706, 'AF', 204),
(206, 'SR', 'Suriname', 'Republic of Suriname', 'SUR', 740, 'SA', 210),
(207, 'ST', 'Sao Tome and Principe', 'Democratic Republic of Sao Tome and Principe', 'STP', 678, 'AF', 194),
(208, 'SV', 'El Salvador', 'Republic of El Salvador', 'SLV', 222, 'NA', 66),
(209, 'SY', 'Syria', 'Syrian Arab Republic', 'SYR', 760, 'AS', 215),
(210, 'SZ', 'Swaziland', 'Kingdom of Swaziland', 'SWZ', 748, 'AF', 212),
(211, 'TC', 'Turks and Caicos Islands', 'Turks and Caicos Islands', 'TCA', 796, 'NA', 228),
(212, 'TD', 'Chad', 'Republic of Chad', 'TCD', 148, 'AF', 44),
(213, 'TF', 'French Southern Lands', 'French Southern Territories', 'ATF', 260, 'AN', 78),
(214, 'TG', 'Togo', 'Togolese Republic', 'TGO', 768, 'AF', 221),
(215, 'TH', 'Thailand', 'Kingdom of Thailand', 'THA', 764, 'AS', 219),
(216, 'TJ', 'Tajikistan', 'Republic of Tajikistan', 'TJK', 762, 'AS', 217),
(217, 'TK', 'Tokelau', 'Tokelau', 'TKL', 772, 'OC', 222),
(218, 'TL', 'Timor-Leste', 'Democratic Republic of Timor-Leste', 'TLS', 626, 'AS', 220),
(219, 'TM', 'Turkmenistan', 'Turkmenistan', 'TKM', 795, 'AS', 227),
(220, 'TN', 'Tunisia', 'Tunisian Republic', 'TUN', 788, 'AF', 225),
(221, 'TO', 'Tonga', 'Kingdom of Tonga', 'TON', 776, 'OC', 223),
(222, 'TR', 'Turkey', 'Republic of Turkey', 'TUR', 792, 'AS', 226),
(223, 'TT', 'Trinidad and Tobago', 'Republic of Trinidad and Tobago', 'TTO', 780, 'NA', 224),
(224, 'TV', 'Tuvalu', 'Tuvalu', 'TUV', 798, 'OC', 229),
(225, 'TW', 'Taiwan', 'Taiwan', 'TWN', 158, 'AS', 216),
(226, 'TZ', 'Tanzania', 'United Republic of Tanzania', 'TZA', 834, 'AF', 218),
(227, 'UA', 'Ukraine', 'Ukraine', 'UKR', 804, 'EU', 231),
(228, 'UG', 'Uganda', 'Republic of Uganda', 'UGA', 800, 'AF', 230),
(229, 'UM', 'United States Minor Outlying Islands', 'United States Minor Outlying Islands', 'UMI', 581, 'OC', 234),
(230, 'US', 'United States of America', 'United States of America', 'USA', 840, 'NA', 1),
(231, 'UY', 'Uruguay', 'Eastern Republic of Uruguay', 'URY', 858, 'SA', 236),
(232, 'UZ', 'Uzbekistan', 'Republic of Uzbekistan', 'UZB', 860, 'AS', 237),
(233, 'VA', 'Vatican City', 'Holy See (Vatican City State)', 'VAT', 336, 'EU', 97),
(234, 'VC', 'Saint Vincent and the Grenadines', 'Saint Vincent and the Grenadines', 'VCT', 670, 'NA', 191),
(235, 'VE', 'Venezuela', 'Bolivarian Republic of Venezuela', 'VEN', 862, 'SA', 239),
(236, 'VG', 'Virgin Islands, British', 'British Virgin Islands', 'VGB', 092, 'NA', 34),
(237, 'VI', 'Virgin Islands, U.S.', 'United States Virgin Islands', 'VIR', 850, 'NA', 235),
(238, 'VN', 'Vietnam', 'Socialist Republic of Vietnam', 'VNM', 704, 'AS', 240),
(239, 'VU', 'Vanuatu', 'Republic of Vanuatu', 'VUT', 548, 'OC', 238),
(240, 'WF', 'Wallis and Futuna Islands', 'Wallis and Futuna', 'WLF', 876, 'OC', 241),
(241, 'WS', 'Samoa', 'Independent State of Samoa', 'WSM', 882, 'OC', 192),
(242, 'YE', 'Yemen', 'Yemen', 'YEM', 887, 'AS', 243),
(243, 'YT', 'Mayotte', 'Mayotte', 'MYT', 175, 'AF', 142),
(244, 'ZA', 'South Africa', 'Republic of South Africa', 'ZAF', 710, 'AF', 205),
(245, 'ZM', 'Zambia', 'Republic of Zambia', 'ZMB', 894, 'AF', 244),
(246, 'ZW', 'Zimbabwe', 'Republic of Zimbabwe', 'ZWE', 716, 'AF', 245);
-- --------------------------------------------------------
--
-- Table structure for table `customers_db`
--
CREATE TABLE `customers_db` (
`customerNumber` int(11) NOT NULL,
`fullname` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`continent_id` char(2) CHARACTER SET utf8 DEFAULT NULL,
`country_id` int(11) DEFAULT NULL,
`city_id` mediumint(8) DEFAULT NULL,
`address_line_1` varchar(250) NOT NULL,
`address_line_2` varchar(50) DEFAULT NULL,
`postalCode` varchar(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `customers_db`
--
INSERT INTO `customers_db` (`customerNumber`, `fullname`, `phone`, `continent_id`, `country_id`, `city_id`, `address_line_1`, `address_line_2`, `postalCode`) VALUES
(250, 'Lyon Souveniers', '+33 1 46 62 7555', 'EU', 74, 188, '27 rue du Colonel Pierre Avia', NULL, NULL),
(299, 'Norway Gifts By Mail, Co.', '+47 2212 1555', 'EU', 166, 192, 'Drammensveien 126A', 'PB 211 Sentrum', 'N 0106'),
(303, 'Schuyler Imports', '+31 20 491 9555', 'EU', 165, 200, 'Kingsfordweg 151', NULL, '1043 GR'),
(314, 'Petit Auto', '(02) 5554 67', 'EU', 21, 204, 'Rue Joseph-Bens 532', NULL, 'B-1180'),
(319, 'Mini Classics', '9145554562', 'NA', 230, NULL, '3758 North Pendale Street', NULL, '24067'),
(320, 'Mini Creations Ltd.', '5085559555', 'NA', 230, NULL, '4575 Hillside Dr.', NULL, '50553'),
(323, 'Down Under Souveniers, Inc', '+64 9 312 5555', 'OC', 170, NULL, '162-164 Grafton Road', 'Level 2', NULL),
(334, 'Suominen Souveniers', '+358 9 8045 555', 'EU', 69, NULL, 'Mannerheiminaukio 2, 00100', 'SEC Oy', 'FIN-02271'),
(335, 'Cramer Spezialitäten, Ltd2', '0555-09555', 'AS', 33, NULL, 'Maubelstr. 90', NULL, '14776'),
(350, 'Marseille Mini Autos', '91.24.4555', 'EU', 74, 190, '12, rue des Bouchers', NULL, '13008'),
(356, 'SAR Distributors, Co', '+27 21 550 3555', 'AF', 244, NULL, '1250 Pretorius Street', NULL, '0028'),
(357, 'GiftsForHim.com', '64-9-3763555', 'OC', 170, NULL, '199 Great North Road', NULL, NULL),
(361, 'Kommission Auto', '0251-555259', 'EU', 56, NULL, 'Luisenstr. 48', NULL, '44087'),
(369, 'Lisboa Souveniers, Inc', '(1) 354-2555', 'EU', 183, NULL, 'Jardim das rosas n. 32', NULL, '1675'),
(379, 'Collectables For Less Inc.', '6175558555', 'NA', 230, NULL, '7825 Douglas Av.', NULL, '58339'),
(382, 'Salzburg Collectables', '6562-9555', 'EU', 13, NULL, 'Geislweg 14', NULL, '5020'),
(385, 'Cruz & Sons Co.', '+63 2 555 3587', 'AS', 176, NULL, '15 McCallum Street', 'NatWest Center #13-03', '1227 MM'),
(386, 'L\'ordine Souveniers', '0522-556555', 'EU', 109, 196, 'Strada Provinciale 124', NULL, '42100'),
(412, 'Extreme Desk Decorations, Ltd', '04 499 9555', 'OC', 170, NULL, '101 Lambton Quay', 'Level 11', NULL),
(424, 'Classic Legends Inc.', '2125558493', 'NA', 230, NULL, '5905 Pompton St.', 'Suite 750', '10022'),
(448, 'Scandinavian Gift Ideas', '0695-34 6555', 'EU', 196, NULL, 'Åkergatan 24', NULL, 'S-844 67'),
(594, 'KAFEINA Coffee Grinding & Roasting Shop', '+30 21 0807 0058', 'EU', 88, 70, 'Leof. Kifisias 359', 'Kifisia', '145 64'),
(595, 'Queen\'s Lane Coffee House', '+44 1865 240082', 'EU', 76, 31, '40 High St', NULL, 'OX1 4AP');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `cities`
--
ALTER TABLE `cities`
ADD PRIMARY KEY (`id`),
ADD KEY `country_id` (`country`);
--
-- Indexes for table `continents`
--
ALTER TABLE `continents`
ADD PRIMARY KEY (`code`);
--
-- Indexes for table `countries`
--
ALTER TABLE `countries`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `idx_code` (`code`) USING BTREE,
ADD KEY `idx_continent_code` (`continent_code`) USING BTREE;
--
-- Indexes for table `customers_db`
--
ALTER TABLE `customers_db`
ADD PRIMARY KEY (`customerNumber`),
ADD KEY `continent_id` (`continent_id`),
ADD KEY `country_id` (`country_id`),
ADD KEY `city_id` (`city_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `cities`
--
ALTER TABLE `cities`
MODIFY `id` mediumint(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=206;
--
-- AUTO_INCREMENT for table `countries`
--
ALTER TABLE `countries`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=247;
--
-- AUTO_INCREMENT for table `customers_db`
--
ALTER TABLE `customers_db`
MODIFY `customerNumber` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=596;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `cities`
--
ALTER TABLE `cities`
ADD CONSTRAINT `cities_ibfk_1` FOREIGN KEY (`country`) REFERENCES `countries` (`id`);
--
-- Constraints for table `countries`
--
ALTER TABLE `countries`
ADD CONSTRAINT `countries_ibfk_1` FOREIGN KEY (`continent_code`) REFERENCES `continents` (`code`) ON UPDATE CASCADE;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment