Pratique des sous-requêtes SQL : 15 exercices avec solutions

Les sous-requêtes sont souvent un défi pour les débutants. C'est en forgeant qu'on devient forgeron, alors rejoignez-nous pour faire ces 15 exercices d'entraînement aux sous-requêtes SQL !

En SQL, une sous-requête est une requête imbriquée dans une autre requête. Elle simplifie la construction de requêtes complexes pour récupérer des données qui répondent à des conditions spécifiques à partir de différentes tables.

Dans cet article, nous présentons différentes façons d'utiliser les sous-requêtes pour créer des requêtes complexes. Nous commençons par présenter les sous-requêtes SQL et les cas d'utilisation courants. Ensuite, nous vous proposons 15 exercices pratiques sur les sous-requêtes SQL, accompagnés de solutions et d'explications.

Si vous n'avez jamais entendu parler des sous-requêtes SQL, consultez notre guide gratuit pour débutants sur les sous-requêtes SQL. Vous pouvez également acquérir une expérience pratique de la construction de sous-requêtes dans notre cours de SQL pour les débutants cours.

Mais si vous connaissez les bases et que vous êtes prêt à améliorer vos connaissances, commençons à pratiquer les sous-requêtes SQL.

L'essentiel des sous-requêtes SQL

Pour résumer, une sous-requête SQL est une instruction SELECT intégrée à une autre instruction SELECT. Vous pouvez considérer les sous-requêtes comme des éléments constitutifs de requêtes complexes : elles nous permettent de décomposer des tâches compliquées en parties plus petites et de rendre le code plus facile à lire.

Imaginez que vous mettiez une question à l'intérieur d'une autre - c'est ainsi que fonctionnent les sous-requêtes. Grâce aux sous-requêtes, vous pouvez obtenir en une seule fois des informations spécifiques provenant de différentes tables et filtrées par différentes conditions.

Voici quelques cas d'utilisation courants des sous-requêtes SQL :

• Filtrage des données: Utilisez les sous-requêtes dans la clause WHERE pour filtrer les données en fonction de conditions spécifiques, ce qui rend vos requêtes plus dynamiques. Abordé dans les exercices pratiques sur les sous-requêtes 1, 2, 3, 4, 8 et 9.
• Agrégations imbriquées: Employer des sous-requêtes pour effectuer des agrégations à l'intérieur d'agrégations, permettant des calculs plus complexes. Couvert par les exercices pratiques sur les sous-requêtes 5, 6et 7.
• Vérification de l'existence: Déterminer si une valeur spécifique existe dans une autre table en utilisant des sous-requêtes avec EXISTS ou IN . Couvert par les exercices pratiques de sous-requête 1, 2 et 14.
• Sous-requêtes corrélées: Créer des sous-requêtes qui font référence à des colonnes de la requête externe, permettant un filtrage contextuel. Couvert par les exercices pratiques sur les sous-requêtes 10, 11, 12 et 13.
• Sous-requête dans la clause SELECT : Inclure une sous-requête dans la clause SELECT pour récupérer une seule valeur ou un ensemble de valeurs qui peuvent être utilisées dans la requête principale. Couvert par les exercices pratiques 10 et 13 sur les sous-requêtes.
• Sous-requête dans la clause FROM : Utiliser une sous-requête dans la clause FROM pour créer une table temporaire, permettant des jointures plus complexes. Couvert par les exercices pratiques de sous-requête 14 et 15.
  

Exercices sur les sous-requêtes SQL

Ensemble de données : Orchestres

Les exercices suivants utilisent le jeu de données orchestras qui contient trois tables.

