Search for notes by fellow students, in your own course and all over the country.
Browse our notes for titles which look like what you need, you can preview any of the notes via a sample of the contents. After you're happy these are the notes you're after simply pop them into your shopping cart.
Title: exercice sql avec correction
Description: you find here all type of exercice of sql
Description: you find here all type of exercice of sql
Document Preview
Extracts from the notes are below, to see the PDF you'll receive please use the links above
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
...
Num_Agence = COMPTE
...
Clients ayant un compte à “La Rochelle”
select CLIENT
...
Num_Agence = COMPTE
...
Num_Client = COMPTE
...
Ville = “La Rochelle”
3
...
Nom from CLIENT, AGENCE, COMPTE
where CLIENT
...
Num_Client
and AGENCE
...
Num_Agence
and AGENCE
...
Nom from CLIENT, AGENCE, EMPRUNT
where CLIENT
...
Num_Client
and AGENCE
...
Num_Agence
and AGENCE
...
Clients ayant un compte et un emprunt à “La Rochelle”
select CLIENT
...
Num_Client = COMPTE
...
Num_Agence = COMPTE
...
Ville = “La Rochelle”
intersect
select CLIENT
...
Num_Client = EMPRUNT
...
Num_Agence = EMPRUNT
...
Ville = “La Rochelle”
5
...
Nom from CLIENT, AGENCE, COMPTE
where CLIENT
...
Num_Client
and AGENCE
...
Num_Agence
and AGENCE
...
Nom from CLIENT, AGENCE, EMPRUNT
where CLIENT
...
Num_Client
and AGENCE
...
Num_Agence
and AGENCE
...
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
Deuxième solution :
select Nom, Ville from CLIENT
where Num_Client in (
select Num_Client from COMPTE)
7
...
Nom, CLIENT
...
Num_Client = COMPTE
...
Num_Agence = COMPTE
...
Nom = “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
...
Num_Client = COMPTE
...
Num_Client = COMPTE
...
Num_Client = COMPTE
...
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 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 CLIENT
...
Num_Client = COMPTE
...
Num_Agence = AGENCE
...
Ville = “Orsay”
12
...
Solde moyen des comptes-clients de chaque agence
select Nom, avg(Solde) from AGENCE, COMPTE
where AGENCE
...
Num_Agence
group by Nom
14
...
Num_Agence = COMPTE
...
Nombre de clients habitant “Paris”
select count(*) from CLIENT where Ville = “Paris”
16
...
Num_Client = COMPTE
...
Num_Agence = AGENCE
...
Nom = “Paris-Bastille”
17
...
Diminuer l'emprunt de tous les clients habitant “Marseille” de “5%”
update EMPRUNT set Montant = Montant * 0
...
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
and CLIENT
...
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)
and Num_Client not in (
select Num_Client from EMPRUNT where EMPRUNT
...
Num_Agence)
Title: exercice sql avec correction
Description: you find here all type of exercice of sql
Description: you find here all type of exercice of sql