SQL : reporting et analyse

Maîtrisez SQL pour le reporting et l'analyse quotidienne des données : sélection, filtrage et tri, personnalisation des sorties, et reporting d'agrégats depuis une base !

Actualisé 19 sept. 2026 · 15 min lire

Explorer avec l’IA

ChatGPT Claude Perplexity

Dans ce tutoriel, nous nous concentrerons sur le SQL ANSI (American National Standards Institute), compatible avec toutes les bases de données comme Oracle, MySQL, Microsoft SQL Server, etc. ! Commençons par une introduction à SQL (Structured Query Language) et pourquoi un data scientist en a besoin.

SQL et data science

Dans cette partie, vous verrez pourquoi un data scientist doit apprendre SQL. Voici comment SQL vous aidera dans votre carrière :

SQL est devenu un prérequis pour la plupart des métiers de la data : data analyst, développeur BI (Business Intelligence), programmeur, développeur bases de données. SQL vous permet d'interagir avec la base et de travailler sur vos données.
Si vous avez utilisé Tableau ou un autre outil de reporting/visualisation, vous savez que l'on se connecte à une base, puis on glisse-dépose des graphiques en choisissant simplement des champs : le logiciel gère le reste. En coulisses, ces opérations en interface graphique génèrent du SQL qui dialogue avec la base. En apprenant SQL, vous pouvez interagir directement avec la base.
SQL s'intègre avec tous les langages de développement applicatif (PHP, Java, etc.). Vous pouvez créer vos propres visualisations en l'intégrant à l'application, ou extraire des données et les convertir en XML/JSON pour des services web ou des API.
Les bases ont évolué avec l'essor du Big Data et l'usage massif des données au quotidien ; les bases NoSQL gagnent en popularité. Apprendre SQL vous donne de solides fondamentaux pour comprendre quand utiliser des bases structurées et quand préférer du NoSQL, et en apprécier les différences.

Nous allons passer en revue une brève introduction et le vocabulaire SQL/base de données pour accélérer votre apprentissage ! Si vous savez déjà ce qu'est une base relationnelle et SQL, rendez-vous directement au code.

Introduction à SQL et aux bases de données

Une base de données est un ensemble organisé d'informations. Pour les gérer, on utilise des SGBD (systèmes de gestion de bases de données, DBMS). Un SGBD stocke, retrouve et modifie les données à la demande.

Plusieurs modèles se sont succédé : hiérarchique, réseau, relationnel, puis NoSQL. Une base relationnelle est un ensemble de relations ou tables bidimensionnelles.

Terminologie employée dans un SGBDR :

Terme	Définition
Table	Structure de base d'un SGBDR. Elle stocke toutes les informations nécessaires sur un élément du monde réel. Exemple : Employees.
Ligne ou tuple	Regroupe toutes les données concernant un élément particulier (ex. : un employé). Chaque ligne peut être identifiée par une clé primaire, garantissant l'absence de doublon.
Colonne ou attribut	Caractéristique d'une entité
Clé primaire	Champ identifiant de manière unique une ligne
Clé étrangère	Colonne identifiant la relation entre les tables. Elle réfère une clé primaire d'une autre table.

Vous pouvez relier plusieurs tables via clés primaires et étrangères. Chaque ligne est identifiée de façon unique par une clé primaire (PK). On réfère une autre table via une clé étrangère (FK). Par exemple :

On accède aux bases relationnelles avec le Structured Query Language, ou SQL. Toute base prend en charge le SQL ANSI (standard), mais chaque moteur ajoute sa syntaxe propre pour certaines opérations. Dans ce tutoriel, vous apprendrez le SQL ANSI pour pouvoir travailler sur toutes les bases. On peut regrouper SQL ANSI en cinq volets ; nous citerons les cinq, mais nous nous concentrerons ici sur deux : extraction de données et DML :

Extraction de données :
- SELECT.
Data Manipulation Language (DML) :
- INSERT, UPDATE, DELETE, MERGE
Data Definition Language (DDL) :
- CREATE, ALTER, DROP, RENAME, TRUNCATE.
Data Control Language (DCL) :
- GRANT, REVOKE.
Gestion des transactions :
- COMMIT, ROLLBACK, SAVEPOINT.

Il existe différents éditeurs de SGBDR. Les plus courants :

Oracle (Oracle Corporation)
Microsoft SQL Server (Microsoft)
MySQL (Oracle Corporation)
PostgreSQL (PostgreSQL Global Development Group)
SQLite (développé par D. Richard Hipp)

Où exécuter vos requêtes SQL ? Par exemple :

Un outil de reporting lisant la base et affichant les données (ex. : Tableau, Microsoft BI)
Une interface graphique d'administration (ex. : TOAD, SQL Developer pour Oracle, phpMyAdmin pour MySQL)
Une console en ligne de commande (ex. : SQL*Plus pour Oracle)

