Exam on SQL Queries and PL/SQL Programs

Ce document est un examen portant sur les requêtes SQL et les programmes PL/SQL. Il évalue les compétences en conception de vues, procédures stockées, fonctions, gestion des exceptions et triggers dans un contexte de gestion d’audience télévisuelle.

Exercice 1

Créer une vue V_PROG_HEBD qui affiche le titre du programme, le nom de l’animateur et le nom de la chaîne pour les programmes dont la périodicité est hebdomadaire.

Pour créer cette vue, il faut joindre les tables PROGRAMME, ANIMATEUR et CHAINETV via leurs clés étrangères, puis filtrer sur la périodicité « hebdomadaire ».

CREATE VIEW V_PROG_HEBD AS
SELECT P.titre, A.nomA, C.nomCH
FROM PROGRAMME P
JOIN ANIMATEUR A ON P.idA = A.idA
JOIN CHAINETV C ON P.idCH = C.idCH
WHERE P.périodicité = 'hebdomadaire';

Réponse : La vue V_PROG_HEBD est créée avec la requête ci-dessus.

Exercice 2

Créer une procédure stockée PS_AUD_PROG qui affiche le total des audimats pour chaque type de programme.

On doit sommer la colonne audimat de la table SONDAGE groupée par le type de programme (typeP) de la table PROGRAMME. Il faut donc joindre SONDAGE et PROGRAMME sur les identifiants du programme.

