Code SQL copié dans le presse-papiers

SQLtest.online
  1. 1.  Introduction aux bases de données
  2. 2.  Types de bases de données
  3. 3.  Concepts des bases relationnelles
  4. 4.  Types de données de base
  5. 5.  Comprendre les valeurs NULL en SQL
  6. 6.  Aperçu du SQL
  1. 1.  Sélectionner des données d'une table
  2. 2.  Filtrage des données
  3. 3.  Combiner plusieurs conditions
  4. 4.  Alias de colonnes
  5. 5.  Trier les résultats
  6. 6.  Limiter les résultats avec LIMIT et OFFSET
  7. 7.  Tout combiner : WHERE, ORDER BY et LIMIT
  1. 1.  Fonctions SQL intégrées
  2. 2.  Fonctions de chaîne courantes
  3. 3.  Fonctions mathématiques courantes
  4. 4.  Fonctions de date et d’heure
  5. 5.  Opérateur conditionnel
  1. 1.  Fonctions d'agrégation de base
  2. 2.  Regrouper les données
  3. 3.  Filtrer les données groupées
  4. 4.  Agrégation conditionnelle
  5. 5.  Agrégation avancée
  1. 1.  Fondamentaux des JOINs en SQL
  2. 2.  INNER JOIN — Combiner les lignes correspondantes
  3. 3.  LEFT JOIN — Inclure toutes les lignes de la table de gauche
  4. 4.  RIGHT JOIN — Inclure tous les enregistrements de la table de droite
  5. 5.  FULL OUTER JOIN — Tout combiner des deux tables
  6. 6.  CROSS JOIN — Le produit cartésien
  7. 7.  SELF JOIN - Joindre une table à elle-même
  8. 8.  Scénarios et techniques pratiques de JOIN
  9. 9.  Algorithmes de jointure
  1. 1.  Introduction aux sous-requêtes - Requêtes imbriquées et vues en ligne
  2. 2.  Sous-requêtes dans la clause WHERE
  3. 3.  Sous-requêtes corrélées
  4. 4.  Expressions de Table Commune (CTE)
  5. 5.  CTE Récursives
  6. 6.  Application des CTE récursifs
  1. 1.  Fonctions de fenêtre
  2. 2.  Utiliser ROW_NUMBER, RANK, DENSE_RANK et NTILE
  3. 3.  Fenêtres de calcul — Contrôler les limites de la fenêtre
  1. 1.  L'instruction INSERT INTO
  2. 2.  L'instruction UPDATE
  3. 3.  L'instruction DELETE
  1. 1.  L’instruction CREATE TABLE
  2. 2.  Les instructions TRUNCATE et DROP TABLE
  3. 3.  Tables temporaires
  4. 4.  Vues (VIEW)
  1. 1.  Bonnes pratiques pour un code SQL lisible et maintenable
  2. 2.  Ecriture de requetes SQL efficaces
  3. 3.  Comprendre les methodes d'optimisation des requetes
  1. 1.  L'option de vol la plus rapide
  2. 2.  Calculer le taux d'occupation moyen des vols
  3. 3.  Carte des sièges d'avion

Explication de la Carte des sièges d'avion

Objectif principal de la tâche

L'objectif est de créer une carte visuelle des sièges pour un modèle d'avion spécifique (ici, le 'Boeing 777-300'). Le résultat doit être une liste de rangées, où pour chaque rangée et classe de service ('Business', 'Économie', 'Confort'), un tableau des sièges disponibles est affiché.

Par exemple, pour la 1ère rangée en classe business :

  • Rangée : 1
  • Sièges : ["A", "C", "D", "F"]
  • Classe : Business

Logique de la solution (étape par étape)

  1. Étape 1 : Sélection du bon avion.

    • Filtrez les données pour ne travailler qu'avec les sièges du modèle 'Boeing 777-300'.
    • Pour cela, faites une jointure entre la table seats et aircrafts_data sur le champ commun aircraft_code.
    • Ajoutez dans la clause WHERE le modèle d'avion souhaité.

  2. Étape 2 : Séparer le numéro de siège en rangée et lettre.

    • Le numéro de siège dans la table seats est du type 12A, 30K, où 12 est la rangée et A la lettre du siège.
    • La rangée s'obtient en retirant la partie lettre du champ seat_no (par exemple avec la fonction rtrim ou des expressions régulières).
    • La lettre du siège s'obtient en prenant le dernier caractère de seat_no (par exemple avec la fonction right).

  3. Étape 3 : Regrouper les sièges.

    • L'idée est de regrouper tous les sièges qui sont dans la même rangée et la même classe de service (fare_conditions).
    • La clause GROUP BY doit donc inclure la rangée calculée et la classe de service.

  4. Étape 4 : Agréger les lettres de sièges dans un tableau.

    • Pour chaque groupe (ex : "rangée 10, classe économie"), il faut rassembler toutes les lettres de sièges (A, B, C, etc.) dans une liste ou un tableau.
    • La fonction d'agrégation json_agg (PostgreSQL) est idéale, elle crée un tableau JSON à partir des valeurs.
    • Pour garantir l'ordre des sièges (A, B, C, pas C, A, B), il faut ajouter un tri dans la fonction json_agg.

  5. Étape 5 : Tri final.

    • Pour que la carte soit logique (rangées de l'avant vers l'arrière), le résultat doit être trié par numéro de rangée.
Spoiler : la requête SQL de la solution est cachée ci-dessous. Cliquez pour révéler. 

        select 
            rtrim(seat_no, 'ABCDEFGHKJ')::int "row", 
            json_agg(right(seat_no, 1) order by right(seat_no, 1)) seats , 
            fare_conditions 
        from seats
        join aircrafts_data using(aircraft_code)
        where aircrafts_data.model->>'fr' = 'Boeing 777-300'
        group by rtrim(seat_no, 'ABCDEFGHKJ'), fare_conditions
        order by "row";