Guide détaillé syntaxe SQL

Résumé

Ce guide complet couvre l’ensemble des concepts fondamentaux de SQL, des opérations de base jusqu’aux fonctionnalités avancées.

N’en faites pas une lecture exhaustive mais cherchez-y des solutions aux problèmes rencontrés.

Table des matières

  1. Agrégation et Regroupement

  2. Tri et Filtrage

  3. Relations et Jointures

  4. Alias et Expressions

  5. Vues et Droits d’accès

  6. Requêtes avancées

1. Agrégation et Regroupement

Les fonctions d’agrégation permettent de calculer des statistiques sur des ensembles de données. Combinées avec GROUP BY, elles permettent d’analyser les données par sous-groupes.

GROUP BY

Fonctionnalité: Regroupe les lignes ayant les mêmes valeurs dans les colonnes spécifiées. Essentiel pour l’analyse de données par catégories ou caractéristiques communes.

Syntaxe:

SELECT colonne1, colonne2, ..., fonction_agrégation(colonneX)
FROM table
GROUP BY colonne1, colonne2, ...;

Points importants:

  • Toute colonne dans le SELECT qui n’est pas dans une fonction d’agrégation doit apparaître dans le GROUP BY
  • L’ordre des colonnes dans GROUP BY n’affecte pas le résultat
  • Peut être utilisé avec plusieurs colonnes pour créer des sous-groupes plus précis

Exemple:

SELECT NumeroClient, COUNT(*) as nombre_achats, 
       SUM(p.Prix) as total_depense
FROM Vente v
JOIN Produit p ON v.NumeroProduit = p.Numero
GROUP BY NumeroClient;

Explication: Cette requête analyse les habitudes d’achat par client en:

  1. Comptant le nombre total d’achats de chaque client (COUNT(*))
  2. Calculant le montant total dépensé par client (SUM(p.Prix))
  3. Le regroupement par NumeroClient assure que nous obtenons une ligne par client

HAVING

Fonctionnalité: Filtre les résultats après l’agrégation, contrairement à WHERE qui filtre avant l’agrégation.

Syntaxe:

SELECT colonne1, fonction_agrégation(colonne2)
FROM table
GROUP BY colonne1
HAVING fonction_agrégation(colonne2) condition;

Points importants:

  • S’utilise uniquement avec GROUP BY
  • Utilise des fonctions d’agrégation dans ses conditions
  • S’exécute après le GROUP BY, contrairement à WHERE qui s’exécute avant

Exemple:

SELECT NumeroClient, COUNT(*) as nombre_achats
FROM Vente
GROUP BY NumeroClient
HAVING COUNT(*) > 5 AND SUM(Montant) > 1000;

Explication: Cette requête identifie les clients fidèles en:

  1. Regroupant d’abord toutes les ventes par client
  2. Ne conservant que les clients ayant effectué plus de 5 achats ET dépensé plus de 1000€

SUM

Fonctionnalité: Calcule la somme totale des valeurs d’une colonne numérique.

Syntaxe:

SELECT SUM(colonne_numerique)
FROM table;

Points importants:

  • Ignore automatiquement les valeurs NULL
  • Ne peut être utilisé qu’avec des colonnes numériques
  • Peut être combiné avec GROUP BY pour des sous-totaux

Exemple:

SELECT c.Nom, 
       SUM(p.Prix) as total_achats,
       SUM(p.Prix)/COUNT(*) as panier_moyen
FROM Vente v
JOIN Client c ON v.NumeroClient = c.Numero
JOIN Produit p ON v.NumeroProduit = p.Numero
GROUP BY c.Nom;

Explication: Cette requête analyse les dépenses des clients en:

  1. Calculant le montant total des achats pour chaque client
  2. Calculant également le panier moyen (total divisé par nombre d’achats)
  3. Le regroupement par nom de client permet de voir ces statistiques par client

AVG

Fonctionnalité: Calcule la moyenne des valeurs d’une colonne numérique.

Syntaxe:

SELECT AVG(colonne_numerique)
FROM table;

Points importants:

  • Ignore automatiquement les valeurs NULL
  • Ne fonctionne que sur les colonnes numériques
  • Peut donner des résultats avec décimales même sur des entiers
  • Souvent utilisé avec ROUND() pour formater le résultat

Exemple:

