SQL pour Oracle Perfectionnement BRANCHE INTERBRANCHE 2 J. SQL pour Oracle Perfectionnement Objectifs A la fin du cours, le stagiaire sera capable de : Interroger des données de qualité hétérogène (casse, erreurs de frappe) Comprendre les champs à impliquer prioritairement pour diminuer les temps de réponse Maîtriser des fonctions statistiques avancées en SQL (Etablir des sous-totaux, Calculer des répartitions en pourcentage, Calculer les fréquences organisées en classe, Trouver des rangs, Etablir des palmarès, Calculer une médiane, Calculer un décile, Bâtir des échantillons) Créer des scripts de requêtes ré-employables Créer des tables personnelles d'archivage. Manipuler des enregistrements dans son espace personnel Prérequis Indispensables : Connaissance de base des instructions SELECT Connaissance de base des instructions DDL Pédagogie L'apprentissage prend appui sur une base école dans laquelle sont développés les exemples et exercices d'application. Programme 1. Rappels : Structuration de la base et optimisation des requêtes. Rappels sur les notions de clé primaire & étrangère, d'index et de partition. Intérêt de la mobilisation des champs indexés et partitionnés pour l'optimisation des requêtes. 2. Le requêteur SQL+ commandes avancées Sauvegarder une instruction (SAVE) en écrasant (REPLACE) ou complétant un script (APPEND) Créer un SPOOL – La commande Sho All 1/4 MÉTIERS DE L'INFORMATIQUE SQL pour Oracle Perfectionnement Les options d'environnement : création d'un fichier login personnalisé. Les paramètres FEEDBACK, NUMWIDTH, AUTOCOMMIT, WRAP, TIMING La commande PROMPT 3. Les instructions SELECT : Particularités des opérateurs avertissement sur la gestion des valeurs NULL avec l'opérateur <> lever le problème de la casse avec UPPER recherche par sonorité approchée avec SOUNDEX 4. Les instructions SELECT : les requêtes regroupement les critères sur le résultat de la fonction d'agrégat avec la clause HAVING les requêtes regroupement présentant les sous-totaux avec ROLLUP, CUBE, GROUPING SETS les regroupements sur les dates avec TO_CHAR : les extractions YYYY, MM, MONTH, DAY, Q, WW, DD, DDD 5. Les instructions SELECT : les requêtes avec champs calculés L'affichage de valeurs de substitution aux NULL avec NVL L'affichage de valeurs de substitution aux textes avec DECODE L'affichage de résultats organisés par classe avec CASE WHEN 6. Les sous-requêtes a) Select imbriqué dans le WHERE ou le HAVING: les critères calculés sur un agrégat Les sous-requêtes renvoyant plusieurs lignes avec les opérateurs IN, ANY, ALL. Applications : enregistrements supérieurs à la moyenne, Moyennes par regroupement b) select dans la clause FROM Requêtes effectuant un traitement sur les résultats d'un champ calculé ou d'un regroupement - applications : les palmarès (n premiers /derniers) grâce à ROWNUM -constitutions d'échantillons (sélection au hasard de n enregistrements avec dbms_random.value) 7. Les requêtes paramétrées les variables éphémères avec & les variables persistantes avec && et ACCEPT – rôle de DEFINE & UNDEFINE 8. Les fonctions analytiques La clause de partitionnement PARTITION BY() – Les fonctions de synthèse spécifique au contexte analytique : RANK(), DENSE_RANK() La clause d'ordre ORDER BY Applications : 2/4 MÉTIERS DE L'INFORMATIQUE SQL pour Oracle Perfectionnement a) réalisation d'une fréquence brute cumulée b) Etude de répartition en % grâce à RATIO_TO_REPORT() Les clauses d'ordre avec fenêtrage : création d'une moyenne mobile hebdomadaire. Calcul d'une médiane, d'un décile avec PERCENTILE_CONT(n) Exercice : calcul du 9ème décile 9. Les Instructions de définition de données (commandes DDL) a) Créer une table : l'instruction CREATE TABLE Définition des champs et mise en place de leurs contraintes (type de données, Null interdit, intervalle) Création d'une table à partir du jeu d'enregistrements retournés par une requête CREATE TABLE AS SELECT b) Analyser une table (ANALYSE STATISTICS) c) Créer une clé Primaire : la clause PRIMARY KEY d) Créer un Index (CREATE INDEX ON) e) Supprimer une table (DROP TABLE) f) modifier une table (ALTER TABLE) g) Décrire une table (DESC) h) Créer une clé étrangère : la clause FOREIGN KEY - Les règles d'intégrité référentielle. 10. Les instructions de manipulation de données (commandes DML) a) Ajouter des enregistrements à une table en déclarant les valeurs de chaque enregistrement avec INSERT INTO VALUES en utilisant le jeu d'enregistrements retournés par une requête avec INSERT INTO SELECT b) Mettre à jour les valeurs d'un enregistrement UPDATE..SET Mettre à jour une table à partir des résultats d'une requête : la clause EXISTS c) Supprimer des enregistrements DELETE FROM WHERE d) La validation ou l'annulation des transactions Les commandes ROLLBACK/COMMIT Création d'un savepoint 11. Les instructions de contrôle des donnes (commandes DCL) Donner un privilège à un autre utilisateur avec GRANT Supprimer un privilège avec REVOKE 3/4 MÉTIERS DE L'INFORMATIQUE SQL pour Oracle Perfectionnement Informations pédagogiques PUBLIC Techniciens, informaticiens DURÉE 2 jours (14 heures) CERTIFICATION Un questionnaire d'évaluation de l'acquisition des connaissances est proposé au format papier ou disponible sur Internet à l'issue du stage. MODALITÉ Présentiel INTERVENANTS Spécialiste SQL pour Oracle > Modalités pratiques Ref. CN71P001 4/4 MÉTIERS DE L'INFORMATIQUE