Perfectionnez votre maîtrise de SQL

La compagnie DVD Rental souhaite maintenant réaliser des analyses plus détaillées sur les transactions de leurs clients et employés. Pour répondre à cette demande, vous allez devoir utiliser des requêtes SQL avancées, notamment des sous-requêtes imbriquées et des Common Table Expressions (CTEs), afin de simplifier l'extraction et la manipulation des données tout en optimisant les performances.

Avant de plonger dans ces concepts avancés, il est important de bien maîtriser les bases des jointures SQL, qui sont essentielles pour relier plusieurs tables et récupérer des informations pertinentes.

Pour vous aider, voici une image qui résume visuellement les différents types de jointures SQL :

Simplifiez l’extraction des données avec des requêtes imbriquées

Sofia vous adresse un message sur Slack :

Salut,

Nous avons besoin de réaliser des analyses plus complexes. J'aimerais savoir combien de clients ont loué un film plus de 5 fois en mai et juin 2005. C’est une donnée importante pour évaluer la fidélité de nos clients.
Peux-tu nous aider en créant une requête pour extraire cette information ?

Merci d'avance

Pas nécessairement ! Plutôt que d'effectuer deux requêtes séparées puis de les joindre, vous pouvez utiliser une requête imbriquée. Celle-ci vous permet de calculer le nombre de locations par client directement à l'intérieur de la requête principale, sans avoir besoin de créer une deuxième requête et de la joindre.

Imaginons que vous deviez lister les clients qui ont loué un film plus d'une fois au cours des 6 derniers mois.

SELECT customer_id, first_name, last_name
FROM
customer
WHERE
customer_id IN (
SELECT customer_id
FROM rental
WHERE
rental_date >= '2005-05-01'
AND rental_date <= '2005-06-30'
GROUP BY customer_id
HAVING COUNT(rental_id) > 5
);

Explication :

• La sous-requête à l'intérieur de la clause WHERE retourne les customer_id qui ont effectué plus de cinq locations en mai et juin 2005.

• La requête principale affiche les détails des clients correspondants en utilisant la sous-requête pour filtrer.

Avant de plonger dans l’extraction des données à l’aide des sous-requêtes, il est important de bien comprendre deux concepts fondamentaux des jointures complexes : les Semi-join et les Anti-join. Ces types de jointures permettent de filtrer des ensembles de données en fonction de la présence ou de l’absence de correspondances dans d'autres tables.

• Semi-join : Retourne les lignes de la table principale qui ont une correspondance dans la sous-requête. Dans l'exemple ci-dessus, la sous-requête agit comme un semi-join. 

• Anti-join : Retourne les lignes de la table principale qui n'ont pas de correspondance dans la sous-requête. Exemple : Trouver les clients qui n'ont pas loué plus de 5 films en mai et juin 2005.

La requête d’Anti-join donnerait donc tout simplement :

SELECT customer_id, first_name, last_name
FROM
customer
WHERE
customer_id NOT IN (
SELECT customer_id
FROM rental
WHERE
rental_date >= '2005-05-01'
AND rental_date <= '2005-06-30'
GROUP BY customer_id
HAVING COUNT(rental_id) > 5
);

Maintenant que vous avez vu les concepts de Semi-join et Anti-join, voyons comment utiliser les requêtes imbriquées dans différents contextes, tels que les clausesSELECT,FROMetWHERE pour rendre vos requêtes encore plus puissantes et flexibles.

Utilisez une requête imbriquée dans une clause SELECT, FROM ou WHERE

Il est possible d’accomplir certaines tâches avec des jointures classiques, mais pour mieux comprendre les possibilités offertes par les sous-requêtes imbriquées, nous allons voir comment ces sous-requêtes peuvent être utilisées dans trois parties différentes d'une requête :SELECT,FROMetWHERE.

Cela permet d'enrichir les résultats en exécutant des calculs ou des filtres dans des sous-requêtes sans nécessiter de multiples jointures. Nous allons voir comment manipuler les données de différentes manières dans chacune de ces clauses. Chaque approche a ses avantages et peut rendre vos requêtes plus modulaires et faciles à maintenir.

Dans cette vidéo, nous allons explorer comment utiliser des sous-requêtes imbriquées dans différentes parties d'une requête SQL :SELECT,FROMetWHERE.

Dans cette vidéo, vous avez vu :

• L'utilisation d'une sous-requête dans la clauseSELECTpour calculer des informations spécifiques sur chaque ligne, comme le total de locations par film. 

• L'utilisation d'une sous-requête dans la clauseFROMpour préparer et manipuler des ensembles de données avant de les joindre ou d'afficher les résultats. 