• La table orchestras contient tous les orchestres. Les colonnes sont id, name, rating, city_origin, country_origin, et year dans lesquelles l'orchestre a été fondé.
• La table concerts contient tous les concerts donnés par les orchestres. Les colonnes sont id, city, country, year, rating, et orchestra_id (référence à la table). orchestras tableau).
• Le tableau members contient les membres (c'est-à-dire les musiciens qui jouent dans) de chaque orchestre. Les colonnes sont id, name, position (c'est-à-dire l'instrument joué), wage, experience, et orchestra_id (référence au tableau). orchestras tableau).

Maintenant que nous sommes familiarisés avec l'ensemble de données, passons aux exercices d'entraînement SQL. Les exercices suivants proviennent du cours Exercices Pratiques de SQL cours.

Exercice 1 : Sélectionner les orchestres avec une ville d'origine où un concert a eu lieu en 2013

Exercice :

Sélectionner les noms de tous les orchestres qui ont la même ville d'origine qu'une ville dans laquelle un orchestre s'est produit en 2013.

Solution :

SELECT name
FROM orchestras
WHERE city_origin IN (SELECT city FROM concerts WHERE year = 2013);

Explication de la solution :

Nous voulons sélectionner des noms d'orchestres qui remplissent une certaine condition. Nous commençons donc par SELECT name FROM orchestras. Ensuite, la condition sera imposée à la colonne city_origin, comme indiqué dans les instructions.

Nous voulons sélectionner uniquement les orchestres dont la ville d'origine appartient au groupe des villes où des concerts ont été donnés en 2013. Pour créer cette condition dans la clause WHERE, nous utilisons une sous-requête SQL.

Créons une (sous-)requête qui sélectionne toutes les villes où des concerts ont eu lieu en 2013 : SELECT city FROM concerts WHERE year = 2013. Elle renvoie une colonne contenant des noms de villes.

Pour s'assurer que la ville d'origine appartient aux villes retournées par la sous-requête, nous utilisons l'opérateur IN.

Exercice 2 : sélectionner des membres appartenant à des orchestres très appréciés

Exercice :

Sélectionner les noms et les positions (c'est-à-dire l'instrument joué) de tous les membres d'orchestre qui ont plus de 10 ans d'expérience et qui n'appartiennent pas à des orchestres dont la note est inférieure à 8,0.

Solution :

SELECT
  name,
  position
FROM members
WHERE experience > 10
AND orchestra_id NOT IN (SELECT id FROM orchestras WHERE rating < 8.0);

Explication de la solution :

Nous voulons sélectionner les noms et les postes des membres de l'orchestre qui remplissent les conditions indiquées dans les instructions. Nous commençons donc par SELECT name, position FROM members.

Ensuite, nous imposons des conditions de filtrage sur les années d'expérience des membres et les orchestres auxquels ils appartiennent. Nous voulons sélectionner les membres dont les années d'expérience sont supérieures à 10. Nous ajoutons donc la première condition de la clause WHERE: experience > 10.

Nous ne voulons pas sélectionner les membres qui appartiennent à des orchestres dont les notes sont inférieures à 8.0. Créons une (sous-)requête qui sélectionne tous les orchestres dont la note est inférieure à 8,0 : SELECT id FROM orchestras WHERE rating < 8.0.

Pour s'assurer que les orchestres n'appartiennent pas aux orchestres listés par cette sous-requête, nous utilisons l'opérateur NOT IN.

Exercice 3 : sélectionner les membres qui gagnent plus que les violonistes

Exercice :

Indiquez le nom et la position des membres de l'orchestre qui gagnent plus que le salaire moyen de tous les violonistes.

Solution :

SELECT name, position
FROM members
WHERE wage > (SELECT AVG(wage)
              FROM members
              WHERE position = 'violin');

Explication de la solution :

Nous sélectionnons les colonnes nom et position du tableau. members table.

Nous utilisons une sous-requête pour trouver le salaire moyen de tous les violonistes : SELECT AVG(wage) FROM members WHERE position = 'violin'.

Pour nous assurer que nous sélectionnons les membres de l'orchestre dont le salaire est supérieur au salaire moyen de tous les violonistes, nous imposons une condition à la colonne salaire, qui doit être supérieure à la valeur moyenne renvoyée par la sous-requête.

Exercice 4 : sélection d'orchestres de haut niveau plus récents que l'orchestre de chambre

Exercice :

Afficher les noms des orchestres qui ont été créés après l'"Orchestre de chambre" et qui ont une note supérieure à 7,5.

Solution :

SELECT name
FROM orchestras 
WHERE year > (SELECT year FROM orchestras 
              WHERE name = 'Chamber Orchestra') 
AND rating > 7.5;

Explication de la solution :

Tout d'abord, nous sélectionnons les noms dans le tableau orchestras table.

Ensuite, nous créons une sous-requête qui renvoie l'année de création de l'orchestre de chambre.

Comme nous voulons dresser la liste des orchestres créés après l'Orchestre de chambre, nous imposons une condition à la colonne année, qui doit être supérieure à l'année renvoyée par cette sous-requête.

Enfin, nous définissons une condition sur la colonne rating qui doit être supérieure à 7,5.

Exercice 5 : Sélectionner les musiciens des grands orchestres

Exercice :

Indiquez le nom et le nombre de membres de chaque orchestre dont le nombre de membres est supérieur à la moyenne de tous les orchestres du tableau.

Solution :

SELECT 
  o.name,
  COUNT(m.id)
FROM orchestras o
JOIN members m
ON o.id = m.orchestra_id
GROUP BY o.name
HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d);

