142 lines
2.3 KiB
SQL
142 lines
2.3 KiB
SQL
//TP2
|
|
|
|
//1
|
|
CREATE VIEW VinDesViticulteurs(idVin, Cru, idViticulteur, nomViticulteur)
|
|
AS SELECT numVin, Cru, numVitic, nom
|
|
FROM Vin NATURAL JOIN Viticulteur
|
|
WHERE region = 'BOURGOGNE';
|
|
|
|
//2
|
|
CREATE VIEW BuvCom(numero, nom, nbrCommandes)
|
|
AS (SELECT numBuveur, nom, COUNT(*)
|
|
FROM Buveur NATURAL JOIN Commande
|
|
GROUP BY numBuveur, nom)
|
|
UNION
|
|
(SELECT numBuveur, nom, NULL
|
|
FROM Buveur
|
|
WHERE numBuveur NOT IN(SELECT numBuveur FROM Commande));
|
|
|
|
INSERT INTO BuvCom VALUES(1750, 'JAMET', null);
|
|
|
|
UPDATE BuvCom
|
|
SET
|
|
WHERE ;
|
|
|
|
DELETE BuvCom
|
|
;
|
|
|
|
DROP VIEW BuvCom;
|
|
|
|
//3
|
|
SELECT numVitic, nom
|
|
FROM Viticulteur NATURAL JOIN Vin NATURAL JOIN Commande;
|
|
|
|
//4
|
|
SELECT numVitic, nom
|
|
FROM Vin V NATURAL JOIN Viticulteur
|
|
WHERE NOT EXISTS(
|
|
SELECT *
|
|
FROM Commande
|
|
WHERE Commande.numVin = V.numVin);
|
|
|
|
//5
|
|
SELECT
|
|
FROM
|
|
WHERE ;
|
|
|
|
//6
|
|
SELECT
|
|
FROM
|
|
WHERE ;
|
|
|
|
//7
|
|
SELECT
|
|
FROM
|
|
WHERE ;
|
|
|
|
|
|
//TP3
|
|
//Q1
|
|
//1
|
|
ALTER TABLE Buveur ADD CODE_TARIF varchar2(50) DEFAULT 'BASE';
|
|
|
|
//2
|
|
CREATE TABLE Cave_Cooperative(
|
|
NumCav NUMBER PRIMARY KEY,
|
|
Ville_Cav varchar2(30) NOT NULL,
|
|
NumResp NUMBER NOT NULL REFERENCES Viticulteur
|
|
);
|
|
|
|
//3
|
|
ALTER TABLE Viticulteur ADD NumCav NUMBER REFERENCES Cave_Cooperative NOT NULL;
|
|
|
|
//4
|
|
INSERT INTO Cave_Cooperative VALUES(10, 'MACON', 01);
|
|
|
|
//5
|
|
INSERT INTO Viticulteur VALUES(40, 'WEISS', 'JACQUES', 'COLMAR', null);
|
|
INSERT INTO Cave_Cooperative VALUES(12, 'STRASBOURG', 40);
|
|
COMMIT;
|
|
|
|
//Q2
|
|
|
|
//1
|
|
CREATE TABLE Buveur2(
|
|
NumBuveur NUMBER PRIMARY KEY,
|
|
Nom VARCHAR2(25) NOT NULL,
|
|
Prenom VARCHAR2(25),
|
|
Ville VARCHAR2(30) NOT NULL
|
|
);
|
|
ALTER TABLE Buveur2 ADD CODE_TARIF varchar2(50) DEFAULT 'BASE';
|
|
|
|
insert into Buveur2 (select * from raban.buveur);
|
|
//2
|
|
ALTER TABLE Buveur2 DROP COLUMN Ville;
|
|
|
|
//3
|
|
CREATE TABLE Commande2(
|
|
NumCom NUMBER PRIMARY KEY,
|
|
NumBuveur NUMBER REFERENCES Buveur2,
|
|
NumVin NUMBER REFERENCES Vin,
|
|
Qtte INT NOT NULL,
|
|
DateCom DATE NOT NULL
|
|
);
|
|
|
|
//4
|
|
ALTER TABLE Buveur2 DROP COLUMN numBuveur;
|
|
|
|
//Q3
|
|
|
|
//1
|
|
ALTER TABLE Buveur2 MODIFY Nom varchar2(15);
|
|
|
|
//2
|
|
ALTER TABLE Buveur2 MODIFY Prenom varchar2(5);
|
|
|
|
//3
|
|
ALTER TABLE Buveur2 RENAME COLUMN Nom TO NomBuveur;
|
|
|
|
//4
|
|
ALTER TABLE Buveur2 RENAME COLUMN NumBuveur TO idBuv;
|
|
|
|
//5
|
|
/*Suivant si les données,on peut modifier le type, et on peut modifier le nom*/
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|