Avec des identifiants valides, vous pouvez explorer les objets de la base via des requêtes ou via une interface graphique, selon votre moteur.

C'est parti ! Passons à l'apprentissage de SQL...

Types de données SQL

Chaque colonne d'une base possède un nom, un type de données et parfois une taille. Le concepteur de la base choisit les types selon les besoins et les volumes.

En tant que data scientist, vous devez connaître ces types pour utiliser correctement les fonctions de la base et écrire des requêtes précises. Il existe un type pour chaque nature de donnée : nom de personne, texte, nombres, image stockée, etc.

Voici les principaux types pour Oracle Server, SQL Server et MySQL :

Pour aller plus loin :

SQL et data reporting

Pour tous les exemples SQL de ce tutoriel, nous utiliserons le schéma ci-dessous :

Considérez une base avec deux tables : emp, contenant les données employés, et dept, contenant les services.

La table emp comprend : numéro d'employé (empno), nom (ename), salaire (sal), commission (comm), intitulé de poste (job), id manager (mgr), date d'embauche (hiredate) et numéro de service (deptno). Le manager étant lui-même un employé avec un empno, mgr réfère l'un des empno dont le job est "MANAGER".

La table dept comprend : numéro (deptno), nom du service (dname) et localisation (loc).

Attention : chaque base a un format de date par défaut différent. Ici, DD-MON-YY correspond au format Oracle. SQL Server et MySQL utilisent par défaut YYYY-MM-DD.

Vos tables seront sans doute différentes : adaptez simplement les noms de tables et d'attributs. Dans ce tutoriel, vous lirez uniquement des données (pas d'écriture, mise à jour ou création d'objets). Aucun risque de perte ou de modification de données !

Information : un schéma est un ensemble d'objets (tables, fonctions, procédures, vues) appartenant à un utilisateur.

Dans le schéma ci-dessus, emp comporte six attributs pour l'entité Employee. La table dept en comporte trois pour l'entité Department.

Récupérer des données avec SELECT

Une instruction SELECT extrait des informations de la base. Elle permet :

1. Projection : choisir les colonnes à renvoyer. Vous pouvez en sélectionner aussi peu ou autant que nécessaire.

2. Sélection : choisir les lignes à renvoyer selon des critères pour restreindre le résultat.

3. Jointure : réunir des données stockées dans différentes tables en créant un lien entre elles.

La requête SELECT de base indique les colonnes souhaitées et la table source via FROM. Par exemple : quels sont les noms et postes de tous les employés ? SELECT ename, job FROM employee; Cette requête renverra, d'après notre schéma, un tableau comme suit :

ename	job
A	Salesman
B	Manager
C	Manager

Pour sélectionner toutes les colonnes et lignes d'une table, on utilise l'opérateur * :

SELECT *
FROM employee;
The output will be:

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

Quelques conseils pour écrire vos requêtes :

SQL n'est pas sensible à la casse

Mettez chaque clause sur une nouvelle ligne pour améliorer la lisibilité

Une instruction peut tenir sur une ou plusieurs lignes

Vous pouvez créer des expressions en utilisant les opérateurs +, -, /, * sur les dates et les nombres. Par exemple, calculer 20 % du salaire de tous les employés :

SELECT ename, sal*(20/100)
FROM employee;
Output:

    ENAME      SAL*(20/100)
   ---------- ------------
    SMITH               160
    ALLEN               320
    WARD                250
    JONES               595
    BLAKE               570

Information :

Utilisez des parenthèses pour clarifier les expressions

La division et la multiplication priment sur l'addition et la soustraction

En cas de même priorité, l'évaluation se fait de gauche à droite

Les valeurs NULL sont traitées spécifiquement. NULL signifie valeur inconnue. Toute opération impliquant NULL renvoie NULL. Chaque base propose des fonctions pour gérer les NULL ; COALESCE est commune à MySQL, SQL Server et Oracle.

SELECT ename, sal+COALESCE(comm,0)
FROM employee;
Output:

    ENAME      SAL+COALESCE(COMM,0)
---------- --------------------
    SMITH                       800
    ALLEN                      1900
    WARD                       1750
    JONES                      2975
    BLAKE                      2850

Alias de colonnes et concaténation

Dans les sorties précédentes, l'en-tête de colonne reprend le nom du champ ou de l'expression. Pour vos rapports, vous pouvez renommer ces en-têtes via des alias.

SELECT ename AS "Emp Name", sal*(20/100) as "20% of Salary"
FROM employee;
Output:

    Emp Name      20% of Salary
   ---------- ------------
    SMITH               160
    ALLEN               320
    WARD                250
    JONES               595
    BLAKE               570

Si l'alias contient des espaces, utilisez des guillemets doubles. Sinon, ils sont facultatifs et AS peut être omis.

