Cours Introduction au langage SQL en PDF (Avancé)

Le SQL (Structured Query Language) est le langage standard de communication avec les SGBDR, permettant de définir le schéma, manipuler les données et interroger des jeux de tuples stockés en tables. La maîtrise des constructions DDL/DML/DQL, des contraintes déclaratives (clé primaire, clé étrangère, NOT NULL) et des mécanismes procéduraux (procédures stockées, curseurs, triggers) est essentielle pour concevoir, interroger et maintenir des bases relationnelles en production. Le cours présente le langage conforme au standard ISO/ANSI et met en évidence les différences de dialecte pertinentes pour Oracle, MySQL et PostgreSQL.

🎯 Ce que vous allez apprendre

• Architecture SGBDR et vocabulaire — comprendre la distinction entre SGBD et SGBDR, les rôles serveur/client et les différences pratiques entre Oracle, MySQL et SQL Server. Situer les composants (catalogue, schéma, tables) et utiliser les vues d'administration (par ex. USER_CONSTRAINTS, SHOW TABLE STATUS) pour inspecter un système.
• Définition du schéma : DDL et contraintes déclaratives — créer et modifier des tables avec CREATE TABLE et ALTER TABLE, déclarer PRIMARY KEY, FOREIGN KEY, DEFAULT et NOT NULL, nommer et supprimer des contraintes. Résultat concret : modéliser l'intégrité référentielle et empêcher les insertions invalides entre tables liées.
• Manipulation et interrogation : SELECT, WHERE, ORDER BY — composer des requêtes avec projection, filtrage (conditions, NULL, BETWEEN), tri et élimination des doublons avec DISTINCT. Relier les opérations d'algèbre relationnelle (projection, sélection, jointure, produit cartésien) aux expressions SQL pour produire des rapports opérationnels et déboguer des résultats liés aux valeurs NULL.
• Jointures et produit cartésien — maîtriser le principe des jointures (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN), détecter et corriger un produit cartésien involontaire et appliquer des jointures réflexives.
• Agrégation et groupage — utiliser les fonctions d'agrégation (SUM, COUNT, AVG, MIN, MAX) conjointement avec GROUP BY pour synthétiser des jeux de données. Comprendre les règles de granularité et d'agrégation pour éviter les erreurs de regroupement.
• SQL procédural : vues, sous-requêtes, procédures, curseurs et triggers — créer des vues pour masquer la complexité, écrire sous-requêtes scalaires et corrélées, et implémenter des procédures stockées (y compris PL/SQL pour Oracle), curseurs et triggers pour des traitements côté serveur. Automatiser des tâches, gérer des flux transactionnels et mesurer l'impact des curseurs/triggers sur la performance.

📑 Sommaire du document

1. Notes de cours
2. Livraisons Sans contraintes
3. Modules et prérequis
4. Géométrie
5. Livraisons
6. Arbre généalogique
7. Comptes bancaires

💡 Pourquoi choisir ce cours ?

Le document d'Alexandre Meslé combine exposé conceptuel et exemples opérationnels tirés de cas concrets (livraisons, banque, arbre généalogique) : de la définition du schéma aux requêtes de production. L'approche privilégie la pratique avec extraits de consoles Oracle/MySQL, requêtes types et commandes d'administration pour faciliter la mise en oeuvre en environnement réel. La présence de modules procéduraux (procédures stockées, curseurs, triggers et PL/SQL) fournit des outils adaptés aux contraintes de production. Document rédigé par Alexandre Meslé, fondé sur des cas d'usage en production et conforme aux recommandations ISO/ANSI.

👤 À qui s'adresse ce cours ?

• Public cible : étudiants en informatique et développeurs backend qui conçoivent ou interrogent des bases relationnelles, administrateurs de bases souhaitant formaliser les contraintes d'intégrité et développeurs produisant des rapports multi-tables.
• Prérequis : connaissance pratique de la modélisation relationnelle (tables, clés), familiarité avec les requêtes SELECT/WHERE de base et accès à un SGBDR (MySQL ou Oracle) via console ou client SQL.
• Pont théorie-pratique : le support fait le lien entre le modèle relationnel et la pratique, en illustrations comment les opérateurs relationnels se traduisent en requêtes avancées.

