Scribd Logo
Importer

Bienvenue sur Scribd !

  • 218 vues

    BDW1 TD2 Corrections

    Transféré par

    GVHNJY
    Ce document contient les corrections d'un TD sur les sous-requêtes SQL avec des opérateurs tels que ANY, ALL, IN et EXISTS. Il présente 11 exercices avec leurs réponses sous forme de requêtes SQL.

    Droits d'auteur :

    © All Rights Reserved
    Téléchargez comme PDF, TXT ou lisez en ligne sur Scribd

    BDW1 TD2 Corrections

    Transféré par

    GVHNJY
    218 vues5 pages
    Ce document contient les corrections d'un TD sur les sous-requêtes SQL avec des opérateurs tels que ANY, ALL, IN et EXISTS. Il présente 11 exercices avec leurs réponses sous forme de requêtes SQL.

    Description originale:

    hi

    Titre original

    BDW1-TD2-Corrections (1)

    Copyright

    © © All Rights Reserved

    Formats disponibles

    PDF, TXT ou lisez en ligne sur Scribd

    Avez-vous trouvé ce document utile ?

    Ce contenu est-il inapproprié ?

    Ce document contient les corrections d'un TD sur les sous-requêtes SQL avec des opérateurs tels que ANY, ALL, IN et EXISTS. Il présente 11 exercices avec leurs réponses sous forme de requêtes SQL.

    Droits d'auteur :

    © All Rights Reserved
    Téléchargez comme PDF, TXT ou lisez en ligne sur Scribd
    Télécharger au format pdf ou txt
    218 vues5 pages

    BDW1 TD2 Corrections

    Transféré par

    GVHNJY
    Ce document contient les corrections d'un TD sur les sous-requêtes SQL avec des opérateurs tels que ANY, ALL, IN et EXISTS. Il présente 11 exercices avec leurs réponses sous forme de requêtes SQL.

    Droits d'auteur :

    © All Rights Reserved
    Téléchargez comme PDF, TXT ou lisez en ligne sur Scribd
    Télécharger au format pdf ou txt
    Vous êtes sur la page 1sur 5

    BDW1 - TD 2

    Sous-requêtes en SQL
    UCBL - Département Informatique de Lyon 1 – 2018

    Corrections

    Ce TD se focalise tout particulièrement sur les requêtes imbriquées et tout particulièrement


    celles introduites par les opérateurs (ANY, ALL, IN, EXISTS. . .)

    Exercice 1 : Base TOURISME D’HIVER


    Soit le schéma de la base de données TOURISME D’HIVER suivante :
    STATION (numSta, nomSta, altitude, dept)
    numSta : numéro identifiant de station ; nomSta : nom de station
    altitude : altitude où se trouve la station ; dept : département
    HOTEL (numHot, nomHot, #numSta, categorie)
    numHot : numéro identifiant d’hôtel ; nomHot : nom de l’hôtel
    numSta : numéro référençant la station où se trouve l’hôtel
    categorie : catégorie de l’hôtel
    CHAMBRE (#numHot, numCh, nbLits)
    numHot : numéro référençant l’hôtel ; numch : numéro de la chambre
    nbLits : nombre de lits dans la chambre
    CLIENT (numCli, nomCli, adrCli, telCli)
    numCli : numéro identifiant du client ; nomcli : nom du client
    adrCli : adresse du client ; telCli : numéro de téléphone du client
    RESERVATION (#numcli, #numHot, #numCh, dateDeb, dateFin, nbPers)
    numCli : numéro référençant le client qui a effectué la réservation
    numHot : numéro référençant l’hôtel concerné par la réservation
    numCh : numéro référençant la chambre réservée
    dateDeb : date de début de séjour
    dateFinN : date de fin de séjour
    nbPers : nombre de personnes concernées par la réservation

    Donner les requêtes SQL permettant de répondre aux besoins suivants :

    1. Donner le nom des hôtels qui se trouvent dans un département contenant le mot ’Alpes’.
    2. Donner le nom des clients ayant effectué une réservation dans un hôtel d’une station de
    ski se trouvant à une altitude supérieure à 1500m. Vous proposerez une requête SQL ne
    contenant aucun signe ’=’.
    3. Donner le nom des clients n’ayant jamais réservé dans un hôtel se trouvant dans le
    département ’SAVOIE’.
    4. Donner le nom de la station d’altitude la plus élevée du département ’SAVOIE’.
    5. Donner le nom des clients ayant fini au moins un de leurs séjours avant le début de
    n’importe quel séjour effectué par le client nommé ’Pignon’.

    1
    6. Donner le nom des hôtels qui ont au moins une chambre de capacité strictement plus
    grande qu’une des chambres de l’hôtel ’Les Flocons’ de la station ’Chamrousse’.
    7. Donner le nom des clients qui ont occupé au moins une fois une des chambres réservées
    par le client nommé ’Leblanc’. (une version avec un IN et une version avec un ANY)
    8. Donner le nom des stations ayant au moins un hôtel nommé ’Bellevue’.
    9. Donner le nom des clients n’ayant jamais réservé d’hôtels dans une station du départe-
    ment ’ISERE’ (sans utiliser de IN).
    10. Donner le nom des clients ayant fait une réservation en 2014 (vous utiliserez une requête
    imbriquée dans le FROM pour identifier les réservations de 2014)
    11. Donner le nom des hôtels ayant hébergé au moins une fois tous les clients de l’hôtel ’Les
    3 marmottes’.

    BDW1 - Nicolas Lumineau 2 Partie SQL


    Correction du TD

    Réponse de l’exercice 1
    1. SELECT H. nomHot
    FROM H o t e l H
    WHERE H. numSta IN (SELECT S . numSta
    FROM S t a t i o n S
    WHERE S . dept LIKE ’%Alpes%’ ) ;

    2. SELECT C. nom
    FROM C l i e n t C
    WHERE C. numCli IN (SELECT R. numCli
    FROM R e s e r v a t i o n R
    WHERE R. numHot IN (SELECT H. numHot
    FROM H o t e l H
    WHERE H. numSta IN ( SELECT S . numSta
    FROM S t a t i o n S
    WHERE S . a l t i t u d e > 1500
    )
    )
    );
    La version avec les égales étant :
    SELECT C. nom
    FROM C l i e n t C JOIN R e s e r v a t i o n R ON C. numCli=R. numCli
    JOIN H o t e l H ON R. numHot = H. numHot
    JOIN S t a t i o n S ON H. numSta = S . numSta
    WHERE S . a l t i t u d e > 1 5 0 0 ;

    3. SELECT C. nomCli
    FROM CLIENT C
    WHERE C. numCli NOT IN (SELECT R. numCli
    FROM R e s e r v a t i o n R H o t e l H ON R. numHot = H. numHot
    JOIN S t a t i o n S ON H. numSta = S . numSta
    WHERE S . dept = ’SAVOIE ’ ) ;

    4. SELECT S . nomSta
    FROM S t a t i o n S
    WHERE S . dept = ’SAVOIE ’
    AND S .ALTITUDE >= ALL(SELECT S2 . a l t i t u d e
    FROM S t a t i o n S2
    WHERE S2 . dept = ’SAVOIE ’ ) ;
    −− ou −−
    SELECT S . nomSta
    FROM S t a t i o n S
    WHERE S . dept = ’SAVOIE ’
    AND S . a l t i t u d e = (SELECT max( S2 . a l t i t u d e )
    FROM S t a t i o n S2

    BDW1 - Nicolas Lumineau 3 Partie SQL


    WHERE S2 . dept = ’SAVOIE ’ ) ;

    5. SELECT C. nomCli
    FROM C l i e n t C JOIN R e s e r v a t i o n R ON C. numCli=R. numCli
    WHERE R. d a t e F i n < ALL(SELECT R2 . dateDeb
    FROM C l i e n t C2 JOIN R e s e r v a t i o n R2 ON C2 . numCli=R2 . numCli
    WHERE R2 . numCli = C2 . numCli and C2 . numCli = ’ Pignon ’ ) ;

    6. SELECT H. nomHot
    FROM Chambre CH JOIN H o t e l H ON H. numHot= CH. numHot
    WHERE CH. NBLITS > ANY(SELECT CH. n b L i t s
    FROM Chambre CH JOIN H o t e l H ON H. numHot= CH. numHot
    JOIN S t a t i o n S ON H. numSta = S . numSta
    WHERE H. nomHot= ’ Les ␣ F l o c o n s ’ AND S . nomSta= ’ Chamrousse ’ ) ;

    7. SELECT C. nomCli
    FROM C l i e n t C JOIN R e s e r v a t i o n R ON C. numCli=R. numCli
    WHERE (R. numHot , R. numCh ) IN
    (SELECT R2 . numHot , R2 . numCh
    FROM C l i e n t C2 JOIN R e s e r v a t i o n R2 ON C2 . numCli=R2 . numCli
    WHERE C2 . nom = ’ Leblanc ’ AND C2 . numCli != C. numCli ) ;

    et

    SELECT C. nomCli
    FROM C l i e n t C JOIN R e s e r v a t i o n R ON C. numCli=R. numCli
    WHERE (R. numHot , R. numCh ) =ANY
    (SELECT R2 . numHot , R2 . numCh
    FROM C l i e n t C2 JOIN R e s e r v a t i o n R2 ON C2 . numCli=R2 . numCli
    WHERE C2 . nom = ’ Leblanc ’ AND C2 . numCli != C. numCli ) ;
    NB : il n’est pas nécessaire de réécrire complètement la requête,
    il suffit juste de remplacer IN par =ANY

    8. SELECT S . nomSta
    FROM S t a t i o n S
    WHERE EXISTS(SELECT ∗
    FROM HotelL H
    WHERE H. nomHot = ’ B e l l e v u e ’ AND H. numSta = S . numSta ) ;

    9. SELECT C. nomCli
    FROM C l i e n t C
    WHERE NOT EXISTS (SELECT ∗
    FROM R e s e r v a t i o n R JOIN H o t e l H ON R. numHot = H. numHot
    JOIN S t a t i o n S ON H. numSta = S . numSta
    WHERE S . dept= ’ ISERE ’ AND R. numCli = C. numCli ) ;

    10. SELECT C. nomCli


    FROM (SELECT R. numCli as n C l i

    BDW1 - Nicolas Lumineau 4 Partie SQL


    FROM R e s e r v a t i o n R
    WHERE R. dateDeb LIKE ’%2014% ’ ) R2014 , C l i e n t C
    WHERE R2011 . n C l i = C. numCli ;

    11. SELECT H. nomHot


    FROM H o t e l H JOIN R e s e r v a t i o n R ON H. numHot= R. numHot
    WHERE NOT EXISTS
    (SELECT ∗ FROM R e s e r v a t i o n R2
    WHERE H2 . nomHot = ’ Les ␣3␣ marmottes ’
    AND R2 . numCli NOT IN (SELECT R3 . numCli FROM R e s e r v a t i o n R3
    WHERE R3 . numHot = R. numHot )
    );

    BDW1 - Nicolas Lumineau 5 Partie SQL

    Vous aimerez peut-être aussi