Quelques requêtes sql

[sqlman]

Bonjour à tous ;

J'aurais voulu avoir svp votre aide afin de construire quelques requêtes sql car étant débutant dans ce domaine je me heurte à des petites difficultés. Ce qui m'intéresse particulièrement, ce sont les explications ; un bref commentaire sur une requête que vous me proposerez sera bien plus efficace que la requête présentée seule, c'est ce qui va m'aider à faire le déclic.
Je vous remercie d'avance pour votre aide. Je vous présente un extrait d'un modèle relationnel :

PILOTE (idPilote, nom, prenom, dateDeNaissance, idPays)
idPilote : clé primaire
idPays : clé etrangère en réf à idPays de Pays

COURSE (dateCourse, idPays)
dateCourse : clé primaire

CLASSEMENT (idPilote, dateCourse, positionDep, positionArr)
idPilote, dateCourse :clés primaires
idPilote : clé etrangère en ref à idPilote de Pilote
dateCourse : clé etrangère en réf à dateCourse deCourse

ECURIE (idEcurie, nomEcurie, dateCreation)
idEcurie : clé primaire

Voilà, à partir de là, il nous est proposé de donner la liste des pilotes qui ont plus de 3 courses non terminées en 2010 en affichant également le nombre de ces courses. (à savoir qu'une course non terminée est caractérisée par une absence de valeur saisie dans positionArr)

Je vous mets la requête que j'ai "pondue"

Code :

1
2
3
4
5
SELECT nom, prenom, positionArr
FROM pilote NATURAL JOIN classement
WHERE SELECT (positionArr IS NULL
                      FROM classement
                      ) > 3

Je suis certain qu'elle est maladroite, si vous pouvez me l'arranger svp...

Et pour finir, une autre requête assez complexe je pense, si vous pouvez m'aiguiller ça m'avancerait bien svp... alors, il est demandé de donner l'écurie qui a le plus de victoires à son palmarès (nom & nombre). Comment procéderiez-vous par ex ?

[seabs]

Bonjour,

Je pense que cette requête doit répondre à ta demande :

Code :

1
2
3
4
5
SELECT P.idPilote, P.nom, P.prenom, SUM(CASE WHEN C.positionArr = NULL THEN 1 ELSE 0) AS Nbre
FROM PILOTE P
  INNER JOIN CLASSEMENT C ON C.idPilote = P.idPilote
GROUB BY P.idPilote, P.nom, P.prenom
HAVING SUM(CASE WHEN positionArr = NULL THEN 1 ELSE 0) > 3

Je n'ai pas tester, pas le courage de créer les tables nécessaires.

Dans une jointure, il semble indispensable de surnommer les tables P et C dans l'exemple.

Pour totaliser le nombre d'abandons, il est impossible d'utilier Count(*), car il prend pas en compte les NULL. Il faut donc utiliser un test de condition qui ajoute 1 pour chaque valeur de positionArr NULL.

Si la requête comprend un agrégat, il est nécessaire de mettre la clause GROUP BY qui permet le regroupement des informations de cumul. Cette clause doit comporter tous les éléments à l'exception des agrégats.

La clause HAVING permet de créer le filtre pour ne présenter que les pilotes qui ont plus de trois abandons.

Pour la 2ème question, essaies de présenter une requête, nous la corrigerons si nécessaire.

Pour te documenter plus complétement, tu as ce site http://sqlpro.developpez.com/

A+

[estofilo]

Envoyé par seabs

Pour totaliser le nombre d'abandons, il est impossible d'utilier Count(*), car il prend pas en compte les NULL. Il faut donc utiliser un test de condition qui ajoute 1 pour chaque valeur de positionArr NULL.

Non, il est tout à fait possible d'utiliser count(*), que des champs soient à NULL ou pas.

En revanche dans ta requête, les conditions du genre C.positionArr = NULL ne peuvent jamais être vraies, c'est justement l'erreur à ne pas faire avec les NULL.

[seabs]

Bonjour,

estofilo

En revanche dans ta requête, les conditions du genre C.positionArr = NULL ne peuvent jamais être vraies, c'est justement l'erreur à ne pas faire avec les NULL.

Effectivement, il s'agit d'une erreur impardonnable, ma requête devrait être

Code :

1
2
3
4
5
SELECT P.idPilote, P.nom, P.prenom, SUM(CASE WHEN C.positionArr IS NULL THEN 1 ELSE 0 END) AS Nbre
FROM PILOTE P
  INNER JOIN CLASSEMENT C ON C.idPilote = P.idPilote
GROUB BY P.idPilote, P.nom, P.prenom
HAVING SUM(CASE WHEN C.positionArr IS NULL THEN 1 ELSE 0 END) > 3

Code :

Non, il est tout à fait possible d'utiliser count(*), que des champs soient à NULL ou pas.

C'est exact, count(*) compte le nombre de lignes. Il serait donc plus judicieux d'écrire

Code :

1
2
3
4
5
6
SELECT P.idPilote, P.nom, P.prenom, COUNT(*) AS Nbre
FROM PILOTE P
  INNER JOIN CLASSEMENT C ON C.idPilote = P.idPilote
WHERE C.positionArr IS NULL
GROUB BY P.idPilote, P.nom, P.prenom
HAVING COUNT(*) > 3

Autant pour moi.

A+

[sqlman]

MCela parait tellement évident une fois la requête construite, mais le plus dur est d'en arriver là justement.

[sqlman]

Merci beaucoup à tous pour vos interventions. Mais est-ce qu'il n'y aurait pas une méthodologie particulière pour bien réussir ce genre d'exercices (en dehors de l'expérience et de l'aspect réflexion) ?

Dans le monde professionnel, les requêtes sont de ce niveau assez complexe ou c'est plus simple ?

A présent, si ça ne vous dérange pas, pourriez-vous m'aider svp pour construire une requête donnant l'écurie qui a le plus de victoires à son palmarès (nom et nombre) ?

Là, je n'ai vraiment aucune idée de comment procéder car je ne vois pas comment relier ECURIE à une autre table vu qu'il n'y a aucune CIF, association, ou lien identifient. Je ne demande pas vraiment la solution mais juste un indice permettant le départ et un déclic... si possible
J' y ai pourtant passé du temps mais sans résultat...

Merci d'avance

[Waldar]

Envoyé par sqlman

Dans le monde professionnel, les requêtes sont de ce niveau assez complexe ou c'est plus simple ?

Vos requêtes sont extrêmement simples.

[seabs]

Bonjour

A présent, si ça ne vous dérange pas, pourriez-vous m'aider svp pour construire une requête donnant l'écurie qui a le plus de victoires à son palmarès (nom et nombre) ?

Là, je n'ai vraiment aucune idée de comment procéder car je ne vois pas comment relier ECURIE à une autre table vu qu'il n'y a aucune CIF, association, ou lien identifient. Je ne demande pas vraiment la solution mais juste un indice permettant le départ et un déclic... si possible
J' y ai pourtant passé du temps mais sans résultat...

A mon avis, il n'y a aucune idée à chercher, cette requête est impossible car la table écurie n'est relié à aucune table. Il faut d'abord, mettre en relation la table Pilote et la table Ecurie.

La table Ecurie devient :

ECURIE (idEcurie, nomEcurie, dateCreation, #idPilote)
idPilote : clé etrangère en ref à idPilote de Pilote

Ensuite, tu peux faire ta requête en t'appuyant sur les exemples déjà donnés

L'objectif de ton exercice est peut être de comprendre où placer les clés étrangères ?

Bon courage

A+

[sqlman]

Non, l'objectif de l'exercice n'est pas de comprendre où placer les clés étrangères, mais bien de se familiariser avec le sql.
Alors, ne connaissant pas grand chose aux formules 1, j'ai peut-être oublié de présenter une table permettant d'effectuer ce traitement (rappel : donner l'écurie qui a le plus de victoires à son palmarès (nom et nombre)), je ne sais pas si elle a de l'importance, la voici :

ENGAGER (idEcurie, annee, idPilote)
idEcurie, annee, idPilote : clé primaire
idEcurie : clé etrangère en référence à idEcurie de ECURIE
idPilote : clé étrangère en réf à idPilote de PILOTE

On dirait une association ternaire, mais ça n'a pas d'importance...
On m'a dit que l'engager est celui qui prend le départ de la course, je ne sais pas si cela a un rapport...

[seabs]

Bonjour,

Sans être un spécialiste de formule 1, il est clair que pour connaître l'écurie qui a le plus de victoire, il doit obligatoire être possible de créer une relation entre les tables ECURIE, PILOTE et CLASSEMENT. Sinon, il est impossible de répondre à la question posée. Cette relation se fait par la table ENGAGER, non seulement elle a de l'importance, mais elle est indispensable.

Tu dis que cela serait une association ternaire, je ne pense pas. Essaies de faire ton MCD, tu verras qu'il ne s'agit pas d'une ternaire.

Avec ces informations, tu dois pouvoir présenter ta requête. A toi de travailler, car c'est le seul moyen pour comprendre.

Nous corrigerons, le cas échéant, ta requête si elle est inexacte.

Le erreurs faites sont le seul moyen pour les éviter ensuite. Encore que, il est toujours possible de se tromper sur les NULL et le décompte COUNT(*). Il faut que tu saches que je ne fais pas des requêtes toute la journée, j'en suis même très loin, et donc obligation, quand je fais une réponse, d'effectuer des recherches dans la documentation.

A+

[sqlman]

Effectivement comme tu le fais si bien remarquer Seabs, il ne s'agit pas du tout d'une ternaire, je me reprends, c'est une faute vraiment impardonnable de ma part. Je ne sais pas ce qui m'a donné cette impression. C'est une association à deux liens directs tout simplement avec ECURIE et PILOTE. Revenons en à la requête.

A l'heure où j'écris, j'ai rendu le devoir car je ne voulais pas non plus qu'il soit effectué par d'autres personnes, mais l'essentiel est de comprendre le principe maintenant.

Je sais que cela doit paraître tellement évident pour les professionnels du forum, mais il faut avouer quand même que pour en arriver à ce stade il faut des années d'expérience dans ce domaine et de la pratique.
Personnellement, je suis étudiant en première année de BTS Informatique de gestion et ce n'est pas cette facette de l'informatique qui m'attire le plus.
Ce qui me reste à faire à présent, c'est de bien étudier le corrigé.

Merci pour votre aide.

A+

[seabs]

Bonjour,

La requête suivante ne doit pas être loin du résultat, elle te donnera le palmarès de chacune des écuries.

Code :

1
2
3
4
5
6
SELECT E1.NomEcurie, COUNT(*) AS Nbre
FROM ECURIE E0
  INNER JOIN ENGAGER E1 ON E1.idEcurie = E0.IdEcurie
  INNER JOIN CLASSEMENT C ON C.idPilote = E1.IdPilote
WHERE C.positionArr = 1 
GROUP BY E1.NomEcurie

Bon je n'ai pas testé, je te laisse le soin. Si erreur, nous corrigerons.

Lorsque tu as une réponse, il faut tester car les bénévoles qui interviennent n'ont pas toujours le temps de créer un modèle pour faire les tests. Il peut y avoir un souci entre INNER JOIN et un LEFT JOIN en raison des NULL qui sont présents dans une ou plusieurs tables ou les retours de calcul qui sont à NULL.

D'ailleurs à ce sujet, je pense qu'il serait préférable de mettre l'attribut positionArr de la table CLASSEMENT à 0 ou -1 dans le cas ou le concurrent n'a pas terminé la course. Cette valeur pourrait s’inscrire par défaut.

Enfin, il s'agit d'une simple idée car les NULL dans les requêtes ce n'est jamais très facile à traiter.

A+ et bonne chance pour tes études.

[sqlman]

Ok merci pour ces requêtes, je comparerai avec le corrigé.
++