SELECT 
    EXTRACT(YEAR FROM v.Date) as annee,
    ROUND(AVG(p.Prix), 2) as panier_moyen,
    COUNT(*) as nombre_ventes
FROM Vente v
JOIN Produit p ON v.NumeroProduit = p.Numero
GROUP BY EXTRACT(YEAR FROM v.Date);

Explication: Cette requête analyse l’évolution des ventes en:

  1. Regroupant les ventes par année
  2. Calculant le panier moyen pour chaque année
  3. Comptant le nombre de ventes pour contexte

2. Tri et Filtrage

ORDER BY

Fonctionnalité: Trie les résultats selon un ou plusieurs critères.

Syntaxe:

SELECT colonnes
FROM table
ORDER BY 
    colonne1 [ASC|DESC] [NULLS FIRST|LAST],
    colonne2 [ASC|DESC] [NULLS FIRST|LAST];

Points importants:

  • ASC est le tri par défaut (croissant)
  • Peut utiliser plusieurs colonnes pour un tri hiérarchique
  • Peut trier sur des colonnes non sélectionnées
  • Peut trier sur des expressions ou des calculs

Exemple:

SELECT 
    c.Nom,
    COUNT(*) as nb_achats,
    SUM(p.Prix) as total_depense
FROM Client c
JOIN Vente v ON c.Numero = v.NumeroClient
JOIN Produit p ON v.NumeroProduit = p.Numero
GROUP BY c.Nom
ORDER BY total_depense DESC, nb_achats DESC;

Explication: Cette requête classe les clients par valeur en:

  1. Calculant leurs statistiques d’achat
  2. Les triant d’abord par montant total dépensé
  3. À montant égal, les triant par nombre d’achats

LIKE

Fonctionnalité: Permet la recherche de motifs dans les chaînes de caractères.

Syntaxe:

SELECT colonnes
FROM table
WHERE colonne_texte LIKE pattern;

Points importants:

  • % : représente n’importe quel nombre de caractères (y compris zéro)
  • _ : représente exactement un caractère
  • Sensible à la casse par défaut (utiliser ILIKE pour ignorer la casse)
  • Peut utiliser NOT LIKE pour inverser la correspondance

Exemple:

SELECT 
    p.Description,
    p.Prix,
    COUNT(v.Numero) as nombre_ventes
FROM Produit p
LEFT JOIN Vente v ON p.Numero = v.NumeroProduit
WHERE p.Description LIKE '%Gaming%'
   OR p.Description LIKE '%Gamer%'
GROUP BY p.Description, p.Prix
ORDER BY nombre_ventes DESC;

Explication: Cette requête analyse les produits gaming en:

  1. Recherchant les produits avec “Gaming” ou “Gamer” dans leur description
  2. Comptant leurs ventes
  3. Les classant par popularité

BETWEEN

Fonctionnalité: Simplifie la sélection d’une plage de valeurs.

Syntaxe:

SELECT colonnes
FROM table
WHERE colonne BETWEEN valeur1 AND valeur2;

Points importants:

  • Inclusif des deux bornes
  • Fonctionne avec des nombres, dates, et caractères
  • Équivalent à: colonne >= valeur1 AND colonne <= valeur2
  • La première valeur doit être inférieure à la seconde

Exemple:

SELECT 
    p.Description,
    p.Prix,
    COUNT(v.Numero) as ventes,
    ROUND(AVG(p.Prix), 2) as prix_moyen
FROM Produit p
LEFT JOIN Vente v ON p.Numero = v.NumeroProduit
WHERE p.Prix BETWEEN 100 AND 500
  AND v.Date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY p.Description, p.Prix
HAVING COUNT(v.Numero) > 0;

Explication: Cette requête analyse les ventes de produits de gamme moyenne en:

  1. Sélectionnant les produits entre 100€ et 500€
  2. Ne gardant que les ventes de 2023
  3. Calculant les statistiques de vente

AVG

Fonctionnalité: Calcule la moyenne des valeurs d’une colonne numérique.

Syntaxe:

SELECT AVG(colonne_numerique)
FROM table;

Points importants:

  • Ignore automatiquement les valeurs NULL
  • Ne fonctionne que sur les colonnes numériques
  • Peut donner des résultats avec décimales même sur des entiers
  • Souvent utilisé avec ROUND() pour formater le résultat

Exemple:

