SQL : les fondamentaux

Le langage SQL (Structured Query Language) est incontournable dans le paysage informatique car c'est le langage de référence pour interroger les bases de données. Il permet également de mettre à jour les données et sa standardisation permet de communiquer avec tous les systèmes de gestion de base de données relationnels (SGBDR) tels qu'Oracle, SQL Server, MySQL, PostgreSQL, DB2, etc.

Le succès du SQL provient principalement de la simplicité de sa syntaxe de base qui s'articule toujours autour du même motif (« select …from … where …) même si celui-ci peut s'enrichir pour écrire des requêtes plus complexes (jointures, fonctions, regroupement, etc.). Le SQL peut également se suffire à lui-même même si le résultat brut de nos requêtes est le plus souvent intégré dans des applications de reporting, d'aide à la décision (BI), des outils divers comme les ETL (Extract Transform Load), des applications ou site WEB, etc.

Cette formation SQL vous propose de découvrir comment les SGBDR structurent les données (algèbre relationnelle) pour que le langage SQL puisse les exploiter à travers différents contextes d'utilisations tout en vous rendant autonome dans l'écriture des requêtes SQL fondamentales.

Objectif opérationnel :

Savoir exploiter des bases de données en utilisant les principales fonctionnalités du langage SQL.

Objectifs pédagogiques :

A l'issue de cette formation SQL, vous aurez acquis les connaissances et compétences nécessaires pour :

• Comprendre le modèle d'algèbre relationnelle sur lequel repose le SQL
• Appréhender l'écriture des requêtes SQL (conception et compréhension des requêtes SQL)
• Décrire les principales fonctions d'un SGBDR
• Être à l'aise dans son environnement de développement SQL (IDE)
• Extraire des données avec le SQL (select)
• Mettre à jour les données avec le SQL (insert, update, delete)
• Extraire les données de plusieurs tables (jointure)
• Connaître les principales fonctions du langage SQL
• Comprendre la place du SQL dans les applications et les outils

Cette formation vous prépare au passage de la certification Langage SQL : Exploiter une base de données relationnelle. Cette épreuve d’évaluation “Langage SQL : Exploiter une base de données relationnelle” vient évaluer les compétences suivantes : l’extraction de données à l’aide de requêtes simples et complexes, mettre à jour les données d’une table, et créer des vues.

Elle est constituée de cas pratique qui positionnent le candidat dans une situation similaire à son environnement de travail ainsi que de questions sous la forme d’un questionnaire à choix multiple et à choix unique. *

Cette épreuve d’évaluation couvre les 4 compétences du référentiel de la certification “Langage SQL : Exploiter une base de données relationnelle”, elle est chronométrée et dure 1 heure 30 maximum. La certification est obtenue si le candidat obtient 700 points minimum sur 1000. Un cas pratique vaut dix fois plus de points qu’une question de type QCM/QCU. Un candidat ne peut donc pas obtenir sa certification uniquement avec les questions de type QCM/QCU. Le résultat ainsi obtenu permet de valider les compétences du candidat dans l’exploitation de données avec le langage SQL, dans une base de données relationnelle.

La certification est obtenue si le candidat obtient un score supérieur à 81 points sur 116 soit 700 points minimum sur 1000 (un ratio est fait en fonction du nombre de points obtenus).

Le passage de la certification n'est pas obligatoire et n'est pas compris dans le tarif de la formation. Le prix d'achat de la certification est de 355€

Maîtriser l'utilisation de son poste de travail sous Windows et avoir des notions de gestion des données dans l'entreprise (par exemple la création de tableaux simples dans Excel se référant à des données structurées : référence, nom, adresse, etc.).

J'évalue mes connaissances pour vérifier que je dispose des prérequis nécessaires pour profiter pleinement de cette formation en faisant ce test.

Cette formation est dispensée par un·e ou plusieurs consultant·es d'OCTO Technology ou de son réseau de partenaires, expert·es reconnus des sujets traités.

Le processus de sélection de nos formateurs et formatrices est exigeant et repose sur une évaluation rigoureuse leurs capacités techniques, de leur expérience professionnelle et de leurs compétences pédagogiques.

Jour 1

