SGBD 1 : Examen de Fin Module V1 du 06/10/2010 (avec Solution)

DUREE : 2h00 BAREME : ………../20

Exercice :

Soit le schéma relationnel suivant, représentant une gestion des comptes clients et leur emprunt dans une banque.

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)

Section 1 : Création de la base de données

1)Créez la base de données DB_Banque en spécifiant les paramètres de création.... (1 pts)

2)Créez toutes les tables avec les contraintes d’intégrité PK et FK, et ajouter un enregistrement par table......................................................................................... (3 pts)

Section 2 : Mise à jour des données :

1)Modifier la valeur Null des Soldes par la valeur 0............................................... (0,5 pts)

2)Modifier les villes des agences en majuscule........................................................ (0,5 pts)

3)Diminuer l'emprunt de tous les clients habitant “Casablanca” de “5%”................. (1 pts)

4)ajouter une contraint strictement positif (>) pour Solde........................................ (1 pts)

Section 3 : Requêtes d’interrogation de la base de données :

Formuler en SQL les requêtes suivantes, et vérifier à chaque fois que le résultat obtenu est sans doublon.

1)les clients ou le nom commence par B, et le troisième caractère est un A............ (0,5 pts)

2)Liste des agences ayant des comptes-clients........................................................ (0,5 pts)

3)Clients ayant un compte à “Casablanca”............................................................... (1 pts)

4)Clients ayant un compte ou un emprunt à “Rabat”............................................... (1 pts)

5)Clients ayant un compte à la ville où ils habitent................................................... (1 pts)

6)Client ayant un compte et emprunt dans la même agence..................................... (1 pts)

7)Solde moyen des comptes-clients de chaque agence............................................... (1 pts)

8)Totale solde par agence.......................................................................................... (1 pts)

9)le client qui a le plus grand total emprunt............................................................ (1,5 pts)

10)Clients ayant un emprunt dans toutes les agences de “Casablanca”.................... (1,5 pts)

Section 4 : les vues, créer les vues qui affiche les requêtes suivantes :

1)Une vue qui affiche les clients avec leur total solde et total emprunt................... (1,5 pts)

2)une vue qui affiche les agences avec un total emprunt supérieur au total Solde..........(1,5 pts)

Proposition de Solution :

-- Section 1

-- 1)

Use Master

create Database DB_Banque

On primary

(name='DB_Banque_dat',filename='C:\DB_Banque_dat.mdf',size=3MB,filegrowth=10%)

Log on

(name='DB_Banque_log',filename='C:\DB_Banque_log.ldf',size=1MB,filegrowth=10%)

Go

Use DB_Banque

Go

-- 2)

Create Table Agence(Num_Agence int primary key, Nom varchar(50), Ville Varchar(50), Actif varchar(5))

Create Table Client(Num_Client int primary key, Nom varchar(50), Ville Varchar(50))

Create Table Compte(Num_Compte int primary key, Num_Agence int foreign key references Agence(Num_Agence), Num_Client int foreign key references Client(Num_Client), Solde real)

Create Table Emprunt(Num_Emprunte int primary key, Num_Agence int foreign key references Agence(Num_Agence), Num_Client int foreign key references Client(Num_Client), Montant real)

Go

Insert into Agence values (1,'Med VI','Casablanca','Oui')

Insert into Client values (1,'Ali','Casablanca')

Insert into Compte values (1,1,1,500)

Insert into Emprunt values (1,1,1,200)

Go

-- Section 2

-- 1)

Update Compte set Solde=0 where Solde is null

-- 2)

Update Agence Set Ville = Upper(ville)

-- 3)

Update Emprunt Set Montant = Montant*0.95 Where Num_Client in (Select Num_Client From Client Where ville like 'Casablanca')

-- 4)

Alter table Compte Add Constraint sl_ch Check (solde>0)

-- Section 3

-- 1)

Select DISTINCT * From Client where nom like 'B_A%'

-- 2)

Select DISTINCT * From Agence where Num_Agence in (Select Num_Agence From Compte)

-- 3)

Select DISTINCT Client.* From Client,Compte, Agence Where Client.Num_Client=Compte.Num_Client AND Compte.Num_Agence=Agence.Num_Agence AND Agence.ville like 'Casablanca'

-- 4)

Select DISTINCT Client.* From Client Where Num_Client In (Select Num_Client From Compte, Agence Where Compte.Num_Agence=Agence.Num_Agence and ville like 'Rabat') OR Num_Client In (Select Num_Client From Emprunt, Agence Where Emprunt.Num_Agence=Agence.Num_Agence and ville like 'Rabat')

-- 5)

Select DISTINCT Client.* From Client, Compte, Agence Where Client.Num_Client=Compte.Num_Client AND Compte.Num_Agence=Agence.Num_Agence and Agence.ville like Client.ville

-- 6)

Select DISTINCT Client.* From Client, Compte, Emprunt Where Client.Num_Client=Compte.Num_Client AND Client.Num_Client=Emprunt.Num_Client AND Emprunt.Num_Agence=Compte.Num_Agence

-- 7)

Select DISTINCT Agence.Num_Agence, Nom, AVG(Solde) From Agence, Compte Where Agence.Num_Agence=Compte.Num_Agence Group by Agence.Num_Agence, Nom

-- 8)

Select DISTINCT Agence.Num_Agence, Agence.Nom, SUM(Solde) From Agence, Compte Where Agence.Num_Agence=Compte.Num_Agence Group by Agence.Num_Agence, Nom

-- 9)

Select Client.Num_Client, Client.Nom, Sum(Montant) From Client,Emprunt Where Client.Num_Client=Emprunt.Num_Client group by Client.Num_Client, Nom Having SUM(Montant)>= All(Select SUM(Montant) From Emprunt group by Num_Client)

-- 10)

select Client.Num_Client, Nom From Client, Emprunt

Where Client.Num_Client=Emprunt.Num_Client AND Num_Agence in (Select Num_Agence From Agence Where ville like 'Casablanca')

Group by Client.Num_Client, Nom

having Count(DISTINCT Num_Agence)=(Select Count(Num_Agence) From Agence)

-- Section 4

-- 1)

Create view Vue1 as

Select Client.Num_Client, Nom, SUM(Solde), Sum(Montant) From Client,Compte, Emprunt

Where Client.Num_Client=Compte.Num_Client AND Client.Num_Client=Emprunt.Num_Client

Group by Client.Num_Client, Nom

-- 2)

Create view Vue2 as

Select Agence.Num_Agence, Nom From Agence,Compte, Emprunt

Where Agence.Num_Agence=Compte.Num_Agence AND Agence.Num_Agence=Emprunt.Num_Agence

Group by Agence.Num_Agence, Nom

Having Sum(Montant)>Sum(solde)