SELECT 
    EXTRACT(YEAR FROM v.Date) as annee,
    ROUND(AVG(p.Prix), 2) as panier_moyen,
    COUNT(*) as nombre_ventes
FROM Vente v
JOIN Produit p ON v.NumeroProduit = p.Numero
GROUP BY EXTRACT(YEAR FROM v.Date);

Explication: Cette requête analyse l’évolution des ventes en:

  1. Regroupant les ventes par année
  2. Calculant le panier moyen pour chaque année
  3. Comptant le nombre de ventes pour contexte

2. Tri et Filtrage

ORDER BY

Fonctionnalité: Trie les résultats selon un ou plusieurs critères.

Syntaxe:

SELECT colonnes
FROM table
ORDER BY 
    colonne1 [ASC|DESC] [NULLS FIRST|LAST],
    colonne2 [ASC|DESC] [NULLS FIRST|LAST];

Points importants:

  • ASC est le tri par défaut (croissant)
  • Peut utiliser plusieurs colonnes pour un tri hiérarchique
  • Peut trier sur des colonnes non sélectionnées
  • Peut trier sur des expressions ou des calculs

Exemple:

SELECT 
    c.Nom,
    COUNT(*) as nb_achats,
    SUM(p.Prix) as total_depense
FROM Client c
JOIN Vente v ON c.Numero = v.NumeroClient
JOIN Produit p ON v.NumeroProduit = p.Numero
GROUP BY c.Nom
ORDER BY total_depense DESC, nb_achats DESC;

Explication: Cette requête classe les clients par valeur en:

  1. Calculant leurs statistiques d’achat
  2. Les triant d’abord par montant total dépensé
  3. À montant égal, les triant par nombre d’achats

LIKE

Fonctionnalité: Permet la recherche de motifs dans les chaînes de caractères.

Syntaxe:

SELECT colonnes
FROM table
WHERE colonne_texte LIKE pattern;

Points importants:

  • % : représente n’importe quel nombre de caractères (y compris zéro)
  • _ : représente exactement un caractère
  • Sensible à la casse par défaut (utiliser ILIKE pour ignorer la casse)
  • Peut utiliser NOT LIKE pour inverser la correspondance

Exemple:

SELECT 
    p.Description,
    p.Prix,
    COUNT(v.Numero) as nombre_ventes
FROM Produit p
JOIN Vente v ON p.Numero = v.NumeroProduit
WHERE p.Description LIKE '%Gaming%'
   OR p.Description LIKE '%Gamer%'
GROUP BY p.Description, p.Prix
ORDER BY nombre_ventes DESC;

Explication: Cette requête analyse les produits gaming en:

  1. Recherchant les produits avec “Gaming” ou “Gamer” dans leur description
  2. Comptant leurs ventes
  3. Les classant par popularité

BETWEEN

Fonctionnalité: Simplifie la sélection d’une plage de valeurs.

Syntaxe:

SELECT colonnes
FROM table
WHERE colonne BETWEEN valeur1 AND valeur2;

Points importants:

  • Inclusif des deux bornes
  • Fonctionne avec des nombres, dates, et caractères
  • Équivalent à: colonne >= valeur1 AND colonne <= valeur2
  • La première valeur doit être inférieure à la seconde

Exemple:

SELECT 
    p.Description,
    p.Prix,
    COUNT(v.Numero) as ventes,
    ROUND(AVG(p.Prix), 2) as prix_moyen
FROM Produit p
JOIN Vente v ON p.Numero = v.NumeroProduit
WHERE p.Prix BETWEEN 100 AND 500
  AND v.Date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY p.Description, p.Prix
HAVING COUNT(v.Numero) > 0;

Explication: Cette requête analyse les ventes de produits de gamme moyenne en:

  1. Sélectionnant les produits entre 100€ et 500€
  2. Ne gardant que les ventes de 2023
  3. Calculant les statistiques de vente

3. Relations et Jointures

Jointure

En informatique et plus particulièrement dans les bases de données relationnelles, la jointure ou appariement est l’opération permettant d’associer plusieurs tables ou vues de la base par le biais d’un lien logique de données entre les différentes tables ou vues, le lien étant vérifié par le biais d’un prédicat. Le résultat de l’opération est une nouvelle table.

Source : Définition de jointure sur Wikipedia, Consultée le 29 avril 2020

JOIN (INNER JOIN)