• L'utilisation d'une sous-requête dans la clauseWHEREpour filtrer les résultats en fonction de critères dérivés d'une autre requête, comme la vérification des films ayant plus de 30 locations.

Intégrez les opérateurs de comparaison

Rodolphe vous adresse un email :

Object : Analyse des films loués par les employés

Salut,

Je travaille sur certaines statistiques concernant les clients et les films qu'ils ont loués. J'aimerais que tu m'aides à répondre à deux questions :

1. Existe-t-il un film que tous les clients ont loué ?

2. Y a-t-il des films qui n'ont été loués par aucun client ?

Peux-tu me préparer des requêtes SQL pour analyser ces points ?

Merci pour ton aide !

Cordialement,
Rodolphe

Les opérateurs de comparaison tels queIN,ALL,ANYetEXISTS sont parfaits pour répondre à ce genre de question. Ils permettent de vérifier si une valeur satisfait à une condition dans un ensemble de résultats retournés par une sous-requête.

L'opérateur IN 

L'opérateurIN, que vous avez déjà utilisé précédemment, permet de vérifier si une valeur spécifique existe dans un ensemble de résultats. C'est l'un des opérateurs les plus couramment utilisés pour comparer une valeur à une liste ou un ensemble retourné par une sous-requête.

Prenons un exemple simple. Vous souhaitez afficher les films qui ont été loués par des clients résidant à "Tallahassee" aux Etats-Unis :

SELECT
f.title
FROM
film f
WHERE
f.film_id IN (
SELECT
i.film_id
FROM
rental r
JOIN inventory i on i.inventory_id = r.inventory_id
JOIN customer c ON r.customer_id = c.customer_id
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country cn ON cn.country_id = ci.country_id
WHERE
ci.city = 'Tallahassee'
AND country = 'United States'
);

Explication :

• La sous-requête retourne les film_id des films loués par des clients résidant à "Tallahassee".

• La requête principale utiliseINpour lister les titres de ces films.

L'opérateur ALL

L'opérateurALLvérifie si une condition est vraie pour toutes les valeurs d’un ensemble de résultats retournés par une sous-requête. Il est parfait pour répondre à la première question de Rodolphe : savoir s'il existe un film que tous les clients ont loué. 

SELECT f.title
FROM film f
WHERE f.film_id = ALL (
SELECT i.film_id
FROM
rental r
JOIN inventory i on i.inventory_id = r.inventory_id
JOIN customer c ON r.customer_id = c.customer_id
);

Explication :

• La sous-requête retourne tous les film_id des films loués par les clients.

• ALLvérifie si chaque film dans la table film a été loué par tous les clients.

Aucun résultat n’étant retourné, cela signifie qu’aucun film n’a été loué par tous les clients de dvdrental.

L'opérateur ANY

L'opérateurANY(ouSOME) permet de vérifier si une condition est vraie pour au moins une des valeurs dans un ensemble. Il est utile pour vérifier, par exemple, si un film a été loué par au moins un client dans une période donnée.

Vous souhaitez afficher les films qui ont été loués au moins une fois par des clients du 6 au 15 juin 2005.

SELECT
f.title
FROM
film f
WHERE
f.film_id = ANY (
SELECT
i.film_id
FROM
rental r
JOIN inventory i on i.inventory_id = r.inventory_id
WHERE
r.rental_date BETWEEN '2005-06-06' AND '2005-06-15'
)

Explication :

• La sous-requête retourne les film_id des films loués entre le 6 juin 2005 et le 15 juin 2005.

• ANYvérifie si le film_id de la table film est présent dans la sous-requête, ce qui permet d'afficher les films qui ont été loués au moins une fois.

L'opérateur EXISTS

L'opérateurEXISTSpermet de vérifier l'existence de lignes retournées par une sous-requête. Il est idéal pour répondre à la deuxième question de Rodolphe : trouver les films qui n'ont été loués par aucun client.

SELECT
f.film_id
FROM
film f
WHERE
NOT NOT EXISTS (
SELECT 1
FROM
rental r
INNER JOIN inventory i on i.inventory_id = r.inventory_id
WHERE
i.film_id = f.film_id
)

Explication :

• La sous-requête vérifie l'existence des film_id dans la tablerental.

• NOT EXISTSretourne les films qui n'ont jamais été loués car il ne trouve aucune correspondance dans la sous-requête.

Ces opérateurs permettent de gérer des comparaisons complexes avec des ensembles de données retournés par des sous-requêtes.

Voici un récapitulatif :

