SGBD 1 : Examen de Fin Module V2 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, VilleAg, Actif)

CLIENT (Num_Client, Nom, VilleCl)

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 Montants par la valeur 0.......................................... (0,5 pts)

2)Modifier les villes des Clients en minuscule......................................................... (0,5 pts)

3)Augmenter le solde de tous les clients habitant “Rabat” de “0,5%”....................... (1 pts)

4)ajouter une contraint strictement positif (>) pour Montant................................... (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)liste des clients ou le nom termine par E et le quatrième caractère est un A......... (0,5 pts)

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

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

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

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

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

7)Emprunt moyenne des clients dans chaque agence................................................ (1 pts)

8)Totale emprunte par client..................................................................................... (1 pts)

9)le client qui a le moins des totaux emprunts........................................................ (1,5 pts)

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

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

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

12)une vue qui affiche les clients avec un total emprunt plus grand que le 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 Emprunt set Montant=0 where Montant is null

-- 2)

Update Client Set Ville = Lower(ville)

-- 3)

Update Compte Set Solde = Solde*1.05 Where Num_Client in (Select Num_Client From Client Where ville like 'Rabat')

-- 4)

Alter table Emprunt Add Constraint mt_ch Check (Montant>0)

-- Section 3

-- 1)

Select DISTINCT * From Client where nom like '___A%E'

-- 2)

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

-- 3)

Select DISTINCT Client.* From Client,Emprunt, Agence Where Client.Num_Client=Emprunt.Num_Client AND Emprunt.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') AND 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, Emprunt, Agence Where Client.Num_Client=Emprunt.Num_Client AND Emprunt.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_AgenceCompte.Num_Agence

-- 7)

Select DISTINCT Agence.Num_Agence, Nom, AVG(Montant) From Agence, Emprunt Where Agence.Num_Agence=Emprunt.Num_Agence Group by Agence.Num_Agence, Nom

-- 8)

Select DISTINCT Client.Num_Client, Nom, SUM(Montant) From Client, Emprunt Where Client.Num_Client=Emprunt.Num_Client Group by Client.Num_Client, 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, Compte

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

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 Agence.Num_Agence, Nom, SUM(Solde), Sum(Montant) From Agence,Compte, Emprunt

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

Group by Agence.Num_Agence, Nom

-- 2)

Create view Vue2 as

Select Client.Num_Client, Nom From Client,Compte, Emprunt

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

Group by Client.Num_Client, Nom

Having Sum(Montant)>Sum(solde)