--Solution du laboratoire 1
--Question 1

--2)
--CREATE DATABASE StockClg;
--3)
use stocksClg;

--DROP TABLE lignecommande;
--DROP TABLE Commandes;
--DROP TABLE ARTICLES;
--DROP TABLE Clients;
go;
CREATE TABLE Clients
(
	idClient SMALLINT IDENTITY(1,1),
	nom VARCHAR(20) NOT NULL,
	prenom VARCHAR(20) NOT NULL,
	typeClient CHAR(1) DEFAULT 'r',
	CONSTRAINT pk_Clients_idClient PRIMARY KEY(idClient),
	CONSTRAINT ck_Clients_typeClient CHECK(typeClient = 'r' OR typeClient = 'o' OR typeClient = 'v')
);

CREATE TABLE Articles
(
	idArticle SMALLINT IDENTITY(1,1),
	descriptions VARCHAR(30) NOT NULL,
	prix MONEY NOT NULL,
	quantitestock SMALLINT NOT NULL,
	CONSTRAINT pk_Articles_idArticle PRIMARY KEY (idArticle),
	CONSTRAINT ck_Articles_quantitestock CHECK(quantitestock >= 1),
	CONSTRAINT ck_Articles_prix CHECK(prix > 0)
);

CREATE TABLE Commandes
(
	idcommande INT IDENTITY(10,1),
	dateCommande DATE NOT NULL,
	idClient SMALLINT NOT NULL,
	CONSTRAINT pk_Commandes_idcommande PRIMARY KEY(idcommande),
	CONSTRAINT fk_Commandes_idClient FOREIGN KEY(idClient) REFERENCES Clients(idClient)
);

CREATE TABLE LigneCommande
(
	idcommande INT NOT NULL,
	idArticle SMALLINT NOT NULL,
	quantite INT NOT NULL,
	CONSTRAINT fk_LigneCommande_idArticle FOREIGN KEY(idArticle) REFERENCES Articles(idArticle),
	CONSTRAINT fk_LigneCommande_idcommande FOREIGN KEY(idcommande) REFERENCES Commandes(idcommande)
);

--4)
ALTER TABLE LigneCommande ADD CONSTRAINT PK_ligneCommande 
 PRIMARY KEY(idCommande,idArticle);

--5)
ALTER TABLE Clients ADD adresse VARCHAR(50);

--6)
EXEC SP_RENAME 'LigneCommande.quantite', 'quantiteCommande', 'COLUMN';


--7)
ALTER TABLE LigneCommande ADD CONSTRAINT ck_quantite
 CHECK(quantiteCommande >= 1);

--8)

/*
INSERT INTO Clients(nom, prenom,typeClient, adresse) VALUES 
('Patoche','Alain','v','14 rue Jupiter, Montréal'),
('LeRoy','Singe','v','11 avenue de la Lune, Laval'),
('LeRigolo','Coluche','r','177 rue de Venus, Montréal'),
('Lefou','Ducoin','o','12 rue de Saturne Laval');
INSERT INTO clients VALUES('Le magnifique','Simba','r','789 rue des Chats, Laval');
INSERT INTO clients VALUES('Ce client','prenom','r','789 rue des clients, Montréal');

--
INSERT INTO Articles(descriptions, prix, quantitestock) VALUES
('Imprimantes Laser HP 8000',700,20),
('Écrans tactiles 19p',550,45),
('Routeurs sans fils Azus',200,75),
('Disques durs SSD 500 Go',175,45);

INSERT INTO Articles(descriptions, prix, quantitestock) VALUES ('Chaises de bureau',400,20);
INSERT INTO Articles(descriptions, prix, quantitestock) VALUES ('Cet Article',400,5);
INSERT INTO Articles(descriptions, prix, quantitestock) VALUES ('Mon article',100,10);

---

INSERT INTO Commandes(datecommande,idClient) VALUES('2023-08-21',1);
INSERT INTO Commandes(datecommande,idClient) VALUES('2023-08-28',1);
INSERT INTO Commandes(datecommande,idClient) VALUES('2022-12-05',1);
INSERT INTO Commandes(datecommande,idClient) VALUES('2022-11-17',2);
INSERT INTO Commandes(datecommande,idClient) VALUES('2022-09-12',2);
INSERT INTO Commandes(datecommande,idClient) VALUES('2023-08-12',3);
----------------------------------------

--commande numéro 10
INSERT INTO Lignecommande VALUES(10,1,2);
INSERT INTO Lignecommande VALUES(10,2,3);
INSERT INTO Lignecommande VALUES(10,3,5);
INSERT INTO Lignecommande VALUES(10,4,6);
--commande 11
INSERT INTO Lignecommande  VALUES(11,4,2);
INSERT INTO Lignecommande  VALUES(11,3,3);

--commnande 12
INSERT INTO Lignecommande  VALUES(12,3,5);

---commande numéro 13
INSERT INTO Lignecommande  VALUES(13,1,1);
INSERT INTO Lignecommande VALUES(13,2,5);
INSERT INTO Lignecommande  VALUES(13,3,6);
--commnade14
INSERT INTO Lignecommande  VALUES(14,3,1);
INSERT INTO Lignecommande  VALUES(14,4,1);

--commande 15
INSERT INTO Lignecommande  VALUES(15,3,1);



SELECT * FROM Articles;
SELECT * FROM Clients;
SELECT * FROM Commandes;
SELECT * FROM LigneCommande;
*/
--Question 2

