12 questions d'entraînement sur les fonctions SQL

Les fonctions SQL permettent de manipuler les données, y compris les chaînes de caractères et les valeurs numériques. Cet article présente 12 questions d'entraînement sur les fonctions SQL - avec solutions et explications détaillées - qui vous aideront à consolider vos connaissances en SQL.

Pratiquer SQL est important si vous voulez améliorer vos compétences. Mais il est difficile de trouver des exercices d'entraînement SQL. Nous voulons vous aider, c'est pourquoi nous avons publié des compilations gratuites d'exercices d'entraînement SQL. Dans cet article, nous avons fourni 12 questions d'entraînement sur les fonctions SQL, avec les solutions et les explications. Si vous appréciez cet article, consultez notre piste complète de La pratique du SQL vous pouvez essayer les premiers exercices gratuitement !

Commençons. Avant de nous lancer dans les exercices sur les fonctions SQL, passons rapidement en revue les différents types de fonctions SQL et examinons notre ensemble de données.

Qu'est-ce qu'une fonction SQL ?

Une fonction SQL prend un ou plusieurs arguments en entrée, traite le(s) argument(s) et renvoie une sortie. SQL offre un ensemble de fonctions prédéfinies qui fonctionnent avec des valeurs de chaîne (par exemple UPPER(), SUBSTRING()), des valeurs numériques (par exemple FLOOR(), GREATEST()) et des valeurs NULL (par exemple ISNULL()).

Les fonctions SQL peuvent être classées en fonctions scalaires, agrégées et de fenêtre. Les fonctions scalaires prennent un seul argument et renvoient un seul résultat, par exemple LEN(nom) renvoie le nombre de caractères de chaque nom. Les fonctions d'agrégation renvoient une valeur unique pour un groupe de lignes (c'est-à-dire qu'elles renvoient une valeur agrégée). Par exemple, la fonction SUM(sales) renvoie le total de toutes les valeurs de la colonne des ventes. Les fonctions d'agrégation sont associées à GROUP BY pour calculer des statistiques.

Les fonctions de fenêtre travaillent sur des groupes de lignes (appelées fenêtres) qui peuvent changer par rapport à la ligne actuelle. Comme les fonctions d'agrégation, les fonctions de fenêtre sont appliquées à un groupe de lignes ; contrairement aux fonctions d'agrégation, elles renvoient une valeur par ligne. Pour en savoir plus sur la différence entre les fonctions agrégées et les fonctions de fenêtre, cliquez ici.

Si vous souhaitez approfondir les fonctions SQL, je vous recommande notre cours de Fonctions SQL standards cours. Ses 211 défis de codage vous donneront certainement beaucoup de pratique !

Présentation de l'ensemble de données

Faisons maintenant rapidement connaissance avec le jeu de données. Nos exercices de pratique des fonctions SQL utiliseront un exemple de jeu de données de location de DVD que nous avons importé dans une base de données PostgreSQL. Voici le schéma du jeu de données :

Et voici les tables que nous utiliserons dans nos exercices :

• La table customer stocke des informations sur les clients de location de DVD. Elle se connecte à la base de données rental et payment Chaque client peut avoir zéro ou plusieurs enregistrements dans ces tables.
• La table rental contient des informations sur chaque location effectuée par les clients. Elle est connectée à la table inventory car chaque location nécessite exactement un article d'inventaire. Il se connecte également à la table payment afin qu'un enregistrement de paiement puisse être affecté à chaque location.
• La table payment stocke les informations relatives au paiement de chaque location. Il se connecte également aux tables rental et staff pour nous permettre d'affecter une location et un membre du personnel à chaque paiement.
• La table staff contient des informations sur les membres du personnel du magasin de DVD. Elle se connecte aux tables payment et rental pour affecter un membre du personnel à chaque paiement et à chaque location.
• La table inventory stocke tous les articles disponibles dans l'inventaire du magasin de location de DVD. Elle se connecte à la table rental pour affecter un seul élément d'inventaire à chaque location. Il se connecte également à la table film pour affecter chaque film à un magasin de location.
• Le tableau film stocke les détails de tous les films disponibles à la location. Elle se connecte à la table inventory pour affecter chaque film à un magasin. Il se connecte également à la table language pour indiquer dans quelle(s) langue(s) le film est disponible. Enfin, il se connecte à la table film_actor pour affecter les acteurs aux films (et les films aux acteurs).
• Le tableau language stocke toutes les langues des films proposés par le loueur de DVD. Elle se connecte à la table film pour attribuer une langue à chaque film.
• La table film_actor stocke les relations de plusieurs à plusieurs entre les tables film et actor (parce que chaque acteur peut jouer dans plusieurs films et que chaque film peut avoir plusieurs acteurs). Elle se connecte aux tables film et actor pour affecter chaque film à tous les acteurs qui y ont joué et chaque acteur à tous les films dans lesquels il a joué.
• La table actor contient tous les acteurs qui ont joué dans les films disponibles à la location de DVD. Elle se connecte à la table film_actor qui attribue les acteurs aux films.

