SQL (Structured Query Language) est le langage standard pour interagir avec les bases de données relationnelles. On peut se demander si la maîtrise de SQL ne constitue pas l’une des compétences les plus durables en data science, tant ce langage reste inchangé depuis des décennies ?

Pourquoi SQL en data science ?

SQL offre plusieurs avantages cruciaux :

• Universalité : Compatible avec MySQL, PostgreSQL, SQLite, BigQuery, etc.
• Performance : Optimisé pour manipuler des millions de lignes
• Clarté : Syntaxe déclarative proche du langage naturel
• Standard : Compétence transférable entre projets et entreprises
• Puissance : Agrégations, jointures, fenêtres analytiques

Les trois niveaux de SQL

Les 8 mots-clés fondamentaux

L’ordre canonique

Propriété essentielle : L’ordre des clauses SQL est rigide et doit toujours être respecté.

SELECT   -- 1. Quelles colonnes afficher ?
FROM     -- 2. Quelle est la source ?
JOIN     -- 3. Joindre d'autres tables ?
WHERE    -- 4. Quelles lignes inclure ?
GROUP BY -- 5. Regrouper sur quelles colonnes ?
HAVING   -- 6. Filtrer après agrégation ?
ORDER BY -- 7. Comment trier ?
LIMIT    -- 8. Combien de lignes retourner ?

Mnémotechnique : “Select From Join Where Group Having Order Limit”

Décryptage clause par clause

SELECT : Projection

Définition : Spécifie les colonnes à afficher (projection).

SELECT player_name, weight, height
FROM Player;

Propriétés :

• SELECT * : Sélectionne toutes les colonnes (⚠️ à éviter en production)
• Peut inclure des calculs : SELECT height / 2.54 AS height_cm
• Peut inclure des fonctions : SELECT UPPER(player_name)

FROM : Source des données

Définition : Indique la table source.

SELECT *
FROM Player;

Propriété : Toute requête SELECT nécessite un FROM (sauf cas particuliers comme SELECT 1 + 1).

WHERE : Filtrage des lignes

Définition : Filtre les lignes avant l’agrégation.

SELECT player_name, weight
FROM Player
WHERE weight > 200;

Opérateurs disponibles :

• Comparaison : =, !=, <, >, <=, >=
• Logiques : AND, OR, NOT
• Patterns : LIKE, IN, BETWEEN
• Nullité : IS NULL, IS NOT NULL

GROUP BY : Agrégation

Définition : Regroupe les lignes ayant les mêmes valeurs.

SELECT country_id, COUNT(*)
FROM Match
GROUP BY country_id;

Règle d’or : Toute colonne dans SELECT doit être :

• Soit dans GROUP BY
• Soit agrégée (COUNT, SUM, AVG, etc.)

HAVING : Filtrage post-agrégation

Définition : Filtre les groupes après l’agrégation.

SELECT country_id, COUNT(*) AS nb_matches
FROM Match
GROUP BY country_id
HAVING COUNT(*) > 100;

Différence WHERE vs HAVING :

ORDER BY : Tri

Définition : Trie les résultats.

SELECT player_name, weight
FROM Player
ORDER BY weight DESC;

Propriétés :

• ASC : Ordre croissant (défaut)
• DESC : Ordre décroissant
• Tri multiple : ORDER BY country_id, weight DESC

LIMIT : Pagination

Définition : Limite le nombre de lignes retournées.

SELECT player_name, weight
FROM Player
ORDER BY weight DESC
LIMIT 10;

Usage typique : Top N, pagination, échantillonnage.

Agrégations : Résumer les données

Agrégation globale

Définition : Calculer des statistiques sur toute la table, résultant en une seule ligne.

Fonctions d’agrégation standard :

Exemple : Statistiques sur le poids des joueurs

SELECT COUNT(*) AS nb_players,
       MIN(weight) AS min_weight,
       AVG(weight) AS avg_weight,
       MAX(weight) AS max_weight
FROM Player;