Fonctionnalité: Combine les lignes de deux tables lorsqu’elles correspondent aux critères de jointure.

Syntaxe:

SELECT colonnes
FROM table1
JOIN table2 ON table1.colonne = table2.colonne
[JOIN table3 ON condition_jointure];

Points importants:

  • Ne retourne que les lignes ayant des correspondances dans les deux tables
  • Peut utiliser plusieurs conditions de jointure avec AND/OR
  • L’ordre des tables peut affecter les performances mais pas le résultat
  • INNER JOIN et JOIN sont équivalents

Exemple:

SELECT 
    c.Nom,
    p.Description,
    v.Date,
    p.Prix,
    COUNT(*) OVER (PARTITION BY c.Numero) as total_achats_client
FROM Vente v
JOIN Client c ON v.NumeroClient = c.Numero
JOIN Produit p ON v.NumeroProduit = p.Numero
WHERE v.Date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY v.Date DESC;

Explication: Cette requête analyse l’historique d’achat récent en:

  1. Combinant les informations des trois tables
  2. Ne gardant que les correspondances exactes
  3. Calculant le nombre total d’achats pour chaque client

LEFT JOIN

Fonctionnalité: Retourne toutes les lignes de la table de gauche et les correspondances de la table de droite.

Syntaxe:

SELECT colonnes
FROM table1
LEFT [OUTER] JOIN table2 ON table1.colonne = table2.colonne;

Points importants:

  • Le mot OUTER est optionnel
  • Les lignes sans correspondance ont des NULL dans les colonnes de la table de droite
  • L’ordre des tables est important car il détermine quelle table est “complète”
  • Utile pour trouver des éléments manquants ou pour des rapports exhaustifs

Exemple:

SELECT 
    p.Description,
    p.Prix,
    COUNT(v.Numero) as nombre_ventes,
    CASE 
        WHEN COUNT(v.Numero) = 0 THEN 'Jamais vendu'
        WHEN COUNT(v.Numero) < 5 THEN 'Faibles ventes'
        WHEN COUNT(v.Numero) < 20 THEN 'Ventes moyennes'
        ELSE 'Produit populaire'
    END as statut_ventes
FROM Produit p
LEFT JOIN Vente v ON p.Numero = v.NumeroProduit
GROUP BY p.Description, p.Prix
ORDER BY nombre_ventes DESC;

Explication: Cette requête analyse le catalogue complet en:

  1. Gardant tous les produits, même ceux jamais vendus
  2. Comptant les ventes pour chaque produit
  3. Catégorisant les produits selon leurs performances

RIGHT JOIN

Fonctionnalité: Similaire au LEFT JOIN mais garde toutes les lignes de la table de droite.

Syntaxe:

SELECT colonnes
FROM table1
RIGHT [OUTER] JOIN table2 ON table1.colonne = table2.colonne;

Points importants:

  • Moins couramment utilisé que LEFT JOIN
  • Peut toujours être réécrit en LEFT JOIN en inversant l’ordre des tables
  • Utile quand la table principale est à droite dans la requête

Exemple:

SELECT 
    COALESCE(c.Nom, 'Client Supprimé') as nom_client,
    v.Date,
    p.Description,
    p.Prix
FROM Client c
RIGHT JOIN Vente v ON c.Numero = v.NumeroClient
JOIN Produit p ON v.NumeroProduit = p.Numero
WHERE v.Date >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY v.Date DESC;

Explication: Cette requête préserve l’historique des ventes en:

  1. Gardant toutes les ventes, même si le client a été supprimé
  2. Utilisant COALESCE pour gérer les clients manquants
  3. Ne montrant que les 90 derniers jours

FULL OUTER JOIN

Fonctionnalité: Combine les résultats d’un LEFT JOIN et d’un RIGHT JOIN.

Syntaxe:

SELECT colonnes
FROM table1
FULL [OUTER] JOIN table2 ON table1.colonne = table2.colonne;

Points importants:

  • Retourne toutes les lignes des deux tables
  • Remplit avec NULL quand il n’y a pas de correspondance
  • Utile pour trouver les incohérences dans les données
  • Moins fréquemment utilisé que les autres types de JOIN

Exemple:

SELECT 
    COALESCE(c.Nom, 'INCONNU') as client,
    COALESCE(p.Description, 'PRODUIT SUPPRIMÉ') as produit,
    v.Date,
    v.Numero as numero_vente