Maintenant que nous connaissons l'ensemble de données, commençons à pratiquer les fonctions SQL !

Pratique des fonctions SQL : Chaînes de caractères

SQL fournit de nombreuses fonctions qui vous permettent de manipuler des valeurs de chaînes de caractères. Voici quelques-unes des plus courantes :

• CONCAT() concatène deux ou plusieurs valeurs de chaîne en une seule
• LENGTH() renvoie le nombre de caractères contenus dans la valeur de la chaîne.
• LOWER() transforme la chaîne en lettres minuscules.
• REPLACE() remplace une partie de la chaîne par une autre chaîne.
• SUBSTRING() extrait une partie de la chaîne.
• UPPER() transforme la chaîne en majuscules

Lisez cet article pour découvrir toutes les fonctions de chaîne de caractères de SQL.

Exercice 1 : Mise à jour des courriels du personnel

Question : Le magasin de location de DVD Sakila a ouvert des succursales au Royaume-Uni. Mettez à jour les adresses électroniques du personnel en remplaçant le domaine actuel sakilastaff.com par sakila.uk; appelez cette colonne new_email.

Solution :

SELECT REPLACE(email, 'sakilastaff.com', 'sakila.uk')
          AS new_email
FROM staff;

Explication : Nous utilisons la fonction REPLACE() pour remplacer une partie de la chaîne par une autre chaîne.

Chaque valeur de la colonne email du tableau staff est transmise à la fonction REPLACE(), qui remplace sakilastaff.com par sakila.uk.

Exercice 2 : Liste de tous les clients

Question : Dressez la liste des noms de tous les clients actifs en utilisant le format nom de famille, première lettre du prénom. Appelez cette colonne customer_name et classez la liste par ordre alphabétique.

(Indice : les clients actifs ont un 1 dans la colonne active ).

Solution :

SELECT CONCAT(last_name, ', ', SUBSTRING(first_name, 1, 1))
          AS customer_name
FROM customer
WHERE active = 1
ORDER BY customer_name;

Explication : Nous sélectionnons le prénom et le nom de famille dans le customer tableau. Pour ne sélectionner que les clients actifs, nous définissons une clause WHERE dont la condition est que la valeur de active soit 1.

Pour renvoyer les noms des clients dans le format donné, nous utilisons la fonction CONCAT() pour concaténer toutes les parties en une seule chaîne :

• La première partie est la colonne last_name.
• La deuxième partie est une virgule.
• La troisième partie est la première lettre du prénom, qui est extraite par la fonction SUBSTRING().

Enfin, nous classons la liste par ordre alphabétique dans la colonne last_name.

Exercice 3 : Liste de films avec des descriptions courtes

Question : Affichez chaque titre de film et les 100 premiers caractères de sa description, suivis de trois points si la description est plus longue que 100 caractères. Nommez cette colonne truncated_description. Si la description comporte 100 caractères ou moins, affichez la description complète dans la même colonne.

Solution :

SELECT title,
       CASE
         WHEN LENGTH(description) <= 100 THEN description
         ELSE SUBSTRING(description, 1, 100) || '...'
       END AS truncated_description
FROM film;

Explication : Nous sélectionnons les colonnes title et description dans le tableau. film tableau.

Nous utilisons l' instruction CASE WHEN pour décider du contenu de la colonne truncated_description. Elle est équivalente à l'instruction IF…ELSE… dans d'autres langages de programmation.