Vous pouvez formater la sortie par concaténation : ajoutez votre texte avec CONCAT ou des opérateurs comme || ou + selon le moteur :

Oracle : CONCAT() et || (CONCAT() n'accepte que deux arguments ; imbriquez-les si besoin).
MySQL : CONCAT()
Microsoft SQL Server : opérateur + et CONCAT().

Oracle : SELECT '20% of salary of '||ename||' is '||sal*(20/100) as "20% of salary" FROM employee;

SELECT CONCAT(CONCAT('20% of salary of',ename),CONCAT(' is ',sal(20/100))) as "20% of salary" FROM employee; MySQL et Microsoft SQL Server : SELECT CONCAT('20% of salary of ',ename,' is ',sal(20/100)) as "20% of salary" FROM employee; Tous produisent la même sortie :

    20% of salary
-----------------------------------------------------------------------
    20% of salary of SMITH is 160
    20% of salary of ALLEN is 320
    20% of salary of WARD is 250
    20% of salary of JONES is 595
    20% of salary of BLAKE is 570

Supprimer les doublons avec DISTINCT

SELECT deptno
FROM employee;
Above query will result in:


    DEPTNO
----------
        20
        30
        30
        20
        30

Ici, les deptno 20 et 30 se répètent. Éliminez les doublons avec DISTINCT dans la clause SELECT.

SELECT distinct ename, deptno, job
FROM employee;



    DEPTNO
----------
        30
        20

Restreindre et trier les données

La clause WHERE filtre les données selon une condition.

Trouver tous les employés dont le poste est CLERK :

SELECT ename, job
FROM employee
WHERE job='CLERK';
    ENAME      JOB
---------- ---------
    SMITH      CLERK

Vous pouvez combiner différents critères avec des opérateurs, symboles conditionnels et mots-clés :

L'usage de =, <>, !=, >=, <=, >, < est direct, comme ci-dessus avec =.

Syntaxe AND & OR : SELECT column1, column2,.. FROM table_name WHERE condition1 AND condition2 AND condition 3...;

SELECT column1, column2,.. FROM table_name WHERE condition1 OR condition2 OR condition 3...; Trouver les employés dont le poste est MANAGER et appartenant au service 30 :

SELECT ename
FROM employee
WHERE job='MANAGER' AND deptno=30;
    ENAME
    ----------
    BLAKE

SELECT ename
FROM employee
WHERE job='MANAGER' OR deptno=30;
    ENAME
    ----------
    ALLEN
    WARD
    JONES
    BLAKE

Syntaxe NOT : SELECT column1, column2, ... FROM table_name WHERE NOT condition; Trouver tous les employés dont le poste n'est pas SALESMAN :

SELECT ename, job
from employee
WHERE NOT job='SALESMAN';
    ENAME      JOB
    ---------- ---------
    SMITH      CLERK
    JONES      MANAGER
    BLAKE      MANAGER

Vous pouvez créer des conditions complexes en combinant AND, OR et NOT. Ordre de priorité :

Trouver les employés dont le poste n'est pas CLERK et qui appartiennent au service 20 :

SELECT ename, job
from employee
WHERE NOT job='SALESMAN' AND sal>800;
    ENAME      JOB
    ---------- ---------
    JONES      MANAGER
    BLAKE      MANAGER

Ici, NOT est évalué avant AND.

Autres opérateurs utiles :

BETWEEN...AND :

SELECT *
FROM employee
WHERE sal BETWEEN 1000 AND 2000;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

LIKE :

LIKE utilise deux jokers : le pourcentage % et le souligné _ pour représenter le nombre de caractères dans le motif.

% signifie zéro, un ou plusieurs caractères
- %M% : toute chaîne contenant M
- M% : commence par M
- %M : se termine par M
- M%A : commence par M et se termine par A

Les motifs sont sensibles à la casse.

_ spécifie le nombre de caractères inconnus avant/après le caractère connu. Un souligné = un caractère.
- _r% : r en deuxième position.

Récupérer les noms des employés commençant par « B » :

SELECT *
FROM employee
WHERE ename LIKE 'B%';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

Récupérer les noms commençant par « A » et contenant « E » après :

SELECT *
FROM employee
WHERE ename LIKE 'A%E%';

        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

IN(value1,value2, value3,..) :

IN() accepte une ou plusieurs valeurs et filtre une colonne sur ces valeurs entre parenthèses dans WHERE :

SELECT ename, job, hiredate
FROM employee
WHERE job IN ('CLERK','SALESMAN');
    ENAME      JOB       HIREDATE
    ---------- --------- ---------
    SMITH      CLERK     17-DEC-80
    ALLEN      SALESMAN  20-FEB-81
    WARD       SALESMAN  22-FEB-81

Vous pouvez aussi utiliser une sous-requête dans IN(). Exemple :

SELECT ename, job, hiredate
FROM employee
WHERE deptno IN (select deptno FROM department WHERE loc='CHICAGO');
    ENAME      JOB       HIREDATE
    ---------- --------- ---------
    ALLEN      SALESMAN  20-FEB-81
    WARD       SALESMAN  22-FEB-81
    BLAKE      MANAGER   01-MAY-81

La SELECT dans IN() est une sous-requête. Nous y reviendrons plus loin !

IS NULL :

IS NULL teste la présence de NULL dans un attribut. Par exemple, trouver les employés sans commission :

SELECT ename, job, sal
FROM employee
WHERE comm IS NULL;
    ENAME      JOB              SAL
    ---------- --------- ----------
    SMITH      CLERK            800
    JONES      MANAGER         2975
    BLAKE      MANAGER         2850

Pour lister ceux qui ont une commission : IS NOT NULL :

SELECT ename, job, sal, comm
FROM employee
WHERE comm IS NOT NULL;
    ENAME      JOB              SAL       COMM
    ---------- --------- ---------- ----------
    ALLEN      SALESMAN        1600        300
    WARD       SALESMAN        1250        500

Pour filtrer sur une date, utilisez le format par défaut du moteur. Pour d'autres formats, appliquez des fonctions de date (voir plus loin). Trouver les employés embauchés après le 21 février 1981 :

(Ici, format Oracle par défaut)

SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81';
    ENAME      JOB              SAL       COMM
    ---------- --------- ---------- ----------
    WARD       SALESMAN        1250        500
    JONES      MANAGER         2975
    BLAKE      MANAGER         2850

Les expressions conditionnelles complexes combinent les opérateurs ci-dessus ; attention à la priorité d'opérateurs (ordre d'évaluation). Règles selon les bases :