Résultat :

Agrégation par groupes

Concept : Au lieu d’une seule ligne, on obtient une ligne par groupe.

Exemple : Nombre de matchs par pays

SELECT country_id, COUNT(*) AS nb_matches
FROM Match
GROUP BY country_id;

Résultat :

Propriété cruciale : Si une colonne apparaît dans SELECT sans être agrégée, elle doit apparaître dans GROUP BY.

-- ❌ ERREUR
SELECT country_id, match_date, COUNT(*)
FROM Match
GROUP BY country_id;

-- ✅ CORRECT
SELECT country_id, match_date, COUNT(*)
FROM Match
GROUP BY country_id, match_date;

Manipulation de données

Opérations arithmétiques

Concept : SQL permet des calculs sur les colonnes.

Exemple : Convertir le poids de livres en kilogrammes

SELECT COUNT(*) AS nb_players,
       ROUND(MIN(weight) / 2.205, 0) AS min_weight_kg,
       ROUND(AVG(weight) / 2.205, 0) AS avg_weight_kg,
       ROUND(MAX(weight) / 2.205, 0) AS max_weight_kg
FROM Player;

Opérateurs disponibles :

• Arithmétiques : +, -, *, /, % (modulo)
• Fonctions : ROUND(), FLOOR(), CEIL(), ABS()

Fonctions de chaînes de caractères

Fonctions courantes :

Exemple : Extraire le prénom des joueurs

SELECT player_name,
       SUBSTR(player_name, 1, INSTR(player_name, ' ') - 1) AS first_name
FROM Player
LIMIT 5;

Window Functions : Puissance analytique

Concept fondamental

Définition : Les fonctions de fenêtre (window functions) permettent d’effectuer des calculs sur un ensemble de lignes en relation avec la ligne courante, sans réduire le nombre de lignes.

Différence clé avec GROUP BY :

Analogie : Imaginez une feuille de calcul Excel où vous ajoutez une colonne avec une formule qui “regarde” d’autres lignes.

Syntaxe générale

<fonction>() OVER (
    PARTITION BY <colonnes>  -- Optionnel : crée des groupes
    ORDER BY <colonnes>      -- Optionnel : définit l'ordre
)

Propriétés :

• PARTITION BY : Divise les données en sous-groupes (comme un mini GROUP BY)
• ORDER BY : Définit l’ordre dans chaque partition (crucial pour rangs, cumuls)
• Les deux sont indépendants et optionnels

Fonctions de fenêtre principales

Exemples

Exemple 1 : Fenêtre globale

Objectif : Calculer la part de chaque commande dans le total des ventes.

Sans fenêtre (agrégation classique) :

SELECT SUM(amount) AS total
FROM orders;

Résultat : Une seule ligne avec le total.

Avec fenêtre :

SELECT amount,
       SUM(amount) OVER() AS total,
       amount / SUM(amount) OVER() AS share
FROM orders;

Résultat : Chaque ligne conserve son amount, avec le total répété et la share calculée.

Exemple 2 : Partition par client

Objectif : Part de chaque commande dans le total du client.

SELECT customer_id,
       amount,
       SUM(amount) OVER(PARTITION BY customer_id) AS customer_total,
       amount / SUM(amount) OVER(PARTITION BY customer_id) AS share
FROM orders;

Résultat :

Interprétation : PARTITION BY customer_id crée une “fenêtre” par client.

Exemple 3 : Cumul temporel

Objectif : Somme cumulée des ventes d’un client au fil du temps.

SELECT customer_id,
       ordered_at,
       amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY ordered_at
       ) AS cumulative_amount
FROM orders;

Résultat :

Propriété : ORDER BY transforme la fenêtre en fenêtre glissante (du début jusqu’à la ligne courante).

Exemple 4 : Classement (RANK)

Objectif : Classer les commandes d’un client par ordre chronologique.

SELECT customer_id,
       ordered_at,
       amount,
       RANK() OVER (
           PARTITION BY customer_id
           ORDER BY ordered_at
       ) AS order_rank
