#
#  Copyright 2017 Amazon.com
#
#  Licensed under the Apache License, Version 2.0 (the "License");
#  you may not use this file except in compliance with the License.
#  You may obtain a copy of the License at
#
#    http://www.apache.org/licenses/LICENSE-2.0
#
#  Unless required by applicable law or agreed to in writing, software
#  distributed under the License is distributed on an "AS IS" BASIS,
#  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#  See the License for the specific language governing permissions and
#  limitations under the License.



create table if not exists person
(ID MEDIUMINT  NOT NULL AUTO_INCREMENT,
 full_name        varchar(60) NOT NULL,
 last_name        varchar(30),
 first_name       varchar(30),
 constraint person_pk primary key(id)
);

INSERT INTO person(full_name, last_name, first_name)
SELECT  CONCAT(first.name, ' ', last.name)
       ,last.name
       ,first.name
FROM   name_data first, name_data last
WHERE  first.name_type != 'LAST'
AND    last.name_type  = 'LAST';