Filières : LP MSI
UNIVERSITE ABDELMALEK ESSAADI
Module : SGBDR
Faculté Polydisciplinaire - Tétouan
Pr. AZZOUZ Karim
Travaux Dirigées : le Langage SQL (La Banque)
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)
A) Créer les tables en SQL. (Champs soulignés sont des clés primaires et double soulignéss des clés
étrangères)
CREATE TABLE AGENCE
(Num_Agence char (50), NOTNULL, PRIMARY KEY,
Nom char(50),
Ville char(50),
Addresse char(50),
Ville char(50),
Actif char(25));
CREATE TABLE CLIENT
(Num_Client char (50), NOTNULL, PRIMARY KEY,
Nom char(50),
Ville char(50));
CREATE TABLE COMPTE
(Num_Compte char (50), NOTNULL, PRIMARY KEY,
Num_Agence char(50) FOREIGN KEY references AGENCE(Num_Agence),
Num_Client char(50) FOREIGN KEY references CLIENT (Num_Client),
Solde FLOAT(50));
CREATE TABLE EMPRUNT
(Num_Emprunt char (50), NOTNULL, PRIMARY KEY,
Num_Agence char(50) FOREIGN KEY references AGENCE(Num_Agence),
Num_Client char(50) FOREIGN KEY references CLIENT (Num_Client),
Montant FLOAT(50));
B) 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
1
2. Clients ayant un compte dans la Ville de à “ Tanger ”
select CLIENT.Nom from CLIENT, AGENCE, COMPTE
where AGENCE.Num_Agence = COMPTE.Num_Agence
and CLIENT.Num_Client = COMPTE.Num_Client
and AGENCE.Ville = “ Tanger ”
3. Clients ayant un compte ou un emprunt à “ Tanger ”
select CLIENT.Nom from CLIENT, AGENCE, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client
and AGENCE.Num_Agence = COMPTE.Num_Agence
and AGENCE.Ville = “ Tanger ”
union
select CLIENT.Nom from CLIENT, AGENCE, EMPRUNT
where CLIENT.Num_Client = EMPRUNT.Num_Client
and AGENCE.Num_Agence = EMPRUNT.Num_Agence
and AGENCE.Ville = “ Tanger ”
4. Clients ayant un compte et un emprunt à “ Tanger ”
select CLIENT.Nom from CLIENT, AGENCE, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client
and AGENCE.Num_Agence = COMPTE.Num_Agence
and AGENCE.Ville = “ Tanger ”
intersect
select CLIENT.Nom from CLIENT, AGENCE, EMPRUNT
where CLIENT.Num_Client = EMPRUNT.Num_Client
and AGENCE.Num_Agence = EMPRUNT.Num_Agence
and AGENCE.Ville = “ Tanger ”
5. Clients ayant un compte et pas d’emprunt à “ Tanger ”
select CLIENT.Nom from CLIENT, AGENCE, COMPTE
where CLIENT.Num_Client = COMPTE.Num_Client
and AGENCE.Num_Agence = COMPTE.Num_Agence
and AGENCE.Ville = “ Tanger ”
minus
select CLIENT.Nom from CLIENT, AGENCE, EMPRUNT
where CLIENT.Num_Client = EMPRUNT.Num_Client
and AGENCE.Num_Agence = EMPRUNT.Num_Agence
and AGENCE.Ville = “ Tanger ”
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)
2
7. Clients ayant un compte à “Tétouan” et nom de la ville où ils habitent
Première solution :
select CLIENT.Nom, CLIENT.Ville from CLIENT, AGENCE, COMPTE
where CLIENT.Num_Client = COMPTE. Num_Client
and AGENCE.Num_Agence = COMPTE.Num_Agence
and AGENCE.Nom = “ Tétouan ”
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 = “Tétouan”))
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 de “Fès”
select Nom from AGENCE where Actif > all (
select Actif from AGENCE where Ville = “Fès”)
10. Clients ayant un compte dans au-moins une agence de “ Fès ”
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 = “Fès”))
Deuxième solution :
select CLIENT.Nom from CLIENT, COMPTE, AGENCE
where CLIENT.Num_Client = COMPTE.Num_Client
and COMPTE.Num_Agence = AGENCE.Num_Agence
and AGENCE.Ville = “ Fès ”
11. Emprunteurs de l'agence “Oujda” 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 = “Oujda”))
order by Nom
12. 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
3
13. 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
14. Nombre de clients habitant “ Agadir ”
select count(*) from CLIENT where Ville = “Agadir”
15. Nombre de clients de l'agence “ Azrou ” n'ayant pas leur adresse dans la table 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 = “Azrou”))
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 AGENCE.Nom = “Azrou”
16. Insérer le n-uplet <Said, Rabat> dans la table CLIENT
insert into CLIENT values (130765, “ Said ”, “Rabat”)
17. Diminuer l'emprunt de tous les clients habitant “ Ouarzazate ” de “5%”
update EMPRUNT
set Montant = Montant * 0.95
where Num_Client in (
select Num_Client from CLIENT where Ville = “ Ouarzazate ”)
18. Fermer les comptes de “Lina”
Première solution :
delete from COMPTE where Num_Client in (
select Num_Client from CLIENT where Nom = “Lina”)
Deuxième solution :
delete from COMPTE
where COMPTE.Num_Client = CLIENT.Num_Client
and CLIENT.Nom = “ Lina ”)
19. Supprimer de la table 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)
