Les requêtes SQL

[dominiqu]

Bonjour,

Etant en train d'apprendre le SQL , j'ai un petit exercice à faire. Il y'a un certain nombre de requêtes à écrire, j'ai essayé d'en écrire certaines , il y en a d'autres que je n'ai pas su faire. Etant débutante j'aurais besoin d'un petit coup de pouce de votre part.

Voici l'exercice en question avec mes tentatives:

PILOTE (idpilote,nom,prénom,dateDeNaissance,idPays)
idPilote:clé primaire
idPays:Clé étrangère en référence à idPays de PAYS
PAYS (idpays,nompays)
idPays:Clé primaire
COURSE (dateCourse ,idPays)
datecourse :Clé primaire
CLASSEMENT (idPilote,dateCourse,positionDep,positionArr)
idPilote,dateCours:Clé primaire
idPilote :clé étrangère en référence à idPilote de PILOTE)
dateCourse :Clé étrangère en référence à dateCourse de COURSE
ECURIE (idEcurie,nomEcurie,dateCreation)
idEcurie :Clé primaire
ENGAGER (idEcurie,annee,idPilote)
idEcurie,annee,idPilote:Clé primaire
idEcurie:Clé étrangère en réfénce à idEcurie de ECURIE
idPilote:Clé étangère en référence à idPilote de PILOTE


Indications:
positionDep:Correspond à la position sur la grille de départ d'un pilote.
positionArr:Correspond à la position d'arrivée d'un pilote.
Remarque:Lorsqu'un pilote ne termine pas une course (abandon,accident,disqualification,...) aucune valeur n'est saisie dans positionArr.

Ecrire les requêtes suivantes:

Requête 1:
donner la liste des pilotes (noms,prénoms) en commençant par le plus jeune.

Code :

1
2
SELECT nom,prenom
FROM pilote

Requête 2:
Combien de courses a remporté le pilote "Jean ALESI" dans la carrière ?

Code :

1
2
SELECT COUNT (course)
FROM pilote AS"Jean ALESI"

Requête 3:
Quel est le meilleur résultat au palmarès d'un pilote Suisse?

Code :

1
2
SELECT dateCourse.nompays
FROM pilote RIGHTJOIN pays USING (idpays)

Requête 4:
Combien d'hymnes nationaux différents ont déjà été joués au total dans l'histoire des grands prix?

Requête 5:
donner la liste des écuries (noms) et des pilotes (noms et prénoms) engagés en 2010.

Code :

1
2
3
SELECT nom,prenom
FROM écurie JOIN pilote USING (idpilote)
WHERE engager='2010'

Requête 6:
donner le classement (positions,noms et prénoms des pilotes) de la dernière course (en
commençant par le vainqueur bien évidemment).

Code :

1
2
3
SELECT position,nom et prenom
FROM classement
WHERE dateCourse

Requête 7:
donner la liste des pilotes qui ont plus de 3 courses non terminées (abandon,accident,disqualification,...) en 2010,vous afficherez également le nombre de courses non
terminées.

Code :

1
2
SELECT COUNT (*) AS"Nombre de courses"
FROM classement

Requête 8:
donner l'écurie qui a le plus de victoires à son palmarès (nom et nombre).

Code :

1
2
3
SELECT nom,nombre
FROM ecurie
WHERE course

Requête 9:
Le 09/05/2010 il y a le grand prix d'Espagne. Mettre à jour la base pour prendre en compte cette information.

je vous remercie pour votre aide

[SQLpro]

1) rajouter un ORDER BY

2) totalement faux. Vous obtiendrez toujours 1. Il faut faire une jointure de la table des pilotes avec les tables CLASSEMENT et COURSE. De plus il faut un filtre WHERE sur les colonnes nom et prenom avec les valeurs Jean et ALESI.
Enfin les chaines de caractères dans SQL sont délimitées par des apostrophes et non des guillemets !

3) totalement faux. Il faut faire une sous requête en imbriquant les agrégats COUNT et MAX

4) totalement faux. Il faut faire un COUNT(DISTINCT ....)

Bref, vous avez du manqué nombre de cours.... Êtes vous fait pour l'informatique ????

SQL est un véritable langage. Mon site web comme mon livre peuvent vous aider à l'apprendre !

A +

[soazig]

Bonjour,
2/Ce n'est pas que vous obtiendrez un, c'est surtout une erreur car il n'y a pas de colonne course dans la table pilote, de plus le fait de mettre un alias "Jean Alesi" sur la table pilote ne filtre pas, il faut donc une jointure.

