INSTITUT SUPERIEUR DE TECHNOLOGIE APPLIQUEE ET DE GESTION Etablissement Privé d’Enseignement Supérieur Accord de création n°05/0081/MINESUP du 07/09/2005 - Autorisation d’ouverture n° 06/0113/MINESUP du 02 /10/ 2006 ANNEE ACADEMIQUE 2016 -2017 BTS BLANC SEMESTRE I SPECIALITE : IG EPREUVE :EPREUVE PROFESSIONNELLE DE SYNTHESE NIVEAU : 2 DUREE : 6 heures CORRECTION PARTIE III : MANIPULATION DES BASES DE DONNEES (10 points) 1. b.Faux (1 pt) 2. Requêtes SQL : a. Création des tables VEHICULE, EMPRUNTEUR et CONTRAT. (1 pt x 3 = 3 pts) CREATE TABLE Vehicule (imat Varchar(10) PRIMARY KEY, serie Varchar(20), marque Varchar(20), type Varchar(20), dateMiseCirculation Date, prixAchat Integer CHECK(prixAchat>0) ) ; Ou bien CREATE TABLE Vehicule (imat Varchar(10), serie Varchar(20), marque Varchar(20), type Varchar(20), dateMiseCirculation Date, prixAchat Integer CHECK(prixAchat>0) , PRIMARY KEY(imat) ) ; ---------------CREATE TABLE Emprunteur (numEmp Varchar(4) PRIMARY KEY, nom Varchar(20), prenom Varchar(20), adresse Varchar(20), profession Varchar(20), telephone Varchar(20) ) ; Ou bien CREATE TABLE Emprunteur (numEmp Varchar(4), nom Varchar(20), prenom Varchar(20), adresse Varchar(20), profession Varchar(20), telephone Varchar(20), PRIMARY KEY(numEmp) ) ; ---------------CREATE TABLE Contrat (numContrat Varchar(7) PRIMARY KEY, montantPret Integer taux CHECK(montantPret>0), datePret CHECK(taux>0), Date, dureePret mensualiteEcheance CHECK(dureePret>0), imat CHECK(mensualiteEcheance>0), REFERENCES Numeric(2,2) Vehicule(Imat), Integer Integer Varchar(10) numEmp Varchar(4) REFERENCES Emprunteur(numEmp) ); Oubien CREATE TABLE Contrat (numContrat Varchar(7), taux CHECK(montantPret>0), CHECK(taux>0), datePret CHECK(dureePret>0), ISTAG montantPret Date, Integer Numeric(2,2) dureePret mensualiteEcheance Integer Integer 6/7 CHECK(mensualiteEcheance>0), imat Varchar(10), numEmp Varchar(4), PRIMARY KEY(numContrat), FOREIGN KEY imat REFERENCES Vehicule(imat), FOREIGN KEY numEmp REFERENCES Emprunteur(numEmp) ); b. Afficher tous les véhicules de marque « Toyota » dont le montant total des échéances de remboursement est inférieur à 10 000 000 FCFA. (1 pt) SELECT V.imat, serie, marque, type, dateMiseCirculation, prixAchat, mensualiteEcheance * dureePret AS MontantRembourse FROM Vehicule V, Contrat C WHERE (V.marque = ‘Toyota’) AND (V.imat = C.imat) AND (MontantRembourse < 10 000 000) ; | prixAchat < 10 000 000 c. Réaliser, en date du 20 avril 2017, le crédit automobile numéro « C201205 » du véhicule d’immatriculation « CE 123 EN » accordé au client « Shakyl » ayant comme numéro « E003 ». Son montant de prêt étant de 25 000 000 FCFA au taux de 5,2% pour une durée 20 mois. (1 pt) INSERT INTO Contrat (numContrat, montantPret, taux, datePret, dureePret, mensualiteEcheance, imat, numEmp) VALUES (‘C201205’, 25 000 000, 5.2, #20-04-2017#, 20, 1 250 000, ‘CE 123 EN’, ‘E003’) ; d. Mettre à jour l’adresse de l’emprunteur « Karlos » identifié par le code « E001 » et qui s’est installé à « Nsimeyong ». (1 pt) UPDATE Emprunteur UPDATE Emprunteur SET adresse = ‘Nsimeyong’ SET adresse = ‘Nsimeyong’ WHERE nom = ‘Karlos’ WHERE numEmp = ‘E001’ e. Afficher le nombre et montant de crédits automobiles contractés par chaque emprunteur. (1 pt) SELECT C.numEmp, nom, prenom, COUNT (numContract), SUM(montantPret) FROM Emprunteur E , Contract C WHERE (E.numEmp = C.numEmp) GROUP BY C.numEmp, nom, prenom ; f. Calculer le chiffre d’affaires des prêts des véhicules d’ « occasion ». (1 pt) SELECT SUM(MontantPret) AS CAOccasions FROM Vehicule V , Contract C WHERE (V.type = ‘occasion’) AND (V.imat = C.imat) ; g. Lister les emprunteurs (numéro et nom) dont le montant de prêt n’excède pas 12 000 000 FCFA. (1 pt) SELECT C.numEmp, nom FROM Emprunteur E , Contract C WHERE (montantPret < 12 000 000 ) AND (E.numEmp = C.numEmp) ; ISTAG 7/7