Retour aux cours de Systemes d Information

Systemes d Information - Terminale STMG

SQL Avance

Apres avoir maîtrise les bases de SQL, decouvrez les techniques avancees utilisees par les data analysts de Netflix, Spotify et les grandes banques pour exploiter pleinement leurs données.

Objectifs du chapitre

Maîtriser les différents types de jointures (INNER, LEFT, RIGHT, FULL)
Utiliser les sous-requetes pour des analyses complexes
Manipuler les données avec INSERT, UPDATE et DELETE
Comprendre les transactions et la securite des données

Introduction : Aller Plus Loin avec SQL

Chez Netflix, les data analysts utilisent des requetes SQL complexes pour repondre a des questions comme : "Quels utilisateurs qui ont regarde Stranger Things n ont jamais regarde une comedie française, mais ont un profil similaire a ceux qui les adorent ?"

Ce type d analyse necessite des sous-requetes, des jointures multipleset une maîtrise avancee de SQL. C est ce que vous allez apprendre dans ce chapitre.

Chiffres clés

Spotify analyse 500 petaoctets de données utilisateurs avec SQL chaque jour.
BNP Paribas execute 50 millions de requetes SQL par jour pour la detection de fraude.
Les salaires des experts SQL sont 20% plus eleves que la moyenne IT.

Partie 1 : Les Jointures Avancees

1.1 Rappel : INNER JOIN

L INNER JOIN ne retourne que les lignes qui ont une correspondance dans les deux tables. C est la jointure la plus courante.

-- INNER JOIN : seulement les clients qui ont passe des commandes

SELECT c.nom, c.prenom, co.date_commande, co.montant

FROM clients c

INNER JOIN commandes co ON c.id_client = co.id_client;

1.2 LEFT JOIN (ou LEFT OUTER JOIN)

Le LEFT JOIN retourne TOUTES les lignes de la table de gauche, même celles sans correspondance. Les valeurs manquantes sont remplies par NULL.

-- LEFT JOIN : TOUS les clients, même ceux sans commande

SELECT c.nom, c.prenom, co.montant

FROM clients c

LEFT JOIN commandes co ON c.id_client = co.id_client;

Cas d entreprise : Netflix

Netflix utilise des LEFT JOIN pour identifier les abonnes inactifs : "Donnez-moi tous les utilisateurs, même ceux qui n ont rien regarde ce mois-ci". Cela permet d envoyer des emails de re-engagement cibles.

1.3 RIGHT JOIN et FULL JOIN

RIGHT JOIN

Inverse du LEFT JOIN : retourne TOUTES les lignes de la table de droite. Moins utilise que LEFT JOIN (on peut toujours inverser l ordre des tables).

FULL JOIN

Combine LEFT et RIGHT : retourne TOUTES les lignes des deux tables, avec NULL la ou il n y a pas de correspondance.

-- FULL JOIN : tous les clients ET toutes les commandes

SELECT c.nom, co.id_commande, co.montant

FROM clients c

FULL JOIN commandes co ON c.id_client = co.id_client;

Type de Jointure	Table Gauche	Intersection	Table Droite
INNER JOIN	Non	Oui	Non
LEFT JOIN	Oui	Oui	Non
RIGHT JOIN	Non	Oui	Oui
FULL JOIN	Oui	Oui	Oui

Attention au Bac

Au Bac, on vous demandera souvent de choisir le bon type de jointure. Retenez : LEFT JOIN = "TOUS les X, meme ceux sans Y". Exemple : "Tous les clients, meme ceux sans commande" = LEFT JOIN.

Partie 2 : Les Sous-Requetes

Une sous-requete est une requete SQL imbriquee dans une autre requete. Elle permet de faire des analyses en plusieurs etapes en une seule instruction.

2.1 Sous-requete dans WHERE

-- Clients ayant passe une commande superieure a la moyenne

SELECT nom, prenom

FROM clients

WHERE id_client IN (

SELECT id_client

FROM commandes

WHERE montant > (SELECT AVG(montant) FROM commandes)

);

2.2 Sous-requete dans SELECT

-- Afficher chaque client avec son nombre de commandes

SELECT

nom,

prenom,

(SELECT COUNT(*) FROM commandes co WHERE co.id_client = c.id_client) AS nb_commandes

FROM clients c;

2.3 Sous-requete dans FROM

-- Moyenne des totaux par client (sous-requete comme table temporaire)

SELECT AVG(total_client) AS moyenne_par_client

FROM (

SELECT id_client, SUM(montant) AS total_client

FROM commandes

GROUP BY id_client

) AS totaux;

Cas d entreprise : Spotify

Spotify utilise des sous-requetes pour ses recommandations : "Trouvez les artistes ecoutes par des utilisateurs ayant des gouts similaires aux miens, mais que je n ai pas encore decouverts." Ce sont des requetes imbriquees sur 3-4 niveaux !

Partie 3 : Manipuler les Donnees (INSERT, UPDATE, DELETE)

Jusqu ici, nous avons fait des requetes de lecture (SELECT). SQL permet aussi de modifier les données avec INSERT, UPDATE et DELETE.

3.1 INSERT : Ajouter des données

