Cas pratiques Oracle

À partir de la table employees (id, nom, departement_id, salaire), écrivez une requête qui retourne pour chaque departement_id le salaire moyen (AVG) et le nombre d’employés (COUNT). Expliquez l’utilisation de GROUP BY et HAVING si vous voulez filtrer des groupes.

On vous fournit une table employees (id, nom, departement_id, salaire). Écrivez une requête SQL qui retourne le nom et le salaire des employés du département 10 dont le salaire est supérieur à 3000, triés par salaire décroissant. Expliquez brièvement l’usage de la clause WHERE et ORDER BY.

Expliquez comment vous vous connectez à une base Oracle (via SQL*Plus ou un client SQL) avec un utilisateur donné, puis écrivez la requête permettant de lister toutes les tables appartenant à un schéma spécifique. Indiquez les éléments de configuration nécessaires (connexion, SID/Service, utilisateur/mot de passe).

Vous devez rédiger une requête Oracle utilisant une fonction analytique pour calculer le salaire cumulatif (SUM OVER) des employés classés par salaire décroissant. La table employees contient les colonnes (id, nom, departement_id, salaire). Expliquez le rôle de la clause OVER et de ORDER BY dans le contexte des fenêtres analytiques.

Vous avez deux tables : orders (order_id, customer_id, amount) et customers (customer_id, name). On vous demande d’ajouter une contrainte de clé étrangère dans orders pointant vers customers. Expliquez comment créer cette contrainte, ce qu’elle garantit et ce qui se passe si l’on tente d’insérer un order avec un customer_id inexistant.

Vous devez créer une procédure stockée Oracle appelée increase_salary qui reçoit en paramètres : l’identifiant d’un employé (employee_id), un pourcentage d’augmentation (percentage). La procédure doit mettre à jour le salaire de cet employé dans la table employees et afficher un message confirmant l’opération. La table employees est structurée comme suit : employee_id (NUMBER) – identifiant unique de l’employé first_name (VARCHAR2) – prénom last_name (VARCHAR2) – nom department_id (NUMBER) – identifiant du département salary (NUMBER) – salaire actuel hire_date (DATE) – date d’embauche is_active (NUMBER, 0 ou 1) – indique si l’employé est actif Expliquez la structure générale d’une procédure PL/SQL, la gestion des paramètres, le bloc BEGIN … END, ainsi que la requête UPDATE nécessaire pour appliquer l’augmentation.

Vous devez créer un déclencheur Oracle sur la table employees afin de contrôler les mises à jour de salaire. Les règles sont les suivantes : Si un salaire augmente de plus de 20% en une seule mise à jour, l’opération doit être bloquée. Toute modification du salaire, même validée, doit être inscrite dans une table d’audit salary_audit contenant : l’ID de l’employé, l’ancien salaire, le nouveau salaire, la date du changement, l’utilisateur Oracle ayant effectué l’opération. Le déclencheur doit gérer les erreurs, empêcher les boucles et garantir que les données d’audit ne peuvent pas être modifiées par le déclencheur lui-même. Expliquez la structure du déclencheur (BEFORE UPDATE), la façon d’accéder aux valeurs :OLD et :NEW, la logique de validation, l’insertion dans la table d’audit, ainsi que la gestion des exceptions.

Une requête Oracle utilisée par l’équipe BI met plus de 30 secondes à s’exécuter. Elle joint trois tables volumineuses : orders, customers et products, avec plusieurs filtres et ORDER BY. On vous demande d’optimiser cette requête. Votre travail consiste à : analyser le plan d’exécution avec EXPLAIN PLAN, identifier les opérations coûteuses (FULL TABLE SCAN, SORT, HASH JOIN), proposer la création ou modification d’index pertinents, éventuellement réécrire la requête pour améliorer les performances (réduire les sous-requêtes, simplifier les filtres, déplacer certaines conditions), expliquer les impacts possibles sur les performances et la maintenance. Décrivez votre démarche d’analyse, vos recommandations et la justification technique des optimisations proposées.

Vous devez développer un traitement PL/SQL performant pour mettre à jour 2 millions de lignes de la table transactions. Un traitement ligne-par-ligne serait trop lent. Votre mission : récupérer les données par lots (BULK COLLECT avec LIMIT), appliquer une transformation métier (ex : recalcul d’un montant), écrire les mises à jour massives via FORALL, gérer les erreurs ligne par ligne avec SAVE EXCEPTIONS, enregistrer les erreurs dans une table dédiée. Expliquez la structure du bloc PL/SQL, l’utilisation des collections, la gestion des exceptions massives, et l’intérêt des opérations BULK pour améliorer drastiquement les performances.