Deux fonctions utiles en condition : ANY(), ALL(). Par exemple :

SELECT ename, job, sal, comm
FROM employee
WHERE deptno=ANY(SELECT deptno from dept WHERE loc='NEW YORK');

SELECT ename, job, sal, comm
FROM employee
WHERE deptno=ALL(SELECT deptno from dept WHERE dname='SALES');

Notez l'usage des sous-requêtes : elles seront détaillées plus loin.

Trier les résultats avec ORDER BY

Vous pouvez trier en ordre croissant (ASC) ou décroissant (DESC) sur un ou plusieurs attributs, y compris des alias définis dans SELECT :

SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal desc;
    ENAME      JOB              SAL       COMM
    ---------- --------- ---------- ----------
    JONES      MANAGER         2975
    BLAKE      MANAGER         2850
    WARD       SALESMAN        1250        500

Remarque : l'ordre par défaut est croissant (ASC), il est inutile de le préciser.

SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal;
    ENAME      JOB              SAL       COMM
    ---------- --------- ---------- ----------
    WARD       SALESMAN        1250        500
    BLAKE      MANAGER         2850
    JONES      MANAGER         2975

Vous pouvez spécifier plusieurs colonnes dans ORDER BY ; le tri suit l'ordre des colonnes listées. Exemple : trier d'abord par deptno croissant, puis par nom décroissant :

SELECT ename, job, sal, comm, deptno
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY deptno ASC, ename DESC;
    ENAME      JOB              SAL       COMM     DEPTNO
    ---------- --------- ---------- ---------- ----------
    JONES      MANAGER         2975                    20
    WARD       SALESMAN        1250        500         30
    BLAKE      MANAGER         2850                    30

Personnaliser la sortie avec des fonctions ligne à ligne

Les SGBDR proposent de nombreuses fonctions : longueur de chaîne, concaténation, formatage, mathématiques, etc. On distingue :

Fonctions ligne à ligne
Fonctions multi-lignes

Fonctions ligne à ligne :

Elles s'appliquent à chaque ligne et renvoient un résultat par ligne. CONCAT() est une fonction de manipulation de chaînes. On peut les utiliser dans SELECT, WHERE et ORDER BY. On retrouve dans tous les SGBDR des fonctions pour :

Manipulation de chaînes
Date et heure
Numériques
Conversion

Les noms peuvent différer selon les bases. Voici des fonctions utiles communes à SQL Server, Oracle et MySQL (certaines ont déjà été vues). Des liens complets figurent en fin de section.

Par catégorie :

Fonctions de manipulation de chaînes

LOWER() : convertit en minuscules.

SELECT lower(ename) as ename
FROM employee;
    ENAME
    ----------
    smith
    allen
    ward
    jones
    blake

UPPER() : convertit en majuscules.

SELECT upper(ename) as ename
FROM employee;
    ENAME
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    BLAKE

SUBSTR()[Oracle, MySQL] : extrait une sous-chaîne SUBSTR(string, start-position, length)

SUBSTRING()[SQL Server] : équivalent SUBSTRING(string, start-position, length).

SELECT SUBSTR(ename,2,3) as substr_ename
FROM employee;

