SQLize | PHPize | SQLtest

/* Création de la table CLIENT */ CREATE TABLE CLIENT ([ID_CLIENT] int, [NOM] varchar(30), [PRENOM] varchar(30), [DATE_NAISSANCE] date, [SEXE] int, [OPTIN] int, [OPTIN_PARTENAIRE] int, [CODE_POSTAL] char(5), CONSTRAINT PK_ID_CLIENT PRIMARY KEY ([ID_CLIENT])) ; INSERT INTO CLIENT ([ID_CLIENT], [NOM], [PRENOM], [DATE_NAISSANCE], [SEXE], [OPTIN], [OPTIN_PARTENAIRE], [CODE_POSTAL]) VALUES (1, 'ALBERTFORT', 'Jean', '2001-07-01', 1, 1, 1, '59109'), (2, 'BALLE', 'Martine', '1975-05-21', 2, 0, 0, '62475'), (3, 'COUSIN', 'Tatiana', '1948-05-06', 1, 0, 0, '59000'), (4, 'DUPONT', 'José', '1999-10-12', 2, 1, 0, '75894'), (5, 'EVERAERE', 'Rodolph', '1986-02-28', 1, 1, 0, '10458'), (6, 'FILOU', 'Ric', '1998-12-15', 1, 0, 0, '62457'), (7, 'RUPPERT', 'Jose', '1989-07-14', 1, 1, 1, '10123'), (8, 'EVERAERE', 'Delphine', '2002-05-21', 2, 1, 0, '96000'), (9, 'MOULIN', 'Nicolas', '1975-08-09', 1, 0, 1, '75000'), (10, 'BALLE', 'Sylvie', '1982-04-10', 2, 1, 0, '59190') ; /* Création de la table PRODUIT */ CREATE TABLE PRODUIT ([ID_PRODUIT] int, [DENOMINATION] varchar(30), [CATEGORIE] varchar(30), [DATE_CREATION] datetime, [DATE_DEBUT_COMMERCIALISATION] datetime, [DATE_FIN_COMMERCIALISATION] datetime, [PRIX_UNITAIRE] numeric(17,2), CONSTRAINT PK_ID_PRODUIT PRIMARY KEY ([ID_PRODUIT])) ; INSERT INTO PRODUIT ([ID_PRODUIT], [DENOMINATION], [CATEGORIE], [DATE_CREATION], [DATE_DEBUT_COMMERCIALISATION], [DATE_FIN_COMMERCIALISATION], [PRIX_UNITAIRE]) VALUES (1, 'TEE-SHIRT', 'HAUT', '2021-08-20 00:00:00', '2021-09-18 00:00:00', '2022-12-10 00:00:00', 29.99), (2, 'CHAUSSURES', 'CHAUSSES', '2022-01-04 00:00:00', '2021-02-06 00:00:00', '2022-07-01 00:00:00', 41.99), (3, 'PANTALON', 'BAS', '2021-11-28 00:00:00', '2021-12-25 00:00:00', '2022-05-10 00:00:00', 32.99), (4, 'GILET', 'HAUT', '2021-11-04 00:00:00', '2021-11-25 00:00:00', '2022-12-07 00:00:00', 42.99), (5, 'PULL', 'HAUT', '2021-12-25 00:00:00', '2022-01-31 00:00:00', '2023-05-22 00:00:00', 38.99), (6, 'MANTEAU', 'HAUT', '2021-12-08 00:00:00', '2021-12-24 00:00:00', '2022-03-06 00:00:00', 37.99), (7, 'BASQUETTES', 'CHAUSSES', '2022-04-17 00:00:00', '2022-05-27 00:00:00', '2022-07-25 00:00:00', 42.99), (8, 'CULOTTE', 'LINGERIE', '2021-09-17 00:00:00', '2021-09-22 00:00:00', '2022-02-08 00:00:00', 25.99), (9, 'BOXER', 'LINGERIE', '2022-01-09 00:00:00', '2022-01-29 00:00:00', '2023-07-29 00:00:00', 12.99), (10, 'JUPE', 'BAS', '2022-06-18 00:00:00', '2022-06-30 00:00:00', '2023-04-15 00:00:00', 31.99) ; /* Création de la table MAGASIN */ CREATE TABLE MAGASIN ([ID_MAGASIN] int, [NOM_MAGASIN] varchar(30), [DATE_CREATION] date, CONSTRAINT PK_ID_MAGASIN PRIMARY KEY ([ID_MAGASIN])) ; INSERT INTO MAGASIN ([ID_MAGASIN], [NOM_MAGASIN], [DATE_CREATION]) VALUES (1, 'LILLE', '2019-08-01'), (2, 'SAINT_OMER', '2020-05-07'), (3, 'DUNKERQUE', '2020-12-01') ; /* Création de la table COMMANDE */ CREATE TABLE COMMANDE ([ID_COMMANDE] int, [ID_CLIENT] int, [ID_MAGASIN] int, [DATE_ACHAT] datetime, [REMISE_COMMANDE] numeric(17,2), CONSTRAINT PK_ID_COMMANDE PRIMARY KEY ([ID_COMMANDE]), CONSTRAINT FK_ID_MAGASIN_T_COMMANDE FOREIGN KEY ([ID_MAGASIN]) REFERENCES MAGASIN(ID_MAGASIN), CONSTRAINT FK_ID_CLIENT_T_COMMANDE FOREIGN KEY ([ID_CLIENT]) REFERENCES CLIENT(ID_CLIENT)) ; INSERT INTO COMMANDE ([ID_COMMANDE], [ID_CLIENT], [ID_MAGASIN], [DATE_ACHAT], [REMISE_COMMANDE]) VALUES (1, 1, 1, '2022-06-01 12:02:08', 0.00), (2, 3, 1, '2021-12-24 17:55:12', 0.00), (3, 2, 2, '2022-08-12 14:32:01', 5.00), (4, 2, 2, '2022-07-04 09:21:54', 4.99), (5, 10, 1, '2022-04-08 10:12:45', 0.00), (6, 1, 1, '2022-09-12 15:34:51', 0.00) ; /* Création de la table LIGNE_COMMANDE */ CREATE TABLE LIGNE_COMMANDE ([ID_LIGNE_COMMANDE] int, [ID_COMMANDE] int, [ID_PRODUIT] int, [QUANTITE] int, [REMISE_PAR_PRODUIT] numeric(17,2), CONSTRAINT PK_ID_LIGNE_COMMANDE PRIMARY KEY ([ID_LIGNE_COMMANDE]), CONSTRAINT FK_ID_COMMANDE_T_LIGNE_COMMANDE FOREIGN KEY ([ID_COMMANDE]) REFERENCES COMMANDE(ID_COMMANDE), CONSTRAINT FK_ID_CLIENT_T_LIGNE_COMMANDE FOREIGN KEY ([ID_PRODUIT]) REFERENCES PRODUIT(ID_PRODUIT)) ; INSERT INTO LIGNE_COMMANDE ([ID_LIGNE_COMMANDE], [ID_COMMANDE], [ID_PRODUIT], [QUANTITE], [REMISE_PAR_PRODUIT]) VALUES (1, 1, 1, 1, 0.00), (2, 1, 4, 1, 0.00), (3, 1, 9, 2, 3.99), (4, 2, 8, 3, 12.99), (5, 3, 1, 1, 0.00), (6, 3, 4, 1, 0.00), (7, 3, 10, 1, 0.00), (8, 4, 7, 1, 9.99), (9, 5, 3, 1, 0.00), (10, 5, 4, 2, 0.00), (11, 6, 10, 2, 5.99) ;