Untitled

SELECT now();

SELECT concat('INSERT INTO regions (name) VALUES (''',region,''');')
FROM world.country
GROUP BY region;

SELECT region
FROM world.country
GROUP BY region;

SELECT regiao FROM
(
SELECT DISTINCT region AS regiao FROM world.country
) tb1;

SELECT concat('asfasdf','dfdfdf');


-- Criar tabela regions
CREATE TABLE regions (
id_region TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name varchar(50) NOT NULL,
population bigint NOT NULL DEFAULT 0
);

SELECT * FROM world.regions;

DELETE FROM world.regions;


-- Popular a tabela regions
INSERT INTO regions (name) SELECT DISTINCT region FROM world.country;

-- Criar o campo de regiao em county
ALTER TABLE world.country ADD id_region TINYINT UNSIGNED AFTER region;
SELECT * FROM world.country;

-- Criar o relacionamento
ALTER TABLE world.country
ADD FOREIGN KEY (id_region) REFERENCES world.regions (id_region)
ON UPDATE CASCADE
ON DELETE RESTRICT;

-- Popular o id_region de country
SELECT
tb1.name AS 'País',
tb1.Region AS 'Região',
tb2.name AS 'Regiao de regions',
tb1.id_region,
tb2.id_region
FROM
world.country tb1 JOIN world.regions tb2
ON tb1.region = tb2.name;

UPDATE
world.country tb1 JOIN world.regions tb2
ON tb1.region = tb2.name
SET tb1.id_region = tb2.id_region;

SELECT * FROM world.country;

SELECT * FROM regions;

-- Remover o campo region de country
ALTER TABLE world.country DROP COLUMN region;

SELECT
id_region,
sum(Population) AS pop
FROM world.country 
GROUP BY id_region;

UPDATE
world.regions tb1 JOIN
(
SELECT
id_region AS id,
sum(Population) AS pop
FROM world.country 
GROUP BY id_region
) tb2
ON tb1.id_region = tb2.id
SET tb1.population = tb2.pop;

ALTER TABLE regions DROP population;