Sous SQL Server, remplacez par SUBSTRING.

SUBSTR_ENAME
------------
MIT
LLE
ARD
ONE
LAK

LENGTH()[Oracle, MySQL] : longueur de chaîne

LEN()[SQL Server] : longueur de chaîne

SELECT LENGTH(ename) as len_ename
FROM employee;

Sous SQL Server, utilisez LEN.

Les fonctions d'ajout/remplissage à gauche/droite, de remplacement, etc., varient en syntaxe selon les moteurs. Listes de référence :

Fonctions numériques

Nom	Rôle
ROUND(m,n) :	Arrondit m à n décimales.
ABS(m) :	Valeur absolue d'un nombre
FLOOR(n) :	Plus grand entier inférieur ou égal à n.
MOD(m,n) :	Reste de la division de m par n (non disponible tel quel sous SQL Server ; utiliser % : 35 % 6)

Oracle :

    SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24) from dual;

OUTPUT:

    ROUND(45.926,2)  MOD(11,5) FLOOR(34.4)   ABS(-24)
--------------- ---------- ----------- ----------
          45.93          1          34         24

MySQL :

    SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24);

SQL Server :

    SELECT ROUND(45.926,2) as round,11%5 as mod, FLOOR(34.4) as floor,ABS(-24) as abs;

Listes de fonctions numériques :

Fonctions de conversion

Elles convertissent un type de données en un autre. Les fonctions varient selon les serveurs. Exemple Oracle avec to_char pour personnaliser l'affichage (format par défaut : DD-MON-YY) :

SELECT ename, to_char(hiredate,'DD, MONTH YYYY') as Hiredate, to_char(hiredate,'DY') as Day
from employee;
    ENAME      Hiredate                                     DAY
    ---------- -------------------------------------------- ------------
    SMITH      17,DECEMBER  1980                            WED
    ALLEN      20,FEBRUARY  1981                            FRI
    WARD       22,FEBRUARY  1981                            SUN
    JONES      02,APRIL     1981                            THU
    BLAKE      01,MAY       1981                            FRI

Chaque serveur propose de nombreuses fonctions de conversion. Pour approfondir :

Fonctions de date et d'heure

Elles permettent de manipuler les dates (ajouter des jours, calculer des mois entre deux dates, etc.), très utile en reporting. Les noms et signatures varient selon les bases. Voici quelques exemples et des liens vers les fonctions de date des bases les plus répandues : Oracle, SQL Server et MySQL. Essayez-les sur votre serveur !

Grouper vos résultats SQL

Les fonctions de regroupement (multi-lignes) s'appliquent à des groupes et renvoient un résultat par groupe. Elles servent à calculer, par exemple, le total par trimestre, le prix moyen sur une période, le plus gros investissement du mois, etc.

Produire des agrégats avec les fonctions de groupe

La clause GROUP BY regroupe les résultats, souvent avec COUNT, MAX, MIN, AVG, SUM. Syntaxe :

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Points à retenir sur GROUP BY :

On ne peut pas utiliser des alias dans GROUP BY
WHERE précède toujours GROUP BY
HAVING suit GROUP BY et porte sur les fonctions d'agrégat
ORDER BY vient en dernier

COUNT compte le nombre de lignes selon un critère (ou sans). Les valeurs NULL sont ignorées par COUNT.

Compter les employés dont le nom commence par « A » :

SELECT count(ename)
FROM employee
WHERE ename LIKE 'A%';

Output:

COUNT(ENAME)
------------
           1

Total, moyenne, minimum et maximum des salaires :

SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL"
FROM employee;

     TOTAL SAL    AVG SAL    MIN SAL    MAX SAL
    ---------- ---------- ---------- ----------
      9475       1895        800       2975

Grouper ces résultats par service :

SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL", deptno
FROM employee
GROUP BY deptno;
     TOTAL SAL    AVG SAL    MIN SAL    MAX SAL     DEPTNO
    ---------- ---------- ---------- ---------- ----------
      5700       1900       1250       2850         30
      3775     1887.5        800       2975         20

Vous pouvez aussi calculer la variance et l'écart-type :

Pour Oracle et MySQL	MS SQL SERVER
SELECT STDDEV(column_name) FROM table_name;	SELECT STDEV(column_name) FROM table_name;
SELECT VARIANCE(column_name) FROM table_name;	SELECT VAR(column_name) FROM table_name;

On peut grouper sur plus d'une colonne.

Somme des salaires par poste au sein de chaque service :

SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job;

     TOTAL SAL     DEPTNO   JOB
    ---------- ---------- ---------
       800         20       CLERK
      2850         30       SALESMAN
      2975         20       MANAGER
      2850         30       MANAGER

Vous pouvez restreindre les groupes avec HAVING (exclusivement pour les conditions sur agrégats).

