Corrigé :
I -
Create database SPDB
go
create table S(
Snum varchar(100) primarykey,
Sname varchar(100),
Statut numeric,
City varchar(100)
)
Create table P(
Pnum varchar(100)primarykey,
Pname varchar(100),
Color varchar(100),
Weighte varchar(5),
City varchar(100)
)
Create table SP(
Snum varchar(100)foreign
key references S(Snum),
Pnum varchar(100)foreign
key references P(Pnum),
Qty int
)
Insert into s values('S1','Smith',20,'London')
Insert into s values('S2','Jones',10,'Paris')
insert into s values('S3','Blake',30,'Paris')
insert into s values('S4','Clark',20,'London')
insert into s values('S5','Adams',30,'Athens')
insert into P values('P1','Chaise','Red',12.00,'London')
insert into P values('P2','Table','Green',17.00,'Paris')
insert into P values('P3','Télé','Blue',17.00,'Rome')
insert into P values('P4','Chaise','Red',14.00,'London')
insert into P values('P5','Verre','Blue',12.00,'Paris')
insert into P values('P6','Porte','Red',19.00,'London')
insert into SP values('S1','P1',300)
insert into SP values('S1','P2',200)
insert into SP values('S1','P3',400)
insert into SP values('S1','P4',200)
insert into SP values('S1','P5',100)
insert into SP values('S1','P6',100)
insert into SP values('S2','P1',300)
insert into SP values('S2','P2',400)
insert into SP values('S3','P2',200)
insert into SP values('S4','P2',200)
insert into SP values('S4','P4',300)
insert into SP values('S4','P5',400)
II -
1. Tous les détails sur tous les fournisseur :
SELECT * FROM fournisseurs;
2. Le nom et le statut des fournisseur de 'Paris' :
1. Tous les détails sur tous les fournisseur :
SELECT * FROM fournisseurs;
2. Le nom et le statut des fournisseur de 'Paris' :
SELECT sname, status FROM fournisseurs WHERE city='paris' ;
3. Idem par ordre décroissant de statut :
SELECT sname, status FROM fournisseurs WHERE city='paris' order by status
desc;
SELECT F.snum, P.pnum FROM produits P, Fournisseurs F WHERE P.city = F.city
5. Le numéro des fournisseurs et le nom des produits qu'ils fournissent :
SELECT D.snum, P.pname FROM `détail` D, Produits P
WHERE P.pnum = D.pnum
6. Le nom des fournisseurs et le nom des produits qu'ils fournissent :
SELECT F.sname, P.pname FROM produits P, Fournisseurs F, Détail D
WHERE D.pnum = P.pnum
AND D.snum = F.snum
7. Les numéros de fournisseurs et numéro de produits fournis et situé dans la même ville :
SELECT F.snum, P.pnum FROM produits P, Fournisseurs F
WHERE F.city = P.city
8. Les paires numéros de fournisseurs ayant même statut :
SELECT F.snum, FF.snum FROM Fournisseurs F, Fournisseurs FF WHERE
F.status = FF.status AND F.snum != FF.snum GROUP BY F.status
9. Il y a combiens d'expéditions du produit de numéro 'P2' :
SELECT sum( qty ) FROM détail
WHERE pnum
= 'P2';
10. Nom des fournisseurs du produit de numéro 'P2'
SELECT F.sname FROM détail D, Fournisseurs F
WHERE F.snum = D.snum and D.pnum =`P2`;
11. Nom des fournisseurs qui fournissent au moins un produit de couleur rouge ('Red') :
SELECT F.sname FROM détail D, Fournisseurs F, Produits p
WHERE F.snum = D.snum AND D.pnum = P.pnum
AND P.color = 'red'
Group by
F.sname
12. Nom des fournisseurs qui ne fournissent pas le produit 'P2' :
SELECT F.sname FROM Fournisseurs F WHERE F.snum
NOT IN (SELECT DD.snum FROM détail dd
WHERE dd.pnum = 'P2')
13. Ajouter la colonne <<Price>> dans la table des produits Type INT :
ALTER TABLE `produits` ADD `Price` INT NOT NULL
14. Ajouter la ligne suivante (‘P7’,’PC’,’Blanc’,’15’,’Rome’,’2000’) :
INSERT INTO produits (`pnum`, `pname`, `color`, `weigth`, `city`, `Price`)
VALUES ('P9', 'PC','Blanc', '15', 'Rome', '2000');
- Insertion des prix (Table=260,Télé=300,Verre=29,Porte=130, ,Chaise{(P1)=120,(P4)=140})
UPDATE produits SET Price = '260' where pname ='Table' ;
UPDATE produits SET Price = '300' where pname ='télé' ;
UPDATE produits SET Price = '29' where pname ='Verre';
UPDATE produits SET Price = '130' where pname ='Porte' ;
UPDATE produits SET Price = '120' where pname ='chaise' and pnum ='P1';
UPDATE produits SET Price = '140' where pname ='chaise' and pnum ='P4';
- Ajouter 100 à la quantité de tous
les produits avec une quantité inférieure à 200
UPDATE détail SET qty = qty+100 where qty <200