CONCEPTS ET FONCTIONNEMENT DES SGBDR

• Différences entre données structurées et non structurés
• Besoin d'un formalisme conceptuel pour représenter les données (Modèle Conceptuel des Données, MCD)
• Liens entre algèbre relationnelle, MCD, SGBDR
• Passage au format logique des données dans une base relationnelle (table, colonne, ligne)
• Liens entre les données et contraintes d’intégrité (clefs primaire et étrangères)
• Problématiques liées à l’implémentation physique des données
• Les solutions proposées par les SGBDR
• Un petit mot sur les bases NoSQL par rapport à un SGBDR
• Aperçu des possibilités du langage SQL (select, insert, update, delete)
• Standardisation du SQL par l’ANSI
• Les dialectes
• Utilisateurs et droits (GRANT, REVOKE)
• Notions de transactions (ACID)
• Accès concurrents et verrous (commit, rollback)
• L’objet de base dans un SGBDR : la table
• Pourquoi doit-on typer les données d’une table (varchar, number, date…) ?
• Notion de schéma par utilisateur

Ateliers

Objectifs : Comprendre les fonctions de base d’un SGBDR

Contenu : Conception guidée d’une petite base de données afin de prendre en main la structure relationnelle entre les données, les contraintes d’intégrité (clef primaire, clef secondaire) qui en découlent et faire nos premiers pas en SQL.

L'ENVIRONNEMENT DE TRAVAIL POUR LE SQL

Les IDE (Environnement de Développement Intégré) du marché 

• Les grands éditeurs du marché (Oracle, DB2, SQL Server, PostgreSQL, MySQL, etc.)
• Fonctionnalités des IDE pour un développeur SQL
• Oracle et SQL Developer
• SQL Server et DBA Management Studio
• MySQL et SQL Workbench ou phpMyAdmin
• PostgreSQL et DBeaver
• Autres outils (DbVisualizer, Devart…)
• Paramétrer sa session de travail (formats d’affichage)

Import/Export de données

• Savoir exporter les données de sa base dans un fichier texte
• Savoir importer les données d'un fichier texte, CSV, XML dans la base
• Découvrir comment présenter les résultats des requêtes SQL dans un fichier Excel

Ateliers

Objectif : Prendre en main son environnement

Contenu : Prise en main de l’IDE choisi par le participant (par défaut nous utilisons SQL Developer) afin de se connecter à une base de données, parcourir les éléments fondamentaux des menus et exécuter nos premières requêtes SQL en mode interactif et via des scripts SQL. Exécution du script SQL conçu dans l’atelier précédent permettant de générer la base que nous allons utiliser durant la formation.

SAVOIR ÉCRIRE DES REQUÊTES SQL

Les requêtes SQL de base

• Bien comprendre le schéma relationnel sous-jacent
• Vue générale d’une requête SQL (select, from, where)
• Exemple de requête d’extraction simple sur une seule table
• Les clauses de filtrage (where)
• Trier les résultats (ORDER BY)
• Conditions et opérateurs de comparaison
• Les mots clefs IN, BETWENN, LIKE, IS NULL

Récupérer les données de plusieurs tables

• Comprendre le produit cartésien pour comprendre la jointure
• Récupération de données à partir de plusieurs tables :
• Les jointures internes (INNER JOIN)
• Les corrélations
• Les jointures Himself
• Présentation des jointures externes (OUTER JOIN)

Synthèse

• Comment appréhender l’écriture d’une requête SQL ?
• Par quoi commencer (select ? from ? where ?)
• Modélisation des jointures pour clarifier une requête (arbre)

Ateliers

Objectif : Ecrire des requêtes SQL

Contenu : Exécution de requêtes SQL de difficultés croissantes reprenant les éléments de ce chapitre (filtrage, tri, jointure, etc.).

Jour 2

MANIPULATION DES DONNÉES DE LA BASE

La genèse des données 

• Création de tables en SQL
• Les types de données
• Modification du schéma d’une table (alter table)
• La suppression d'une table (drop table)
• Manipulations de séquences

La mise à jour des données 

• L'insertion de données (insert)
• La mise à jour (update)
• La suppression d'informations (delete)
• Ne pas confondre NULL, 0, et chaîne vide (˜˜)