Explication de la solution :

Pour afficher le nom de l'orchestre, il suffit de sélectionner la colonne nom dans le tableau. orchestras tableau. Et pour afficher le nombre de membres de chaque orchestre, nous devons joindre le tableau orchestras avec la table members sur leur colonne commune (l'ID de l'orchestre). Ensuite, nous utilisons la fonction COUNT() pour compter tous les membres (COUNT(m.id)), en les regroupant par la colonne du nom dans le tableau ( ). orchestras table (GROUP BY o.name).

Nous voulons choisir uniquement les orchestres qui ont un nombre de membres supérieur à la moyenne. Par conséquent, nous devons imposer une condition à COUNT(m.id) pour qu'il soit supérieur au nombre moyen de membres. Pour imposer une condition à une fonction agrégée, nous devons utiliser la clause HAVING qui suit la clause GROUP BY.

Nous pouvons trouver le nombre moyen de membres de l'orchestre en utilisant des sous-requêtes. Pour être exact, nous utilisons une sous-requête imbriquée - une sous-requête dans une sous-requête.

• La sous-requête interne trouve le nombre de membres de chaque orchestre à l'aide de la fonction d'agrégation COUNT():

SELECT orchestra_id, COUNT(id) FROM members GROUP BY orchestra_id

• La sous-requête externe calcule la moyenne de toutes les valeurs COUNT(id) renvoyées par la sous-requête interne à l'aide de la fonction d'agrégation AVG() :

SELECT AVG(d.count) FROM (<inner subquery>) AS d

Enfin, la sous-requête totale est :

(SELECT AVG(d.count) 
 FROM (SELECT orchestra_id, COUNT(id) 
       FROM members GROUP BY orchestra_id) AS d)

Elle renvoie le nombre moyen de membres par orchestre.

Maintenant que nous disposons du nombre moyen de membres d'un orchestre, nous pouvons imposer une condition à COUNT(m.id) pour nous assurer qu'il est supérieur au nombre moyen de membres d'un orchestre :

HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d)

Ensemble de données : Université

Les exercices suivants utilisent le jeu de données Université, qui contient six tables.

• La table course contient des informations sur les cours. Les colonnes sont id, title, learning_path, short_description, lecture_hours, tutorial_hours, ects_points, has_exam, et has_project.
• La table lecturer contient des informations sur les conférenciers. Les colonnes sont id, first_name, last_name, degree, et email.
• La table student contient des informations sur les étudiants. Les colonnes sont id, first_name, last_name, email, birth_date, et start_date.
• La table academic_semester contient des informations sur chaque semestre d'études. Les colonnes sont id, calendar_year, term, start_date, et end_date.
• Le tableau course_edition contient des informations sur les professeurs qui enseignent chaque cours dans chaque semestre. Les colonnes sont id, course_id (référence au tableau), (référence au tableau), (référence au tableau), (référence au tableau). course ), academic_semester_id (référence au tableau academic_semester tableau), et lecturer_id (référence au lecturer tableau).
• Le tableau course_enrollment contient des informations sur les étudiants inscrits à chaque cours. Les colonnes sont course_edition_id (fait référence au tableau), (fait référence au tableau) et (fait référence au tableau). course_edition table), student_id (fait référence à la table student table), midterm_grade, final_grade, course_letter_grade, et passed.

Maintenant que nous sommes familiarisés avec l'ensemble de données, passons aux exercices d'entraînement SQL. Les exercices suivants sont tirés du cours de base de l'Université La pratique du SQL .