FROM Vente v
FULL OUTER JOIN Client c ON v.NumeroClient = c.Numero
FULL OUTER JOIN Produit p ON v.NumeroProduit = p.Numero
WHERE c.Numero IS NULL OR p.Numero IS NULL
ORDER BY v.Date DESC;

Explication: Cette requête vérifie l’intégrité des données en:

  1. Identifiant les ventes avec clients ou produits manquants
  2. Montrant toutes les anomalies possibles
  3. Facilitant le nettoyage des données

4. Alias et Expressions

AS

Fonctionnalité: Renomme temporairement les colonnes ou tables pour plus de clarté.

Syntaxe:

-- Pour les colonnes
SELECT colonne AS nom_alias
-- Pour les tables
FROM table AS alias

Points importants:

  • Le mot-clé AS est optionnel mais recommandé pour la lisibilité
  • Les alias de tables doivent être uniques dans la requête
  • Utile pour les auto-jointures ou les calculs complexes
  • Les alias de colonnes sont utilisables dans ORDER BY mais pas dans WHERE

Exemple:

SELECT 
    c.Nom AS nom_client,
    ventes.total_achats,
    ventes.panier_moyen,
FROM Client AS c
JOIN (
    SELECT 
        NumeroClient,
        COUNT(*) AS total_achats,
        ROUND(AVG(p.Prix), 2) AS panier_moyen
    FROM Vente AS v
    JOIN Produit AS p ON v.NumeroProduit = p.Numero
    GROUP BY NumeroClient
) AS ventes ON c.Numero = ventes.NumeroClient;

Explication: Cette requête utilise les alias pour:

  1. Simplifier la référence aux tables dans les jointures
  2. Donner des noms explicites aux calculs
  3. Créer une sous-requête lisible
  4. Faciliter la catégorisation des clients

CASE

Fonctionnalité: Crée des conditions pour transformer les données.

Syntaxe:

-- Forme simple
CASE colonne
    WHEN valeur1 THEN resultat1
    WHEN valeur2 THEN resultat2
    ELSE resultat_defaut
END

-- Forme recherchée
CASE
    WHEN condition1 THEN resultat1
    WHEN condition2 THEN resultat2
    ELSE resultat_defaut
END

Points importants:

  • Si pas de ELSE, retourne NULL pour les cas non traités
  • Peut être imbriqué dans un autre CASE
  • Peut être utilisé dans SELECT, WHERE, ORDER BY, et GROUP BY
  • Toutes les branches THEN doivent retourner le même type de données

Exemple:

SELECT 
    p.Description,
    p.Prix,
    CASE 
        WHEN p.Prix < 100 THEN 'Économique'
        WHEN p.Prix < 500 THEN 'Standard'
        WHEN p.Prix < 1000 THEN 'Premium'
        ELSE 'Luxe'
    END AS gamme_prix,
    COUNT(v.Numero) AS ventes,
    CASE 
        WHEN COUNT(v.Numero) = 0 THEN 'À promouvoir'
        WHEN COUNT(v.Numero) < AVG(COUNT(v.Numero)) OVER () THEN 'Sous-performant'
        ELSE 'Performant'
    END AS statut_commercial
FROM Produit p
JOIN Vente v ON p.Numero = v.NumeroProduit
GROUP BY p.Description, p.Prix;

Explication: Cette requête analyse les produits en:

  1. Catégorisant les prix en gammes
  2. Évaluant les performances de vente
  3. Comparant avec la moyenne générale

COALESCE

Fonctionnalité: Renvoie le premier argument non NULL dans une liste.

Syntaxe:

COALESCE(valeur1, valeur2, ..., valeurN)

Points importants:

  • Prend un nombre illimité d’arguments
  • Les arguments doivent être de types compatibles
  • Retourne NULL uniquement si tous les arguments sont NULL
  • Très utile pour gérer les valeurs manquantes ou par défaut

Exemple:

SELECT 
    c.Numero,
    c.Nom,
    COALESCE(
        derniere_vente.date_vente,
        c.created_at,
        '2000-01-01'
    ) as derniere_activite,
    COALESCE(
        stats_client.total_achats, 
        0
    ) as nombre_achats,
    COALESCE(
        c.Telephone, 
        c.Email, 
        'Aucun contact'
    ) as contact_principal
