-- 1. Lister tous les vins de la région 'Alsace', et, pour chacun des vins, le nombre d'articles correspondant.
SELECT VinAlsace.nom, 
(SELECT count(idarticle) FROM Article WHERE vin = VinAlsace.idvin)
FROM (SELECT Vin.nom AS nom, Vin.idvin AS idvin
	FROM Vin JOIN Region ON Region.idregion=vin.provenance
	WHERE region.nom = 'Alsace') AS VinAlsace
GROUP BY VinAlsace.idvin, VinAlsace.nom; -- 1

-- 2. Indiquer le prix maximum, minimum, moyen des vins en général, puis par région.
SELECT MIN(Article.prixht), MAX(Article.prixht), AVG(Article.prixht)
FROM Article;--2

SELECT MIN(Article.prixht), MAX(Article.prixht), AVG(Article.prixht), Vin.provenance
FROM VIN JOIN Article ON Vin.idvin = Article.vin
GROUP BY Vin.provenance;--2

-- 3. Lister, pour chaque client, le vin le moins cher en 75cl disponible dans la région.
SELECT C.nom, Vin.nom, Article.millesime, Article.prixht, Article.idarticle
FROM Client as C
JOIN VIN ON Vin.provenance = C.habite
JOIN Article ON Vin.idvin = Article.vin
WHERE Article.contenance = 75 
GROUP BY C.idclient, Article.idarticle,vin.nom
HAVING Article.prixht = (
	SELECT MIN(Article.prixht)
	FROM Article JOIN Vin ON Vin.idvin = Article.vin
	WHERE Vin.provenance = C.habite AND
	Article.contenance = 75 );--3

--4. Lister les vins disponibles en 150cl, ainsi que leur prix et leur millésime.

SELECT DISTINCT Vin.nom, Article.millesime, Article.prixht
FROM Vin
JOIN Article ON Vin.idvin = Article.vin
WHERE Article.contenance = 150;--4

-- 5. Lister les appellations dont certains des vins disponibles sont antérieurs à 1992. Lister, avec
--chacune de ces appellations, le prix maximum des vins et l'année du vin en question.

SELECT V.appellation, Article.millesime, Article.prixht
FROM Vin AS V JOIN Article ON Article.vin = V.idvin
GROUP BY V.appellation, Article.idarticle
HAVING MIN(Article.millesime) <= TO_DATE('1992','YYYY')
AND Article.prixht = (
SELECT MAX(Article.prixht)
FROM Article JOIN Vin ON Article.vin = Vin.idvin
WHERE Vin.appellation = V.appellation);--5

--6. Pour chaque appellation, lister le plus vieux des vins rouges.

SELECT V.appellation, Article.millesime, V.nom
FROM Vin AS V JOIN Article ON V.idvin = Article.vin
WHERE V.type = 'rouge'
AND Article.millesime =(
SELECT MIN(Article.millesime)
FROM Article JOIN Vin ON Vin.idvin = Article.vin
WHERE vin.type = 'rouge'
AND vin.appellation = V.appellation); --6