TP SQL Avancé : Exercices SQL et Travaux Pratiques (TP)

TP SQL proposés pour progresser sur l'écriture de requêtes complexes et la manipulation de transactions. Jeux d'exercices corrigés SQL inclus : conception de schémas, requêtes multi-jointures, sous-requêtes corrélées, optimisation avec index et analyse de plans d'exécution. Objectifs pédagogiques : renforcer le modèle relationnel pratique, automatiser des traitements via procédures et scripts, et valider les résultats sur un SGBDR réel. TP bases de données prévoient des cas métiers (livraisons, comptes bancaires, arbre généalogique) et fournissent jeux de données ainsi que corrections commentées pour chaque exercice.

• Exercices corrigés SQL : requêtes complexes, sous-requêtes et transactions.
• TP bases de données : scripts d'initialisation, données de test et étapes d'évaluation.
• Mesures d'impact : comparer plans d'exécution avant/après indexation.

❓ Foire Aux Questions (FAQ)

Quelle est la conséquence d'un produit cartésien en SQL ?

Un produit cartésien résulte d'une jointure sans condition de liaison et multiplie le nombre de tuples, faussant les résultats et dégradant fortement les performances. La correction implique l'ajout d'une clause de jointure appropriée (ON ou WHERE) qui lie les clés primaires/étrangères ou la réécriture en jointure explicite.

Quand utiliser une vue vs une procédure stockée ?

Une vue convient pour encapsuler une projection ou un filtre réutilisable et améliorer la lisibilité des requêtes, tandis qu'une procédure stockée est adaptée pour des traitements complexes, des opérations DML ou des logiques transactionnelles côté serveur. Le choix dépend des besoins en paramètres, des effets de bord et des contraintes de performance.

Lien avec l'algèbre relationnelle

Le SQL implémente le modèle relationnel d'E. F. Codd : les opérations de projection, sélection, union, différence et jointure se traduisent en constructions SQL identifiables. Comprendre these correspondances facilite la formulation de requêtes correctes et performantes et permet d'anticiper les implications algorithmiques des plans d'exécution.

Définitions essentielles : l'union combine les tuples de deux relations compatibles en éliminant les doublons ; la différence (R − S) retourne les tuples présents dans R et pas dans S ; le produit cartésien crée toutes les paires possibles entre deux relations et sert de base aux jointures mais peut être source d'erreurs si utilisé sans condition.

Correspondance Algèbre Relationnelle

Syntaxe des jointures normalisées

Les principales jointures standardisées et leur usage : INNER JOIN pour l'intersection, LEFT JOIN et RIGHT JOIN pour conserver les lignes d'une table externe, et FULL JOIN pour l'union des ensembles. L'INNER JOIN est souvent qualifié d'EQUIJOIN lorsqu'il repose sur l'égalité entre colonnes. Le NATURAL JOIN effectue la jointure sur toutes les colonnes homonymes ; son usage demande prudence car il peut introduire des liaisons implicites inattendues.

FULL OUTER JOIN — simulation et pourquoi MySQL l'exige : certains moteurs, MySQL en particulier, n'ont pas d'implémentation native de FULL JOIN historique. Pour obtenir l'équivalent, combiner une LEFT JOIN et une RIGHT JOIN avec UNION permet de couvrir toutes les lignes des deux tables; il faut veiller à éliminer les doublons et à gérer explicitement les colonnes nulles résultantes.

-- Simulation d'un FULL OUTER JOIN à l'aide de LEFT + RIGHT UNION
SELECT a.*, b.*
FROM A a
LEFT JOIN B b ON a.id = b.id
UNION
SELECT a.*, b.*
FROM A a
RIGHT JOIN B b ON a.id = b.id;

