Description de la formation SQL Oracle Avancé
Le langage SQL (Structured Query Language) est dédié avant tout à l’extraction de données même si sa grammaire permet de manipuler d’autres objets que les tables et leurs contenus. Il est donc assez logique qu’au fil des années il se soit enrichi afin de proposer aux développeurs SQL de plus en plus de fonctions et de possibilités.
Cette formation SQL avancé pour Oracle vous apprendra à utiliser la puissance méconnue du langage SQL à travers notamment les expressions régulières, les requêtes hiérarchiques, les opérateurs ensemblistes, et les aspects avancés de la consultation et de la manipulation des données en SQL.
Enfin un dernier chapitre vous fera comprendre comment Oracle stocke les données pour mieux maîtriser votre environnement de développement et être autonome dans le diagnostic des problèmes rencontrés (espace, performance, droit, etc.) et les solutions à apporter.
Objectifs
Objectif opérationnel :
Savoir utiliser la puissance méconnue du langage SQL.
Objectifs pédagogiques :
À l’issue de cette formation SQL avancé pour Oracle vous aurez acquis les connaissances et les compétences nécessaires :
- Connaitre les expressions régulières et le SQL
- Savoir écrire des requêtes hiérarchiques
- Utiliser les opérateurs ensemblistes en SQL
- Maitriser les fonctionnalités SQL avancées en consultation
- Connaitre la gestion des structures de stockage
À qui s’adresse cette formation ?
Public :
Développeur, Chef de projet, Administrateur de base de données (DBA)
Prérequis :
Avoir suivi la formation SQL pour Oracle (BSPO) ou avoir les connaissances équivalentes.
Contenu du cours SQL Oracle Avancé
Expressions régulières et SQL
Limites de l’opérateur LIKE
Philosophie des expressions régulières
Etendre les méta-caractères (^,. ,*,[, …)
Syntaxe générale des fonctions
Fonction : regexp_like.
Fonction : regexp_substr.
Fonction : regexp_instr.
Fonction : regexp_replace.
Fonction : regexp_count.
Travaux pratiques
Extraction de paragraphe dans des flux JSON ou XML
Récupération de noms de fichiers sans extension dans une arborescence
Recherche sur de motifs complexes dans des numéros de téléphone
Requêtes hiérarchiques
Définition du lien entre les noeuds de l’arbre : CONNECT BY.
Début du parcours de l’arbre : START WITH.
Niveau du noeud dans l’arbre : LEVEL.
Elimination de lignes ou de branches.
Fonctions propres aux requêtes hiérachiques.
Travaux pratiques
Manipulation d’organigramme de ressources humaines (hiérarchie de personnes dans les départements et régions, extraction de sous-branches selon différents critères)
Utiliser les opérateurs ensemblistes en SQL
Règles à respecter pour assembler des requêtes :
nombre d’expressions projetées,
types des expressions projetées.
Les opérateurs UNION, UNION ALL, INTERSECT et MINUS.
Trier les information.
Travaux pratiques
Rassembler plusieurs requêtes en une seule (ensembliste
Comparaison des performances
Compléments sur le regroupement de données
Rappels sur GROUP BY et HAVING.
Constitution de sous-groupes avec ROLLUP et CUBE.
Application partielle et totale ROLLUP et CUBE.
Identification de lignes générées par ROLLUP et CUBE avec les fonctions GROUPING et GROUPING_ID.
Gestion des NULLs.
Identification de groupes dupliqués avec la fonction GROUP_ID.
Travaux pratiques
Comparaisons des performances de requêtes SQL utilisant UNION, ROLLUP, ou CUBE
Comment gérer les NULL « natifs » et ceux générés par ROLLUP ou CUBEManipulation d’organigramme de ressources humaines(hiérarchie de personnes dans les départements et régions)
Fonctionnalités SQL avancées en consultation
Sous-requête avec WITH.
Numérotation des lignes par RANK, DENSE_RANK et ROW_NUMBER
avec ou sans partitionnement.
Passer de lignes en colonnes et inversement avec PIVOT et UNPIVOT.
Utilisation de fonctions analytiques (LAG, LEAD, …)
Travaux pratiques
Requête prévisionnelles (place dans une série de chiffre d’affaires, de salaires, …)
Extraire un pourcentage d’une série (nombre de personnes présentes dans telle première partie d’une série)
Variations sur la présentation des données avec PIVOT
Fonctionnalités SQL avancées en manipulation de données
Insertion multitables
inconditionnelles,
inconditionnelles avec “rotation”,
conditionnelles,
conditionnelles exclusives.
Insertion ou modification avec la commande MERGE.
Utilisation de sous requêtes corrélées ou interrogation sur tuples.
Manipulation et consultation avec la clause RETURNING.
Travaux pratiques
Fusion des données d’une table dans une autre sans écrasement
Affichage par département de la personne présentant le plus petit salaire (interrogation sur tuple)
Gestion des structures de stockage
Espace de stockage à travers les tablespaces.
Tablespaces par défaut et accessibles.
Quotas sur tablespaces.
Désignation de tablespaces pour les structutres tables et index.
Désignation de tablespaces au niveau des contraintes d’unicité et d’identifiant.
Objets avancés : Tables externes, Database Link, Vues matérialisées.
Travaux pratiques
Mise en place d’un environnement de développement de A à Z (tablespace, utilisateur, rôle, droits, etc.) afin de disposer de recul sur les différents problèmes potentiels rencontrés au quotidien (espace, performances, droits, etc.) en comprenant le mécanisme de stockage proposé par Oracle et en le mettant soi-même en œuvre, comme le ferait un DBA pour une nouvelle application ou un nouvel utilisateur.
Travaux Pratiques
Ils représentent 60% du temps de la formation et sont réalisés par défaut sur une version Oracle 12c.