FROM orders;

Résultat :

Note : Deux commandes à la même date ont le même rang (2), et le rang suivant saute (4).

Différence entre RANK, DENSE_RANK et ROW_NUMBER

SELECT value,
       ROW_NUMBER() OVER (ORDER BY value) AS row_num,
       RANK() OVER (ORDER BY value) AS rank,
       DENSE_RANK() OVER (ORDER BY value) AS dense_rank
FROM scores;

Résultat :

Différences :

• ROW_NUMBER() : Toujours unique, même en cas d’égalité
• RANK() : Saute des rangs en cas d’égalité
• DENSE_RANK() : Ne saute pas de rangs

CTE (Common Table Expressions) : Structurer les requêtes

Définition et syntaxe

Définition : La clause WITH crée une table temporaire nommée (CTE) réutilisable dans la requête principale.

Syntaxe :

WITH nom_cte AS (
    -- Requête SQL interne
    SELECT ...
)
SELECT ...
FROM nom_cte;

Propriétés :

• La CTE existe uniquement pendant la requête
• Peut être référencée comme une table normale
• Améliore la lisibilité et la maintenabilité

Pourquoi utiliser les CTE ?

Exemple : Calcul de variance

Objectif : Calculer la variance des poids des joueurs.

Formule : $\text{Var}(X) = \frac{1}{n} \sum (x_i - \bar{x})^2$

Sans CTE (sous-requête imbriquée) :

SELECT AVG(
    POWER(weight - (SELECT AVG(weight) FROM Player), 2)
) AS var_weight
FROM Player;

Problème : La moyenne est recalculée pour chaque ligne !

Avec CTE (calcul optimisé) :

WITH avg_weight_cte AS (
    SELECT AVG(weight) AS avg_weight
    FROM Player
)
SELECT AVG(
    POWER(weight - avg_weight_cte.avg_weight, 2)
) AS var_weight
FROM Player, avg_weight_cte;

Avantages :

• Moyenne calculée une seule fois
• Code plus lisible
• Facile à maintenir

CTE multiples

Concept : On peut enchaîner plusieurs CTE.

Exemple : Moyenne → Variance → Écart-type

WITH avg_cte AS (
    SELECT AVG(weight) AS avg_weight
    FROM Player
),
var_cte AS (
    SELECT AVG(POWER(weight - avg_cte.avg_weight, 2)) AS var_weight
    FROM Player, avg_cte
)
SELECT SQRT(var_cte.var_weight) AS std_dev
FROM var_cte;

Processus :

1. avg_cte : Calcule la moyenne
2. var_cte : Utilise avg_cte pour calculer la variance
3. Requête finale : Utilise var_cte pour l’écart-type

CTE + Window Functions

Synergie puissante : Combiner CTE et fenêtres pour des analyses complexes.

Exemple : Cumul mensuel de matchs

Étape 1 : CTE pour compter les matchs par mois

WITH matches_per_month AS (
    SELECT STRFTIME('%Y-%m', DATE(date)) AS period,
           COUNT(*) AS cnt
    FROM Match
    GROUP BY period
)
SELECT period, cnt
FROM matches_per_month
ORDER BY period;

Étape 2 : Ajout du cumul avec une fenêtre

WITH matches_per_month AS (
    SELECT STRFTIME('%Y-%m', DATE(date)) AS period,
           COUNT(*) AS cnt
    FROM Match
    GROUP BY period
)
SELECT period,
       cnt,
       SUM(cnt) OVER (ORDER BY period) AS cumulative_count
FROM matches_per_month;

Résultat :

Interprétation : On voit l’évolution progressive du nombre total de matchs.

CTE récursives (avancé)

Définition : Une CTE peut s’appeler elle-même pour parcourir des hiérarchies ou construire des séquences.

Exemple : Générer une séquence de 1 à 5

WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 5
)
SELECT * FROM numbers;

Résultat :

