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

Analyse de la tâche SQL : Trouver des vols avec une escale

Cette tâche illustre l'utilisation d'un des outils les plus puissants en SQL pour analyser les relations dans les données : joindre une table à elle-même (SELF JOIN). Décomposons la logique de la solution étape par étape.

Description de la tâche

Nous devons trouver toutes les options de vol possibles de l'aéroport de Pulkovo (LED) à Bryansk (BZK) avec exactement une escale.

Idée clé

Un trajet avec une escale consiste en deux vols indépendants reliés par un aéroport intermédiaire.

  • Segment 1 : Départ de LED vers un aéroport d'escale C.
  • Segment 2 : Départ du même aéroport C et arrivée à BZK.

Notre objectif est de trouver toutes les paires de vols où l'aéroport d'arrivée du premier vol correspond à l'aéroport de départ du second.

Approche technique : SELF JOIN

Pour implémenter cette logique, nous considérons la table des vols comme deux tables différentes, en utilisant des alias comme first_leg (premier segment) et second_leg (second segment).

Plan de solution étape par étape

  1. Définir le premier segment du trajet. On utilise l'alias first_leg pour trouver tous les vols partant de LED.
    • Condition : first_leg.departure_airport = 'LED'.
  2. Définir le second segment du trajet. Avec l'alias second_leg, on cherche tous les vols arrivant à BZK.
    • Condition : second_leg.arrival_airport = 'BZK'.
  3. Trouver le point de connexion (condition de JOIN clé). On relie les deux ensembles de vols sur la condition que l'aéroport d'arrivée du premier segment doit correspondre à l'aéroport de départ du second.
    • Condition de connexion : first_leg.arrival_airport = second_leg.departure_airport.
    • Cet aéroport commun est notre escale (connection_airport).
  4. Vérifier le temps d'escale : Il faut aussi s'assurer que le second vol décolle après l'arrivée du premier. Condition :
    • second_leg.scheduled_departure > first_leg.scheduled_arrival
  5. Former le résultat. Après la jointure, toutes les informations nécessaires sont dans une ligne. On peut sélectionner :
    • Aéroport de départ (first_leg.departure_airport)
    • Aéroport d'escale (first_leg.arrival_airport ou second_leg.departure_airport)
    • Aéroport d'arrivée (second_leg.arrival_airport)
  6. Tri. Enfin, il suffit de trier la liste par l'aéroport d'escale, comme demandé.

Ainsi, le SELF JOIN permet de "déplier" une table et de faire correspondre ses lignes entre elles pour trouver des itinéraires complexes.

Spoiler : la requête SQL avec la solution est cachée ci-dessous. Cliquez pour révéler. 

        select 
            first_leg.flight_no flight1_no,
            first_leg.departure_airport,
            first_leg.arrival_airport connection_airport,
            first_leg.scheduled_arrival - first_leg.scheduled_departure flight1_time,
            second_leg.scheduled_departure - first_leg.scheduled_arrival connection_time,
            second_leg.flight_no flight2_no,
            second_leg.arrival_airport,
            second_leg.scheduled_arrival - second_leg.scheduled_departure flight2_time,
            (second_leg.scheduled_arrival - first_leg.scheduled_departure) total_trip_time
        from flights first_leg
        join flights second_leg on first_leg.arrival_airport=second_leg.departure_airport 
            and second_leg.arrival_airport = 'BZK'
            and second_leg.scheduled_departure > first_leg.scheduled_arrival
        where first_leg.departure_airport = 'LED'
            and first_leg.scheduled_departure between '2017-08-16' and '2017-08-17'
            and second_leg.scheduled_departure between '2017-08-16' and '2017-08-17'
        order by (second_leg.scheduled_arrival - first_leg.scheduled_departure)
        limit 1;