Corrigé SQL

Corrigés 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 PUF.NP = P.NP 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 PUF.NP = P.NP 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 PUF.NP = P.NP and PUF.NF = F.NF and PUF.NU = U.NU
and (U.Ville = 'Londres' or U.Ville = '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 PUF.NF = F.NF and PUF.NU = U.NU and U.Ville = F.Ville 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 PUF.NF = F.NF and PUF.NU = U.NU and F.Ville = 'Londres'
and U.Ville = '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 PUF.NU
from PUF, F, U
where PUF.NF = F.NF and PUF.NU = U.NU and U.Ville ( F.Ville 10. Donner les numéros des fournisseurs qui approvisionnent à la fois les
usines n°1 et n°2 10a. select distinct first.NF
from PUF first, PUF second
where first.NF = second.NF and first.NU = 1 and second.NU = 2 10b. select distinct NF
from PUF
where NU = 2
and NF in (select NF from PUF where NU = 1 ) 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 p1.Poids > 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 PUF.NP = P.NP and PUF.NF = F.NF
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 puf.NF
from PUF puf, PUF puf1, PUF puf2, P
where couleur = 'rouge'
and P.NP = puf2.NP and puf2.NF = puf1.NF and puf1.NP =
puf.NP 14b. select distinct NF from PUF
where NP in
(select NP
from PUF
where NF in
(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 F.Ville, NP, U.Ville
from PUF, U, F
where PUF.NF = F.NF and PUF.NU = U.NU
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 F.Ville, NP, U.Ville
from PUF, U, F
where F.Ville ( U.Ville and PUF.NF = F.NF and PUF.NU = U.NU
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 P.NP = PUF.NP and U.NU
= PUF.NU)
)
) 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 F.NF = PUF.NF
and U.NU = PUF.NU
and P.NP = PUF.NP)
)
) 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:
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