54 lines
983 B
MySQL
54 lines
983 B
MySQL
|
//1.1
|
||
|
|
SELECT *
|
||
|
|
FROM Vin V LEFT OUTER JOIN Commande C ON V.NumVin = C.NumVin;
|
||
|
|
|
||
|
|
//1.2
|
||
|
|
SELECT NumBuveur
|
||
|
|
FROM Buveur
|
||
|
|
WHERE NumBuveur NOT IN (SELECT NumBuveur FROM Commande);
|
||
|
|
|
||
|
|
//1.3
|
||
|
|
(SELECT NumBuveur, Nom
|
||
|
|
FROM Buveur NATURAL JOIN Commande NATURAL JOIN Vin
|
||
|
|
WHERE Region = 'BOURGOGNE')
|
||
|
|
MINUS
|
||
|
|
(SELECT NumBuveur, Nom
|
||
|
|
FROM Buveur NATURAL JOIN Commande NATURAL JOIN Vin
|
||
|
|
WHERE Region != 'BOURGOGNE');
|
||
|
|
|
||
|
|
//1.4
|
||
|
|
(SELECT NumBuveur, Nom
|
||
|
|
FROM Buveur NATURAL JOIN Commande NATURAL JOIN Vin
|
||
|
|
WHERE Region = 'BOURGOGNE')
|
||
|
|
INTERSECT
|
||
|
|
(SELECT NumBuveur, Nom
|
||
|
|
FROM Buveur NATURAL JOIN Commande NATURAL JOIN Vin
|
||
|
|
WHERE Region = 'BORDEAUX');
|
||
|
|
|
||
|
|
//2.5
|
||
|
|
INSERT INTO Vin
|
||
|
|
VALUES (200, 'ARBOIS', 1985, 'JURA', 20);
|
||
|
|
|
||
|
|
SELECT *
|
||
|
|
FROM Vin;
|
||
|
|
|
||
|
|
SELECT *
|
||
|
|
FROM Livraison;
|
||
|
|
|
||
|
|
SELECT *
|
||
|
|
FROM Vin;
|
||
|
|
//2.6
|
||
|
|
UPDATE Commande
|
||
|
|
SET Qtte = 12
|
||
|
|
WHERE numCom = 7;
|
||
|
|
|
||
|
|
//2.7
|
||
|
|
DELETE FROM Livraison
|
||
|
|
WHERE NumCom IN (SELECT NumCom FROM Buveur NATURAL JOIN Commande WHERE Nom = 'DUPOND');
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
//2.8
|
||
|
|
|
||
|
|
INSERT INTO Commande
|
||
|
|
VALUES (10, 1500, 100, 18, CURRENT);
|