EMNA GAMMOUDI
CREATE TABLE Fournisseur (
constraint pk_fornisseur FNO INT PRIMARY KEY,
FNOM VARCHAR(20),
STATUT number,
VILLE VARCHAR(20)
);
drop table Fournisseur;
drop table FP;
drop table produit;
CREATE TABLE Produit (
constraint pk_produit PNO NUMBER PRIMARY KEY,
PNOM VARCHAR(20),
COULEUR VARCHAR(50),
POIDS DECIMAL(10, 2),
VILLE VARCHAR(20)
);
CREATE TABLE FP (
FNO INT,
PNO NUMBER,
Quantite INT,
CONSTRAINT pk_fp PRIMARY KEY (FNO, PNO),
CONSTRAINT fk_fp1 FOREIGN KEY (FNO) REFERENCES Fournisseur(FNO),
CONSTRAINT fk_fp2 FOREIGN KEY (PNO) REFERENCES Produit(PNO)
);
INSERT INTO Fournisseur VALUES (1, 'Fournisseur A', 0, 'Tunis');
INSERT INTO Fournisseur VALUES (2, 'Fournisseur B', 0, 'Sfax');
INSERT INTO Fournisseur VALUES (3, 'Fournisseur C', 1, 'Ariana');
INSERT INTO Fournisseur VALUES (4, 'Fournisseur D', 1, 'Marsa');
INSERT INTO Produit VALUES(1,'fromage','blanc',5.00,'Tunis');
INSERT INTO Produit VALUES(2,'lait','blanc',10.00,'Sfax');
INSERT INTO Produit VALUES(3,'baguettes','beige',15.00,'Ariana');
INSERT INTO Produit VALUES(4,'P2','rouge',25.00,'Ariana');
INSERT INTO FP VALUES(1,1,100);
INSERT INTO FP VALUES(2,2,200);
INSERT INTO FP VALUES(3,3,300);
desc FP;
desc Produit;
desc Fournisseur;
select object_name,object_type from user_objects;
SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME =
'PNO' ;
SELECT SUM(Quantite) AS SUM
FROM FP
WHERE PNO = 'P2';
SELECT FNO,FNOM,STATUT FROM Fournisseur where STATUT <(SELECT
AVG(STATUT) FROM Fournisseur);
SELECT [Link], [Link], [Link], [Link]
FROM Fournisseur F, Produit P, FP
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = (SELECT VILLE FROM Fournisseur WHERE FNO = [Link]);
SELECT PNO
FROM FP
GROUP BY PNO
HAVING COUNT(FNO) > 1;
SELECT FNOM
FROM Fourniseur
WHERE FNO NOT IN (SELECT FNO FROM FP);
fournisseur F2.
SELECT DISTINCT [Link]
FROM FP FP1
WHERE [Link] IN (SELECT PNO FROM FP WHERE FNO = 2);
ALTER TABLE Fourniseur
ADD CONSTRAINT chk_statut CHECK (STATUT BETWEEN 10 AND 50);
SELECT CONSTRAINT_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'FOURNISEUR';
INSERT INTO Fourniseur VALUES (7, 'AHMED', 17, 'Tunis');
ALTER TABLE Produit
MODIFY PNO VARCHAR2(10);
DELETE FROM Fourniseur
WHERE FNO = 7;