FROM Client c
LEFT JOIN (
    SELECT 
        NumeroClient,
        MAX(Date) as date_vente,
        COUNT(*) as total_achats
    FROM Vente
    GROUP BY NumeroClient
) stats_client ON c.Numero = stats_client.NumeroClient;

Explication: Cette requête utilise COALESCE pour:

  1. Déterminer la dernière activité du client en prenant la première date non NULL entre:
    • Sa dernière vente
    • Sa date de création
    • Une date par défaut
  2. Gérer les clients sans achats en remplaçant NULL par 0
  3. Fournir un moyen de contact en priorisant téléphone, puis email
  4. Utile pour créer des rapports complets sans valeurs manquantes

Cas d’utilisation courants:

  • Rapports et exports de données sans NULL
  • Chaînes de substitution dans les interfaces utilisateur
  • Calculs avec valeurs par défaut
  • Fusion de données provenant de différentes sources

INTERVAL

Fonctionnalité: Manipule les dates et les durées.

Syntaxe:

date [+|-] INTERVAL 'quantité unité'

Points importants:

  • Unités disponibles: year, month, day, hour, minute, second
  • Peut combiner plusieurs unités: 1 year 2 months
  • Peut être utilisé avec les opérateurs + et -
  • Utile pour les calculs de dates et les fenêtres temporelles

Exemple:

SELECT 
    DATE_TRUNC('month', v.Date) AS mois,
    COUNT(*) AS nombre_ventes,
    SUM(CASE 
        WHEN v.Date >= CURRENT_DATE - INTERVAL '30 days' THEN 1
        ELSE 0
    END) AS ventes_recentes,
    SUM(CASE 
        WHEN v.Date >= CURRENT_DATE - INTERVAL '1 year' AND
             v.Date < CURRENT_DATE - INTERVAL '1 year' + INTERVAL '30 days'
        THEN 1
        ELSE 0
    END) AS ventes_annee_precedente
FROM Vente v
GROUP BY DATE_TRUNC('month', v.Date)
ORDER BY mois DESC;

Explication: Cette requête analyse les tendances temporelles en:

  1. Regroupant les ventes par mois
  2. Comptant les ventes des 30 derniers jours
  3. Comparant avec la même période l’année précédente

EXTRACT

Fonctionnalité: Extrait une partie spécifique d’une date ou d’un timestamp.

Syntaxe:

EXTRACT(partie FROM date)

Parties disponibles:

  • CENTURY, DECADE, YEAR, MONTH, DAY
  • HOUR, MINUTE, SECOND
  • DOW (jour de la semaine, 0-6)
  • DOY (jour de l’année, 1-366)
  • WEEK (semaine de l’année)
  • QUARTER (trimestre, 1-4)

Points importants:

  • Retourne un nombre
  • Peut être utilisé dans GROUP BY et WHERE
  • Format 24h pour les heures (0-23)

Exemple:

SELECT 
    EXTRACT(YEAR FROM v.Date) as annee,
    EXTRACT(MONTH FROM v.Date) as mois,
    COUNT(*) as nombre_ventes,
    ROUND(AVG(p.Prix), 2) as panier_moyen,
    STRING_AGG(DISTINCT CASE 
        WHEN EXTRACT(DOW FROM v.Date) IN (0, 6) THEN 'Weekend'
        ELSE 'Semaine'
    END, '/') as periode_vente
FROM Vente v
JOIN Produit p ON v.NumeroProduit = p.Numero
GROUP BY 
    EXTRACT(YEAR FROM v.Date),
    EXTRACT(MONTH FROM v.Date)
HAVING COUNT(*) > 10
ORDER BY annee DESC, mois DESC;

Explication: Cette requête analyse les patterns temporels en:

  1. Décomposant les dates en année et mois
  2. Calculant les statistiques mensuelles
  3. Identifiant si les ventes ont lieu en semaine/weekend

5. Vues et Droits d’accès

CREATE VIEW

Fonctionnalité: Crée une vue virtuelle permettant de simplifier des requêtes complexes.

Syntaxe:

CREATE [OR REPLACE] [MATERIALIZED] VIEW nom_vue [(colonnes)]
AS query
[WITH [CASCADE | LOCAL] CHECK OPTION];

