PLB CONSULTANT : 01. SQL : les fondamentaux

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

Analyse d’un fichier Excel représentant une base de données relationnelles et partagé en réseau entre plusieurs utilisateurs afin de montrer ses limites et donner des exemples concrets des apports d’un SGBDR.

Visite guidée d’une base SQL en production afin de prendre en main l’activité d’un SGBDR au quotidien et se familiariser avec le vocabulaire (bases, tables, utilisateurs, connexion, requêtes SQL, scripts, etc.).

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

Insertion de données dans une table non visualisée par les autres utilisateurs (transaction). Variantes autour des commits et rollbacks pour bien comprendre le rôle d’un SGBDR dans la gestion automatique ou explicite des verrous dans les transactions.

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

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 (création des tables et insertion des données. Nous revenons plus tard sur ce script afin de détailler la syntaxe SQL (séquence, type de données, create table, insert, contraintes).

Le dernier jour de la formation on réalise un export de sa base de données qui est importée par un autre participant selon 2 formats (schéma + données et schéma seul).

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

Exécution de requêtes d’extraction sur une seule table pour prendre en main la syntaxe de baseVariantes sur le filtrage et le tri (where, between, order by, etc.)

« Dessin » des combinaisons du produit cartésien entre 2 tables pour bien ancrer pédagogiquement le concept de jointure dans les SGBDR permettant l’extraction des données depuis plusieurs tables.

Requêtes classiques autour des jointures (caractéristiques des articles d’une commande, liste d’ouvrages publié par un éditeur, détails des missions assurées par une personne, etc.)

Quizz de synthèse sur la journée

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

Reprise détaillée du script de création de l’environnement (tables, types de données, séquences, insert…).

Ce script présente le gros intérêt de pouvoir régénérer son environnement à tout instant afin de ne pas perdre de temps suite à une mauvaise manipulation.

Mise à jour des données (update, delete) selon différents critères et en intégrant la casse (problèmes de différences entre minuscule et majuscule dans un nom)

Copie d’une table dans une autre selon critères (insert into select)

Exécution de requêtes utiles au quotidien dans le dictionnaire des données (description d’une table, visualisation des contraintes…)

Quizz autour des erreurs classiques sur la mise à jour des données

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

Chaque fonction de ce chapitre fait l’objet d’un exemple et d’un exercice que nous consolidons ensuite dans des requêtes plus globales (fonctions, opérations arithmétiques, etc.) qui affichent des statistiques fines sur notre base (ventes d’articles par secteur sur certaines périodes selon certains critères).

Manipulations sur les dates afin de comprendre comment les données sont stockées lorsque toutes les informations ne sont pas renseignées lors d’un insert, update, etc. et les solutions aux éventuels problèmes rencontrés lors des extractions (select … where date =’….’).

Quizz reprenant les points clefs du chapitre. Plusieurs requêtes sont proposées pour un même énoncé il faut retenir l’unique bon résultat. Ce quizz fait également office de synthèse sur l’essentiel à retenir sur les fonctions.

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

Utilisation des requêtes ensemblistes dans une approche progressive. Seules d’abord puis « mélangées » avec les concepts vus précédemment (group by, fonctions, jointure, etc.) afin de disposer d’une vue globale et synthétique des possibilités du SQL. Exemples : 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

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 pour comprendre comment elles peuvent aider à la lisibilité des traitements (au-delà l’aspect sécurité).

Dessin des arbres de requêtes correspondants

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)

Ateliers

Exemple de site web utilisant des données issues d’une requête SQL (simple page PHP par exemple).

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

Quiz final d’évaluation des acquis