SQLize Online / PHPize Online  /  SQLtest Online

/* efesfsefsefe */ /* 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) ; SELECT * FROM PRODUIT WHERE PRIX_UNITAIRE < 30; SELECT * FROM PRODUIT WHERE DATE_CREATION >= '2022-01-04'; SELECT [DENOMINATION], [CATEGORIE] FROM PRODUIT WHERE [CATEGORIE] ='HAUT'; SELECT [DENOMINATION], [CATEGORIE] FROM PRODUIT WHERE [CATEGORIE] <>'HAUT'; SELECT [NOM] FROM CLIENT WHERE [NOM] LIKE '%L%'; SELECT [NOM],[DATE_NAISSANCE] FROM CLIENT WHERE [DATE_NAISSANCE] BETWEEN '1990-01-01'AND '2010-12-31'; SELECT * FROM CLIENT WHERE [NOM] = 'BALLE' OR [NOM] = 'EVERAERE'; SELECT * FROM PRODUIT WHERE ([CATEGORIE] ='HAUT'AND [PRIX_UNITAIRE] > 35) OR ([CATEGORIE] ='LINGERIE'AND [PRIX_UNITAIRE] > 20); SELECT [ID_CLIENT], [PRENOM], [NOM], CONCAT(NOM, PRENOM) AS PRENOM_NOM FROM CLIENT; SELECT [ID_CLIENT], [PRENOM], [NOM], CONCAT(PRENOM,' ', NOM) AS PRENOM_NOM FROM CLIENT; SELECT [ID_CLIENT], [PRENOM], [NOM], CONCAT(PRENOM,' ', CONCAT(UPPER(LEFT(NOM,1)),LOWER(RIGHT(NOM, LEN(NOM)-1)))) AS PRENOM_NOM, CONCAT(UPPER(LEFT(NOM,1)),LOWER(RIGHT(NOM, LEN(NOM)-1))) AS NOM_BIS FROM CLIENT; SELECT * FROM CLIENT WHERE MONTH(DATE_NAISSANCE) = 5 AND DAY(DATE_NAISSANCE) = 21; SELECT *, CASE WHEN LEFT (CODE_POSTAL, 2) = 59 THEN '1' ELSE '0' END as TOP_59 FROM CLIENT; SELECT *, CEILING (PRIX_UNITAIRE) AS PRIX_UNITAIRE_ARRONDI FROM PRODUIT; SELECT [CATEGORIE], COUNT(ID_PRODUIT) as SOMME_QUANTITE_PRODUIT FROM PRODUIT GROUP BY CATEGORIE; SELECT * FROM CLIENT ORDER BY (DATE_NAISSANCE); SELECT YEAR(DATE_ACHAT) AS YEAR, COUNT(ID_COMMANDE) AS NOMBRE_COMMANDE FROM COMMANDE GROUP BY YEAR(DATE_ACHAT) ORDER BY YEAR(DATE_ACHAT) DESC; SELECT CATEGORIE, COUNT(ID_PRODUIT) AS NOMBRE_PRODUIT, AVG (PRIX_UNITAIRE) AS PRIX_MOYEN FROM PRODUIT GROUP BY CATEGORIE HAVING AVG(PRIX_UNITAIRE) > 40;