Points importants:

  • OR REPLACE permet de mettre à jour une vue existante
  • MATERIALIZED stocke physiquement les résultats (améliore les performances)
  • CHECK OPTION garantit que les modifications de données via la vue respectent les conditions définies dans la vue (c’est à dire qu’il est impossible d’ajouter ou de modifier des données qui n’apparaîtraient pas dans la vue)
  • Les vues peuvent être utilisées comme des tables dans les requêtes

Exemple:

-- Vue pour l'analyse des ventes
CREATE OR REPLACE VIEW analyse_ventes AS
SELECT 
    DATE_TRUNC('month', v.Date) as mois,
    p.Description,
    COUNT(*) as nombre_ventes,
    SUM(p.Prix) as chiffre_affaires,
    COUNT(DISTINCT v.NumeroClient) as clients_uniques
FROM Vente v
JOIN Produit p ON v.NumeroProduit = p.Numero
GROUP BY DATE_TRUNC('month', v.Date), p.Description;

Explication: Cette vue:

  1. Crée un résumé mensuel des ventes par produit
  2. Calcule des métriques de base (nombre de ventes, chiffre d’affaires, clients uniques)
  3. Simplifie l’accès aux analyses courantes

CREATE ROLE

Fonctionnalité: Crée un rôle pour gérer les permissions d’accès.

Syntaxe:

CREATE ROLE nom_role [WITH]
    [SUPERUSER | NOSUPERUSER]
    [CREATEDB | NOCREATEDB]
    [CREATEROLE | NOCREATEROLE]
    [INHERIT | NOINHERIT]
    [LOGIN | NOLOGIN]
    [PASSWORD 'mot_de_passe'];

Points importants:

  • Un rôle peut représenter un utilisateur ou un groupe
  • Les options définissent les capacités globales du rôle
  • Les rôles peuvent hériter des permissions d’autres rôles
  • LOGIN permet au rôle de se connecter à la base

Exemple:

-- Création des rôles pour différents niveaux d'accès
CREATE ROLE commercial_readonly WITH 
    LOGIN 
    PASSWORD 'secret'
    NOSUPERUSER 
    INHERIT;

CREATE ROLE commercial_manager WITH 
    LOGIN 
    PASSWORD 'secret'
    NOSUPERUSER 
    INHERIT;

-- Attribution des permissions
GRANT SELECT ON analyse_ventes TO commercial_readonly;

GRANT SELECT, INSERT, UPDATE ON Vente TO commercial_manager;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO commercial_manager;

Explication: Cette configuration:

  1. Crée deux niveaux d’accès distincts
  2. Limite les commerciaux à la lecture seule des analyses
  3. Donne plus de droits aux managers
  4. Maintient la sécurité des données

GRANT

Fonctionnalité: Attribue des privilèges spécifiques à des rôles.

Syntaxe:

GRANT privileges ON objet TO role [WITH GRANT OPTION];

Privilèges disponibles:

  • SELECT : Lecture des données
  • INSERT : Ajout de données
  • UPDATE : Modification des données
  • DELETE : Suppression de données
  • TRUNCATE : Vidage de table
  • REFERENCES : Création de clés étrangères
  • TRIGGER : Création de triggers
  • ALL PRIVILEGES : Tous les droits

Points importants:

  • WITH GRANT OPTION permet au rôle de transmettre ses privilèges
  • Les privilèges peuvent être accordés au niveau table/colonne
  • Peut accorder des privilèges sur des schémas entiers
  • Certains privilèges nécessitent d’être propriétaire de l’objet

Exemple:

-- Configuration des droits pour l'équipe commerciale
BEGIN;

-- Droits de base pour tous les commerciaux
GRANT SELECT ON vw_produits_disponibles TO role_commercial;

-- Droits étendus pour les seniors
GRANT SELECT, INSERT ON Vente TO role_commercial_senior;
GRANT SELECT ON 
    Client, 
    Produit, 
    vw_analyse_ventes 
TO role_commercial_senior;

-- Droits spécifiques pour les managers
GRANT ALL PRIVILEGES ON 
    Vente,
    Client,
    Produit 
TO role_commercial_manager;

-- Droits sur des colonnes spécifiques
GRANT SELECT (Numero, Nom, Email) ON Client 
TO role_support_client;
GRANT UPDATE (Statut) ON Vente 
TO role_support_client;

COMMIT;