Exercice 6 : Sélectionner les cours du semestre de printemps

Exercice :

Afficher les ID et les titres de tous les cours qui ont eu lieu au cours d'un trimestre de printemps.

Solution :

SELECT
  id,
  title
FROM course
WHERE id = ANY (SELECT course_id
                FROM course_edition ce
                JOIN academic_semester asem
                ON ce.academic_semester_id = asem.id
                WHERE asem.term = 'spring');

Explication de la solution :

Nous commençons par sélectionner les ID et les titres dans le tableau course dans la table. Dans la clause WHERE, nous devons imposer une condition à la colonne id du tableau, en utilisant des sous-requêtes et l'opérateur . course en utilisant des sous-requêtes et l'opérateur ANY.

Nous voulons sélectionner les cours qui ont eu lieu au moins une fois au cours du trimestre de printemps, alors commençons par créer une sous-requête qui sélectionne ces ID de cours. Notez que nous devons joindre la table course_edition et la table academic_semester sur leur colonne commune (academic_semester_id et id, respectivement) pour pouvoir filtrer les cours du trimestre de printemps.

L'opérateur ANY renvoie un résultat positif si au moins une valeur renvoyée par la sous-requête remplit la condition.

Illustrons-le :

La ligne en vert renvoie un résultat positif parce que 9 est égal à l'un des nombres renvoyés par la sous-requête.

La ligne en rouge renvoie un résultat faux car 3 n'est égal à aucun des nombres renvoyés par la sous-requête.

Exercice 7 : Sélectionner tous les étudiants qui ont réussi au moins un cours

Exercice :

Sélectionner les identifiants et les noms des étudiants qui ont réussi au moins un cours.

Solution :

SELECT
  id,
  first_name,
  last_name
FROM student
WHERE id = ANY (SELECT student_id
                FROM course_enrollment
                WHERE passed = 't');

Explication de la solution :

Nous commençons par sélectionner les identifiants et les noms dans la table student table. Dans la clause WHERE, nous devons imposer une condition à la colonne id du tableau à l'aide de sous-requêtes et de l'opérateur . student à l'aide de sous-requêtes et de l'opérateur ANY.

Nous voulons sélectionner les étudiants qui ont réussi au moins un cours, alors commençons par créer une sous-requête qui sélectionne les ID de tous les étudiants qui ont réussi un ou plusieurs cours : SELECT student_id FROM course_enrollment WHERE passed = 't'

L'opérateur ANY renvoie un résultat positif si au moins une valeur renvoyée par la sous-requête remplit la condition.

Illustrons-le :

Les lignes en vert renvoient un résultat positif parce que 5 et 8 appartiennent aux ID renvoyés par la sous-requête.

La ligne en rouge renvoie un résultat faux car 3 n'appartient pas aux ID renvoyés par la sous-requête.

Exercice 8 : Sélectionner le(s) professeur(s) qui donne(nt) le moins de cours

Exercice :

Trouvez le(s) professeur(s) qui donne(nt) le moins de cours. Affichez le nom et le prénom du professeur ainsi que le nombre de cours qu'il dispense (sous la forme no_of_courses).

Solution :

SELECT
  l.first_name,
  l.last_name,
  COUNT(ce.id) AS no_of_courses
FROM lecturer l
JOIN course_edition ce
ON l.id = ce.lecturer_id
GROUP BY l.first_name, l.last_name
HAVING COUNT(ce.id) 
            <= ALL (SELECT COUNT(id)
                    FROM course_edition
                    GROUP BY lecturer_id);

Explication de la solution :

Nous sélectionnons les noms dans le tableau lecturer en l'associant à la table course_edition sur l'ID de l'enseignant. Nous comptons les lignes du tableau course_edition pour chaque conférencier : COUNT(ce.id) AS no_of_courses. Nous procédons ainsi à un regroupement par noms de conférenciers.

Pour nous assurer que nous ne sélectionnons que le(s) professeur(s) ayant dispensé le moins de cours, nous devons imposer une condition à COUNT(ce.id), qui doit être inférieure ou égale au nombre de cours dispensés pour chaque professeur.

