Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

NFA011 – Développement d’applications avec les bases de données T P P L / S Q L

NFA011 – Développement d’applications avec les bases de données T P P L / S Q L ( C o r r i g é ) E x e r c i c e s S Q L ( o p t i o n n e l ) Donnez les commandes SQL suivantes : 1. Les pilotes qui gagnent plus de 20000 euros. Solution : select * from pilote where salaire >= 20000; 2. Le nombre de vols arrivant à Paris. Solution : select count(*) from vol where ville_arrivee = 'Paris'; 3. Les pilotes qui gagnent le plus (salaire le plus grand). Solution : select nom, salaire from pilote where salaire = (select max(salaire) from pilote); 4. Les villes classés en ordre décroissant du nombre de vols départ ? Solution : select ville_depart, count(*) as nb_vols from vol group by ville_depart order by nb_vols desc; 5. Noms de paires de pilotes qui ont le même age mais salaire différent. Solution : select p1.nom, p2.nom from pilote p1, pilote p2 where p1.age = p2.age and p1.salaire <> p2.salaire and p1.nom < p2.nom; 6. Combien de vols arrivent dans une ville d’où il n’y a pas de vol départ. Solution : select ville_arrivee, count(*) from vol where ville_arrivee in ((select ville_arrivee from vol) minus (select ville_depart from vol)) group by ville_arrivee; NFA011 NFA011 – Développement d’applications avec les bases de données 7. Villes à partir desquelles il y au moins un vol de départ vers toutes les autres villes. On utilise seulement la table Vol, car la table Escales ne contient forcement toutes les villes. Cette requête donne la liste de toutes les villes dans la table Vol : create table ListeVilles as select * from ((select ville_depart as ville from vol) union (select ville_arrivee as ville from vol)); Une ville candidat (C) fait partie de la réponse s’il n’existe pas une autre ville (différente de C) vers laquelle il n’y a pas de vol a partir du C : c.t.d. la liste des villes vers lesquelles il n’y a pas de vols à partir de C est vide. Cette liste est égale a ListeVilles (toutes les villes) moins la liste des villes vers lesquelles il y a un vol à partir de C. select lv1.ville from ListeVilles lv1 -- lv1.ville est le candidat where not exists (select lv2.ville from ListeVilles lv2 where lv2.ville <> lv1.ville and lv2.ville not in (select ville_arrivee from vol where ville_depart = lv1.ville)); DROP TABLE ListeVilles purge; G é n é r a l i t é s S Q L e t P L S Q L E x e r c i c e 1 . E x é c u t e z s o u s S Q L * P L U S l e s s o l u t i o n s d e s e x e r c i c e s 1 , 2 , 3 , 4 , 5 , 6 d e l a p a r t i e E D . S i d e s t a b l e s s o n t n é c e s s a i r e s i l f a u t a u s s i l e s c r é e r . S o l u t i o n : V o i r c o r r i g é E D . E x e r c i c e 2 . É c r i r e u n e f o n c t i o n P L / S Q L q u i r e t o u r n e l e n - è me n u mé r o d e F i b o n a c c i : xn= xn−1+xn−2 o ù x0=1 e t x1=1 É c r i r e u n e v e r s i o n r é c u r s i v e e t u n e v e r s i o n i t é r a t i v e . S o l u t i o n : CREATE OR REPLACE FUNCTION FIBOREC (N INTEGER) RETURN INTEGER IS BEGIN IF (N<1) THEN RETURN -1; ELSIF(N=1) THEN RETURN 1; ELSIF(N=2) THEN RETURN 1; ELSE RETURN FIBOREC(N-1) + FIBOREC(N-2); END IF; END FIBOREC; NFA011 NFA011 – Développement d’applications avec les bases de données CREATE OR REPLACE FUNCTION FIBOITER (N INTEGER) RETURN INTEGER IS R1 INTEGER := 1; R2 INTEGER := 1; RESULTAT INTEGER; BEGIN IF (N<1) THEN RETURN -1; ELSIF(N=1) THEN RETURN 1; ELSIF(N=2) THEN RETURN 1; ELSE FOR I IN 3..N LOOP RESULTAT := R1 + R2; R2 := R1; R1 := RESULTAT; END LOOP; RETURN RESULTAT; END IF; END FIBOITER; E x e r c i c e 3 . É c r i r e u n e f o n c t i o n q u i p r e n d i n p a r a mè t r e u n n o mb r e e n t i e r N e t q u i r e t o u r n e l a s o mme d e t o u s l e mu l t i p l e s d e 3 o u d e 5 p l u s p e t i t s q u e N . S o l u t i o n : CREATE OR REPLACE FUNCTION SOMMEDIV(N INTEGER) RETURN INTEGER IS S INTEGER := 0; BEGIN FOR i IN 1..N LOOP IF (MOD(i, 3) = 0) OR (MOD(i, 5) = 0) THEN S := S + i; END IF; END LOOP; RETURN S; END; / E x e r c i c e 4 . É c r i v e z u n p r o g r a mme P L / S Q L q u i a ffic h e t o u s l e s v o l s a r r i v a n t à P a r i s . U t i l i s e z u n c u r s e u r . S o l u t i o n : DECLARE CURSOR c IS SELECT * FROM vol WHERE ville_arrivee = 'Paris'; v VOL%ROWTYPE; BEGIN OPEN c; LOOP FETCH c INTO v; EXIT WHEN (c%NOTFOUND OR c%NOTFOUND IS NULL); DBMS_OUTPUT.PUT_LINE(v.numvol); END LOOP; CLOSE c; END; NFA011 NFA011 – Développement d’applications avec les bases de données / E x e r c i c e 5 . É c r i v e z u n p r o g r a mme P L / S Q L q u i c a l c u l e l a mo y e n n e d e s t r o i s p l u s g r a n d s s a l a i r e s d e p i l o t e . S o l u t i o n : DECLARE CURSOR c IS select salaire from pilote order by salaire desc; somme NUMBER := 0; sal pilote.salaire%TYPE; BEGIN OPEN c; FOR i IN 1..3 LOOP FETCH c into sal; somme := somme + sal; END LOOP; CLOSE c; DBMS_OUTPUT.PUT_LINE('Moyenne 3 plus grands salaires de pilote: ' || somme/3); END; / E x e r c i c e 6 . E x p l i q u e z l e s r é s u l t a t s o b t e n u s p a r l ’ e x é c u t i o n d u p r o g r a mme P L / S Q L s u i v a n t : DECLARE n NUMBER := 10; PROCEDURE etudeNocopy (n1 IN NUMBER, n2 IN OUT NUMBER, n3 IN OUT NOCOPY NUMBER) IS BEGIN n2 := 20; DBMS_OUTPUT.PUT_LINE(n1); n3 := 30; DBMS_OUTPUT.PUT_LINE(n1); END etudeNocopy; BEGIN etudeNocopy(n, n, n); DBMS_OUTPUT.PUT_LINE(n); END; S o l u t i o n : L e p r o g r a mme a ffic h e 1 0 3 0 2 0 . C e c i e s t e x p l i q u é p a r l e f a i t q u e p a r d é f a u t l e s p a r a mè t r e s I N s o n t t r a n s mi s p a r r é f é r e n c e e t l e s p a r a mè t r e s O U T e t I N O U uploads/Geographie/ tp-corrige-plsql.pdf