CREATE TABLE CLIENT (
  numCli int PRIMARY KEY,
  nomCli varchar(15),
  prenomCli varchar(15),
  adresseCli varchar(20)
);

CREATE TABLE FACTURE (
  numFac int PRIMARY KEY,
  dateFac date,
  numCli int,
  FOREIGN KEY (numCli) REFERENCES CLIENT (numCli)
);

CREATE TABLE DETAIL (
  numFac int,
  refProd int,
  qte int,
  PRIMARY KEY (numFac, refProd)
);

CREATE TABLE PRODUIT (
  refProd int,
  nomProd varchar(40),
  puProd decimal(6,2),
  PRIMARY KEY (refProd)
);

ALTER TABLE DETAIL ADD FOREIGN KEY (refProd) REFERENCES PRODUIT (refProd);
ALTER TABLE DETAIL ADD FOREIGN KEY (numFac) REFERENCES FACTURE (numFac);

-- les clients
INSERT INTO CLIENT(numCli, nomCli, prenomCli, adresseCli) VALUES
	(1, 'BANAZIAI', 'Jules', 'Grenoble'),
	(2, 'DONGEPLI', 'Armelle', 'Limoges'),
	(3, 'BOBAINO', 'Julie', 'NŒmes'),
	(4, 'BOUTIDICHT', 'Maxime', 'Grenoble');

-- les produits
INSERT INTO PRODUIT(refProd, nomProd, puProd) VALUES
	(1, 'Sucre', 1.2),
	(2, 'Céréales', 1.3),
	(3, 'Biscottes', 1.15),
	(4, 'Cacao', 2.3),
	(5, 'Café', 2.50),
	(6, 'Thé', 3.1);

-- les factures 
INSERT INTO FACTURE(numFac, dateFac, numCli) VALUES
	(1, STR_TO_DATE('14/6/2019','%d/%m/%Y'), 2),
	(2, STR_TO_DATE('26/12/2019','%d/%m/%Y'), 2),
	(3, STR_TO_DATE('14/3/2019','%d/%m/%Y'), 1);

-- les lignes de factures 
INSERT INTO DETAIL(numFac, refProd, qte) VALUES
	(1, 2, 8),
	(1, 3, 2),
	(2, 6, 7),
	(3, 1, 2),
	(3, 3, 2),
	(3, 5, 9);
INSERT INTO CLIENT(numCli, nomCli, prenomCli, adresseCli) VALUES (5, 'RICCOH', 'Léa', 'Orléans');
INSERT INTO PRODUIT(refProd, nomProd, puProd) VALUES (7, 'Jus d''Orange', 2.40);
INSERT INTO FACTURE(numFac, dateFac, numCli) VALUES (4, STR_TO_DATE('03/12/2019', '%d/%m/%Y'), 5);
INSERT INTO DETAIL(numFac, refProd, qte) VALUES (4, 3, 2), (4, 6, 5), (4, 7, 2);

select * from produit;