Table of Contents

1. Challenge

   Install MariaDB/MySQL

   1. Install using the official repo:

   sudo yum install -y mariadb-server
   

   sudo systemctl start mariadb
   

   sudo systemctl enable mariadb
   

   sudo mysql_secure_installation
   

2. Challenge

   Create required resources

   1. Log in to the server:

   mysql -u root -p
   

   2. Then, create the database and the table:

   create database city_data;
   

   use city_data
   

   create table city_info(
   LatD int,
   LatM int,
   LatS int,
   NS char,
   LonD int,
   LonM int,
   LonS int,
   EW char,
   City varchar(50),
   State varchar(50)
   ) ;
   

3. Challenge

   Bulk insert and validate the data

   1. Copy the cities.csv file into the mysql user's home directory:

   sudo cp ./data/cities.csv /var/lib/mysql
   

   2. Perform the bulk insert:

   LOAD DATA INFILE '/var/lib/mysql/cities.csv' INTO table city_info Fields terminated by ',' ENclosed by '"' Lines terminated by '\n' Ignore 1 ROWS;
   

   3. Validate the data:

   select count(city) from city_info where state='KS';