• Nous utilisons la fonction LENGTH() pour obtenir le nombre de caractères présents dans la colonne description. WHEN le nombre de caractères est inférieur ou égal à 100, THEN nous affichons la description complète.
• Nous utilisons la fonction SUBSTRING() pour obtenir les 100 premiers caractères de la colonne description. Nous les concaténons avec trois points à l'aide de la fonction ||. Ce contenu est stocké dans la colonne truncated_description si la condition de la clause WHEN n'est pas remplie.

Exercice 4 : Sélectionner des films et des acteurs

Question : Affichez tous les films en utilisant le format titre du film (année de sortie) comme colonne film. Affichez également tous les acteurs assignés aux films en utilisant le format nom, prénom comme colonne actor. Les données doivent être classées par ordre chronologique de l'année de sortie, puis par ordre alphabétique du titre du film.

Solution :

SELECT f.title || ' (' || f.release_year || ')' AS film,
       a.last_name || ', ' || a.first_name AS actor
FROM film f
JOIN film_actor fa
ON f.film_id = fa.film_id
JOIN actor a
ON fa.actor_id = a.actor_id
ORDER BY f.release_year, f.title;

Explication :

Pour sélectionner les noms des acteurs et les films dans lesquels ils ont joué, nous devons joindre les colonnes actor, film, et film_actor sur leurs colonnes communes. Reportez-vous à cet exercice SQL JOIN pour en savoir plus sur la jointure de plusieurs tables.

Dans l'instruction SELECT, nous concaténons les titres des films et les années de sortie pour l'ensemble de la table film avec les noms et prénoms des acteurs de la table actor de la table.

Enfin, nous classons les données d'abord par film.release_year; pour chaque année, nous classons ensuite les données par ordre alphabétique dans la colonne title.

Pratique des fonctions SQL : Valeurs numériques

SQL fournit de nombreuses fonctions qui vous permettent de manipuler des valeurs numériques. Voici quelques-unes des fonctions les plus courantes :

• ABS() renvoie la valeur absolue de son argument.
• CEILING() retourne la valeur arrondie à l'unité supérieure.
• FLOOR() renvoie la valeur arrondie à l'unité inférieure.
• GREATEST() renvoie le plus grand nombre d'un groupe de nombres.
• LEAST() renvoie le plus petit nombre d'un groupe de nombres.
• ROUND() arrondit le nombre à un nombre défini de décimales.

Lisez cet article pour découvrir toutes les fonctions numériques de SQL.

Exercice 5 : Calculer la longueur moyenne d'une description

Question : Indiquez la longueur moyenne de toutes les descriptions de films. Nommez cette colonne average_film_desc_length. Arrondissez le résultat à l'entier le plus proche.

Solution :

SELECT 
  FLOOR(AVG(LENGTH(description)))
          AS average_film_desc_length
FROM film;

Explication :

Nous sélectionnons la colonne description dans le film dans le tableau. Nous l'intégrons dans trois fonctions pour obtenir la longueur moyenne des descriptions, arrondie à l'entier inférieur le plus proche :