-- Exemple : LEFT JOIN conservant toutes les commandes même sans client associé
SELECT c.client_id, c.nom, o.commande_id, o.montant
FROM clients c
LEFT JOIN commandes o ON c.client_id = o.client_id;

Conformité et portabilité entre dialectes

Le matériel insiste sur les bonnes pratiques d'écriture portable et repère les différences de dialecte (types, fonctions intégrées, gestion des transactions). Les alternatives proposées couvrent Oracle, MySQL et PostgreSQL afin d'écrire des requêtes robustes et maintenables, tout en expliquant quand exploiter des extensions spécifiques.

Principes de l'algèbre relationnelle et SQL

Les principes fondamentaux de l'algèbre relationnelle fournissent une base formelle pour le langage de définition de données et les requêtes. Le cours relie les opérateurs algébriques aux constructions SQL et explique comment ces correspondances influent sur l'optimisation et le plan d'exécution. L'approche présente à la fois la théorie nécessaire pour raisonner sur la correction des requêtes et des exemples pratiques pour valider les résultats sur un SGBD relationnel.

Différences entre les dialectes SQL (Oracle, MySQL, PostgreSQL)

• Gestion des dates et fonctions temporelles : les types et fonctions diffèrent (par ex. DATE/TIMESTAMP, formats et fonctions de parsing). Adapter les requêtes garantit la portabilité entre Oracle, MySQL et PostgreSQL.
• Auto-incrémentation / séquences : MySQL propose AUTO_INCREMENT, tandis qu'Oracle utilise des SEQUENCEs combinées à TRIGGERs ; PostgreSQL offre SERIAL ou GENERATED ... AS IDENTITY.
• Syntaxe de limitation et support de FULL JOIN : le mot-clé LIMIT est courant en MySQL/PostgreSQL, Oracle utilise FETCH FIRST ou la pseudo-colonne ROWNUM, et le support natif de FULL JOIN peut varier.
• Procédural : Oracle propose PL/SQL ; PostgreSQL utilise PL/pgSQL et MySQL ses propres procédures stockées. Ces langages influencent la gestion des transactions, des exceptions et des performances côté serveur.

Installation et Environnement

Pour tester les scripts fournis, installer un environnement local minimal : soit Oracle Database Express Edition (XE) pour reproduire le comportement Oracle, soit MySQL Community Server pour un environnement léger et courant. Après installation, initialiser une instance, créer un utilisateur avec les droits nécessaires et exécuter les scripts via la console (sqlplus pour Oracle, mysql pour MySQL) ou via un client graphique. Vérifier la version du SGBDR et adapter les types/contraintes selon les besoins ; conserver une copie des scripts avant exécution en production.

• MySQL 8.0+
• Oracle 12c / 19c
• PostgreSQL 10+

• DBeaver (client multi-SGBD)
• MySQL Workbench
• sqlplus (Oracle)
• pgAdmin (PostgreSQL)

Cas pratiques : de l'algèbre relationnelle au SQL

Exercices guidés transposant opérateurs algébriques en requêtes : formulation de projections et sélections pour produire des rapports métier, réécriture d'expressions relationnelles en sous-requêtes corrélées, et conversion d'un produit cartésien accidentel en jointure correcte. Ces cas pratiques incluent jeux de données simplifiés, scripts d'initialisation et requêtes d'analyse permettant de vérifier la correction des résultats et d'observer l'impact sur les plans d'exécution. L'objectif est de rendre la démarche reproductible en production.

Optimisation des requêtes et plans d'exécution

Présentation des méthodes d'analyse des plans d'exécution (EXPLAIN/EXPLAIN ANALYZE), interprétation des coûts et techniques d'optimisation : indexation adaptée, réécriture des jointures, limitation des champs projetés et gestion des statistiques. Exemples montrant comment une mauvaise sélection d'index ou l'usage excessif de curseurs peut dégrader les performances. Bonnes pratiques d'optimisation pour Oracle, MySQL et PostgreSQL afin d'orienter les choix en production.