-- Inserer un nouveau client

INSERT INTO clients (nom, prenom, email, ville)

VALUES ('Durand', 'Pierre', 'p.durand@email.fr', 'Marseille');

-- Inserer plusieurs lignes

INSERT INTO clients (nom, prenom, email, ville)

VALUES

('Martin', 'Julie', 'j.martin@email.fr', 'Lyon'),

('Bernard', 'Luc', 'l.bernard@email.fr', 'Paris');

3.2 UPDATE : Modifier des données

-- Modifier l email d un client specifique

UPDATE clients

SET email = 'nouveau.email@gmail.com'

WHERE id_client = 42;

-- Augmenter tous les prix de 10%

UPDATE produits

SET prix = prix * 1.10;

-- Modifier plusieurs colonnes

UPDATE clients

SET ville = 'Bordeaux', statut = 'VIP'

WHERE id_client = 42;

3.3 DELETE : Supprimer des données

-- Supprimer un client specifique

DELETE FROM clients

WHERE id_client = 42;

-- Supprimer les commandes anciennes

DELETE FROM commandes

WHERE date_commande < '2020-01-01';

Attention au Bac (et en entreprise !)

TOUJOURS mettre une clause WHERE avec UPDATE et DELETE ! Sans WHERE :

UPDATE clients SET ville = 'Paris' → TOUS les clients deviennent Parisiens !
DELETE FROM commandes → TOUTES les commandes sont supprimees !

Chez GitLab en 2017, un admin a execute un DELETE sans WHERE en production : 300 Go de données clients supprimes. Incident mondial !

Partie 4 : Les Transactions (ACID)

Une transaction est un groupe d operations SQL qui doivent être executees ensemble. Soit TOUT reussit, soit TOUT echoue (rollback). C est essentiel pour la coherence des données.

4.1 Les proprietes ACID

A - Atomicite

Tout ou rien. Si une operation echoue, toutes les operations sont annulees.

C - Coherence

La base passe d un état valide a un autre état valide. Les règles sont respectees.

I - Isolation

Les transactions concurrentes ne s interferent pas. Chacune voit un état coherent.

D - Durabilite

Une fois validee, la transaction est permanente, même en cas de panne.

4.2 Exemple de transaction bancaire

-- Transfert de 1000 euros du compte A vers le compte B

BEGIN TRANSACTION;

-- Etape 1 : Debiter le compte A

UPDATE comptes SET solde = solde - 1000 WHERE id_compte = 'A';

-- Etape 2 : Crediter le compte B

UPDATE comptes SET solde = solde + 1000 WHERE id_compte = 'B';

-- Si tout OK, valider

COMMIT;

-- Si erreur, annuler

ROLLBACK;

Cas d entreprise : BNP Paribas

BNP Paribas traite 10 millions de transactions par jour. Chaque virement utilise les proprietes ACID pour garantir que votre argent ne disparait jamais "entre" deux comptes, même en cas de panne reseau.

Partie 5 : Les Vues

Une vue est une requete enregistree qui se comporte comme une table virtuelle. Elle simplifie les requetes complexes et peut servir a controler l acces aux données.

-- Creer une vue : synthese des ventes par client

CREATE VIEW vue_synthese_clients AS

SELECT

c.id_client,

c.nom,

c.prenom,

COUNT(co.id_commande) AS nb_commandes,

SUM(co.montant) AS total_achats

FROM clients c

LEFT JOIN commandes co ON c.id_client = co.id_client

GROUP BY c.id_client, c.nom, c.prenom;

-- Utiliser la vue comme une table

SELECT * FROM vue_synthese_clients WHERE total_achats > 1000;

Avantages des vues :

Simplicite : Une requete complexe devient un simple SELECT sur la vue
Securite : Cacher certaines colonnes sensibles (salaires, mots de passe)
Coherence : Tous les utilisateurs voient les mêmes calculs

Resume des Points Cles pour le Bac

Jointures

INNER JOIN : intersection (correspondances)
LEFT JOIN : tout a gauche + intersection
FULL JOIN : tout des deux cotes

Manipulation

INSERT : ajouter des lignes
UPDATE : modifier (avec WHERE !)
DELETE : supprimer (avec WHERE !)

ACID = Atomicite, Coherence, Isolation, Durabilite (proprietes des transactions)

Quiz de Validation - 3 Questions

Question 1 : Pour afficher TOUS les clients, même ceux sans commande, quelle jointure utiliser ?

A. INNER JOIN

B. LEFT JOIN

C. RIGHT JOIN

D. CROSS JOIN

Question 2 : Que se passe-t-il si on execute DELETE FROM produits sans clause WHERE ?

A. Erreur de syntaxe

B. Suppression du premier produit

C. Suppression de TOUS les produits

D. Rien, la requete est ignoree

Question 3 : Que signifie le "A" dans ACID ?

A. Automatique

B. Atomicite

C. Accessibilite

D. Authentification

Correction

Question 1 : B - LEFT JOIN retourne tous les enregistrements de la table de gauche.
Question 2 : C - Sans WHERE, DELETE supprime TOUTES les lignes !
Question 3 : B - Atomicite = tout ou rien.