Correction exercice base de donnée (les requête Serie 1)

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' :

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;

4.    Les paires de numéro de fournisseur et de produit situés dans la même ville :
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');

15. 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';

16. Ajouter 100 à la quantité de tous les produits avec une quantité inférieure à 200

UPDATE détail SET qty = qty+100 where qty <200

Enoncé