33 lines
790 B
Plaintext
33 lines
790 B
Plaintext
|
Q1)
|
||
|
|
CREATE VIEW VinDesViticulteurs (idVin, Cru, idViticulteur, NomViticulteur)
|
||
|
|
AS SELECT NumVin, Cru, NumVitic, Nom
|
||
|
|
FROM Vin natural join Viticulteur
|
||
|
|
WHERE Region='BOURGOGNE';
|
||
|
|
|
||
|
|
Q2)
|
||
|
|
CREATE VIEW CommandeBuveur(num, nom, nbCommande)
|
||
|
|
AS SELECT NumBuveur, Nom, COUNT(*)
|
||
|
|
FROM Buveur NATURAL JOIN Commande
|
||
|
|
GROUP BY NumBuveur, Nom;
|
||
|
|
|
||
|
|
Q3)
|
||
|
|
SELECT NumVitic, Nom
|
||
|
|
FROM Viticulteur NATURAL JOIN Vin
|
||
|
|
WHERE NumVin IN(SELECT NumVin FROM Commande);
|
||
|
|
|
||
|
|
Q4)
|
||
|
|
SELECT NumVitic, Nom
|
||
|
|
FROM Viticulteur
|
||
|
|
WHERE NumVitic NOT IN(SELECT NumVitic FROM Vin NATURAL JOIN Commande);
|
||
|
|
|
||
|
|
Q5)
|
||
|
|
SELECT NumVitic, Nom
|
||
|
|
FROM Viticulteur NATURAL JOIN Vin NATURAL JOIN Commande
|
||
|
|
WHERE Ville='PARIS'
|
||
|
|
INTERSECT
|
||
|
|
SELECT NumVitic, Nom
|
||
|
|
FROM Viticulteur NATURAL JOIN Vin NATURAL JOIN Commande
|
||
|
|
WHERE Ville='MACON'
|
||
|
|
|
||
|
|
Q6)
|