• La fonction LENGTH(), avec description comme argument, obtient le nombre de caractères par description.
• Nous intégrons la sortie de LENGTH() dans la fonction AVG() pour calculer la moyenne de toutes les longueurs de description.
• Enfin, nous insérons AVG(LENGTH(description) dans la fonction FLOOR() pour arrondir la moyenne à l'entier inférieur.

Exercice 6 : Trouver les films les plus longs

Question : Indiquez le(s) titre(s) du (des) film(s) dont la durée est la plus longue.

Solution :

SELECT title
FROM film
WHERE length = (SELECT MAX(length) FROM film);

Explication :

Nous sélectionnons la colonne title dans le film de la table.

Comme nous ne voulons lister que le(s) film(s) dont la durée est la plus longue, nous définissons une condition de clause WHERE. Nous utilisons une sous-requête qui renvoie la valeur de longueur maximale trouvée dans la colonne length. La condition affirme que la valeur de la longueur des titres de films à afficher doit être égale à la valeur renvoyée par la sous-requête.

Pour vous entraîner davantage avec les sous-requêtes SQL, consultez ces exercices pratiques sur les sous-requêtes.

Exercice 7 : Lister les statistiques de location

Question : Affichez la durée moyenne de location en jours (dans la colonne avg_rental_duration_days ) et le paiement moyen par location (dans la colonne avg_payment_per_rental ). Arrondissez les deux valeurs à 2 décimales.

Solution :

SELECT 
  ROUND(AVG(EXTRACT(DAY FROM r.return_date - r.rental_date)), 2)
          AS avg_rental_duration_days,
  ROUND(AVG(p.amount), 2) 
          AS avg_payment_per_rental
FROM rental r
JOIN payment p
ON r.rental_id = p.rental_id;

Explication :

Tout d'abord, nous joignons les colonnes rental et payment sur leur colonne commune.

Pour obtenir la durée moyenne de location en jours, nous devons trouver la différence moyenne entre les colonnes rental_date et return_date du tableau rental . Pour ce faire, nous utilisons le tableau Pour ce faire, nous utilisons la fonction EXTRACT() pour obtenir le nombre de jours qu'a duré la location (sous forme de valeurs entières) ; nous utilisons ensuite AVG() pour calculer la valeur moyenne. Enfin, nous enveloppons le tout dans la fonction ROUND(), en fournissant la valeur moyenne comme premier argument et 2 comme second argument pour obtenir la valeur moyenne arrondie.

Pour obtenir le paiement moyen par location, nous utilisons à nouveau la fonction AVG() - en passant cette fois la colonne du montant du tableau payment comme argument. Comme précédemment, nous l'enveloppons dans ROUND() pour arrondir le résultat à deux décimales.

Exercice 8 : Actualiser les taux de location

Question : Sakila propose une remise spéciale sur les locations comme suit :

• 50% de réduction sur les films classés G.
• 40% de réduction sur les films PG.
• 30% de réduction sur les films PG-13.

Sélectionnez les titres des films ainsi que leurs tarifs de location actualisés (nommez cette colonne new_rental_rate).

Solution :

SELECT title,
       CASE
         WHEN rating = 'G' THEN rental_rate * 0.5
         WHEN rating = 'PG' THEN rental_rate * 0.6
         WHEN rating = 'PG-13' THEN rental_rate * 0.7
         ELSE rental_rate
       END AS new_rental_rate
FROM film;

Explication :

Nous sélectionnons la colonne title dans le tableau film et les tarifs de location actualisés en fonction de la colonne rating.

Nous utilisons l'instruction CASE WHEN pour appliquer des réductions basées sur les valeurs de la colonne rating:

• Si la classification est G, le tarif de location est réduit de 50 % (rental_rate * 0.5).
• Si la classification est PG, le tarif de location est réduit de 40 % (rental_rate * 0.6).
• Si la classification est PG-13, le tarif de location est réduit de 30 % (rental_rate * 0.7).
• Dans le cas contraire, nous renvoyons la valeur rental_rate

Pratique des fonctions SQL : NULLs

SQL fournit de nombreuses fonctions qui vous permettent de gérer les valeurs NULL. Voici quelques-unes des plus courantes :

• ISNULL() retourne vrai si son argument est une valeur NULL; sinon, il retourne
• IFNULL() renvoie une valeur déclarée si la valeur originale est NULL.
• COALESCE() renvoie la première valeur nonNULL de ses arguments.

Exercice 9 : Trouver des clients avec des locations en cours

Question : Sélectionner les noms et prénoms des clients qui ont des locations en cours.

Solution :

SELECT c.first_name, c.last_name
FROM customer AS c
JOIN rental AS r
ON c.customer_id = r.customer_id
WHERE r.return_date IS NULL;

Explication :

Nous sélectionnons les noms et prénoms des clients dans la table des clients.

Pour obtenir les clients qui ont des locations en cours, nous devons trouver des enregistrements dans la table rental où la valeur de la colonne return_date est NULL. Pour ce faire, nous joignons les colonnes customer et rental sur leur colonne commune.

Exercice 10 : Sélectionner des films et leurs langues

Question : Sélectionnez tous les titres de films contenant des informations sur la (les) langue(s) dans laquelle (lesquelles) le film est disponible. Créez une colonne (nommée film_language_info) avec des valeurs suivant ce format :

<film title> is available in <language name>. 

Si le nom de la langue est NULL, utilisez l'expression une langue inconnue à la place du nom de la langue.

Solution :

SELECT f.title || ' is available in ' || 
          COALESCE(l.name, 'an unknown language') AS film_language_info
FROM film AS f
JOIN language AS l
ON f.language_id = l.language_id;

Explication :

Nous sélectionnons la colonne title dans le tableau film et la colonne nom dans la table language dans la table. Par conséquent, nous devons joindre les colonnes film et language sur leur colonne commune.

Nous concaténons les titres des films avec la phrase "est disponible en" et le nom de la langue. Notez que lors de la sélection du nom de la langue, nous utilisons la fonction COALESCE() pour remplacer le nom de la langue par "langue inconnue" si le nom de la langue est NULL.

Exercice 11 : recherche de clients ayant récemment loué un DVD

Question : L'équipe marketing de Sakila souhaite envoyer un courrier électronique aux clients qui n'ont pas loué de DVD depuis deux semaines. Dressez la liste des adresses électroniques de tous les clients qui ont rendu leur dernière location il y a moins de deux semaines. N'incluez pas les clients qui ont des locations en cours.

Solution :

WITH filtered_customers AS (
    SELECT customer_id,
           MAX(return_date) AS most_recent_return_date
    FROM rental
    WHERE return_date IS NOT NULL
    GROUP BY customer_id
    HAVING MAX(return_date) < CURRENT_DATE - INTERVAL '2 weeks'
)

SELECT c.email
FROM customer c
JOIN filtered_customers fc
ON c.customer_id = fc.customer_id;

Explication :

Tout d'abord, nous créons une expression de table commune (CTE) pour filtrer les clients ; nous la nommons filtered_customers. Dans l'ETC, nous sélectionnons la colonne customer_id et la valeur la plus récente de la colonne return_date par client dans la table. rental Nous utilisons donc la clause GROUP BY. Nous ne sélectionnons que les clients qui n'ont pas de location en cours, c'est-à-dire ceux pour lesquels la colonne return_date est IS NOT NULL. Et nous imposons une condition sur la valeur most_recent_return_date qui doit être inférieure à deux semaines.

Ensuite, nous joignons la sortie renvoyée par cet ETC à la table customer et sélectionnons la colonne email, afin d'envoyer des courriers électroniques aux clients qui n'ont pas été actifs au cours des deux dernières semaines.

Exercice 12 : Mise à jour des dates de fin de location NULL

Question : Sélectionnez les identifiants client et les identifiants de location pour tous les clients, ainsi que les dates de début et de fin de chaque location. Si la date de fin de location est NULL, remplacez-la en ajoutant à la date de début de location autant de jours qu'indiqué dans la durée de location pour le film donné.

Solution :

SELECT r.customer_id, 
       r.rental_id, 
       r.rental_date, 
       COALESCE(r.return_date,
                r.rental_date + INTERVAL '1 day' * f.rental_duration)
          AS return_date
FROM rental AS r
JOIN inventory AS i
ON r.inventory_id = i.inventory_id
JOIN film AS f
ON i.film_id = f.film_id;

Explication :

Nous sélectionnons les colonnes customer_id, rental_id, et rental_date du tableau. rental dans la table. Ensuite, nous utilisons la fonction COALESCE(), en passant la colonne return_date comme premier argument. Si la colonne return_date est NULL, nous la remplaçons par la colonne rental_date plus autant de jours qu'indiqué dans la colonne rental_duration du tableau. film de la table.

Notez que pour faire correspondre la valeur de la colonne f.rental_duration avec celle de la colonne r.rental_id, nous devons joindre la table rental avec la table film par l'intermédiaire de la table inventory sur leurs colonnes communes.

Vous voulez plus de pratique sur les fonctions SQL ?

Cet article présente des exemples concrets d'utilisation des fonctions SQL pour obtenir des informations précieuses sur les données. Allez voir ce cours sur Fonctions SQL standards pour vous entraîner davantage.

Si vous n'êtes pas prêt à vous engager dans un cours complet, voici quelques exercices d'entraînement SQL avancés qui mettent vos connaissances SQL à l'épreuve. Vous pouvez également essayer ces exercices d'entraînement pour les fonctions de fenêtre SQL. Suivez ce guide de pratique SQL pour tirer le meilleur parti des ressources de LearnSQL.fr.

Bonne chance !