Créons une sous-requête qui sélectionne le nombre de cours enseignés pour chaque enseignant : SELECT COUNT(id) FROM course_edition GROUP BY lecturer_id.

L'opérateur ALL renvoie un résultat positif si la condition est remplie par toutes les lignes renvoyées par la sous-requête. Ici, nous voulons nous assurer que nous ne sélectionnons que le(s) professeur(s) dont le no_of_courses est inférieur à tous les autres professeurs (et seulement égal au plus petit nombre de cours enseignés, d'où le signe plus petit/égal).

Illustrons cela :

La ligne en vert retourne vrai parce que 4 est inférieur à chaque nombre retourné par la sous-requête et égal seulement au plus petit nombre retourné par la sous-requête.

La ligne rouge renvoie un résultat faux car 8 n'est pas plus petit que tous les nombres renvoyés par la sous-requête (c'est-à-dire 8 > 4, 8 > 5, 8 > 6).

Exercice 9 : sélectionner les étudiants inscrits au plus grand nombre de cours

Exercice :

Trouvez le(s) étudiant(s) inscrit(s) au plus grand nombre d'éditions de cours. Affichez l'identifiant de l'étudiant, son nom et son prénom, ainsi que le nombre d'éditions de cours auxquelles il a été inscrit (sous la forme no_of_course_ed).

Solution :

SELECT
  student_id,
  first_name,
  last_name,
  COUNT(course_edition_id) AS no_of_course_ed
FROM course_enrollment
JOIN student
ON course_enrollment.student_id = student.id
GROUP BY student_id, first_name, last_name
HAVING COUNT(course_edition_id)
            >= ALL (SELECT COUNT(course_edition_id)
                    FROM course_enrollment
                    GROUP BY student_id);

Explication de la solution :

Nous sélectionnons les identifiants et les noms dans la table student et nous joignons student avec la table course_edition sur leur colonne commune (identifiant de l'étudiant). Nous comptons les lignes du tableau course_edition pour chaque étudiant (COUNT(course_edition_id) AS no_of_course_ed). Nous regroupons donc les étudiants en fonction de leur numéro d'identification et de leur nom.

Pour nous assurer que nous ne sélectionnons que les étudiants ayant le plus grand nombre de cours inscrits, nous devons imposer une condition à COUNT(course_edition_id), qui doit être supérieure ou égale au nombre de cours inscrits pour chaque étudiant.

Créons une sous-requête qui sélectionne le nombre de cours inscrits pour chaque étudiant : SELECT COUNT(course_edition_id) FROM course_enrollment GROUP BY student_id

L'opérateur ALL renvoie un résultat vrai si la condition est remplie par toutes les lignes renvoyées par la sous-requête. Ici, nous voulons nous assurer que nous ne sélectionnons que les étudiants dont le site no_of_course_ed est supérieur à celui de n'importe quel autre étudiant (et seulement égal au plus grand nombre de cours inscrits - d'où le signe supérieur/égal).

Illustrons cela :

La ligne en vert renvoie un résultat vrai parce que 9 est plus grand que chaque nombre renvoyé par la sous-requête et égal uniquement au plus grand nombre renvoyé par la sous-requête.

La ligne en rouge renvoie un résultat faux car 6 n'est pas plus grand que tous les nombres renvoyés par la sous-requête (c'est-à-dire 6 < 8 et 6 < 9).

Ensemble de données : Magasin

Les exercices suivants utilisent la base de données store qui contient six tables :

• Le tableau customer contient des informations sur Les colonnes sont customer_id, contact_name, company_name, contact_email, address, city, et country.
• La table product contient des informations sur les produits. Les colonnes sont product_id, product_name, category_id (référence à la table), , , , et . category table), quantity_per_unit, unit_price, units_in_stock, et discontinued.
• La table category contient des informations sur les catégories de produits. Les colonnes sont category_id, name, description, et parent_category_id (fait référence à la table).
• La table purchase contient des informations sur les achats effectués par les clients. Les colonnes sont purchase_id, customer_id (fait référence à la table), (fait référence à la table), (fait référence à la table). customer ), employee_id (fait référence à la table employee table), total_price, purchase_date, shipped_date, ship_address, ship_city, et ship_country.
• Le tableau purchase_item associe tous les achats aux produits. Les colonnes sont purchase_id (référence à la table), (référence à la table). purchase table), product_id (fait référence à la table product table), unit_price, et quantity.
• La table employee stocke des informations sur les employés. Les colonnes sont employee_id, last_name, first_name, birth_date, address, city, country, et reports_to.