--1)
SELECT * FROM Clients
WHERE adresse LIKE '%Montréal%';

--2)
SELECT descriptions FROM Clients
INNER JOIN Commandes ON Clients.idClient = Commandes.idClient
INNER JOIN LigneCommande ON Commandes.idcommande = LigneCommande.idcommande
INNER JOIN Articles ON LigneCommande.idArticle = Articles.idArticle
WHERE Clients.nom = 'Patoche' AND LigneCommande.idcommande = 10;

--3)
SELECT Clients.nom, Clients.prenom 
FROM Clients
LEFT OUTER JOIN Commandes ON Clients.idClient = Commandes.idClient
WHERE Commandes.idcommande IS NULL;

--4)
SELECT Articles.idArticle, Articles.descriptions, 
SUM(LigneCommande.quantiteCommande)AS quantiteCommande
FROM Articles
LEFT OUTER JOIN LigneCommande 
ON Articles.idArticle = LigneCommande.idArticle
GROUP BY Articles.idArticle, Articles.descriptions;


--5)
SELECT COUNT(Commandes.idcommande) AS NbCommandes FROM Clients
INNER JOIN Commandes ON Clients.idClient = Commandes.idClient
WHERE Clients.nom = 'Patoche';


--6)
SELECT Clients.nom, COUNT(Commandes.idcommande) AS nbTotalCommandes 
FROM Clients
LEFT OUTER JOIN Commandes ON Clients.idClient = Commandes.idClient
GROUP BY Clients.nom order by nbTotalCommandes desc;

--7)- a
SELECT TOP 1 Clients.nom, COUNT(Commandes.idcommande) AS nbCommandes 
FROM Clients
LEFT OUTER JOIN Commandes ON Clients.idClient = Commandes.idClient
GROUP BY Clients.nom
ORDER BY COUNT(Commandes.idcommande) DESC;

---7-b
go;
--on cree une vue qui va contenir le nombre total de commandes pour
--chaque client.
CREATE VIEW vTotalCommandes as SELECT  Clients.nom, COUNT(Commandes.idcommande) AS nbCommandes 
FROM Clients
INNER JOIN Commandes ON Clients.idClient = Commandes.idClient
GROUP BY Clients.nom;


--- À partir de la vue nous ramènerons tous les clients qui le 
--nombre de commande égal à MAX.

 SELECT  nom
	FROM vTotalCommandeS
	where nbCommandes = (select max(nbCommandes) from vTotalCommandes)

INSERT INTO Commandes(datecommande,idClient) VALUES('2025-09-12',2);

-- Remarques
--1- SELECT TOP N, ne marcherait pas dans ce cas. 
--2- Nous avons besoin d'une sous requête ou d'une vue.
--Une vue est mieu dans ce cas
--3 Dans Oracle nous avons la possibilité de faire SELECT MAX(COUNT(etc))..
--alors que pour SQL Server on ne peut pas. 
--8)
ALTER TABLE LigneCommande ADD montant MONEY;

--9)

UPDATE LigneCommande 
SET LigneCommande.Montant = LigneCommande.quantiteCommande * (SELECT prix FROM Articles
																WHERE idArticle = 1)
WHERE LigneCommande.idArticle = 1;

