Les concepts fondamentaux d’une base de données
Interrogation des données(suite)
Partie 6
Application:
Soit la base de données relationnelle suivante concernant la gestion des
commandes des lycées pour l’année scolaire 2021/2022:
livre (isbn, titre, pu, stock) Désigne l’ensemble des livres
lycée (code_l, Nom, ville, tel) Désigne l’ensemble des lycées
Commande (ncmd, Date, #code_l) Désigne l’ensemble des commandes
passer par les lycées
Ligne_commandes (#ncmd, #isbn, qte) Désigne l’ensemble des lignes de
commandes.
Les sous requêtes :
Une sous-requête doit être placée à la suite d'une clause WHERE ou HAVING.
• Lorsque la sous-requête remplace une constante utilisée avec des opérateurs
classiques, elle doit obligatoirement renvoyer une seule réponse (une table d'une
ligne et une colonne).
Par exemple :
SELECT … FROM …
WHERE … < (SELECT … FROM …) ;
A noter : il est possible d’utiliser n’importe quel opérateur d’égalité tel que =, >, <,
>=, <= ou <>.
• Lorsque la sous-requête remplace une constante utilisée dans une expression
mettant en jeu les opérateurs IN, ALL ou ANY, elle doit obligatoirement renvoyer
une seule colonne.
SELECT … FROM …
WHERE … IN (SELECT … FROM …) ;
• Lorsque la sous-requête remplace une constante utilisée dans une expression
mettant en jeu l’opérateur EXISTS, elle peut renvoyer une table de n colonnes et m
lignes.
SELECT … FROM …
WHERE … EXISTS (SELECT … FROM …) ;
RQ19 Donner les livres dont les prix unitaires dépassent la moyenne des prix.
SELECT *
FROM livre
WHERE PU > (SELECT AVG(PU) FROM livre);
Le résultat de la sous requête
AVG
3,27
isbn titre pu stock
233-371 ALGORITHMES 3,60 100
livre
560-677 BASES DE DONNEES 4,90 185
isbn titre pu stock
233-371 ALGORITHMES 3,60 100 978-234 ORACLE DATA BASES 3,50 350
233-688 ABC DU BAC 2,00 234
377-655 BUREATIQUE ET 2,35 10
EXERCICES
560-677 BASES DE DONNEES 4,90 185
978-234 ORACLE DATA BASES 3,50 350
RQ20 Donner la liste des noms des lycées qui n’ont pas passé de commande.
SELECT Nom
FROM lycee ly
WHERE NOT EXISTS
(SELECT * FROM Commande C
WHERE ly.code_l =C.code_l);
lycée
ncmd date_cmd commande.code_l lycee.code_l code_l nom ville tel
L876 IBN CHARAF BEJA 78 67 890
C124 03/01/2007 L876 L876
L121 BACHA TUNIS
C125 11/11/2006 L876 L876
L124 BAB EL KAHDRA TUNIS
C133 07/03/2007 L876 L876 L136 HANNIBAL ARIANA 71 236 543
C121 01/10/2006 L121 L121 L156 SEJOUMI TUNIS
C214 10/04/2007 L124 L124 L306 NAPOLIS NABEUL 73 233 760
C215 11/05/2007 L124 L124 L320 TAHAR SAFAR SOUSSE 73 650 650
C123 25/06/2007 L136 L136 L326 SIDI BOU ALI SOUSSE 73 698 432
C126 29/04/2007 L136 L136 L330 BOUMARDESS SOUSSE 73 230 129
C143 15/09/2006 L136 L136 L415 MAJIDA SFAX 74 650 399
BOULILA
C156 16/09/2006 L156 L156
C256 15/11/2006 L156 L156
C356 02/01/2007 L156 L156
C310 12/12/2006 L330 L330 Nom
NAPOLIS
C319 31/01/2007 L330 L330
TAHAR SAFAR
C442 01/10/2006 L415 L415 SIDI BOU ALI
RQ21 Donner les commandes qui ont une date inférieure à chacune des
commandes du lycée ‘L121’.
SELECT *
FROM Commande
WHERE Date_cmd <
ALL
(SELECT date_cmd
FROM commande
WHERE code_l ="L121");
date_cmd code_l
01/10/2006 L121
ncmd date_cmd code_l
C124 03/01/2007 L876
C125 11/11/2006 L876
C133 07/03/2007 L876
C121 01/10/2006 L121
C214 10/04/2007 L124
C215 11/05/2007 L124
C123 25/06/2007 L136
C126 29/04/2007 L136
C143 15/09/2006 L136
C156 16/09/2006 L156
C256 15/11/2006 L156
C356 02/01/2007 L156
C310 12/12/2006 L330
C319 31/01/2007 L330
ncm
date_cmd code_l
C442 01/10/2006 L415 d
C143 15/09/2006 L136
C156 16/09/2006 L156
Opérateurs ensemblistes:
Union : L’opérateur UNION permet de fusionner deux sélections de tables pour
obtenir un ensemble de lignes égal à la réunion des lignes des deux sélections.
Les lignes communes n’apparaîtront qu’une seule fois.
Syntaxe :
Requête1
UNION
Requête2 ;
NB :
• Requête1 et Requête2 doivent avoir la même structure.
• Par défaut les doublons sont automatiquement éliminés.
• Pour conserver les doublons, il est possible d'utiliser une clause UNION ALL.
RQ21 Donner l’ensemble des lycées de Tunis et de Sousse qui ont des commandes.
SELECT ly.code_l, ly.Nom
FROM lycee as ly, Commande as C
WHERE ly.code_l= C.code_l
AND Ville="Tunis"
UNION
SELECT ly.code_l, ly.Nom
FROM lycee ly,Commande C
WHERE ly.code_l= C.code_l
AND Ville ="Sousse" ;
Requête Requête
Requête1
Requête 2
code_l nom
code_l Nom
L121 BACHA code_l nom ville L121 BACHA
L124 BAB EL L330 BOUMARDESS SOUSSE
L124 BAB EL KAHDRA
KAHDRA
L330 BOUMARDESS SOUSSE
L124 BAB EL L156 SEJOUMI
KAHDRA
L330 BOUMARDESS
L156 SEJOUMI
L156 SEJOUMI
L156 SEJOUMI
APPLICATION n°1 :
Soit le schéma de la base de données 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 )
Questions :
A) Créer les tables en SQL.
B) Ecrire les requêtes suivantes en SQL :
1) Liste des agences ayant des comptes-clients
2) Clients (nom)ayant un compte dans la Ville de “ Tanger ”
3) Clients (nom)ayant un compte ou un emprunt à “ Tanger ”
4) Clients (nom, ville)ayant un compte
5) Clients (nom, ville) ayant un compte à “Tétouan”
6) Clients (nom) ayant un compte dans une agence où “Claude” a un compte
7) Agences (nom) ayant un actif plus élevé que toutes les agences de “Fès”
8) Clients (nom) ayant un compte dans au-moins une agence de “ Fès ”
9) Emprunteurs (nom) de l'agence “Oujda” classés par ordre
alphabétique
10) Solde moyen des comptes-clients de chaque agence
11) Solde moyen des comptes-clients des agences dont le solde
moyen est > “10 000”
12) Nombre de clients habitant “ Agadir ”
13) Nombre de clients de l'agence “ Azrou ” n'ayant pas leur adresse
dans la table CLIENT
14) Insérer le n-uplet <Said, Rabat> dans la table CLIENT
15) Diminuer l'emprunt de tous les clients habitant “ Ouarzazate ” de
“5%”
16) Fermer les comptes de “Lina”
17) Supprimer de la table AGENCE toutes les agences sans client
SOLUTION DE L’APPLICATION N°1 :
A) Créer les tables en SQL :
CREATE TABLE AGENCE
(Num_Agence char (50), NOTNULL, PRIMARY KEY,
Nom 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;
2. Clients(nom) 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 ”;
B) Ecrire les requêtes suivantes en SQL :
3. Clients (nom)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 (nom, ville)ayant un compte
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);
5. Clients (nom, ville) ayant un compte à “Tétouan”
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”));
6. Clients (nom)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”));
7. Agences (nom) ayant un actif plus élevé que toutes les agences de “Fès”
select Nom from AGENCE where Actif > all (
select Actif from AGENCE where Ville = “Fès”);
8. Clients (nom) ayant un compte dans au-moins une agence de “ Fès ”
Première 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 ”;
Deuxième 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”));
9. Emprunteurs (nom) 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;
10. 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;
11. 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;
12. Nombre de clients habitant “ Agadir ”
select count(*) from CLIENT where Ville = “Agadir”;
13. Nombre de clients de l'agence “ Azrou ” n'ayant pas leur adresse dans la table
CLIENT
Première 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”;
Deuxième 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”));
14. Insérer le n-uplet <Said, Rabat> dans la table CLIENT
insert into CLIENT values (130765, “ Said ”, “Rabat”);
15. 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 ”);
16. 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 ”);
17. 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);