Maintenant que nous sommes familiarisés avec l'ensemble de données, passons aux exercices d'entraînement SQL. Les exercices suivants sont tirés du cours Basic La pratique du SQL Store.

Exercice 10 : Calculer le pourcentage dépensé par le client pour chaque achat

Exercice :

Pour chaque client ayant effectué au moins un achat, affichez l'identifiant de chaque achat effectué par ce client et le pourcentage de l'argent dépensé pour cet achat par rapport à l'ensemble de l'argent dépensé par ce client. Arrondissez les pourcentages à des nombres entiers. Affichez trois colonnes : contact_name, purchase_id, et percentage.

Solution :

SELECT
  contact_name,
  purchase_id,
  ROUND(total_price * 100.0 
        / (SELECT SUM(total_price)
           FROM purchase
           WHERE customer_id = p.customer_id)) AS percentage
FROM purchase p
JOIN customer c
ON p.customer_id = c.customer_id;

Explication de la solution :

Pour identifier les clients qui ont effectué au moins un achat, nous devons joindre le tableau purchase avec la table customer sur leur colonne commune (identifiant du client).

Pour calculer le pourcentage d'argent dépensé pour un achat par rapport à l'ensemble de l'argent dépensé par ce client, nous avons besoin de sous-requêtes. La sous-requête calcule le montant dépensé par un client pour tous ses achats : SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id

Notez que la sous-requête fait référence à la table purchase de la requête externe (alias p) pour obtenir l'identifiant correct du client. C'est ce qu'on appelle une sous-requête corrélée.

Enfin, nous calculons la valeur en pourcentage en divisant le site total_price par la valeur renvoyée par la sous-requête. En outre, nous devons multiplier cette valeur par 100 pour obtenir le pourcentage et ROUND() pour le convertir en un nombre entier.

Pour en savoir plus sur les sous-requêtes corrélées, lisez Apprendre à écrire une sous-requête corrélée en 5 minutes.

Exercice 11 : Trouver le nombre de produits chers dans chaque catégorie

Exercice :

Indiquez les noms des catégories et le nombre de produits de cette catégorie dont le prix unitaire est supérieur au prix moyen d'un produit de cette catégorie. Ne montrez que les catégories qui ont ce(s) produit(s). Affichez deux colonnes : name (le nom de la catégorie), et expensive_products (le nombre de produits dont le prix est supérieur à la moyenne des produits de cette catégorie).

La solution :

SELECT
  c.name,
  COUNT(*) AS expensive_products
FROM category AS c
JOIN product AS p
ON c.category_id = p.category_id
WHERE p.unit_price > (SELECT AVG(unit_price)
                      FROM product
                      JOIN category
                      ON product.category_id = category.category_id
                      WHERE category.category_id = c.category_id)
GROUP BY c.name;

Explication de la solution :

Nous voulons afficher les noms des catégories et le nombre de produits ; nous devons donc joindre la table category et la table product sur leur colonne commune (ID de la catégorie).

Pour afficher le nombre de produits par catégorie, nous utilisons la fonction COUNT(). Comme nous sélectionnons le nom de la catégorie (c.name) et le nombre de produits par nom de catégorie (COUNT(*)), nous devons grouper par la colonne du nom de la catégorie (GROUP BY c.name).

Dans la fonction COUNT(), nous voulons inclure uniquement les produits dont le prix unitaire est supérieur au prix moyen d'un produit de cette catégorie. Pour ce faire, nous utilisons une sous-requête corrélée.

Dans la sous-requête, nous joignons les colonnes product et category et sélectionnons la valeur moyenne des prix unitaires. Pour garantir que nous prenons la moyenne des valeurs de l'ID de catégorie spécifique, nous imposons une condition dans la clause WHERE stipulant que la sous-requête category_id doit être égale à la requête externe category_id.

La condition de la clause WHERE de la requête principale stipule que unit_price doit être supérieur à la moyenne de unit_price pour cette catégorie, telle que renvoyée par la sous-requête.