Note : n'utilisez pas WHERE pour filtrer sur des fonctions d'agrégat.

SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job
HAVING sum(sal)>1000
ORDER BY sum(sal);
    TOTAL SAL     DEPTNO    JOB
    ---------- ---------- ---------
      2850         30       SALESMAN
      2850         30       MANAGER
      2975         20       MANAGER

Afficher des données issues de plusieurs tables

Nous allons aborder :

Produit cartésien / cross join
Jointure interne / équi-jointure
Natural join
Outer joins (LEFT, RIGHT, FULL)
Self join

Vous aurez souvent à récupérer des données de plusieurs tables. Exemple :

Pour produire un tel rapport, il faut relier employee et department et en extraire des données. C'est le rôle des jointures.

Produit cartésien / cross join :

Le produit cartésien associe chaque ligne d'une relation R à chaque ligne d'une relation S.

Le CROSS JOIN multiplie deux tables pour former toutes les paires possibles. Si R contient I lignes et M attributs, S contient J lignes et N attributs, le produit contient I×J lignes et M+N attributs. Exemple SQL :

SELECT empno, ename, dname
FROM employee, department;

OR

SELECT empno, ename, dname
FROM employee CROSS JOIN department;
       EMPNO      ENAME      DNAME
    ---------- ---------- --------------
      7369        SMITH      ACCOUNTING
      7499        ALLEN      ACCOUNTING
      7521        WARD       ACCOUNTING
      7566        JONES      ACCOUNTING
      7698        BLAKE      ACCOUNTING
      7369        SMITH      RESEARCH
      7499        ALLEN      RESEARCH
      7521        WARD       RESEARCH
      7566        JONES      RESEARCH
      7698        BLAKE      RESEARCH
      7369        SMITH      SALES

     EMPNO        ENAME      DNAME
    ---------- ---------- --------------
      7499        ALLEN      SALES
      7521        WARD       SALES
      7566        JONES      SALES
      7698        BLAKE      SALES

15 lignes renvoyées.

Avec 5 lignes dans employee et 3 dans department, on obtient 5x3=15 lignes. Un produit cartésien est généré quand :

Aucune condition de jointure n'est définie
La condition de jointure est invalide ou mal écrite

Dès que des données de plusieurs tables sont requises, on définit une condition de jointure sur les attributs communs (souvent clé primaire / clé étrangère).

Les produits cartésiens servent aussi à simuler de gros volumes de données pour des tests.

*Jointure interne / équi-jointure :

L'inner join (ou equijoin) s'appuie sur les relations clé primaire/étrangère pour joindre deux tables ou plus :

SELECT ename, dname
FROM employee e,department d
WHERE e.deptno=d.deptno;

OR

SELECT ename, dname
FROM employee e
JOIN department d
ON e.deptno=d.deptno;

    ENAME      DNAME
    ---------- --------------
    SMITH      RESEARCH
    ALLEN      SALES
    WARD       SALES
    JONES      RESEARCH
    BLAKE      SALES

Ici, deptno est clé primaire dans department et clé étrangère dans employee. Vous pouvez ajouter d'autres conditions logiques.

Bonnes pratiques pour les JOINS :

