CREATE DATABASE Base;
USE Base;

CREATE TABLE Utilisateur  (
Nom VARCHAR(50) NOT NULL,
Prenom VARCHAR(50) NOT NULL,
Localisation VARCHAR(50) NOT NULL,
PRIMARY KEY(Nom)
);
CREATE TABLE refcommune (
    CDC DECIMAL(1) NOT NULL ,
    ChefLieu DECIMAL(1) NOT NULL ,
    Region DECIMAL(2) NOT NULL ,
    Departement VARCHAR(3) NOT NULL,
    Commune DECIMAL(3) NOT NULL ,
    Arrondissement DECIMAL(1) ,
    Canton DECIMAL(2) NOT NULL ,
    TNCC DECIMAL(1) NOT NULL,    
	ARTMAJ VARCHAR(5) ,
    NCC VARCHAR(70),
    ARTMIN VARCHAR(5) ,
    NCCENR VARCHAR(70) ,
    ID_Commune int NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (ID_Commune)
);

LOAD DATA LOCAL INFILE './tables/user.csv'  
INTO TABLE BASE.Utilisateur
FIELDS TERMINATED BY ';'
ENCLOSED BY ''
LINES TERMINATED BY '\r';

LOAD DATA LOCAL INFILE './tables/comsimp2013.txt'  
INTO TABLE BASE.refcommune
FIELDS TERMINATED BY '\t'
ENCLOSED BY ''
LINES TERMINATED BY '\n';

SELECT commune.NCCENR, COUNT(user.nom)
FROM `BASE`.`refcommune` AS commune
LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE CONCAT('%', commune.NCCENR , '%')
GROUP BY NCCENR LIMIT 0,36683;