· 8 years ago · Aug 16, 2018, 09:34 AM
1--CC4 SQL
2
3--• En se référant à l'annexe 2 et à l'annexe 3, donner le code sql permettant de créer cette
4--table. Utiliser des types Oracle . Les contraintes de clés seront nommées.
5CREATE TABLE AVENANT
6(
7 CON_NUM VARCHAR2(10),
8 AVE_NUM NUMBER(1),
9 AVE_DATE_SIGNATURE DATE,
10 AVE_JOUR_ECHEANCE NUMBER(1),
11 AVE_NB_MENSUALITE NUMBER(2,0),
12 CONSTRAINT PK1(CON_NUM,AVE_NUM)
13);
14Le code de la clé primaire est faux et il n'y a pas de clé étrangère
15--• Insérer un nouvel avenant.
16INSERT INTO CRE_AVENANT VALUES (1800020350,1,TO_DATE('01/06/2010','DD/MM/YYY'), 4 , 24 );
17
18--• Insérer un garant (Josiane Marie) pour le premier avenant du contrat 01800020350. On
19--n'est pas sensé savoir qu'il y a d'autres garants pour ce contrat.
20--Je ne voit pas
21insert into cre_garant values
22(
23Â (select sig_id from cre_signataire where sig_nom='MARIE' and sig_prenom='Josiane '),
24Â '01800020350',
25Â 1,
26Â (select max(gar_rang)+1 from cre_garant where con_num=01800020350 and ave_num=1)
27);
28
29--• Supprimer les contrats de Bernard LE BOURGEOIS
30DELETE FROM CONTRAT WHERE
31SIG_ID=(SELECT SIG_NOM FROM CRE_SIGNATAIRE WHERE SIG_NOM like 'LE BOURGEOIS%'
32AND SIG_PRENOM like 'Bernard') ;
33
34--• Véronique MARIE s'appelle aujourd'hui Véronique BOUCHEZ. Corriger l'enregistrement
35--dans la base.
36UPDATE CRE_SIGNATURE SET SIG_NOM='MARIE' WHERE SIG_NOM='BOUCHEZ' AND SIG_PRENOM='VERONIQUE' ;
37
38--2.5.1. Afficher les numéros de contrats, les dates de signatures, les montants des prêts avec
39--le nom et prénom des signataires classés par nom et inverse de date de signature
40SELECT CON_NUM, CON_DATE_SIGNATURE, CON_MONTANT_PRET, sig_nom, sig_prenom
41FROM CRE_CONTRAT
42JOIN CRE_SIGNATAIRE USING (SIG_ID)
43ORDER BY sig_nom, CON_DATE_SIGNATURE DESC;
44
45--2.5.2. Afficher les numéros de contrats, les montants des prêts avec le nom et prénom des
46--signataires et des garants quand il y en a
47SELECT CON_NUM, CON_MONTANT_PRET, SIG_NOM AS "NOM SIGNATAIRE",
48SIG_PRENOM AS "PRENOM SIGNATAIRE", SIG_ID AS "SIG ID GARANT" FROM CRE_CONTRAT CRE
49LEFT JOIN CRE_SIGNATAIRE USING (SIG_ID)
50LEFT JOIN CRE_GARANT USING (SIG_ID,CON_NUM)
51LEFT JOIN CRE_AVENANT USING (CON_NUM,AVE_NUM);
52-- Je ne vois pas comment afficher le nom des garants, je n'ai que leur sig_id dans la table
53--CRE_GARANT et je ne vois pas comment récupérer le nom
54
55select sig1.sig_nom,sig1.sig_prenom,con_num,con_montant_pret,sig2.sig_nom as nom_caution, sig2.sig_prenom as prenom_caution
56from cre_contrat con
57join cre_signataire sig1 on sig1.sig_id =Â con.sig_id
58left join cre_avenant using(con_num)
59left join cre_garant gar using(con_num,ave_num)
60left join cre_signataire sig2 on sig2.sig_id = gar.sig_id;
61
62--2.5.3. Afficher le montant des échéances pour les contrats (on ne tient pas compte des
63--avenants). La formule pour le calcul des mensualités est la suivante :
64SELECT ( SELECT CON_MONTANT_PRET* CON_TAUX/12 / 1 − POWER( (1+12) , (-CON_DUREE_INIT) )
65FROM DUAL ) AS "ECHEANCE CONTRAT" FROM CRE_CONTRAT;
66
67select con_num,round((con_montant_pret*con_taux/12/100)/(1-(power(1+con_taux/12/100,-con_duree_init))),2)
68||' €' as montant from cre_contrat;
69
70--2.5.4. Afficher la date de première échéance, la durée initiale (elle est en mois), la date de
71--dernière échéance (à calculer).
72SELECT CON_DAT_PRE_ECHEANCE, CON_DUREE_INIT,
73TO_CHAR(TO_DATE(CON_DAT_PRE_ECHEANCE, 'DD/MM/YYYY') + CON_DUREE_INIT, 'DD/MM/YY') AS "DATE_DERNIERE_ECHEANCE"
74FROM CRE_CONTRAT;
75--J'ajoute une durée cependant, le resultat n'est malheuresement pas celui souhaité
76
77select con_dat_pre_echeance, con_duree_init,
78con_jour_echeance||'/'||to_char(con_dat_pre_echeance+con_duree_init*30.5,'mm/yyyy') as DERNIERE_ECHEANCE
79from cre_contrat;
80
81--2.5.5. Afficher les intervenants qui sont également des signataires
82SELECT INT_NOM, INT_PRENOM FROM CRE_INTERVENANT
83INTERSECT
84SELECT SIG_NOM, SIG_PRENOM FROM CRE_SIGNATAIRE;
85
86--2.5.6. Afficher les nom et le prénom des signataires, leur nombre de contrats et la somme
87--totale empruntée par chacun d'entre eux.
88SELECT SIG_NOM , SIG_PRENOM, COUNT(CON_NUM) AS "NOMBRE CONTRATS" ,
89SUM(CON_MONTANT_PRET) AS "MONTANT PRET" FROM CRE_SIGNATAIRE
90JOIN CRE_CONTRAT USING (SIG_ID)
91GROUP BY SIG_NOM , SIG_PRENOM;
92
93--2.5.7. Même question mais en n'affichant que les signataires qui ont emprunté au moins
94--10000€
95SELECT SIG_NOM , SIG_PRENOM, COUNT(CON_NUM), SUM(CON_MONTANT_PRET) FROM CRE_SIGNATAIRE
96JOIN CRE_CONTRAT USING (SIG_ID)
97GROUP BY SIG_NOM , SIG_PRENOM
98HAVING SUM(CON_MONTANT_PRET) >= 10000;
99
100--2.5.8. Afficher les nom et le prénom des signataires, la somme totale empruntée par chacun
101--d'entre eux ainsi que la somme totale empruntée pour l'ensemble.
102-- Non réalisée
103
104select sig_nom,sig_prenom,sum(con_montant_pret),(select sum(con_montant_pret) from cre_contrat) as total
105from cre_signataire
106join cre_contrat using(sig_id)
107group by sig_nom,sig_prenom;
108
109--2.5.9. Afficher la liste des intervenants qui ont eu des missions non clôturées en utilisant
110--une requête synchronisée
111-- Non réalisée
112
113select * from cre_intervenant inter where exists
114(
115Â select 1 from cre_mission where inter.int_num = int_num
116Â and mis_date_cloture is null
117);
118
119/* ---------------- */
120/*2.6 PL/SQL 15 points*/
121/* --------------- */
122
123--a) Donner le code d'une procédure stockée Cre_Ins_Intervenant qui
124 --• reçoit en paramètre le nom et le prénom d'une nouvelle personne
125 --• vérifie que cette personne n'existe pas
126 --◦ si elle existe, on affiche un message d'erreur (avec gestion d'exception de préférence)
127 --◦ si elle n'existe pas on insère un nouvel intervenant
128 --• indique que la procédure est terminée en cas de succès
129CREATE OR REPLACE PROCEDURE CRE_INS_INTERVENANT(pNom VARCHAR2, pPrenom VARCHAR2) AS
130 cursor curIntervenant is select int_nom,int_prenom from CRE_INTERVENANT
131 WHERE int_nom like pNom and int_prenom = pPrenom;
132 vNomCre CRE_INTERVENANT.int_nom%type;
133 vPrenomCre cre_intervenant.int_prenom%type;
134 presenceNom EXCEPTION;
135BEGIN
136
137 IF NOT curIntervenant%ISOPEN THEN
138 OPEN curIntervenant;
139 END IF;
140
141 LOOP
142 FETCH curIntervenant into vNomCre, vPrenomCre;
143 EXIT WHEN curIntervenant%NOTFOUND;
144 END LOOP;
145 IF vNomCre = pNom AND vPrenomCre = pPrenom
146 THEN
147 RAISE presenceNom;
148 END IF;
149 INSERT INTO CRE_INTERVENANT(INT_NOM,INT_PRENOM) VALUES (pNom,pPrenom);
150 dbms_output.put_line('REALISE AVEC SUCCES');
151EXCEPTION
152 WHEN presenceNom
153 THEN dbms_output.put_line('Nom déja présent dans la base');
154END;
155
156CREATE OR REPLACE PROCEDURE CRE_INS_INTERVENANT (Â pNom IN VARCHAR2Â , pPrenom IN VARCHAR2) AS
157Â vNum cre_intervenant.int_num%TYPE;
158Â errExiste exception;
159BEGIN
160Â select int_num into vNum from cre_intervenant where int_nom=pNom and int_prenom = pPrenom;
161Â raise errExiste;
162Â exception
163Â Â Â when NO_DATA_FOUND then
164Â Â Â Â Â insert into cre_intervenant values ((select max(int_num)+1 from cre_intervenant),pNom,pPrenom);
165Â Â Â Â Â dbms_output.put_line('FIN');
166Â Â Â when errExiste then
167Â Â Â Â Â raise_application_error(-20001,'Cet intervenant existe');
168END CRE_INS_INTERVENANT;
169/
170--b) Donner le code d'un bloc pl/sql permettant de faire la saisie du nom et du prénom d'un
171--nouvel intervenant avant d'appeler
172accept pNom prompt 'Saisir le nom'
173accept pPrenom prompt 'Saisir le prénom'
174DECLARE
175 pNom VARCHAR2(200);
176 pPrenom VARCHAR2(200);
177BEGIN
178 P_PNOM := '&pNom';
179 P_PPRENOM := '&pPrenom'
180 CRE_INS_INTERVENANT( P_PNOM, P_PPRENOM );
181END;
182/
183--c) Donner le code d'une procédure stockée Cre_Aff_Vehicules qui
184--affiche tous les champs des véhicules présents dans la base.
185create or replace PROCEDURE CRE_VEHICULES AS
186
187 cursor curVehicules is select * from CRE_VEHICULES;
188 vChamps varchar2(100); -- il faut un rowtype
189 BEGIN
190
191 open curVehicules;
192
193 LOOP
194 FETCH curVehicules into vChamps;
195 dbms_output.put_line(vChamps); -- on ne peut afficher directment toute une ligne
196 EXIT WHEN curVehicules%notfound;
197 END LOOP;
198
199END;
200
201--d) Donner le code d'une procédure stockée Cre_Capital_Restant permettant de calculer le
202--capital restant à rembourser pour un contrat connaissant le montant des mensualités. Le bloc
203--pl/sql de saisi des pa
204
205--Non réalisé
206Â