Code :

1
2
3
4
5
6
SELECT count(*) AS nbre_course
FROM pilote p
INNER JOIN classement CL
ON CL.idPilote=P.idPilote
WHERE nom='ALESI' AND Prenom='Jean'
AND positionArr=1

3/ Pour la 3 je te laisse faire,
Il faut décomposer
Fait d'abord une requête pour avoir le résultat au palmarès de tous les pilote,
puis tu filtre sur les pilotes suisses, puis tu prend le max du résultat.

Bon courage
Soazig

[dominiqu]

3/

Code :

1
2
SELECT positionArr
FROM classement

[soazig]

Bonjour,
Pour le 3 la première question à se poser est qu'est-ce que le palmares.
Ensuite ce que tu nous montres ne sert à rien, tu n'as même pas le pilote qui a eu ce résultat.
Tu ne filtres pas sur les pilotes suisses, comme je l'ai suggéré
Tu ne prends pas le max .
Si tu veux de l'aide, aide-toi d'abord. Là j'ai l'impression que tu veux qu'on te fasse ton devoir, désolée , c'est ton devoir, on peut t'aider à comprendre comment le faire, si on voit que tu essaie, mais je ne ferai pas ton devoir.
A+
Soazig

[CinePhil]

Reprenons tes essais de requête dans l'ordre...

Envoyé par dominiqu

Voici l'exercice en question avec mes tentatives:

PILOTE (idpilote,nom,prénom,dateDeNaissance,idPays)
idPilote:clé primaire
idPays:Clé étrangère en référence à idPays de PAYS
PAYS (idpays,nompays)
idPays:Clé primaire
COURSE (dateCourse ,idPays)
datecourse :Clé primaire
CLASSEMENT (idPilote,dateCourse,positionDep,positionArr)
idPilote,dateCours:Clé primaire
idPilote :clé étrangère en référence à idPilote de PILOTE)
dateCourse :Clé étrangère en référence à dateCourse de COURSE
ECURIE (idEcurie,nomEcurie,dateCreation)
idEcurie :Clé primaire
ENGAGER (idEcurie,annee,idPilote)
idEcurie,annee,idPilote:Clé primaire
idEcurie:Clé étrangère en réfénce à idEcurie de ECURIE
idPilote:Clé étangère en référence à idPilote de PILOTE


Indications:
positionDep:Correspond à la position sur la grille de départ d'un pilote.
positionArr:Correspond à la position d'arrivée d'un pilote.
Remarque:Lorsqu'un pilote ne termine pas une course (abandon,accident,disqualification,...) aucune valeur n'est saisie dans positionArr.

Ecrire les requêtes suivantes:

Requête 1:
donner la liste des pilotes (noms,prénoms) en commençant par le plus jeune.

Code :

Tu donnes la liste des pilotes mais où est l'ordre du plus jeune au plus vieux ?
Il n'y a même pas leur âge dans la réponse !

Requête 2:
Combien de courses a remporté le pilote "Jean ALESI" dans la carrière ?

Code :

AS est pour donner un nom d'alias à un objet (table ou colonne ou calcul) de la requête. Là tu renommes la table 'pilote' en 'Jean Alesi'.
Pour ne compter que les courses de Jean Alesi, il restreindre la réponse au pilote dont le prénom est 'Jean' et le nom 'Alesi'.
Tu comptes les courses mais on te demande de compter ses victoires !

Requête 3:
Quel est le meilleur résultat au palmarès d'un pilote Suisse?

Code :

dateCourse.nompays veut dire "la colonne 'nompays' de la table 'dateCourse'. As-tu une table qui s'appelle 'dateCourse' ? C'est une virgule qu'il faut entre chaque colonne du SELECT !
Ensuite on ne te demande pas la date d'une course ou le nom d'un pays mais le meilleur résultat d'un pilote suisse !

Après tu changes complètement ta requête :

3/

Code :

Tu vas obtenir avec celle-ci la liste des positions d'arrivée de la table classement :
1, 2, 3, 4, 5, 6, 7...
Passionnant non ?
Ça correspond à ce qui est demandé ?

Requête 5:
donner la liste des écuries (noms) et des pilotes (noms et prénoms) engagés en 2010.

Code :

Tu donnes le nom et le prénom des pilotes mais où est le nom de l'écurie ?
Tu fais une jointure entre les tables 'écurie' et 'pilote' en utilisant idpilote mais idpilote ne figure pas dans la table 'écurie' !
Il te manque une jointure avec une autre table pour que tout ceci soit relié !