Après avoir exploré les opérateurs de comparaison pour manipuler des sous-requêtes, il est temps de découvrir une approche plus élégante et flexible pour structurer vos requêtes complexes : les Common Table Expressions (CTEs). Elles permettent d'éviter la duplication de sous-requêtes et simplifient la réutilisation des résultats intermédiaires.

Évitez de dupliquer les sous requêtes en utilisant des CTEs

Les Common Table Expressions (CTEs) permettent de structurer une requête de manière plus claire, en vous évitant de dupliquer des sous-requêtes. Une CTE est une sorte de table temporaire créée à l'intérieur d'une requête, définie à l'aide de la clauseWITH. Contrairement aux sous-requêtes imbriquées, les CTEs peuvent être utilisées plusieurs fois dans la requête principale, ce qui les rend particulièrement pratiques pour les requêtes complexes et répétitives. 

Une CTE est définie à l'aide de la clauseWITH, suivie du nom de la CTE, de ses colonnes (optionnelles), et de la sous-requête qui génère les données. Une fois la CTE définie, elle peut être utilisée dans la requête principale comme n'importe quelle autre table.

WITH cte_name (column1, column2, ...) AS (
-- CTE query
SELECT ...
)
-- Main query using the CTE
SELECT
...
FROM
cte_name;

• cte_name : Le nom que vous donnez à la CTE. Ce nom sera utilisé dans la requête principale.

• column1, column2 : Les noms des colonnes (optionnels). Si vous ne spécifiez pas de colonnes, elles prendront les noms définis dans la sous-requête.

• CTE query : La sous-requête qui génère les données. Cela peut être n'importe quelle requête SQL, souvent avec des jointures et des agrégations.

• Main query : La requête principale qui utilise la CTE pour récupérer ou manipuler les données.

Calculez les locations totales par client

Imaginons que vous souhaitiez calculer le nombre total de locations par client, puis utiliser ce total dans une autre requête. Voici comment vous pouvez utiliser une CTE pour éviter de dupliquer la sous-requête qui calcule les locations :

WITH total_rentals_per_customer AS (
SELECT
customer_id, COUNT(rental_id) AS total_rentals
FROM
rental
GROUP BY
customer_id
)
-- Main query using the CTE
SELECT
c.customer_id, c.first_name, c.last_name, tr.total_rentals
FROM
customer c
JOIN total_rentals_per_customer tr ON c.customer_id = tr.customer_id
WHERE
tr.total_rentals > 5;

Explication :

• WITH total_rentals_per_customer AS (...): Cette partie définit la CTE. Elle crée une table temporaire contenant le customer_id et le nombre total de locations (total_rentals) par client.

• SELECT c.customer_id, c.first_name, ... : La requête principale utilise la CTE en joignant la table customer avec la CTE total_rentals_per_customer pour afficher les informations des clients ayant effectué plus de 5 locations.

À vous de jouer

Contexte

Sofia et Rodolphe aimeraient obtenir un rapport sur les clients ayant effectué un grand nombre de locations. Ils souhaitent notamment identifier les clients qui ont effectué plus de 10 locations, mais aussi afficher uniquement les clients qui ont payé plus de 100 € au total pour leurs locations. Pour éviter de dupliquer les calculs, ils aimeraient que vous utilisiez une CTE pour stocker les informations de paiements et de locations.

Consignes

1. Créez une CTE qui calcule le nombre total de locations par client.

2. Utilisez cette CTE pour filtrer les clients ayant effectué plus de 10 locations.

3. Créez une autre CTE pour calculer le montant total des paiements par client.

4. Utilisez les deux CTEs pour afficher les clients qui ont plus de 10 locations et ont payé plus de 100 € au total.

En résumé

• Vous avez appris à utiliser les sous-requêtes imbriquées dans les clausesSELECT,FROMetWHEREpour simplifier les requêtes complexes.

• Vous avez exploré les opérateurs de comparaisonIN,ALL,ANYetEXISTSpour manipuler des ensembles de résultats et filtrer vos données efficacement.

• Vous avez découvert les Common Table Expressions (CTEs) pour éviter de dupliquer les sous-requêtes et rendre vos requêtes plus lisibles et réutilisables.

• Vous savez comment optimiser l'utilisation des CTEs pour stocker des tables auxiliaires dans vos requêtes SQL. 

Maintenant que vous maîtrisez l'art de manipuler des requêtes complexes et d'optimiser vos analyses, nous allons aborder une autre technique essentielle pour améliorer l'accessibilité des résultats de vos requêtes : l'utilisation des vues. Dans le prochain chapitre, vous apprendrez à encapsuler des requêtes SQL dans des entités réutilisables pour simplifier et sécuriser l'accès aux données.