Explication: Cette configuration établit:

  1. Une hiérarchie claire des droits d’accès
  2. Des restrictions précises par rôle et responsabilité
  3. Une protection des données sensibles
  4. Un accès granulaire aux fonctionnalités

REVOKE

Fonctionnalité: Retire des privilèges précédemment accordés.

Syntaxe:

REVOKE [GRANT OPTION FOR] privileges 
ON objet 
FROM role [CASCADE | RESTRICT];

Points importants:

  • CASCADE révoque aussi les privilèges dépendants
  • RESTRICT empêche la révocation si des privilèges dépendants existent
  • Peut révoquer des privilèges spécifiques ou tous les privilèges
  • Affecte uniquement les privilèges explicitement accordés

Exemple:

-- Gestion dynamique des droits d'accès
-- Révocation temporaire pendant la maintenance
REVOKE INSERT, UPDATE, DELETE ON Vente 
FROM role_commercial CASCADE;

SET ROLE

Fonctionnalité: Change le rôle actif de l’utilisateur pour la session courante.

Syntaxe:

SET ROLE { role_name | NONE }

Points importants:

  • Change uniquement le rôle actif pour la session en cours
  • Nécessite que l’utilisateur soit membre du rôle cible
  • NONE réinitialise les droits au rôle de connexion initial
  • Les permissions actives deviennent celles du nouveau rôle
  • L’utilisateur doit avoir le droit INHERIT sur le rôle cible

Exemple:

-- Connexion en tant qu'administrateur
SET ROLE admin_role;

-- Effectuer des opérations d'administration
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- Retour au rôle initial
SET ROLE NONE;

-- Passage à un rôle avec moins de privilèges
SET ROLE readonly_user;

-- Tentative d'insertion (échouera)
INSERT INTO users VALUES (1, 'test'); -- ERROR: permission denied

Explication: Cette approche:

  1. Permet de changer temporairement de contexte de sécurité
  2. Facilite le test des permissions des différents rôles
  3. Améliore la sécurité en limitant l’usage des privilèges élevés
  4. Suit le principe du moindre privilège en adaptant les droits au besoin

6. Requêtes avancées

Common Table Expressions (WITH)

Fonctionnalité: Crée des sous-requêtes temporaires nommées pour simplifier les requêtes complexes.

Syntaxe:

WITH nom_cte [(colonnes)] AS (
    requête
)[, nom_cte2 AS (
    requête2
)]
SELECT ... FROM nom_cte, nom_cte2;

Points importants:

  • Peut être récursif avec RECURSIVE
  • Améliore la lisibilité des requêtes complexes
  • Permet de réutiliser les résultats intermédiaires
  • Peut définir plusieurs CTEs dans une seule requête

Exemple:

WITH ventes_mensuelles AS (
    -- Calcul des ventes par mois
    SELECT 
        DATE_TRUNC('month', v.Date) as mois,
        COUNT(*) as nombre_ventes,
        SUM(p.Prix) as chiffre_affaires
    FROM Vente v
    JOIN Produit p ON v.NumeroProduit = p.Numero
    GROUP BY DATE_TRUNC('month', v.Date)
),
croissance_mensuelle AS (
    -- Calcul de la croissance
    SELECT 
        mois,
        nombre_ventes,
        chiffre_affaires,
        LAG(chiffre_affaires) OVER (ORDER BY mois) as ca_mois_precedent,
        ROUND(
            (chiffre_affaires - LAG(chiffre_affaires) OVER (ORDER BY mois)) * 100.0 / 
            NULLIF(LAG(chiffre_affaires) OVER (ORDER BY mois), 0),
            2
        ) as croissance_pourcentage
    FROM ventes_mensuelles
)
-- Analyse finale
SELECT 
    mois,
    nombre_ventes,
    chiffre_affaires,
    croissance_pourcentage,
    CASE 
        WHEN croissance_pourcentage > 10 THEN 'Forte croissance'
        WHEN croissance_pourcentage > 0 THEN 'Croissance'
        WHEN croissance_pourcentage < -10 THEN 'Forte baisse'
        WHEN croissance_pourcentage < 0 THEN 'Baisse'
        ELSE 'Stable'
    END as tendance
FROM croissance_mensuelle
ORDER BY mois DESC;

Explication: Cette requête complexe:

  1. Décompose l’analyse en étapes claires
  2. Calcule la croissance mensuelle
  3. Catégorise les tendances
  4. Facilite la maintenance et la compréhension