Requête 6:
donner le classement (positions,noms et prénoms des pilotes) de la dernière course (en
commençant par le vainqueur bien évidemment).

Code :

Je lis ta requête :
Sélectionner la position, le nom et le prénom du classement où la date de la course.

La phrase est-elle finie ?
Le classement a t-il un nom et un prénom ?
'position' est-il le bon nom de la colonne ? Autrement dit cette colonne ainsi appelée figure t-elle dans la table Classement ?

Requête 7:
donner la liste des pilotes qui ont plus de 3 courses non terminées (abandon,accident,disqualification,...) en 2010,vous afficherez également le nombre de courses non
terminées.

Code :

Là tu comptes le nombre de lignes de la table "classement". Est-ce le nombre de courses ? Est-ce celui de l'année 2010 ? Est-ce celui des courses non terminées ?

Ta requête non plus n'est pas terminée !

Requête 8:
donner l'écurie qui a le plus de victoires à son palmarès (nom et nombre).

Code :

"nom" est une colonne de la table "ecurie" ? Et "nombre" ?
Là encore, la phrase de ta requête n'est pas terminée : "... où la course..."

Je ne sais pas si cet exercice est une tentative d'effort personnel ou un travail scolaire mais pour le moment ta copie vaut un beau zéro !

[scyliatsuki]

Salut à tous,

Pour la 8ème requête je suis complètement bloquée, si vous avez un moment ça serait très gentil...

Ce que j'ai compris : c'est qu'il faut que je trouve les pilotes qui ont le plus de victoires, après ça je les relient à l'écurie en question.

J'ai tenté ça :

Code :

1
2
SELECT idpilote, sum(positionarr) AS "victoire" FROM classement 
WHERE positionarr=1 GROUP BY idpilote HAVING Max(victoire);

Tout seul cette requête marche (et ça m'étonne), j'ai en retour l'id des pilotes avec le total des fois où ils sont arrivés premiers.

Mais quand j'essai de finir la requête et de renvoyer vers l'écurie qui correspond au/x pilote/s on me dit erreur de syntaxe.

Code :

SELECT nomecurie FROM ecurie JOIN engager USING (idecurie) JOIN classement USING (idpilote) WHERE idpilote = (la requête au dessus)

Donc voilà j'ai vraiment essayer de la faire toute seule mais c'est pas évident pour moi, merci d'avance.

[CinePhil]

Bonjour.

Tu as le même exercice à faire un an après dominiqu ?

Reprenons la question :

Requête 8:
donner l'écurie qui a le plus de victoires à son palmarès (nom et nombre).

Envoyé par scyliatsuki

Ce que j'ai compris : c'est qu'il faut que je trouve les pilotes qui ont le plus de victoires, après ça je les relient à l'écurie en question.

C'est un bon début de réflexion en effet puisque les victoires sont associées directement aux pilotes et non pas aux écuries.

J'ai tenté ça :

Code :

Là par contre, ça va moins bien !

Tu as compris qu'il ne faut conserver que les pilotes classés premier grâce à la restriction WHERE positionarr=1 mais pourquoi additionner ces positions alors qu'il suffit de les compter ?
Tu me diras que ça reviens au même mais ce n'est pas sémantiquement ce qui t'es demandé.

Mais la clause HAVING n'est pas bonne. D'ailleurs, tu dis toi-même que tu as tous les pilotes.

Donc cette requête te donne le nombre de victoires par pilote arrivé premier :

Code :

1
2
3
4
SELECT idpilote, COUNT(*) AS nb_victoires 
FROM classement 
WHERE positionarr = 1 
GROUP BY idpilote

Faisons maintenant la jointure avec les écuries et additionnons les victoires de tous ces pilotes :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT ec.nomEcurie, SUM(tmp.nb_victoires) AS total_victoires
FROM ecurie ec
INNER JOIN engager en ON en.idEcurie = ec.idEcurie
	INNER JOIN 
	(
		SELECT idPilote, COUNT(*) AS nb_victoires 
		FROM classement 
		WHERE positionArr = 1 
		GROUP BY idpilote 
	) tmp ON tmp.idPilote = en.idPilote
GROUP BY ec.nomEcurie
HAVING SUM(tmp.nb_victoires) = MAX(SUM(tmp.nb_victoires))

Par contre, je ne suis pas sûr de ma clause HAVING.
À tester.

[scyliatsuki]

Merci beaucoup pour ta réponse,

Envoyé par CinePhil

Tu as le même exercice à faire un an après dominiqu ?

Oui le CNED n'évolue pas très rapidement.

Là par contre, ça va moins bien !

Tu as compris qu'il ne faut conserver que les pilotes classés premier grâce à la restriction WHERE positionarr=1 mais pourquoi additionner ces positions alors qu'il suffit de les compter ?

J'ai essayé d'avoir l'id du pilote qui est arrivé le plus souvent premier, après ça le relier à l'écurie qui l'a embauché et donc qui est demandée dans l'exo .

Donc cette requête te donne le nombre de victoires par pilote arrivé premier :

Code :

Faisons maintenant la jointure avec les écuries et additionnons les victoires de tous ces pilotes :

Code :

Là je n'ai pas tout suivi, c'est quoi "tmp" ?
Tu as déclarer une jointure avec le résultat d'une sous requête c'est bien ça? pour moi après chaque JOIN je dois mettre une table mais là je vois que c'est suivi par la requête établie pour connaitre le nombre de victoire par pilote.

J'ai une autre question, (bête mais ça me chiffonne) pourquoi vous donnez des alias aux tables? j'ai vu ça dans le cours mais je ne comprends pas l'utilité.

[CinePhil]

Là je n'ai pas tout suivi, c'est quoi "tmp" ?

tmp est un alias pour la sous-requête qui est ici indispensable pour rendre accessible à la requête principale le résultat de la sous-requête.

Tu as déclarer une jointure avec le résultat d'une sous requête c'est bien ça? pour moi après chaque JOIN je dois mettre une table mais là je vois que c'est suivi par la requête établie pour connaitre le nombre de victoire par pilote.

Oui, en SQL on peut faire une jointure sur une table ou sur une sous-requête dont le résultat sera en quelque sorte assimilé à une table temporaire.

pourquoi vous donnez des alias aux tables? j'ai vu ça dans le cours mais je ne comprends pas l'utilité.

Les alias permettent :
- de faciliter l'écriture des requête en évitant de répéter les noms des tables qui sont parfois longs ;
- de faciliter la lecture de la requête car il y a moins de texte ;
- de distinguer éventuellement deux colonnes portant le même nom dans deux tables différentes ;
- d'utiliser plusieurs fois la même table dans une seule requête, par exemple lors d'uen auto-jointure.

[Alexx844]

Code :

1
2
3
SELECT nom "ALESI", prenom "Jean", positionarr
FROM PILOTE INNER JOIN CLASSEMENT 
WHERE positionarr = 1

Qu'en pensez-vous ?

[CinePhil]

J'en pense que c'est complètement faux !

1) La partie SELECT de la requête signifie : "Sélectionne la colonne "nom" en la rebaptisant "ALESI", la colonne "prenom" en la rebaptisant "JEAN" et la colonne "positionarr".

