Exemples de requêtes SQL

La base Conférences

Exercices sur le SELECT sur la base de gestion des conférences :

  1. Afficher la liste des personnes (numéro et nom) qui ont réservé une place pour la conférence numéro 16, dans l'ordre alphabétique des noms. corrigé
  2. Afficher pour chaque conférence la liste des personnes ayant réservé dans l'ordre des numéros de conférences et dans l'ordre alphabétique pour chaque conférence. corrigé
  3. Afficher pour chaque conférence son numéro et le nom du conférencier ainsi que la liste des personnes ayant réservé. corrigé
  4. Afficher le programme de chaque salle pour le 15/11/2004. corrigé
  5. Afficher pour chaque participant la liste des conférences pour lesquelles il a réservé. corrigé
  6. Id mais on affichera également ceux n'ayant pas réservé. corrigé
  7. Un participant signale qu'il arrivera le 15/11/2004 à 11h et qu'il doit repartir le 16/11/2004 à 17h. Donnez la liste des conférences auxquelles il pourrait assister. corrigé
  8. Afficher la liste des participants parlant la même langue que monsieur DECOENINCK. corrigé
  9. Affichez la liste des salles données par leur numéro, leur emplacement et la précision petite, moyenne ou grande selon que la capacité est inférieure à 40, entre 40 et 100 ou supérieure à 100. corrigé
  10. La salle A7125 du bâtiment A ne peut être utilisée. Affichez les salles de capacité supérieure. corrigé
  11. La salle A7125 du bâtiment A ne peut être utilisée. Affichez les salles de capacité supérieure et de même niveau d'équipement. corrigé
  12. Affichez le nombre de salles et la capacité totale des salles. corrigé
  13. Affichez la capacité totale des salles de moins de 100 places. corrigé
  14. Affichez la capacité totale des salles par niveau d'équipement. corrigé
  15. Affichez pour chaque conférence le nombre de participants. corrigé
  16. Affichez la liste des conférences ayant plus de 100 participants. corrigé
  17. Lorsque la langue du participant est différente de la langue de l'orateur, il faut prévoir un casque pour la traduction. Donnez le nombre de casques à prévoir pour chacune des conférences. corrigé
  18. Affichez la liste des participants aux conférences du thème finances et des participants aux conférences données en anglais. corrigé
  19. Affichez la liste des conférences ayant le lieu le même jour et dans la même salle que la conférence 37. corrigé
  20. Affichez la liste des salles où sont données des conférences aec des participants parlant le russe. corrigé
  21. Donnez les noms des orateurs qui n'assistent à aucune conférence . corrigé
  22. Donnez les noms des participants ayant assisté à toutes les conférences. corrigé
  23. Donnez le noms de l'orateur ayant eu le plus de succès, c'est-à-dire, l'audience la plus importante à une de ses conférences. corrigé
  24. Donnez le noms de l'orateur ayant eu le plus de succès, c'est-à-dire, l'audience cumulée la plus importante à toutes ses conférences. corrigé

Corrigé des exercices sur le SELECT :