Usage typique : Graphes, arbres, organigrammes, chemins.

SQL avec Python (Pandas + sqlite3)

Connexion à une base de données

Téléchargement d’une base exemple :

!mkdir -p data
!curl -s https://wagon-public-datasets.s3.amazonaws.com/sql_databases/soccer.sqlite > data/soccer.sqlite

Connexion avec context manager :

from sqlite3 import connect
import pandas as pd

with connect('data/soccer.sqlite') as conn:
    pass  # Faire quelque chose avec la connexion

Pourquoi with ?

Le with est un context manager qui garantit :

• Fermeture automatique de la connexion
• Gestion des erreurs propre
• Pas de fuite de ressources

Alternative (déconseillée) :

conn = connect('data/soccer.sqlite')
# Faire quelque chose
conn.close()  # Oubli possible ! Problème si erreur avant !

Lecture de données avec Pandas

Méthode : pd.read_sql(query, con)

with connect('data/soccer.sqlite') as conn:
    query = """
        SELECT *
        FROM Player
        LIMIT 5
    """
    players_df = pd.read_sql(query, con=conn)

players_df

Résultat :

Avantages :

• Résultat directement en DataFrame
• Manipulation avec Pandas ensuite
• Visualisations faciles

Requêtes dynamiques (sécurisées)

❌ Mauvaise méthode : f-string

Danger : Vulnérable aux injections SQL

name = "John%"

with connect('data/soccer.sqlite') as conn:
    query = f"""
        SELECT *
        FROM Player
        WHERE player_name LIKE '{name}'
    """
    players_df = pd.read_sql(query, con=conn)

Problème : Si name vient d’un utilisateur malveillant :

name = "'; DROP TABLE Player; --"

La requête devient :

SELECT * FROM Player WHERE player_name LIKE ''; DROP TABLE Player; --'

💥 La table est supprimée !

✅ Bonne méthode : Paramètres

Substitution de paramètres avec ?

name = "Paul%"

with connect('data/soccer.sqlite') as conn:
    query = """
        SELECT *
        FROM Player
        WHERE player_name LIKE ?
    """
    players_df = pd.read_sql(
        query,
        con=conn,
        params=[name]  # Paramètre injecté de façon sécurisée
    )

Propriétés :

• Les valeurs sont échappées automatiquement
• Impossible d’injecter du code SQL malveillant
• Performances optimales (requête préparée)

Multiples paramètres :

name = "John%"
min_weight = 200

with connect('data/soccer.sqlite') as conn:
    query = """
        SELECT *
        FROM Player
        WHERE player_name LIKE ?
          AND weight > ?
    """
    players_df = pd.read_sql(
        query,
        con=conn,
        params=[name, min_weight]
    )

CRUD : Manipuler les données

Acronyme CRUD

Définition :

• Create : Créer (INSERT)
• Read : Lire (SELECT)
• Update : Mettre à jour (UPDATE)
• Delete : Supprimer (DELETE)

Jusqu’ici, nous avons utilisé uniquement SELECT (DQL - Data Query Language).

Les opérations CUD font partie du DML (Data Manipulation Language).

CREATE (INSERT)

Syntaxe :

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...);

Exemple : Ajouter un nouveau pays

INSERT INTO Country (id, name)
VALUES (99999, 'Wakanda');

Insérer plusieurs lignes :

INSERT INTO Country (id, name)
VALUES
    (99999, 'Wakanda'),
    (99998, 'Atlantis'),
    (99997, 'Narnia');

Propriété : Les colonnes non spécifiées prennent leur valeur par défaut (souvent NULL).

UPDATE (Modifier)

Syntaxe :

UPDATE table
SET column_1 = new_value_1,
    column_2 = new_value_2
WHERE search_condition;

Exemple : Modifier le nom d’un pays

UPDATE Country
SET name = 'République Française'
WHERE id = 4769;

⚠️ Danger : Sans WHERE, toutes les lignes sont modifiées !

-- ❌ CATASTROPHE : Change tous les noms !
UPDATE Country
SET name = 'Unknown';