Ateliers

Objectifs : Comprendre le cycle de vie des données dans une base SQL et savoir les mettre à jour

Contenu : Création de tables, remplissage et mises à jour selon différentes techniques (insert, update, delete, insert associé à un select, etc.) selon une complexité croissante. Script SQL de synthèse permettant de générer une base de données (tables, données, contraintes)

LES FONCTIONS EN SQL

Les classiques

• Présentation des grandes familles de fonctions SQL
• Où peut-on utiliser les fonctions SQL dans une requête ?
• Moyenne des valeurs (avg)
• Nombre d'enregistrements retournés (count)
• Minimum et maximum (min, max)
• Somme des valeurs (sum)

Les fonctions sur les chaînes de caractères

• La longueur d'une chaîne (length)
• Les minuscules et majuscules (lower, upper)
• L'opérateur LIKE et le symbole %
• Extraction d'une partie de la chaîne (substr)
• Gérer les valeurs null (Coalesce, IsNull)

Les fonctions temporelles

• Savoir manipuler une date (current_date)
• Les variantes (premier jour du mois, dernier jour, etc.)
• L’heure courante (current_time)
• Retourner la date et l’heure (current_timestamp)

Les conversions date/nombre/chaîne

• Bien comprendre les formats visibles (AAA-MM-JJ HH :MM :SS, etc.)
• Les pièges classiques avec les dates
• Conversion d'une date en caractères
• Conversion d'une chaîne de caractères en date

Portabilité des fonctions SQL d’un SGBD à un autre

• Exemples de fonctions spécifiques à SQL Server, MySQL, Oracle, etc.
• Les sites des références pour s’assurer de la portabilité de nos requêtes SQL

Ateliers

Objectifs : Savoir utiliser les fonctions SQL standards et rechercher de façon autonome dans les bibliothèques

Contenu : Recherche de texte dans la base, manipulations autour des dates et du temps, opérations arithmétiques, statistiques de ventes d’articles par secteur sur certaines périodes selon des critères fins.

Jour 3

REQUÊTES ENSEMBLISTES EN SQL

• Regroupement et condition (group by, having)
• Le cumul de résultats (union)
• Le recoupement de résultats (intersect, minus)

Ateliers

Objectif : Mettre en œuvre les requêtes ensemblistes

Contenu : Utilisation des requêtes ensemblistes dans une approche progressive.

Exemple : moyenne des ventes par département à partir d’un nombre significatif de ventes minimum.

REQUÊTES COMPLEXES EN SQL  

• Bien comprendre les sous-interrogations dans le where
• Bien comprendre les sous-requêtes synchronisées (where exists)
• Variantes sur la jointure (auto jointure, jointure complexe)
• Retour sur l’arbre de requêtes SQL pour les requêtes complexes
• Principes des vues (view)
• Quand utiliser les vues ?
• Notion de performances des requêtes
• Principes d’un index

Ateliers

Objectifs : Disposer d’une méthode pour écrire des requêtes plus complexes qu’une « simple » jointure

Contenu : Jointure sur 3 tables et plus, modélisation de la requête sous forme d’arbre. Création et utilisation de vues dans les requêtes

LE SQL DANS LES APPLICATIONS ET LES OUTILS

• Utilisation du SQL dans les applications ou sites Web
• Limites du SQL « seul » et apports des procédures stockées
• Liens entre Business Intelligence (BI) et SQL
• SQL et les outils d’aide à la décision (Power BI, Tableau, Webi)
• Création et stockage de requêtes SQL dans les ETL (Extract-Transform-Load)
• Import/Export de données au format SQL

Ateliers

Objectifs : Comprendre qu’au-delà des résultats bruts visibles de façon interactive les requêtes SQL sont encapsulées dans diverses sources applicatives

Contenu : Exemple d’intégration de requête SQL dans le code d’un site ou d’une application WEB. Exemple de requête SQL dans un ETL pour extraire les données d’une base à des fins de chargement dans une autre. Exemple de requêtes SQL utilisées dans une présentation graphique d’aide à la décision avec Power BI. Export des données d’une base (format SQL) et import dans une autre bas