Si tu ne veux sélectionner que les lignes concernant Jean Alesi, il faut placer les conditions de restriction sur le nom et le prénom dans la partie WHERE.

2) Il manque la condition de jointure.

[Alexx844]

Envoyé par CinePhil

J'en pense que c'est complètement faux !

1) La partie SELECT de la requête signifie : "Sélectionne la colonne "nom" en la rebaptisant "ALESI", la colonne "prenom" en la rebaptisant "JEAN" et la colonne "positionarr".

Si tu ne veux sélectionner que les lignes concernant Jean Alesi, il faut placer les conditions de restriction sur le nom et le prénom dans la partie WHERE.

2) Il manque la condition de jointure.

D accord donc si je fait

Code :

1
2
3
SELECT nom , prenom , positionarr
FROM PILOTE INNER JOIN CLASSEMENT 
WHERE ALESI, Jean, positionarr = 1

sa irais mieux ou toujours pas ? désolé je débute en SQL :s

[CinePhil]

Envoyé par Alexx844

D accord donc si je fait

SELECT nom , prenom , positionarr
FROM PILOTE INNER JOIN CLASSEMENT
WHERE ALESI, Jean, positionarr = 1

sa irais mieux ou toujours pas ?

1) Il n'y a toujours pas de condition de jointure !

2) La condition WHERE ALESI, Jean ne signifie rien !

désolé je débute en SQL :s

Tu fais cet exercice parce que tu l'as trouvé ici ou parce que tu es inscrit au CNED ?

Relis toute la discussion et tu trouveras des pistes pour mieux faire cet exercice. Il y a même la solution à la question 2.

Après, tu as les cours de SQLPro pour t'aider efficacement.

[Alexx844]

Envoyé par CinePhil

1) Il n'y a toujours pas de condition de jointure !