Bonne pratique : Toujours tester avec SELECT d’abord !

-- 1. Vérifier quelles lignes seront affectées
SELECT * FROM Country WHERE id = 4769;

-- 2. Puis UPDATE
UPDATE Country SET name = 'République Française' WHERE id = 4769;

-- 3. Vérifier le résultat
SELECT * FROM Country WHERE id = 4769;

DELETE (Supprimer)

Syntaxe :

DELETE FROM table
WHERE search_condition;

Exemple : Supprimer un pays

DELETE FROM Country
WHERE id = 4769;

⚠️ Danger : Sans WHERE, toute la table est vidée !

-- ❌ CATASTROPHE : Supprime tous les pays !
DELETE FROM Country;

Bonne pratique : Même processus que pour UPDATE

-- 1. Vérifier ce qui sera supprimé
SELECT * FROM Country WHERE id = 4769;

-- 2. Puis DELETE
DELETE FROM Country WHERE id = 4769;

-- 3. Vérifier que c'est bien supprimé
SELECT * FROM Country WHERE id = 4769;  -- Doit retourner 0 lignes

Connexion à d’autres SGBD

Au-delà de SQLite

En production, vous utiliserez probablement :

• PostgreSQL : SGBD open-source puissant
• MySQL / MariaDB : Populaires en web
• SQL Server : Microsoft
• Oracle : Entreprises
• BigQuery : Google Cloud (data warehouse)

SQLAlchemy + Pandas

SQLAlchemy est la bibliothèque standard pour se connecter à n’importe quel SGBD.

Exemple avec PostgreSQL :

from sqlalchemy import create_engine, engine

# Créer une connexion
conn = create_engine(
    engine.url.URL.create(
        drivername='postgresql+psycopg2',
        username="dbuser",
        password="dbpassword",
        host="123.456.789.012",
        port=5432,
        database="soccer"
    )
)

# Utiliser exactement comme avec SQLite
players_df = pd.read_sql(
    "SELECT * FROM Player LIMIT 100",
    conn
)

Drivers par SGBD :

BigQuery (Google Cloud)

Connexion directe avec Pandas :

import pandas as pd

query = """
    SELECT *
    FROM `project.dataset.table`
    LIMIT 100
"""

df = pd.read_gbq(
    query,
    project_id='my-project',
    dialect='standard'
)

Avantage : Requêtes sur des pétaoctets de données !

Sécurité : SQL Injection

Le problème

Définition : Une injection SQL est une vulnérabilité où un attaquant insère du code SQL malveillant via une entrée utilisateur.

Exemple : Authentification

Base de données :

Code vulnérable :

def authenticate(username, password):
    query = f"""
        SELECT *
        FROM users
        WHERE username = '{username}'
          AND password = '{password}'
    """
    c.execute(query)
    user = c.fetchone()
    return "Authorized" if user else "Unauthorized"

Test normal :

authenticate("john", "passw0rd")

‘Authorized’

authenticate("john", "wrong_password")

‘Unauthorized’

Attaque :

authenticate("john", "' OR 1=1 --")

‘Authorized’ 😱

Que s’est-il passé ?

La requête devient :

SELECT *
FROM users
WHERE username = 'john'
  AND password = '' OR 1=1 --'

Décomposition :