CREATE OR REPLACE PROCEDURE PS_AUD_PROG IS
BEGIN
  FOR rec IN (
    SELECT P.typeP, SUM(S.audimat) AS total_audimat
    FROM SONDAGE S
    JOIN PROGRAMME P ON S.idP = P.idP AND S.idCH = P.idCH
    GROUP BY P.typeP
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Type de programme : ' || rec.typeP || ' - Audimat total : ' || rec.total_audimat);
  END LOOP;
END;

Réponse : La procédure PS_AUD_PROG affiche le total des audimats par type de programme comme indiqué.

Exercice 3

Créer une fonction stockée FN_NB_PROG(p_idA number) qui retourne le nombre de programmes présentés par l’animateur donné en paramètre. Si l’animateur n’existe pas, la fonction retourne -1.

Étapes :

• Vérifier si l’animateur existe dans ANIMATEUR.
• Si non, retourner -1.
• Sinon, compter le nombre de programmes dans PROGRAMME où idA = p_idA.

CREATE OR REPLACE FUNCTION FN_NB_PROG(p_idA NUMBER) RETURN NUMBER IS
  v_count NUMBER;
  v_exists NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_exists FROM ANIMATEUR WHERE idA = p_idA;
  IF v_exists = 0 THEN
    RETURN -1;
  ELSE
    SELECT COUNT(*) INTO v_count FROM PROGRAMME WHERE idA = p_idA;
    RETURN v_count;
  END IF;
END;

Réponse : La fonction FN_NB_PROG retourne le nombre de programmes ou -1 si l’animateur n’existe pas.

Exercice 4

Créer une procédure stockée PS_NOM_PROG qui affiche pour chaque animateur ayant plus de deux programmes son nom, sa nationalité, ainsi que les titres, types et périodicité des programmes qu’il présente. Utiliser la fonction FN_NB_PROG.

Étapes :

1. Parcourir tous les animateurs.
2. Pour chaque animateur, appeler FN_NB_PROG pour obtenir le nombre de programmes.
3. Si ce nombre est supérieur à 2, afficher le nom et la nationalité de l’animateur.
4. Afficher ensuite les titres, types et périodicité des programmes associés.

CREATE OR REPLACE PROCEDURE PS_NOM_PROG IS
BEGIN
  FOR anim IN (SELECT idA, nomA, nationalité FROM ANIMATEUR) LOOP
    IF FN_NB_PROG(anim.idA) > 2 THEN
      DBMS_OUTPUT.PUT_LINE('Animateur : ' || anim.nomA || ', Nationalité : ' || anim.nationalité);
      FOR prog IN (
        SELECT titre, typeP, périodicité
        FROM PROGRAMME
        WHERE idA = anim.idA
      ) LOOP
        DBMS_OUTPUT.PUT_LINE('  Programme : ' || prog.titre || ', Type : ' || prog.typeP || ', Périodicité : ' || prog.périodicité);
      END LOOP;
    END IF;
  END LOOP;
END;

Réponse : La procédure PS_NOM_PROG affiche les informations demandées en utilisant la fonction FN_NB_PROG.

Exercice 5

Créer une fonction stockée FN_POUR_AUDIMAT(p_idCH number, p_date date) qui retourne, pour une date donnée, le pourcentage de l’audimat d’une chaîne TV dont l’identifiant est donné en paramètre. Si la chaîne n’existe pas, retourner -1.

Le pourcentage d’audimat d’une chaîne est défini par :

pourcentage = (audimat total chaîne) / (audimat total toutes chaînes)

Étapes :

• Vérifier si la chaîne existe dans CHAINETV.
• Calculer la somme des audimats pour cette chaîne à la date donnée.
• Calculer la somme des audimats pour toutes les chaînes à la même date.
• Retourner le ratio.

CREATE OR REPLACE FUNCTION FN_POUR_AUDIMAT(p_idCH NUMBER, p_date DATE) RETURN NUMBER IS
  v_exists NUMBER;
  v_audimat_chaine NUMBER := 0;
  v_audimat_total NUMBER := 0;
  v_pourcentage NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_exists FROM CHAINETV WHERE idCH = p_idCH;
  IF v_exists = 0 THEN
    RETURN -1;
  END IF;

  SELECT NVL(SUM(audimat),0) INTO v_audimat_chaine
  FROM SONDAGE
  WHERE idCH = p_idCH AND dateS = p_date;

  SELECT NVL(SUM(audimat),0) INTO v_audimat_total
  FROM SONDAGE
  WHERE dateS = p_date;

  IF v_audimat_total = 0 THEN
    RETURN 0;
  END IF;

  v_pourcentage := v_audimat_chaine / v_audimat_total;
  RETURN v_pourcentage;
END;

Réponse : La fonction FN_POUR_AUDIMAT retourne le pourcentage d’audimat ou -1 si la chaîne n’existe pas.

Exercice 6

Créer une procédure stockée PS_CLASS_CHAINE(p_date date) qui affiche, pour une date donnée, une liste numérotée des noms des chaînes selon un ordre décroissant du pourcentage de leur audimat. Utiliser la fonction FN_POUR_AUDIMAT.

Étapes :

1. Récupérer la liste des chaînes.
2. Pour chaque chaîne, appeler FN_POUR_AUDIMAT pour obtenir son pourcentage d’audimat à la date donnée.
3. Stocker les résultats dans une collection temporaire.
4. Ordonner cette collection par pourcentage décroissant.
5. Afficher la liste numérotée.

CREATE OR REPLACE PROCEDURE PS_CLASS_CHAINE(p_date DATE) IS
  TYPE t_chaine_pourc IS TABLE OF RECORD (
    nomCH CHAINETV.nomCH%TYPE,
    pourcentage NUMBER
  );
  v_list t_chaine_pourc := t_chaine_pourc();
BEGIN
  FOR ch IN (SELECT idCH, nomCH FROM CHAINETV) LOOP
    v_list.EXTEND;
    v_list(v_list.COUNT).nomCH := ch.nomCH;
    v_list(v_list.COUNT).pourcentage := FN_POUR_AUDIMAT(ch.idCH, p_date);
  END LOOP;

  -- Tri par pourcentage décroissant (tri simple par insertion)
  FOR i IN 2 .. v_list.COUNT LOOP
    DECLARE
      v_key t_chaine_pourc(1)%TYPE := v_list(i);
      j INTEGER := i - 1;
    BEGIN
      WHILE j >= 1 AND v_list(j).pourcentage < v_key.pourcentage LOOP
        v_list(j+1) := v_list(j);
        j := j - 1;
      END LOOP;
      v_list(j+1) := v_key;
    END;
  END LOOP;

  FOR i IN 1 .. v_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(i || '. ' || v_list(i).nomCH || ' - Pourcentage audimat : ' || TO_CHAR(v_list(i).pourcentage * 100, 'FM99990.00') || '%');
  END LOOP;
END;

Réponse : La procédure PS_CLASS_CHAINE affiche la liste numérotée des chaînes triées par pourcentage d’audimat décroissant.

Exercice 7

Créer une procédure stockée PS_TOP_PROG(p_heureDeb number, p_heureFin number, p_date date) qui affiche le(s) nom(s) du ou des programme(s) ayant réalisé l’audimat le plus élevé pour la plage horaire et la date indiquées. Traiter les exceptions suivantes :

• L’heure de fin doit être supérieure à l’heure de début.
• La date ne doit pas être supérieure à la date d’hier.

Étapes :

1. Vérifier que p_heureFin > p_heureDeb, sinon lever une exception.
2. Vérifier que p_date <= SYSDATE - 1, sinon lever une exception.
3. Rechercher dans SONDAGE les programmes dont l’heure de début est comprise dans la plage horaire donnée à la date donnée.
4. Déterminer l’audimat maximal parmi ces programmes.
5. Afficher le(s) titre(s) du ou des programme(s) ayant cet audimat maximal.

CREATE OR REPLACE PROCEDURE PS_TOP_PROG(p_heureDeb NUMBER, p_heureFin NUMBER, p_date DATE) IS
  v_max_audimat NUMBER;
BEGIN
  IF p_heureFin <= p_heureDeb THEN
    RAISE_APPLICATION_ERROR(-20001, 'L''heure de fin doit être supérieure à l''heure de début.');
  END IF;

  IF p_date > TRUNC(SYSDATE) - 1 THEN
    RAISE_APPLICATION_ERROR(-20002, 'La date ne doit pas être supérieure à la date d''hier.');
  END IF;

  SELECT MAX(S.audimat) INTO v_max_audimat
  FROM SONDAGE S
  JOIN PROGRAMME P ON S.idP = P.idP AND S.idCH = P.idCH
  WHERE P.heure_debut BETWEEN p_heureDeb AND p_heureFin
    AND S.dateS = p_date;

  FOR rec IN (
    SELECT P.titre
    FROM SONDAGE S
    JOIN PROGRAMME P ON S.idP = P.idP AND S.idCH = P.idCH
    WHERE P.heure_debut BETWEEN p_heureDeb AND p_heureFin
      AND S.dateS = p_date
      AND S.audimat = v_max_audimat
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Programme avec audimat maximal : ' || rec.titre);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Réponse : La procédure PS_TOP_PROG affiche le(s) programme(s) avec l’audimat maximal dans la plage horaire et la date données, en gérant les exceptions demandées.

Exercice 8

Créer un trigger TRIG_PERIODICITE qui vérifie la valeur de la colonne jour lors de l’insertion dans la table PROGRAMME :

• Si la périodicité est « quotidienne », la colonne jour doit être nulle.
• Si la périodicité est « hebdomadaire », la colonne jour doit avoir une valeur, sinon l’insertion est bloquée.

Étapes :

• Créer un trigger BEFORE INSERT sur PROGRAMME.
• Tester la valeur de périodicité dans la nouvelle ligne.
• Si « quotidienne », vérifier que jour est NULL.
• Si « hebdomadaire », vérifier que jour n’est pas NULL.
• Sinon, lever une exception pour bloquer l’insertion.

CREATE OR REPLACE TRIGGER TRIG_PERIODICITE
BEFORE INSERT ON PROGRAMME
FOR EACH ROW
BEGIN
  IF :NEW.périodicité = 'quotidienne' THEN
    IF :NEW.jour IS NOT NULL THEN
      RAISE_APPLICATION_ERROR(-20003, 'Pour une périodicité quotidienne, la colonne jour doit être nulle.');
    END IF;
  ELSIF :NEW.périodicité = 'hebdomadaire' THEN
    IF :NEW.jour IS NULL THEN
      RAISE_APPLICATION_ERROR(-20004, 'Pour une périodicité hebdomadaire, la colonne jour doit avoir une valeur.');
    END IF;
  END IF;
END;

Réponse : Le trigger TRIG_PERIODICITE contrôle la cohérence de la colonne jour selon la périodicité et bloque l’insertion en cas d’erreur.

Méthode

Ce sujet récompense la maîtrise des jointures SQL, des agrégations, de la gestion des exceptions PL/SQL, des boucles et des curseurs, ainsi que la capacité à utiliser des fonctions dans des procédures. Il est essentiel de respecter les consignes sur les retours d’erreur (valeurs -1), de vérifier les conditions avant d’exécuter les opérations, et de bien gérer les exceptions pour éviter des comportements inattendus.

Les erreurs fréquentes à éviter sont :

• Oublier de vérifier l’existence d’un enregistrement avant de faire un calcul.
• Ne pas gérer les cas où les sommes ou les comptes sont nuls.
• Confondre les clés primaires et étrangères lors des jointures.
• Ne pas respecter les consignes sur les valeurs retournées en cas d’erreur.
• Omettre la gestion des exceptions dans les procédures critiques.
• Ne pas utiliser la fonction stockée demandée dans l’exercice 4.

Enfin, il faut soigner la clarté des affichages avec DBMS_OUTPUT.PUT_LINE pour faciliter la lecture des résultats.