DROP TABLE IF EXISTS ...;
DROP TABLE IF EXISTS ...;
DROP TABLE IF EXISTS ...;

CREATE TABLE marque_tel
(
idMarque   ...
, libelle   ...
, pays   ...
, PRIMARY KEY  ... 
);

CREATE TABLE type_tel
(
idType    ...
, libelle ...
, PRIMARY KEY ...
);

CREATE TABLE telephone
(
idTelephone      ...  
, type_id         ...
, marque_id       ... 
, date_achat   ...
, prix         ...
, proprietaire_id ...
, couleur       ...
, PRIMARY KEY(...)
, FOREIGN KEY ...
, FOREIGN KEY ...
);



INSERT INTO telephone (idTelephone,type_id,marque_id,date_achat,prix,proprietaire_id,couleur) VALUES (1,'SP' ,1,STR_TO_DATE('15/01/2020', '%d/%m/%Y'),139.99,190120,'ROUGE');
-- il est déconseillé de mettre une valeur à une clé primaire => pour vérification
INSERT INTO telephone (idTelephone,type_id,marque_id,date_achat,prix,proprietaire_id) VALUES (NULL,'SP' ,2,STR_TO_DATE('14/03/2020', '%d/%m/%Y'), 99.99,190215);
INSERT INTO telephone
(idTelephone,type_id,marque_id,date_achat,prix,proprietaire_id,couleur) VALUES
(NULL,'CL' ,3,STR_TO_DATE('02/05/2020', '%d/%m/%Y'), 49.11,190001,'NOIR');
INSERT INTO telephone
(idTelephone,type_id,marque_id,date_achat,prix,proprietaire_id,couleur) VALUES
(NULL,'CO' ,4,STR_TO_DATE('25/07/2020', '%d/%m/%Y'), 89.14,190222,'BLANC');
INSERT INTO telephone
(idTelephone,type_id,marque_id,date_achat,prix,proprietaire_id)         VALUES
(NULL,'IP' ,5,STR_TO_DATE('30/09/2020', '%d/%m/%Y'),359.49,190561);
INSERT INTO telephone
(idTelephone,type_id,marque_id,date_achat,prix,proprietaire_id,couleur) VALUES
(NULL,'CO' ,5,STR_TO_DATE('01/01/2021', '%d/%m/%Y'), 99.51,122120,'BLANC'),
(NULL,'SP' ,1,'2013-01-15',189,190622,'ROUGE'),
(NULL,NULL ,NULL,'2013-01-15',20,190623,'ROUGE'),
(NULL,NULL ,1,'2013-01-15',NULL,NULL,NULL);


/* Jeu de test issu d'un exercice sur ORACLE : sur ORACLE to_date('15/01/2021', '%d/%m/%Y') remplace STR_TO_DATE('15/01/2021', '%d/%m/%Y') */

INSERT INTO type_tel VALUES ('SP','Smartphone');
INSERT INTO type_tel VALUES ('CL','clapet');
INSERT INTO type_tel VALUES ('CO','COULISSANT');
INSERT INTO type_tel VALUES ('IP','IPHONE');
INSERT INTO type_tel VALUES ('AU','AUTRE');


INSERT INTO marque_tel VALUES (NULL, 'société SAMSUNG','COREE');
INSERT INTO marque_tel VALUES (NULL, 'entreprise SONY','JAPON');
INSERT INTO marque_tel VALUES (NULL, 'groupe PHILIPS','PAYS BAS');
INSERT INTO marque_tel VALUES (NULL, 'marque MOTOROLA','USA');
INSERT INTO marque_tel VALUES (NULL, 'SOCIETE APPLE','USA');

SHOW FULL COLUMNS FROM telephone;

CREATE TABLE SAV_TELEPHONE SELECT * FROM telephone;
CREATE TABLE SAV_TELEPHONE LIKE telephone;