1. password = '' → Faux
2. OR 1=1 → Toujours vrai !
3. -- → Commente la fin (ignore la ' finale)

Résultat : L’attaquant est authentifié sans connaître le mot de passe ! 💥

Solution : Paramètres

Code sécurisé :

def authenticate_safe(username, password):
    query = """
        SELECT *
        FROM users
        WHERE username = ?
          AND password = ?
    """
    c.execute(query, (username, password))
    user = c.fetchone()
    return "Authorized" if user else "Unauthorized"

Test de l’attaque :

authenticate_safe("john", "' OR 1=1 --")

‘Unauthorized’ ✅

Explication : La valeur ' OR 1=1 -- est traitée comme une chaîne littérale, pas du code SQL.

Autres attaques possibles

Exemple : Suppression de table (Little Bobby Tables)

Formulaire d’inscription :

• Prénom : Robert'); DROP TABLE Students; --

Code vulnérable :

INSERT INTO Students (last_name, first_name)
VALUES ('Smith', '{first_name}')

Devient :

INSERT INTO Students (last_name, first_name)
VALUES ('Smith', 'Robert'); DROP TABLE Students; --');

💥 La table Students est supprimée !

Règle d’or : TOUJOURS utiliser des paramètres pour les valeurs utilisateur.

Bonnes pratiques et astuces

Style de code

Conventions :

• Mots-clés SQL en MAJUSCULES
• Noms de tables/colonnes en minuscules
• Indentation pour la lisibilité
• Virgules en début de ligne (pour faciliter les modifications)

Exemple bien formaté :

SELECT p.player_name
     , p.height
     , p.weight
     , c.name AS country
  FROM Player p
  JOIN Country c ON p.country_id = c.id
 WHERE p.weight > 200
   AND p.height > 180
 ORDER BY p.weight DESC
 LIMIT 10;

Optimisation des performances

Indexes : Créer des index sur les colonnes fréquemment utilisées dans WHERE et JOIN.

CREATE INDEX idx_player_name ON Player(player_name);

EXPLAIN : Analyser le plan d’exécution d’une requête.

EXPLAIN QUERY PLAN
SELECT *
FROM Player
WHERE player_name LIKE 'John%';

*Éviter SELECT ** : Sélectionner uniquement les colonnes nécessaires.

-- ❌ Mauvais
SELECT * FROM Player;

-- ✅ Bon
SELECT player_name, height, weight FROM Player;

Alias et lisibilité

Alias de tables :

SELECT p.player_name, c.name AS country_name
FROM Player p
JOIN Country c ON p.country_id = c.id;

Alias de colonnes :

SELECT AVG(weight) AS avg_weight,
       MAX(weight) AS max_weight
FROM Player;

Transactions (avancé)

Concept : Grouper plusieurs opérations en une unité atomique (tout ou rien).

BEGIN TRANSACTION;

UPDATE Account SET balance = balance - 100 WHERE id = 1;
UPDATE Account SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- Valide si tout est OK
-- ROLLBACK;  -- Annule en cas d'erreur

Propriétés ACID :

• Atomicité : Tout ou rien
• Cohérence : État valide
• Isolation : Transactions indépendantes
• Durabilité : Changements persistants

Conclusion

On peut se demander si SQL ne constitue pas le langage le plus universel de la data science ? Créé dans les années 1970, il reste inchangé dans ses fondamentaux, preuve de sa robustesse conceptuelle.

Fondamentaux :

• 8 mots-clés en ordre rigide
• Agrégations (GROUP BY, HAVING)
• Fonctions de manipulation (chaînes, dates, arithmétique)

Avancé :

• Window Functions (puissance analytique)
• CTE (structuration des requêtes)
• Sécurité (paramètres vs injection)

Intégration :

• Pandas pour analyse
• SQLAlchemy pour connexions universelles
• CRUD pour manipulation complète

Perspectives

Sujets avancés :

• Indexes : B-tree, hash, full-text
• Query optimization : EXPLAIN, profiling
• Stored procedures : Logique côté serveur
• Triggers : Actions automatiques
• Views : Vues virtuelles
• Partitioning : Tables distribuées

Variantes SQL :

• NoSQL : MongoDB, Cassandra (pour données non-structurées)
• NewSQL : CockroachDB, Google Spanner (scalabilité + ACID)
• Time-series : InfluxDB, TimescaleDB (séries temporelles)

Ressources

• 📚 Mode SQL Tutorial
• 🎮 SQLZoo (exercices interactifs)
• 📺 7 Database Paradigms (pour aller au-delà de SQL)
• 🔧 SQL Fiddle (tester en ligne)