Le corrigé utilise la syntaxe SQL-89 où les jointures sont explicitées dans le WHERE. Ceci à l'exception des cas où une requête de type OUTER JOIN est utile.
  1. Afficher la liste des personnes (numéro et nom) qui ont réservé une place pour la conférence numéro 16, dans l'ordre alphabétique des noms.
    SELECT P.numparticipant, P.nom
    FROM PARTICIPANTS P, RESERVATIONS R, CONFERENCES C
    WHERE P.numparticipant = R.refparticipant
    AND R.refconférence = C.numconférence
    AND C.numconférence=16
    ORDER BY P.nom
    retour
  2. Afficher pour chaque conférence la liste des personnes ayant réservé dans l'ordre des numéros de conférences et dans l'ordre alphabétique pour chaque conférence.
    SELECT C.numconférence, P.nom || ' ' || P.prénom
    FROM PARTICIPANTS P, RESERVATIONS R, CONFERENCES C
    WHERE P.numparticipant = R.refparticipant
    AND R.refconférence = C.numconférence
    ORDER BY C.numconférence, P.nom || ' ' || P.prénom
    retour
  3. Afficher pour chaque conférence son numéro et le nom du conférencier ainsi que la liste des personnes ayant réservé. Il faut ouvrir deux fois la table des participants, une première fois pour associer l'orateur, une seconde fois pour retrouver les participants en passant par la table des réservations.
    SELECT C.numconférence, PP.nom conferencier, P.nom participant
    FROM PARTICIPANTS PP, PARTICIPANTS P, RESERVATIONS R, CONFERENCES C
    WHERE PP.numparticipant = C.reforateur
    AND P.numparticipant = R.refparticipant
    AND R.refconférence = C.numconférence
    ORDER BY C.numconférence, P.nom
    retour
  4. Afficher le programme de chaque salle pour le 15/11/2004.
    SELECT S.numsalle, C.titre, C.heuredébut, C.heuredébut+durée
    FROM SALLES S, CONFERENCES C
    WHERE S.numsalle = C.refsalle
    AND C.date = date '2004-11-15'
    ORDER BY S.numsalle, C.heuredébut
    retour
  5. Afficher pour chaque participant la liste des conférences pour lesquelles il a réservé.
    SELECT P.numparticipant, P.nom, C.numconférence, C.titre
    FROM PARTICIPANTS P, RESERVATIONS R, CONFERENCES C
    WHERE P.numparticipant = R.refparticipant
    AND R.refconférence = C.numconférence
    ORDER BY P.numparticipant
    retour
  6. Id mais on affichera également ceux n'ayant pas réservé. Faire une jointure gauche pour faire apparaître un participant n'apparaissant pas dans RESERVATIONS.
    SELECT P.numparticipant, P.nom, C.numconférence, C.titre
    FROM (PARTICIPANTS P LEFT JOIN RESERVATIONS R ON P.numparticipant = R.refparticipant)
    INNER JOIN CONFERENCES ON R.refconférence = C.numconférence
    ORDER BY P.numparticipant
    retour
  7. Un participant signale qu'il arrivera le 15/11/2004 à 11h et qu'il doit repartir le 16/11/2004 à 17h. Donnez la liste des conférences auxquelles il pourrait assister. nous supposons que le champ CONFERENCES.date est de type DATE, le champ CONFERENCES.heuredébut de type TIME et le champ CONFERENCES.duree est de type INTERVAL
    SELECT C.numconférence, C.titre, C.heuredébut, C.heuredébut+durée
    FROM CONFERENCES C
    WHERE C.date + C.heuredébut >= timestamp '2004-11-15 11:00'
    AND C.date + C.heuredébut + durée <= timestamp '2004-11-16 17:00'
    retour
  8. Afficher la liste des participants parlant la même langue que monsieur DECOENINCK. Vous avez le choix entre une auto-jointure ou une requête imbriquée.
    SELECT P.numparticipant, P.nom
    FROM PARTICIPANTS P, PARTICIPANTS PP
    WHERE P.langue = PP.langue
    AND PP.nom = 'DECOENINCK'
    ou
    SELECT P.numparticipant, P.nom
    FROM PARTICIPANTS P
    WHERE P.langue = (SELECT PP.langue FROM PARTICIPANTS PP WHERE PP.nom = 'DECOENINCK')
    retour
  9. Affichez la liste des salles données par leur numéro, leur emplacement et la précision petite, moyenne ou grande selon que la capacité est inférieure à 40, entre 40 et 100 ou supérieure à 100.
    SELECT S.numsalle, S.nom,
    CASE WHEN S.capacité <=40 THEN 'petite'
    WHEN S.capacité <=100 THEN 'moyenne'
    ELSE 'grande'
    FROM SALLES S
    retour
  10. La salle A7125 du bâtiment A ne peut être utilisée. Affichez les salles de capacité supérieure. Une jointure non égalitaire
    SELECT S.numsalle, S.nom, S.capacité
    FROM SALLES S, SALLES SS
    WHERE S.capacité >= SS.capacité
    AND SS.numsalle = 'A7125'
    ou
    SELECT S.numsalle, S.nom, S.capacité
    FROM SALLES S
    WHERE S.capacité >= (SELECT SS.capacité FROM SALLES SS WHERE SS.numsalle = 'A7125' )
    retour
  11. La salle A7125 du bâtiment A ne peut être utilisée. Affichez les salles de capacité supérieure et de même niveau d'équipement.
    SELECT S.numsalle, S.nom, S.capacité
    FROM SALLES S, SALLES SS
    WHERE S.capacité >= SS.capacité
    AND S.équipement = SS.équipement
    AND SS.numsalle = 'A7125'
    retour
  12. Affichez le nombre de salles et la capacité totale des salles.
    SELECT COUNT(S.numsalle) nbsalles, SUM(S.capacité) capacitétotale FROM SALLES S
    retour
  13. Affichez la capacité totale des salles de moins de 100 places.
    SELECT SUM(S.capacité) FROM SALLES S WHERE S.capacité <= 100
    retour
  14. Affichez la capacité totale des salles par niveau d'équipement.
    SELECT SUM(S.capacité) FROM SALLES S GROUP BY S.équipement
    retour
  15. Affichez pour chaque conférence le nombre de participants.
    SELECT C.numconférence, COUNT(R.refparticipant) nbparticipants
    FROM RESERVATIONS R, CONFERENCES C
    WHERE R.refconférence = C.numconférence
    GROUP BY C.numconférence
    retour
  16. Affichez la liste des conférences ayant plus de 100 participants.
    SELECT C.numconférence, COUNT(R.refparticipant) nbparticipants
    FROM RESERVATIONS R, CONFERENCES C
    WHERE R.refconférence = C.numconférence
    GROUP BY C.numconférence
    HAVING nbparticipants >= 100
    retour
  17. Lorsque la langue du participant est différente de la langue de l'orateur, il faut prévoir un casque pour la traduction. Donnez le nombre de casques à prévoir pour chacune des conférences.
    SELECT C.numconférence, COUNT(P.langue) nbcasques
    FROM PARTICIPANTS PP, PARTICIPANTS P, RESERVATIONS R, CONFERENCES C
    WHERE PP.numparticipant = C.reforateur
    AND P.numparticipant = R.refparticipant
    AND R.refconférence = C.numconférence
    AND PP.langue <> P.langue
    GROUP BY C.numconférence
    retour
  18. Affichez la liste des participants aux conférences du thème finances et des participants aux conférences données en anglais. Un exemple d'union
    SELECT P.nom || ' ' || P.prénom
    FROM PARTICIPANTS P, RESERVATIONS R, CONFERENCES C
    WHERE P.numparticipant = R.refparticipant
    AND R.refconférence = C.numconférence
    AND C.thème='finances'
    UNION
    SELECT P.nom || ' ' || P.prénom
    FROM PARTICIPANTS PP, PARTICIPANTS P, RESERVATIONS R, CONFERENCES C
    WHERE PP.numparticipant = C.reforateur
    AND P.numparticipant = R.refparticipant
    AND R.refconférence = C.numconférence
    AND PP.langue='anglais'
    retour
  19. Affichez la liste des conférences ayant le lieu le même jour et dans la même salle que la conférence 37.
    SELECT C.numconférence, C.titre FROM CONFERENCES C, SALLES S
    WHERE C.refsalle = S.numsalle
    AND C.date = (SELECT CC.date FROM CONFERENCES CC WHERE CC.numconférence=37)
    AND S.numsalle = (SELECT SS.numsalle FROM CONFERENCES CC, SALLES SS WHERE CC.refsalle = SS.numsalle AND CC.numconférence=37)
    retour
  20. Affichez la liste des salles où sont données des conférences avec des participants parlant le russe.
    SELECT S.numsalle, S.nom
    FROM PARTICIPANTS P, RESERVATIONS R, CONFERENCES C, SALLES S
    WHERE P.numparticipant = R.refparticipant
    AND R.refconférence = C.numconférence
    AND S.numsalle = C.refsalle
    AND P.langue = 'russe'
    retour
  21. Donnez les noms des orateurs qui n'assistent à aucune conférence .
    SELECT P.nom || ' ' || P.prénom
    FROM PARTICIPANTS P, CONFERENCES C
    WHERE P.numparticipant = C.reforateur
    AND P.numparticipant NOT IN (SELECT R.refparticipant FROM RESERVATIONS R)
    corrigé
  22. Donnez les noms des participants ayant assisté à toutes les conférences.
    le nombre de conférences auxquelles il assiste est égal au nombre total de conférences
    SELECT P.nom || ' ' || P.prénom FROM PARTICIPANTS P WHERE
    (SELECT COUNT(R.refconférence) nbparticipations FROM RESERVATIONS R WHERE R.refparticipant = P.numparticipant)
    =
    (SELECT COUNT(*) nbconférences FROM CONFERENCES)
    ou il n'existe pas de conférence à laquelle il n'a pas participé
    SELECT P.nom || ' ' || P.prénom FROM PARTICIPANTS P WHERE NOT EXISTS
    (SELECT * FROM CONFERENCES C WHERE NOT EXISTS
    (SELECT * FROM RESERVATIONS R WHERE P.numparticipant = R.refparticipant AND R.refconférence = C.numconférence))
    retour
  23. Donnez le noms de l'orateur ayant eu le plus de succès, c'est-à-dire, l'audience la plus importante à une de ses conférences. orateur de la conférence dont le nombre de participants est égal au nombre max de participants à une des conférences. Erreur du débutant : écrire MAX(COUNT()).
    SELECT P.nom || ' ' || P.prénom FROM PARTICIPANTS P, CONFERENCES C
    WHERE P.numparticipant = C.reforateur
    AND C.numconférence =
    (SELECT CC.numconférence FROM CONFERENCES CC
    WHERE (SELECT COUNT(R.refparticipant) nbpart FROM RESERVATIONS R WHERE R.refconférence = CC.numconférence)
    =
    (SELECT MAX(nbparti)
    FROM (SELECT CCC.numconférence, COUNT(RR.refparticipant) nbparti FROM RESERVATIONS RR, CONFERENCES CCC WHERE RR.refconférence = CCC.numconférence)))
    retour
  24. Donnez le noms de l'orateur ayant eu le plus de succès, c'est-à-dire, l'audience cumulée la plus importante à toutes ses conférences. Id avec en plus la somme des comptes.
    retour