Université Abdel Malek Essaâdi Module : Systèmes d’information et base

Faculté des Sciences et technique – de donné

Tanger Groupe : MIP S4
Département Informatique 2017/2018
TD 4 : Pr. Salma GAOU

Exercice SQL

Soit la base de données relationnelle de schéma :

U (NU, NomU, Ville)

P (NP, NomP, Couleur, Poids)
F (NF, NomF, Statut, Ville)
PUF (NP, NU, NF, Quantité)

1. Donner le numéro, le nom et la Ville de toutes les usines

select * from U

2. Donner le numéro, le nom et la Ville de toutes les usines qui ne sont pas situées à Londres

select * from U
where Ville  'Londres'

3. Donner les numéros des fournisseurs qui approvisionnent l'usine n°1 en produit n°1

select NF from PUF

where NU = 1 and NP = 1

4. Donner le nom et la couleur des produits livrés par le fournisseur n°1

4a select distinct NomP, Couleur

from P, PUF
where [Link] = [Link] and NF = 1

4b. select NomP, Couleur

from P
where NP in ( select NP
from PUF where NF = 1)

5. Donner les numéros des fournisseurs qui approvisionnent l'usine n°1 en un produit rouge

5a. select distinct NF

from PUF, P
where Couleur = 'Rouge' and [Link] = [Link] and NU = 1

5b. select distinct NF from PUF

where NU = 1
and NP in (select NP
from P where Couleur = 'Rouge')

6. Donner les noms des fournisseurs qui approvisionnent une usine de Londres ou de Paris en un
produit rouge

6a. select NomF

from PUF, P, F, U
where Couleur = 'rouge'
and [Link] = [Link] and [Link] = [Link] and [Link] = [Link]

1
 Université Abdel Malek Essaâdi Module : Systèmes d’information et base
Faculté des Sciences et technique – de donné
Tanger Groupe : MIP S4
Département Informatique 2017/2018
TD 4 : Pr. Salma GAOU

and ([Link] = 'Londres' or [Link] = 'Paris')

6b. select NomF

