1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
| --bdd personnnes
CREATE TABLE personne_bdd(
personne_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
sexe TEXT NOT NULL,
nom text not NULL,
prenom text not NULL,
region_name text not NULL,
departement_name text not null,
ville_name text not null
);
--insertion data personnes
Insert into personne_bdd(personne_id,sexe,nom,prenom,region_name,departement_name,ville_name) VALUES (01,'Homme','Dupont','Charles','Auvergne-Rhône-Alpes','Cantal','Aurillac');
Insert into personne_bdd(personne_id,sexe,nom,prenom,region_name,departement_name,ville_name) VALUES (02,'Femme','Cuevas','Marie','Auvergne-Rhône-Alpes','Puy-de-Dôme','Clermont-Ferrand');
Insert into personne_bdd(personne_id,sexe,nom,prenom,region_name,departement_name,ville_name) VALUES (03,'Homme','Orr','Gabriel','Auvergne-Rhône-Alpes','Loire','Saint-Etienne');
Insert into personne_bdd(personne_id,sexe,nom,prenom,region_name,departement_name,ville_name) VALUES (04,'Femme','Mercer','Laura','Auvergne-Rhône-Alpes','Ain','Bourg-en-Bresse');
Insert into personne_bdd(personne_id,sexe,nom,prenom,region_name,departement_name,ville_name) VALUES (05,'Homme','Huerta','Valentin','Bourgogne-Franche-Comté','Saône-et-Loire','Mâcon');
Insert into personne_bdd(personne_id,sexe,nom,prenom,region_name,departement_name,ville_name) VALUES (06,'Femme','Lin','Denise','Bourgogne-Franche-Comté','Yonne','Auxerre');
Insert into personne_bdd(personne_id,sexe,nom,prenom,region_name,departement_name,ville_name) VALUES (07,'Homme','Soto','Louis','Bourgogne-Franche-Comté','Jura','Lons');
Insert into personne_bdd(personne_id,sexe,nom,prenom,region_name,departement_name,ville_name) VALUES (08,'Femme','Reese','Bernadette','Bourgogne-Franche-Comté','Doubs','Besançon');
--creation bdd Centre intéret
CREATE TABLE ci_bdd(
ci_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
sport TEXT,
art TEXT,
regime_alimentaire text,
animaux text
);
--insertion données
Insert into ci_bdd(ci_id,sport,art,regime_alimentaire,animaux) VALUES (101,'sport de raquettes','opera','tout','chien');
Insert into ci_bdd(ci_id,sport,art,regime_alimentaire,animaux) VALUES (123,'sport de raquettes','opera','vegetarien','chat');
Insert into ci_bdd(ci_id,sport,art,regime_alimentaire,animaux) VALUES (234,'sport collectif','danse classique','vegan','chat');
Insert into ci_bdd(ci_id,sport,art,regime_alimentaire,animaux) VALUES (345,'sport collectif','peinture','flexitarien','hamster');
Insert into ci_bdd(ci_id,sport,art,regime_alimentaire,animaux) VALUES (456,'sport de combat','cinema','vegetarien','chat');
Insert into ci_bdd(ci_id,sport,art,regime_alimentaire,animaux) VALUES (567,'golf','opera','vegetalien','chien');
Insert into ci_bdd(ci_id,sport,art,regime_alimentaire,animaux) VALUES (678,'sport equin','cinema','vegan','cheval');
Insert into ci_bdd(ci_id,sport,art,regime_alimentaire,animaux) VALUES (789,'golf','theatre','tout','hastmer');
--creation table pivot
Create table personne_ci(
personne_id INTEGER not NULL,
ci_id INTEGER not NULL,
FOREIGN key (personne_id) REFERENCES personne_bdd (personne_id) on delete cascade on update cascade,
FOREIGN key (ci_id) REFERENCES ci_bdd (ci_id) on delete cascade on update cascade,
PRIMARY KEY(personne_id,ci_id)
);
--insertion data
INSERT INTO personne_ci (personne_id,ci_id) values (01,101);
INSERT INTO personne_ci (personne_id,ci_id) values (02,123);
INSERT INTO personne_ci (personne_id,ci_id) values (03,234);
INSERT INTO personne_ci (personne_id,ci_id) values (04,345);
INSERT INTO personne_ci (personne_id,ci_id) values (05,456);
INSERT INTO personne_ci (personne_id,ci_id) values (06,567);
INSERT INTO personne_ci (personne_id,ci_id) values (07,678);
INSERT INTO personne_ci (personne_id,ci_id) values (08,789); |
Partager