Cours de Bases de Données – Exercice « Banque » - Corrigé

Soit le schéma de base de donnée relationnel suivant :

AGENCE (Num_Agence, Nom, Ville, Actif)
CLIENT (Num_Client, Nom, Ville)
COMPTE (Num_Compte, Num_Agence, Num_Client, Solde)
EMPRUNT (Num_Emprunt, Num_Agence, Num_Client, Montant)

Ecrire les requêtes suivantes en SQL :

1. Liste des agences ayant des comptes-clients
select distinct Nom from AGENCE, COMPTE
where AGENCE.Num_Agence = COMPTE.Num_Agence
2. Clients ayant un compte à “La Rochelle”
select [Link] from CLIENT, AGENCE, COMPTE
where AGENCE.Num_Agence = COMPTE.Num_Agence
and CLIENT.Num_Client = COMPTE.Num_Client
and [Link] = “La Rochelle”
3. Clients ayant un compte ou un emprunt à “La Rochelle”
select [Link] from CLIENT, AGENCE, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client
and AGENCE.Num_Agence = COMPTE.Num_Agence
and [Link] = “La Rochelle”
union
select [Link] from CLIENT, AGENCE, EMPRUNT
where CLIENT.Num_Client = EMPRUNT.Num_Client
and AGENCE.Num_Agence = EMPRUNT.Num_Agence
and [Link] = “La Rochelle”
4. Clients ayant un compte et un emprunt à “La Rochelle”
select [Link] from CLIENT, AGENCE, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client
and AGENCE.Num_Agence = COMPTE.Num_Agence
and [Link] = “La Rochelle”
intersect
select [Link] from CLIENT, AGENCE, EMPRUNT
where CLIENT.Num_Client = EMPRUNT.Num_Client
and AGENCE.Num_Agence = EMPRUNT.Num_Agence
and [Link] = “La Rochelle”
5. Clients ayant un compte et pas d’emprunt à “La Rochelle”
select [Link] from CLIENT, AGENCE, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client
and AGENCE.Num_Agence = COMPTE.Num_Agence
and [Link] = “La Rochelle”
minus
select [Link] from CLIENT, AGENCE, EMPRUNT
where CLIENT.Num_Client = EMPRUNT.Num_Client
and AGENCE.Num_Agence = EMPRUNT.Num_Agence
and [Link] = “La Rochelle”
6. Clients ayant un compte et nom de la ville où ils habitent
Première solution :
select Nom, Ville from CLIENT, COMPTE
where CLIENT.Num_Client = COMPTE. Num_Client
Deuxième solution :
select Nom, Ville from CLIENT
where Num_Client in (
select Num_Client from COMPTE)
7. Clients ayant un compte à “Paris-Etoile” et nom de la ville où ils habitent
Première solution :
select [Link], [Link] from CLIENT, AGENCE, COMPTE
where CLIENT.Num_Client = COMPTE. Num_Client
and AGENCE.Num_Agence = COMPTE.Num_Agence
and [Link] = “Paris-Etoile”
Deuxième solution :
select Nom, Ville from CLIENT
where Num_Client in (
select Num_Client from COMPTE where Num_Agence in (
select Num_Agence from AGENCE where Nom = “Paris-Etoile”))
8. Clients ayant un compte dans une agence où “Claude” a un compte
Première solution :
select Nom from CLIENT, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client and Num_Agence in (
select Num_Agence from CLIENT, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client and Nom = “Claude”)
Deuxième solution :
select Nom from CLIENT where Num_Client in (
select Num_Client from COMPTE where Num_Agence in (
select Num_Agence from CLIENT, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client and Nom = “Claude”))
9. Agences ayant un actif plus élevé que toute agence d'“Orsay”
select Nom from AGENCE where Actif > all (
select Actif from AGENCE where Ville = “Orsay”)
10. Clients ayant un compte dans chaque agence d'“Orsay”
11. Clients ayant un compte dans au-moins une agence d'“Orsay”
Première solution :
select Nom from CLIENT where Num_Client in (
select Num_Client from COMPTE where Num_Agence in (
select Num_Agence from AGENCE where Ville = “Orsay”))
Deuxième solution :
select [Link] from CLIENT, COMPTE, AGENCE
where CLIENT.Num_Client = COMPTE.Num_Client
and COMPTE.Num_Agence = AGENCE.Num_Agence
and [Link] = “Orsay”
12. Emprunteurs de l'agence “Paris-Rambuteau” classés par ordre alphabétique
select Nom from CLIENT where Num_Client in (
select Num_Client from EMPRUNT where Num_Agence in (
select Num_Agence from AGENCE where Nom = “Paris-Rambuteau”))
order by Nom
13. Solde moyen des comptes-clients de chaque agence
select Nom, avg(Solde) from AGENCE, COMPTE
where AGENCE.Num_Agence = COMPTE.Num_Agence
group by Nom
14. Solde moyen des comptes-clients des agences dont le solde moyen est > “10 000”
select Nom, avg(Solde) from AGENCE, COMPTE
where AGENCE.Num_Agence = COMPTE.Num_Agence
group by Nom
having avg(Solde) > 10000
15. Nombre de clients habitant “Paris”
select count(*) from CLIENT where Ville = “Paris”
16. Nombre de clients de l'agence “Paris-Bastille” n'ayant pas leur adresse dans la relation CLIENT
Première solution :
select count(*) from CLIENT where Ville = NULL and Num_Client in (
select Num_Client from COMPTE where Num_Agence in (
select Num_Agence from AGENCE where Nom = “Paris-Bastille”))
Deuxième solution :
select count(*) from CLIENT, COMPTE, AGENCE
where Ville = NULL
and CLIENT.Num_Client = COMPTE.Num_Client
and COMPTE.Num_Agence = AGENCE.Num_Agence
and [Link] = “Paris-Bastille”
17. Insérer le n-uplet <Martin, Paris> dans la relation CLIENT
insert into CLIENT values (130765, “Martin”, “Paris”)
18. Diminuer l'emprunt de tous les clients habitant “Marseille” de “5%”
update EMPRUNT set Montant = Montant * 0.95
where Num_Client in (
select Num_Client from CLIENT where Ville = “Marseille”)
19. Fermer les comptes de “Dupont”
Première solution :
delete from COMPTE where Num_Client in (
select Num_Client from CLIENT where Nom = “Dupont”)
Deuxième solution :
delete from COMPTE
where COMPTE.Num_Client = CLIENT.Num_Client
and [Link] = “Dupont”)
20. Supprimer de la relation AGENCE toutes les agences sans client
delete from AGENCE
where Num_Client not in (
select Num_Client from COMPTE where COMPTE.Num_Agence = AGENCE.Num_Agence)
and Num_Client not in (
select Num_Client from EMPRUNT where EMPRUNT.Num_Agence = AGENCE.Num_Agence)