from F
where NF in
(select NF
from PUF
where NP in
(select NP from P
where Couleur = 'Rouge')
and NU in
(select NU from U
where Ville = 'Londres or Ville = 'Paris'))

7. Donner les numéros des produits livrés à une usine par un fournisseur de la même ville

select distinct NP
from PUF, F, U
where [Link] = [Link] and [Link] = [Link] and [Link] = [Link]

8. Donner les numéros des produits livrés à une usine de Londres par un fournisseur de Londres.

8a. select distinct NP

from PUF, F, U
where [Link] = [Link] and [Link] = [Link] and [Link] = ‘Londres’ and [Link] = 'Londres’

8b. select distinct NP

from PUF
where NF in
(select NF
from F
where Ville = 'Londres')
and NU in
(select NU
from U
where Ville = 'Londres')

9. Donner les n° des usines qui ont au moins un fournisseur qui n’est pas de la même ville

select distinct [Link]

from PUF, F, U
where [Link] = [Link] and [Link] = [Link] and [Link]  [Link]

10. Donner les numéros des fournisseurs qui approvisionnent à la fois les usines n°1 et n°2

10a. select distinct [Link]

from PUF first, PUF second
where [Link] = [Link] and [Link] = 1 and [Link] = 2

10b. select distinct NF

from PUF
where NU = 2
and NF in (select NF from PUF where NU = 1 )

2
 Université Abdel Malek Essaâdi Module : Systèmes d’information et base
Faculté des Sciences et technique – de donné
Tanger Groupe : MIP S4
Département Informatique 2017/2018
TD 4 : Pr. Salma GAOU

10c. (select NF from PUF where NU = 1)

intersect
(select NF from PUF where NU = 2)

11. Donner les numéros des usines qui utilisent au moins un produit disponible chez le fournisseur
numéro 3 (le produit n’est pas nécessairement livré à l’usine par le fournisseur numéro 3)

select distinct NU from PUF

where NP in
(select NP from PUF
where NF = 3)

12. Donner le numéro du produit le plus léger (les numéros si plusieurs produits ont ce même poids)

12a. select NP
from P
where Poids in
(select min(Poids) from P)

12b. select NP
from P p1
where not exists (select *
from P
where [Link] > Poids)

12c. select NP
from P
where Poids  all (select Poids from P)

13. Donner les numéros des usines qui ne reçoivent aucun produit rouge d’un fournisseur londonien

13. select NU from U

where NU not in
(select NU
from PUF, U, P, F
where [Link] = [Link] and [Link] = [Link]
and Couleur = 'Rouge' and Ville = 'Londres')

14. Donner les numéros des fournisseurs qui fournissent au moins un produit fourni par au moins un
fournisseur qui fournit au moins un produit rouge

14a. select distinct [Link]

from PUF puf, PUF puf1, PUF puf2, P
where couleur = 'rouge'
and [Link] = [Link] and [Link] = [Link] and [Link] = [Link]

14b. select distinct NF from PUF

where NP in
(select NP
from PUF
where NF in

3
 Université Abdel Malek Essaâdi Module : Systèmes d’information et base
Faculté des Sciences et technique – de donné
Tanger Groupe : MIP S4
Département Informatique 2017/2018
TD 4 : Pr. Salma GAOU

(select NF
from PUF
where NP in
(select NP
from P
where Couleur = 'rouge')))

15. Donner tous les triplets (Ville f, NP, Ville u) telle qu’un fournisseur de la première ville
approvisionne une usine de la deuxième ville avec un produit NP

select distinct [Link], NP, [Link]

from PUF, U, F
where [Link] = [Link] and [Link] = [Link]

16. Donner tous les triplets (Ville f, NP, Ville u) telle qu’un fournisseur de la première ville
approvisionne une usine de la deuxième ville avec un produit NP mais sans les triplets où les deux
villes sont identiques

select distinct [Link], NP, [Link]

from PUF, U, F
where [Link]  [Link] and [Link] = [Link] and [Link] = [Link]

17. Donner les numéros des produits qui sont livrés à toutes les usines de Londres

select NP from P
where not exists (select NU
from U
where Ville = 'Londres'
and not exists (select * from PUF
where [Link] = [Link] and [Link] = [Link])
)
)

Note : Pour tout NP sélectionné, il n’existe pas d’usine à Londres pour laquelle il n’existe pas de produit
NP livré.

18. Donner les n° des fournisseurs qui approvisionnent toutes les usines avec un même produit

select NF from F
where exists
(select NP
from P
where not exists
(select NU
from U
where not exists
(select *
from PUF
where [Link] = [Link]
and [Link] = [Link]

4
 Université Abdel Malek Essaâdi Module : Systèmes d’information et base
Faculté des Sciences et technique – de donné
Tanger Groupe : MIP S4
Département Informatique 2017/2018
TD 4 : Pr. Salma GAOU

and [Link] = [Link])

)
)

Note : Pour tout fournisseur sélectionné, il n’existe pas d’usine qui ne sont pas livrées en produit NP du
fournisseur nf.

20. Donner les numéros des usines qui s'approvisionnent uniquement chez le fournisseur numéro 3

select NU from U
where NU not in (select NU
from PUF
where NF  3)

21. Ajouter un nouveau fournisseur: 45, Alfred, sous-traitant, Chalon.

insert into F: <45, Alfred, sous-traitant, Chalon>

22. Supprimer tous les produits de couleur noire et de numéro compris entre 100 et 199

delete P where NP  100 and NP  199 and couleur = 'noire'

23. Changer la Ville du fournisseur numéro 1, il a déménagé pour Nice

update F: set Ville = 'Nice'

where NF = 1

23. Changer le statut de tous les fournisseurs de Paris et de Lyon ’en sous-traitant’

update F set statut = 'sous-traitant'

where Ville = 'Paris' or Ville = 'Lyon'

24. Donner le nombre d’usines clientes du fournisseur numéro 1

select count(distinct NU)

from PUF
where NF =1

26. Pour chaque produit et chaque usine à laquelle il a été livré, donner le numéro de produit, l’usine et
la quantité totale qui a été livrée

26. select NP, NU, sum(Quantite)

from PUF
group by NP, NU