Exercice 12 : Afficher les produits achetés avec leur quantité maximale achetée

Exercice :

Pour chaque produit acheté, affichez son nom, la plus grande quantité dans laquelle il a été acheté et le nombre de quantités maximales achetées pour ce produit. Affichez trois colonnes : product_name, quantity, et purchases_number.

Solution :

SELECT 
  product_name,
  quantity,
  COUNT(purchase_id) AS purchases_number
FROM purchase_item pi
JOIN product p
ON pi.product_id = p.product_id 
WHERE quantity = (SELECT MAX(quantity) 
                  FROM purchase_item 
                  WHERE product_id = pi.product_id)
GROUP BY pi.product_id, product_name, quantity;

Explication de la solution :

Pour obtenir des informations sur les produits et les quantités dans lesquelles ils ont été achetés, nous devons joindre la table purchase_item avec la table product sur leur colonne commune (ID du produit).

Nous utilisons la fonction d'agrégation COUNT() pour obtenir le nombre d'achats (COUNT(purchase_id)).

Pour nous assurer que nous ne sélectionnons que la plus grande quantité achetée d'un produit particulier, nous devons créer une sous-requête. La sous-requête fait référence à la valeur product_id de la requête externe pour s'assurer que nous choisissons la quantité maximale pour le bon produit - il s'agit donc d'une sous-requête corrélée.

Dans la clause WHERE de la requête principale, nous imposons une condition selon laquelle la valeur de la quantité doit être égale à la valeur renvoyée par la sous-requête.

Exercice 13 : dresser la liste des produits abandonnés, des produits en continuation et des produits totaux dans chaque catégorie

Exercice :

Pour chaque catégorie, afficher :