UPDATE LigneCommande 
SET LigneCommande.Montant = LigneCommande.quantiteCommande * (SELECT prix FROM Articles
																WHERE idArticle = 2)
WHERE LigneCommande.idArticle = 2;

UPDATE LigneCommande 
SET LigneCommande.Montant = LigneCommande.quantiteCommande * (SELECT prix FROM Articles
																WHERE idArticle = 3)
WHERE LigneCommande.idArticle = 3;

UPDATE LigneCommande 
SET LigneCommande.Montant = LigneCommande.quantiteCommande * (SELECT prix FROM Articles
																WHERE idArticle = 4)
WHERE LigneCommande.idArticle = 4;

UPDATE LigneCommande 
SET LigneCommande.Montant = LigneCommande.quantiteCommande * (SELECT prix FROM Articles
																WHERE idArticle = 5)
WHERE LigneCommande.idArticle = 5;

UPDATE LigneCommande 
SET LigneCommande.Montant = LigneCommande.quantiteCommande * (SELECT prix FROM Articles
																WHERE idArticle = 6)
WHERE LigneCommande.idArticle = 6;

UPDATE LigneCommande 
SET LigneCommande.Montant = LigneCommande.quantiteCommande * (SELECT prix FROM Articles
																WHERE idArticle = 7)
WHERE LigneCommande.idArticle = 7;

-- Une solution avec les curseurs viendra
-- Il y'a la possibilité de faire ne jointure dans un UPDATE dans ce SGBD, mais 
-- ce n'est pas conseillé.

--10)
SELECT LigneCommande.idcommande, SUM(LigneCommande.Montant) AS SommesMontants 
FROM LigneCommande
GROUP BY LigneCommande.idcommande;

--11)

SELECT Clients.nom, Commandes.idcommande, Commandes.dateCommande 
FROM Commandes
INNER JOIN Clients ON Commandes.idClient = Clients.idClient
WHERE Commandes.dateCommande >= '2022-10-01'
AND Commandes.dateCommande < '2023-01-01';
GO;

--12)
CREATE VIEW Vcommande_Client AS
SELECT Clients.nom, Commandes.idcommande, SUM(LigneCommande.Montant) AS sommeCommandes 
FROM Clients
INNER JOIN Commandes ON Clients.idClient = Commandes.idClient
INNER JOIN LigneCommande ON Commandes.idcommande = LigneCommande.idcommande
GROUP BY Clients.nom, Commandes.idcommande;
GO;

--13)
SELECT nom, SUM(sommeCommandes) AS totalSommeCommandes 
FROM Vcommande_Client
GROUP BY nom;

--Question 3

--1)
SET IDENTITY_INSERT Clients ON;
INSERT INTO Clients (idClient, nom, prenom, adresse) 
VALUES(50, 'Lenom', 'Leprenom', 'l''Adresse');

select * from clients;
--2)
SET IDENTITY_INSERT Clients OFF;
INSERT INTO Clients (nom, prenom, adresse) 
VALUES('Yacoub', 'Simba', '12 rue LeChat, Chaval');
/*Quelle est la valeur de la clé primaire pour cet enregistrement ? 
La valeur de la clé primaire de cet enregistrement est de 51.
Qu’est-ce que nous pouvons conclure ?
Lorsque nous mettons l'IDENTITY_INSERT à ON, nous pouvons
--alors mettre la valeur voulue pour cette clé primaire, pour cet enregistrement. 
Mais lorsque nous remettons à OFF l'IDENTITY_INSERT et que nous ajoutons un enregistrement à cette table, alors la clé primaire va être
incrémenté selon le dernier enregistrement entré. Donc, certaines valeurs ne seront peut-être pas utilisées sauf si on fait manuellement des INSERT,
en mettant l'IDENTITY_INSERT à ON avant.
*/

--3)
/*Non, nous n'aurions pas pu déclarer Idcommande ou idArticle avec 
--l'option IDENTITY puisque ces attributs sont utilisés comme référence à une
PRIMARY KEY d'une autre table à l'aide d'un FOREIGN KEY.
*/

--4)
SELECT nom,prenom, typeClient =
 CASE typeClient
 WHEN 'v' THEN 'VIP'
 WHEN 'r' THEN 'Régulier'
 WHEN 'o' THEN 'Occasionnel'
 ELSE 'Le type de client est inconnu'
 END
FROM Clients;

--5)--Voir en classe.