Si une même colonne existe dans plusieurs tables, préfixez-la par le nom de table (c'est une bonne pratique en général).

Pour joindre n tables, il faut au minimum n-1 conditions de jointure. Par ex. : 4 tables → 3 jointures.

Alias de tables : dans FROM employee e, department d, e et d simplifient l'écriture et résolvent les ambiguïtés quand des noms de colonnes se recoupent.

On peut créer des non-équi-jointures basées sur d'autres conditions que l'égalité. Exemple avec une table salgrade indiquant le grade selon le salaire :

Trouver le grade de chaque employé en fonction de son salaire (salgrade) et du salaire (employee) :

SELECT e.ename, e.sal, s.grade
FROM employee e, salgrade s
WHERE e.sal between s.losal AND s.hisal;
    ENAME             SAL      GRADE
    ---------- ---------- ----------
    JONES            2975          4
    BLAKE            2850          4
    ALLEN            1600          3
    WARD             1250          2
    SMITH             800          1

Exemple : fournir nom, salaire, grade et nom de service. Le salaire est dans employee, les grades dans salgrade, le nom de service dans department : il faut joindre trois tables :

SELECT e.ename, e.sal, d.dname, s.grade
FROM employee e, department d, salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal;

Output:
ENAME             SAL DNAME               GRADE
---------- ---------- -------------- ----------
JONES            2975 RESEARCH                4
BLAKE            2850 SALES                   4
ALLEN            1600 SALES                   3
WARD             1250 SALES                   2
SMITH             800 RESEARCH                1

Ici, trois tables et deux conditions de jointure (dont une non-équi-jointure).

Natural join :

Les natural joins joignent automatiquement des colonnes portant le même nom. Si les types diffèrent, une erreur est renvoyée.

Syntaxe : SELECT FROM table1 NATURAL JOIN table2; SELECT FROM employee NATURAL JOIN department; Comme natural join détecte lui-même les colonnes correspondantes, il peut en trouver plusieurs avec des types différents et échouer. La clause USING permet alors de spécifier la/les colonnes d'équi-jointure.

Note : NATURAL JOIN et USING sont deux clauses distinctes et exclusives : on n'utilise pas USING avec NATURAL JOIN.

SELECT e.ename, d.dname, e.sal
FROM employee e JOIN department d
USING (deptno)
WHERE deptno=20;

output:

ENAME      DNAME                 SAL
---------- -------------- ----------
SMITH      RESEARCH              800
JONES      RESEARCH             2975

Les colonnes en USING ne doivent pas être préfixées par un nom de table. Par exemple, ceci est incorrect :

SELECT e.ename, d.dname, e.sal
FROM employee e JOIN department d
USING (d.deptno)
WHERE d.deptno=20;

d.deptno est incorrect ; il faut simplement deptno. Outer joins :

Trois jointures externes :

Left outer join : renvoie le résultat de la jointure interne, plus les lignes non appariées de la table de gauche.
Right outer join : idem, avec les lignes non appariées de la table de droite.
Full outer join : renvoie les lignes appariées et toutes les non appariées des deux côtés.

Voyons-les :

SELECT e.ename, s.grade
FROM salgrade s LEFT OUTER JOIN employee e
ON e.sal BETWEEN s.losal AND s.hisal;

Output:

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
JONES              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
BLAKE              30 SALES.sal BETWEEN s.losal AND s.hisal;

Output:

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
JONES              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
BLAKE              30 SALESSELECT e.ename, d.deptno, d.dname

FROM employee e RIGHT OUTER JOIN department d
ON e.deptno=d.deptno;

output:

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
JONES              20 RESEARCH
BLAKE              30 SALES
                   10 ACCOUNTINGSELECT e.ename, d.deptno, d.dname

FROM employee e FULL OUTER JOIN department d
ON e.deptno=d.deptno;

Output:
ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
JONES              20 RESEARCH
BLAKE              30 SALES
                   10 ACCOUNTING

La table department comportait une ligne non appariée pour deptno 10, et aucune non appariée dans employee. De même, salgrade contient une ligne non appariée pour le grade 5.

Self-join :

Parfois, on joint une table avec elle-même. Par exemple, employee contient aussi les managers. Pour lister employés et managers, on réalise une auto-jointure :

SELECT e.ename as Employee, m.ename as Manager
FROM employee e, employee m
WHERE e.mgr=m.empno;
    EMPLOYEE   MANAGER
    ---------- ----------
    ALLEN      BLAKE
    WARD       BLAKE

Utiliser des sous-requêtes

Une sous-requête est une requête imbriquée. Elle permet de décomposer un besoin complexe. On peut les imbriquer et les utiliser dans :

WHERE
FROM
HAVING

Exemple : trouver les employés dont le salaire est supérieur à celui de James. Décomposition :

Récupérer le salaire de James
Comparer ce salaire à tous les employés

On résout avec une sous-requête : l'intérieure s'exécute une fois avant la requête principale.

SELECT empno, ename
FROM employee
WHERE sal>(SELECT sal from employee where ename='JAMES');

Types de sous-requêtes :

Mono-ligne : renvoie une seule ligne.
Multi-lignes : renvoie plusieurs lignes.

Points à retenir :

Encadrez les sous-requêtes de parenthèses.

Placez-les à droite de l'opérateur de comparaison.

Utilisez des opérateurs mono-ligne (>, <, >=, <=, <>) avec des sous-requêtes mono-ligne.

Utilisez IN, ANY, ALL avec des sous-requêtes multi-lignes.

Sous-requête mono-ligne :

Trouver les employés dont le poste est identique à celui de l'empno 7521 :

SELECT ename, job
FROM employee
WHERE job=(SELECT job FROM employee WHERE empno=7521);
    ENAME      JOB
    ---------- ---------
    ALLEN      SALESMAN
    WARD       SALESMAN

Sélectionner les départements dont le salaire maximal est supérieur ou égal au max du département 20 :

SELECT deptno, max(sal)
FROM employee
GROUP BY deptno
HAVING max(sal)>=(SELECT max(sal) FROM employee WHERE deptno=20);
    DEPTNO   MAX(SAL)
---------- ----------
        20       2975

Sous-requête multi-lignes :

Trouver les employés dont le salaire est égal à l'un des salaires des MANAGER :

SELECT ename, job, sal
FROM employee
WHERE sal IN (SELECT sal FROM employee WHERE job='MANAGER');
    ENAME      JOB              SAL
    ---------- --------- ----------
    JONES      MANAGER         2975
    BLAKE      MANAGER         2850

Placées dans FROM, les sous-requêtes agissent comme des tables temporaires (vues). Exemple :

SELECT e.ename, e.job, e.sal
FROM employee e, (SELECT deptno FROM department WHERE loc='DALLAS') d
WHERE e.deptno=d.deptno;
    ENAME      JOB              SAL
    ---------- --------- ----------
    SMITH      CLERK            800
    JONES      MANAGER         2975

Utiliser les opérateurs ensemblistes

Les opérateurs d'ensemble combinent les résultats de plusieurs requêtes en un seul résultat : UNION, MINUS, INTERSECT. Voici comment les mobiliser pour optimiser vos requêtes. Nous verrons :

Information : des requêtes avec opérateurs d'ensemble sont dites composées.

UNION et UNION ALL
INTERSECT
EXCEPT (standard SQL) et MINUS (propre à Oracle)

UNION

Soient deux relations R et S : UNION sélectionne toutes les lignes de R et de S en éliminant les doublons. Le nombre de lignes renvoyées est au plus r+s (r = lignes de R, s = lignes de S).

Sélectionner tous les noms de services, y compris ceux des employés embauchés avant le 23-OCT-1999 :

SELECT dname
FROM department
UNION
SELECT dname
FROM department, employee
WHERE department.deptno=employee.deptno AND employee.hiredate<to_date('23-OCT-1999');
    DNAME
    --------------
    ACCOUNTING
    OPERATIONS
    RESEARCH
    SALES

À savoir sur UNION :

Le nombre et les types de colonnes doivent être identiques dans chaque SELECT.

Les noms de colonnes peuvent différer.

Le résultat est trié par défaut sur la première colonne du premier SELECT.

Les NULL ne sont pas ignorés lors de la déduplication.

UNION ALL combine les résultats sans supprimer les doublons (DISTINCT n'est pas appliqué). Supposons une table emp avec des employés de l'année 2000. Récupérer empno, ename, job depuis emp et employee :

SELECT empno, ename, job
FROM employee
UNION ALL
SELECT empno, ename, job
FROM emp;
       EMPNO      ENAME      JOB
    ---------- ---------- ---------
      7369        SMITH      CLERK
      7499        ALLEN      SALESMAN
      7521        WARD       SALESMAN
      7566        JONES      MANAGER
      7698        BLAKE      MANAGER
      7369        SMITH      CLERK
      7499        ALLEN      SALESMAN
      7521        WARD       SALESMAN
      7566        JONES      MANAGER
      7654        MARTIN     SALESMAN
      7698        BLAKE      MANAGER

     EMPNO        ENAME      JOB
    ---------- ---------- ---------
      7782        CLARK      MANAGER
      7788        SCOTT      ANALYST
      7839        KING       PRESIDENT
      7844        TURNER     SALESMAN
      7876        ADAMS      CLERK
      7900        JAMES      CLERK

INTERSECT

Comme en théorie des ensembles, INTERSECT renvoie l'intersection de R et S (lignes communes).

SELECT empno, ename, job
FROM employee
INTERSECT
SELECT empno, ename, job
FROM emp;
     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER
      7698 BLAKE      MANAGER

À savoir sur INTERSECT :

Nombre et types de colonnes identiques dans chaque SELECT.

Les noms n'ont pas besoin de coïncider.

INTERSECT ne filtre pas les NULL avant intersection.

EXCEPT et MINUS :

EXCEPT (SQL Server) et MINUS (Oracle) ont le même rôle : renvoyer les lignes distinctes du premier SELECT absentes du second. Exemple :

Trouver les produits avec une quantité entre 1 et 100, hors plage 50–75. Sous SQL Server : EXCEPT. Sous Oracle : MINUS.

SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
EXCEPT
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;

Sous Oracle :

SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
MINUS
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;

      PROD_NAME               QTY      
    -------------       ----------
          COLGATE               1
          SENSODYNE             100
          SENSODYNE TOOTHBRUSH  30

À savoir sur EXCEPT/MINUS :

Le nombre et les types de colonnes doivent être identiques dans chaque SELECT.

Les noms de colonnes peuvent différer.

Toutes les colonnes présentes dans WHERE doivent être dans le SELECT pour que MINUS fonctionne.

Félicitations !

Vous êtes au bout du tutoriel. Vous avez acquis une base solide pour maîtriser SQL dans votre parcours data : fondamentaux des bases et de SQL, types de données courants, fonctions pour formater les rapports, agréger et synthétiser, et techniques pour réunir des données issues de plusieurs tables selon vos besoins. Conçu pour les apprenants en data science, ce tutoriel vous aidera tant sur les bases relationnelles que pour aborder sereinement les bases NoSQL et valoriser vos compétences SQL dans le Big Data.

Pour aller plus loin, suivez le cours gratuit de DataCamp : Intro to SQL for Data Science.