\i /home/tazio/Cours/IBD/TP4/vin.sql

#QUESTION 2

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;

SELECT MIN(Article.prixht), MAX(Article.prixht), AVG(Article.prixht)
FROM Article;

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;

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 );

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

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);

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);