2) La condition WHERE ALESI, Jean ne signifie rien !


Tu fais cet exercice parce que tu l'as trouvé ici ou parce que tu es inscrit au CNED ?

Relis toute la discussion et tu trouveras des pistes pour mieux faire cet exercice. Il y a même la solution à la question 2.

Après, tu as les cours de SQLPro pour t'aider efficacement.

D'accord je voie donc pour la condition je peux faire :

Code :

1
2
WHERE nom = "ALESIE"
          prenom = "JEAN"

Pour la jointure je ne comprend pas pourquoi il y a temps de complication que je ne trouve pas dans mes cours :s

Effectivement je suis au CNED.

Merci

[CinePhil]

1) Il faut séparer les conditions de restriction par AND ou OR.
Dans le cas présent, tu veux que le nom soit "ALESI" et le prénom "JEAN".

Attention, à l'avenir, quand il y a plus de 2 conditions avec un mélange de OR et de AND ! Il faut réfléchir et mettre des parenthèses aux endroits appropriés en se souvenant de ses cours de logique.
(A AND B) OR C n'est pas pareil que A AND (B OR C) ! En SQL, le AND est prioritaire sur le OR.

2) La structure générale d'une jointure :

Code :

1
2
FROM une_table a
JOIN une_autre_table b ON a.une_colonne = b.une_colonne

La partie qui suit le ON est la condition de jointure.

Encore une fois, relis les premiers messages de la discussion et tu auras des exemples.

[Alexx844]

D'accord je comprend mieux donc pour ma condition

Code :

1
2
WHERE nom "ALESIE" 
AND prenom "Jean"

Je voie mieux pour la jointure je devrais mettre un ON

Donc

JOIN pilote ON classement ?

Merci de votre aide.

[CinePhil]

Tu prends le temps de réfléchir avant de proposer une nouvelle réponse ?

Code :

Non !
Encore une fois (c'est déjà la troisième !) regarde les premiers messages de cette discussion !

Code :

1
2
WHERE nom = 'ALESI'
    AND prenom = 'Jean'

Pourtant pas dur ! Ça se lit comme une phrase :
"où le nom est égal à ALESI et le prénom est égal à Jean."

En SQL, les valeurs textuelles s'entourent d'apostrophes, pas de guillemets !
C'est dit par SQLPro dans la première réponse !

Code :

JOIN pilote ON classement ?

Tu as lu mon message ? Qu'en plus tu cites !

Code :

1
2
FROM pilote AS p
JOIN classement AS c ON c.idPilote = p.idPilote

Là encore, ça se lit comme une phrase :
"de la table pilote (d'alias p) jointe à la table classement (d'alias c) sur l'égalité des colonnes idPilote des deux tables."

Prends le temps de lire les cours, les liens qu'on te donne. Tu as le temps puisque tu suis les cours du CNED ! Il vaut mieux bien assimiler les notions les unes après les autres que de vouloir se précipiter et envoyer un devoir complètement faux à la correction !

[islamov2000]

je vois qu'il y a beaucoup d'étudiants qui interviennent dans cette discussion.


en grosso modo, ils ont la volenté d'apprendre les initiatives.
même, ils le principe le SQL mais ils leur manquent le syntaxe de SQL.

par exemple, dominiqu a mis

SELECT nom,prenom
FROM écurie JOIN pilote USING (idpilote)
WHERE engager='2010'

il a idée, mais la jointure est faite par JOIN ...ON comme a indiqué CinePhil.
pour info. on peut donner des alias aux colonnes et tables comme

Code :

count(*) AS nombre_course

Code :

1
2
3
...
FROM pilote AS P
...

Etudiants!! quelles sont vos évolutions?

[Moskitox]

Hello, désolé pour le déterrage de topic, j'espère que quelqu'un pourra m'apporter de l'aide.

Après avoir lu tout le fil de discussion, certaines choses sont floues pour moi. Pour la requête 3, vous parlez de requête imbriqué, or il me semble l'avoir fait avec seulement 3 natural join plus la restriction du pays 'Suisse' comme ceci

Code :

1
2
3
4
Requête 3
SELECT MAX(classement.positionArr)
FROM classement NATURAL JOIN pilote NATURAL JOIN pays 
WHERE pays.nomPays = 'Suisse'

De même pour la requête 4, je vois que vous conseillez un Count Distinct, alors qu'au vu de la question cela serais plutôt un SUM DISTINCT étant donnée qu'il nous es demandé combien d'hymnes nationaux différents ont déjà été joués au total

Thoughts ?