• Son nom.
• Le nombre de produits abandonnés (c'est-à-dire qui ne sont plus disponibles) dans cette catégorie (nommez cette colonne discontinued_products).
• Le nombre de produits maintenus (c'est-à-dire actuellement disponibles) dans cette catégorie (nommez cette colonne continued_products).
• Le nombre de tous les produits de cette catégorie (nom de la colonne all_products).

Solution :

SELECT
  c.name,
  (SELECT COUNT(*) FROM product 
    WHERE category_id = c.category_id AND discontinued IS TRUE) 
    AS discontinued_products,
  (SELECT COUNT(*) FROM product 
    WHERE category_id = c.category_id AND discontinued IS FALSE) 
    AS continued_products,
  (SELECT COUNT(*) FROM product 
    WHERE category_id = c.category_id) 
    AS all_products
FROM category c;

Explication de la solution :

Dans cet exercice, nous n'utilisons pas une ou deux mais trois sous-requêtes corrélées.

Nous sélectionnons le nom de la catégorie dans le tableau category de la table.

La première sous-requête corrélée compte tous les produits qui ont été discontinued. Cette sous-requête se réfère à la valeur category_id de la requête externe pour s'assurer que les produits abandonnés sont comptés par catégorie.

La deuxième sous-requête corrélée compte tous les produits qui n'ont pas été abandonnés. Cette sous-requête se réfère à la valeur category_id de la requête externe pour s'assurer que les produits maintenus sont comptés par catégorie.

La troisième sous-requête corrélée compte tous les produits par catégorie. Cette sous-requête fait référence à la valeur category_id de la requête externe pour s'assurer que tous les produits sont comptés par catégorie.

Exercice 14 : compter les achats effectués par chaque employé à Houston

Exercice :

Affichez l'identifiant de l'employé et le nombre total d'achats traités par cet employé. Utilisez une sous-requête pour obtenir des informations sur le nombre de commandes que chaque employé a traitées par client et faites en sorte que la requête principale sélectionne FROM cette sous-requête. Ne considérez que les employés qui habitent à Houston.

Solution :

SELECT
  employee_per_customer.employee_id,
  SUM(employee_per_customer.no_of_purchases) AS total_no_of_purchases
FROM (SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id
     ) AS employee_per_customer
GROUP BY employee_per_customer.employee_id;

Explication de la solution :

Commençons par nous assurer que nous ne prenons en compte que les employés qui vivent à Houston. Pour ce faire, nous utilisons le mot-clé EXISTS. Il renvoie un résultat vrai si la sous-requête renvoie au moins une ligne.

Notez que la sous-requête transmise au mot-clé EXISTS est une sous-requête corrélée, car elle fait référence à la valeur employee_id de sa requête externe (qui est une sous-requête de la requête principale).

Analysons la sous-requête transmise à la requête principale dans la clause FROM. Elle sélectionne les identifiants des employés et des clients et compte le nombre d'achats effectués par employé et par client (d'où le regroupement par valeurs d'identifiants d'employés et de clients).

      SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id

La partie en rouge garantit que nous ne prenons en compte que les employés qui vivent à Houston.

Cette sous-requête est appelée AS employee_per_customer et la requête principale la sélectionne.

La requête principale sélectionne les éléments suivants :

• Les identifiants des employés de la sous-requête (à partir de employee_per_customer),
• Le nombre total d'achats effectués par chaque employé. Pour ce faire, il faut regrouper les données par numéro d'identification de l'employé (GROUP BY employee_per_customer.employee_id).

Notez que la sous-requête corrélée utilise COUNT() pour compter les achats (ou lignes) par employé et par client. Mais la requête principale utilise la fonction SUM() pour additionner toutes les valeurs renvoyées par COUNT() dans la sous-requête.

Pour en savoir plus sur les fonctions d'agrégation, cliquez ici. Et consultez cet article sur l'utilisation de SUM() avec OVER(PARTITION BY) pour obtenir plus de détails sur les fonctions de fenêtre.

Cet exercice a présenté l'idée d'utiliser les sous-requêtes comme des blocs de construction - ici, nous avons utilisé trois blocs de construction pour récupérer les données souhaitées.

Exercice 15 : Trouver le plus grand nombre de catégories de produits dans un achat

Exercice :

Utilisez une sous-requête pour sélectionner l'identifiant de l'achat et le nombre de catégories distinctes contenues dans cet achat. Dans la requête principale, sélectionnez le nombre maximum de catégories à partir de cette sous-requête.

Solution :

SELECT MAX(categories_per_purchase.category_count) 
         AS max_categories_per_purchase
FROM (SELECT 
        purchase_id, 
        COUNT(DISTINCT category_id) AS category_count
      FROM purchase_item pi
      JOIN product p
      ON pi.product_id = p.product_id
      GROUP BY purchase_id) AS categories_per_purchase;

Explication de la solution :

Commençons par la sous-requête qui est passée dans la clause FROM de la requête principale.

Nous joignons la table purchase_item et la table product sur leur colonne commune (ID du produit). Nous sélectionnons l'ID de l'achat et les ID de la catégorie COUNT DISTINCT par achat. Par conséquent, nous regroupons les données en fonction de la colonne purchase_id

La requête principale utilise la fonction MAX() pour sélectionner (à partir de la sous-requête aliasée AS categories_per_purchase) le nombre maximal de catégories de produits contenues dans un achat.

Plus de pratique sur les sous-requêtes SQL

Cet article a présenté différents cas d'utilisation des sous-requêtes, notamment le filtrage avancé des données ou l'imbrication de requêtes les unes dans les autres. Une idée de base sur la manière d'utiliser les sous-requêtes est de décomposer une question en (sous-)questions plus petites - chaque petite (sous-)question doit pouvoir être résolue à l'aide d'une sous-requête.

N'hésitez pas à vous entraîner - c'est la meilleure façon d'apprendre les techniques d'utilisation des sous-requêtes pour simplifier les tâches d'extraction de données complexes. Consultez d 'autres exercices sur les sous-requêtes ici et ici.

Nous vous encourageons à vous plonger dans nos cours de SQL et à vous entraîner. Chacun de ces cours comporte une section distincte sur les sous-requêtes et des sections distinctes sur d'autres sujets SQL difficiles : les requêtes de table unique, les JOIN, le regroupement, etc.

1. Exercices Pratiques de SQL
2. Base La pratique du SQL: Un magasin
3. Base La pratique du SQL: Université
4. Base La pratique du SQL: Données sur le trafic des blogs
5. Basique La pratique du SQL: Suivi des requêtes

Inscrivez-vous maintenant et commencez gratuitement. Bonne chance !