{clos} [SQL] Tout pleins de requêtes en vrac ! - et maintenant, du MERISE... wahouu !

[Mandarounet]

B’soir ! :hello:

Je passe mon concours dans deux jours, du coup je me recoltine les annales.
J’ai essayé de faire ce que je pouvais mais j’aimerais bien un coup de main pour rendre les requêtes plus propres, plus efficaces, etc.

Je redonne le lien vers l’énoncé[/url] ainsi qu’un lien vers le [url=http://mandarzzz.free.fr/upload/archidur.sql]dump de la base que j’ai créée sous MySQL pour faire mes tests.

Je poste aussi ce que j’ai fait (il s’agit de la première partie de l’exercice 1) :

SQL

-- Question 1

– Donner les projets (CodeProjet, NomClient) de chacun des architectes du cabinet
[color=blue;font-weight:bold]SELECT[/color] CodeProjet, NomClient, a.CodeArchi
FROM PROJET p, CLIENT c, ARCHITECTE a
WHERE p.CodeArchi = a.CodeArchi AND p.NumClient = c.CodeClient
ORDER BY CodeArchi;

– Question 2
– Donner le code du projet de plus haut budget
[color=blue;font-weight:bold]SELECT[/color] CodeProjet
FROM PROJET WHERE MtBudget IN ([color=blue;font-weight:bold]SELECT[/color] MAX(MtBudget) FROM PROJET); – Question 3
– Donner le nombre de clients par architecte
[color=blue;font-weight:bold]SELECT[/color] CodeArchi, COUNT(DISTINCT NumClient)
FROM PROJET GROUP BY CodeArchi;

– Question 4 modifiée pour pouvoir être testée
– Donner les projets (CodeProjet, MtBudget) ayant plus de 2 critères d’optimisation renseignés et ayant une moyenne de coefficients supérieurs à 6
[color=blue;font-weight:bold]SELECT[/color] p.CodeProjet, MtBudget
FROM PROJET p, OPTIMISATION o
WHERE p.CodeProjet = o.CodeProjet
GROUP BY o.CodeProjet
HAVING COUNT(CodeCritere) >= 2 AND AVG(Coeff) >= 6;

– Question 5
– Donner les critères d’optimisation communs aux projets de codes A13 et B25
[color=blue;font-weight:bold]SELECT[/color] NomCritere
FROM CRITERE WHERE CodeCritere IN ( [color=blue;font-weight:bold]SELECT[/color] CodeCritere
FROM Optimisation WHERE CodeProjet = ‘A13’
INTERSECT
[color=blue;font-weight:bold]SELECT[/color] CodeCritere
FROM Optimisation WHERE CodeProjet = ‘B25’
);
– Le problème c’est que INTERSECT fonctionne pas sous MySQL donc c’est mort pour mes tests…

– Question 6
– Donner les clients qui n’ont été suivis que par l’architecte numéro 3
[color=blue;font-weight:bold]SELECT[/color] PrenomClient, NomClient
FROM CLIENT, PROJET
WHERE CodeClient = NumClient AND CodeArchi = 3 AND NumClient NOT IN ( [color=blue;font-weight:bold]SELECT[/color] NumClient
FROM PROJET WHERE CodeArchi <> 3
);

– Question 7
– Donner les requêtes qui sont concernés par tous les critères d’optimisation
[color=blue;font-weight:bold]SELECT[/color] CodeProjet
FROM OPTIMISATION HAVING COUNT(DISTINCT CodeCritere) IN ( [color=blue;font-weight:bold]SELECT[/color] COUNT(CodeCritere)
FROM CRITERE );

Merci beaaaaucoup d’avance à ceux qui jetteront un coup d’oeil.
C’est assez important pour moi (le temps imparti pour ces requêtes le jour du concours, l’année dernière était de 1 heure).

Précisions :
Alors j’ai continué à y réfléchir et quelques points précis m’intéressent :

• (Q1) Le "ORDER BY" est-il nécessaire selon vous ?
• (Q2) Y-a-t’il un moyen de remplacer le “IN” par une égalité ou quelque chose d’équivalent puisque je suis sûr qu’il n’y aura qu’un seul résultat ?
• (Q5) La syntaxe d’INTERSECT est-elle bonne ? {il me faut du SQL standard}
• (Q6) Est-ce qu’un MINUS ne pourrait pas être utile ?
• (Q7) Il n’y a pas un mot clé adapté ?Bien-sûr, toute remarque à propos de ma syntaxe me sera utile. N’hésitez pas à critiquer quitte à lancer une discussion, le but c’est que je reprenne un petit peu mes marques afin de pouvoir être efficace en SQL ANSI.

[Mandarounet]

:ouch: 84 lectures sans réponse !

[Sans-Nom]

Je suis gentil :

SQL

[color=blue;font-weight:bold]SELECT[/color] NomCritere

FROM CRITERE WHERE CodeCritere IN ( [color=blue;font-weight:bold]SELECT[/color] CodeCritere
FROM Optimisation WHERE CodeProjet = ‘A13’
INTERSECT
[color=blue;font-weight:bold]SELECT[/color] CodeCritere
FROM Optimisation WHERE CodeProjet = ‘B25’
);

Intersect ne fonctionne pas? Alors ça, ça devrait :

SQL

[color=blue;font-weight:bold]SELECT[/color] NomCritere

FROM CRITERE WHERE CodeCritere IN (
[color=blue;font-weight:bold]SELECT[/color] CodeCritere
FROM Optimisation WHERE CodeProjet = ‘A13’
) AND CodeCritere IN(
[color=blue;font-weight:bold]SELECT[/color] CodeCritere
FROM Optimisation WHERE CodeProjet = ‘B25’
);

Et tu dois même pour voir utiliser un OR sur CodeProjet = ‘A13’ avec DISTINCT

SQL

[color=blue;font-weight:bold]SELECT[/color] NomCritere

FROM CRITERE WHERE CodeCritere IN (
[color=blue;font-weight:bold]SELECT[/color] DISTINCT CodeCritere
FROM Optimisation WHERE CodeProjet = ‘A13’ OR CodeProjet = ‘B25’
);

[deltree]

:ouch: 84 lectures sans réponse !

ben oauis, mais ce forum en bois y voulais pas que je poste:
Dire que j’aivais tappé tout un laius
bref, je reprend

– Question 1
–deltree: OK ras
– Question 2
– Donner le code du projet de plus haut budget
SELECT CodeProjet
FROM PROJET WHERE MtBudget = (SELECT MAX(MtBudget) FROM PROJET);

–deltree: oui, moi j’utilise le = (si c’est permis) dans les update c’est obligatoire par exemple

– Question 3
– Donner le nombre de clients par architecte
SELECT CodeArchi, COUNT(*)
FROM PROJET GROUP BY CodeArchi;
[color=blue]–deltree: DISTINCT NumClient ne sert à rien, moi je met toujours * (il s’en fout du contenu non?)
– toutes faço, le distinct n’est pas à utliser puisque tu as le group by[/color]

– Question 4 modifiée pour pouvoir être testée
– Donner les projets (CodeProjet, MtBudget) ayant plus de 2 critères d’optimisation renseignés et ayant une moyenne de

coefficients supérieurs à 6
SELECT p.CodeProjet, MtBudget
FROM PROJET p, OPTIMISATION o
WHERE p.CodeProjet = o.CodeProjet
GROUP BY o.CodeProjet
HAVING COUNT(*) >= 2 AND AVG(Coeff) >= 6;
deltree pareil COUNTCodeCritere sert à rien

– Question 5
– Donner les critères d’optimisation communs aux projets de codes A13 et B25
– Le problème c’est que INTERSECT fonctionne pas sous MySQL donc c’est mort pour mes tests…
[color=blue] Select NomCritere from Critere c, Optimisation o1, Optimisation o2
where c.CodeCritere = o1.codeCritere
and c.CodeCritere = o2.codeCritere
and o1.CodeProjet=‘A13’
and o2.CodeProjet=‘B25’
–deltree: et voila mes jointures sans requetes imbriquées :D, au niveau perf, vu qu’il y a que des jointure, et qu’on selectionne directement le codeprojet sur les 2 tables, ça doit être bien (mais bon si tu as moyen de tester). comme ça, pas besoin d’intersect ni de minus.
[/color]

– Question 6
– Donner les clients qui n’ont été suivis que par l’architecte numéro 3
SELECT PrenomClient, NomClient
FROM CLIENT, PROJET
WHERE CodeClient = NumClient AND CodeArchi = 3 AND NumClient NOT IN ( SELECT DISTINCT NumClient
  FROM PROJET WHERE CodeArchi <> 3
)
[color=blue]-- il manquait le distinc question de perf!!
– mais j’ai une méthode “overkil” qui plairait pas aux prof, mais qui permet de se passer de ce fameux not in:

SELECT PrenomClient, NomClient
FROM CLIENT c
inner join PROJET p1 on c.codeClient=p1.NumClient
left outer join PROJET p2 on c.codeClient=p2.NumClient
WHERE p1.CodeArchi = 3
AND p2.CodeArchi <> 3
and p2.NumClient is null
)
– ça c’était juste pour le fun :ane: explication: on fait une jointure sur les résultats qu’on veut et ceux qu’on ne veut pas, la deuxieme jointure ouverte permet de sélectionner aussi des lignes sans résultats pour cette jointure, et on ne sélectionne que ceux-ci en testant le “is null” (donc on exclue les résultats qu’on ne veut pas => plus de “not in” © deltree SQL programming since 1995 )[/color]

– Question 7
– Donner les critères qui sont concernés par tous les critères d’optimisation
SELECT CodeProjet
FROM OPTIMISATION group by codePorjet
HAVING COUNT() = ( SELECT COUNT()
  FROM CRITERE )
– même remarque sur les count (honêtement on vous en dit quoi en cours)

[*] (Q1) Le “ORDER BY” est-il nécessaire selon vous ?
- nécessaire non, mais ça répond à l’énoncé “pour chacun des architexte”
[*] (Q2) Y-a-t’il un moyen de remplacer le “IN” par une égalité ou quelque chose d’équivalent puisque je suis sûr qu’il n’y aura qu’un seul résultat ?
- moi je ferais si SQL le permet donc oui
[*] (Q5) La syntaxe d’INTERSECT est-elle bonne ? {il me faut du SQL standard}
- j’utilise un autre méthode
[*] (Q6) Est-ce qu’un MINUS ne pourrait pas être utile ?
- ça a l’air un peu difficile dans ce cas…
[*] (Q7) Il n’y a pas un mot clé adapté ?
- là je vois pas non, mis à part mes remarques.
[/list]
Bien-sûr, toute remarque à propos de ma syntaxe me sera utile. N’hésitez pas à critiquer quitte à lancer une discussion,

est-ce que je suis dans les temps, il y aura un corrigé?

[Mandarounet]

Merci messieurs !

deltree, pas de corrigé non, par contre, je vais tester toutes vos requêtes et répondre un peu plus tard (je suis coincé chez moi par ce qui ressemble à une gastro… je sais sur quoi je vais passer ma journée).

[deltree]

pourvu que ce ne soit pas une fidèle gastro… (pidoum poum tchiii comme dirait l’autre)

[Sans-Nom]

Encore heureux qu’on ne fait que t’aider sur Internet dans ce cas

[Mandarounet]

Je suis gentil :

SQL

[color=blue;font-weight:bold]SELECT[/color] NomCritere

FROM CRITERE WHERE CodeCritere IN ( [color=blue;font-weight:bold]SELECT[/color] CodeCritere
FROM Optimisation WHERE CodeProjet = ‘A13’
INTERSECT
[color=blue;font-weight:bold]SELECT[/color] CodeCritere
FROM Optimisation WHERE CodeProjet = ‘B25’
);

Intersect ne fonctionne pas? Alors ça, ça devrait :

SQL

[color=blue;font-weight:bold]SELECT[/color] NomCritere

FROM CRITERE WHERE CodeCritere IN (
  [color=blue;font-weight:bold]SELECT[/color] CodeCritere
  FROM Optimisation WHERE CodeProjet = ‘A13’
) AND CodeCritere IN(
  [color=blue;font-weight:bold]SELECT[/color] CodeCritere
  FROM Optimisation WHERE CodeProjet = ‘B25’
);

Et tu dois même pour voir utiliser un OR sur CodeProjet = ‘A13’ avec DISTINCT

SQL

[color=blue;font-weight:bold]SELECT[/color] NomCritere

FROM CRITERE WHERE CodeCritere IN (
  [color=blue;font-weight:bold]SELECT[/color] DISTINCT CodeCritere
  FROM Optimisation WHERE CodeProjet = ‘A13’ OR CodeProjet = ‘B25’
);

Ta première requête fonctionne parfaitement, la deuxième non : elle renvoie tous les critères de la table. Mais si le INTERSECT est correct, je crois que c’est ce que je mettrai, histoire de dire que je connais…

<tout pleins de trucs qui m’ont aidé>

Alors :

• toutes tes requêtes fonctionnent à part celle avec le LEFT OUTER JOIN, mais c’était une erreur syntaxique que j’ai eu la flemme de corriger, par contre j’admire la classe
• le signe “=” a l’air de lui plaire, je l’utiliserai
• la double jointure sur la même table est classe, et c’est vrai qu’elle est moins gourmande (j’ai fait un EXPLAIN et moins de champs sont parcourus, plus de détails à ce sujet ce soir sur l’astuce du jour ^^)
• en ce qui concerne COUNT(un_champ) et COUNT() ce n’est pas la même chose : sur la dernière requête j’obtiens un mauvais résultat et toi non.
  Du coup je crois que je vais aussi utiliser des COUNT() dès que je sais pas trop…

pourvu que ce ne soit pas une fidèle gastro… (pidoum poum tchiii comme dirait l’autre)

Alors ça ! :ouch:
D’où te vient cette référence ? Ça m’intéresse autant que mon concours presque !

Encore heureux qu’on ne fait que t’aider sur Internet dans ce cas

T’imagines, si je faisais des études de caligraphie ? :paf:

[deltree]

• toutes tes requêtes fonctionnent à part celle avec le LEFT OUTER JOIN, mais c’était une erreur syntaxique que j’ai eu la flemme de corriger, par contre j’admire la classe
• le signe “=” a l’air de lui plaire, je l’utiliserai
• la double jointure sur la même table est classe, et c’est vrai qu’elle est moins gourmande (j’ai fait un EXPLAIN et moins de champs sont parcourus, plus de détails à ce sujet ce soir sur l’astuce du jour ^^)
• en ce qui concerne COUNT(un_champ) et COUNT() ce n’est pas la même chose : sur la dernière requête j’obtiens un mauvais résultat et toi non.
  Du coup je crois que je vais aussi utiliser des COUNT() dès que je sais pas trop…

[:themika]
Merci beaucoup, mon humilité en prend en coup
Je n’utilisait que des count(), mais du coup je vais me pencher sur le sens des count(qqch). je pense que le count() est malgré tout le plus simple et à utiliser dans la majorité des cas.
Merci pour l’explain, je n’avais jamais trop cherché à regarder, ça confirme ce que je pensais par rapport à l’utilisation des index, des requetes imbriquées.

Je vais regarder ma syntaxe du left outer join. (edit: corrigé: c’était “NumClient” à la place de “codeClient”)

Alors ça ! :ouch:
D’où te vient cette référence ? Ça m’intéresse autant que mon concours presque !

sur bashfr.org, je manque un peu d’imagination, alors il me faut des sources

#5265 (+) 952/1090/9 (-)
guigui : tain j’ai une gastro qui me lache pas depuis 3 jour
hime : une fidèle gastro.

[Mandarounet]

[:themika]
Je vais regarder ma syntaxe du left outer join. (edit: corrigé: c’était “NumClient” à la place de “codeClient”)

sur bashfr.org, je manque un peu d’imagination, alors il me faut des sources

J’ai corrigé les noms de colonnes et ai rajouté des parenthèses, l’erreur était ailleurs alors je me suis arrêté.

OK pour BashFR.
C’est un peu EXACTEMENT une des vannes de notre spectacle (le pidoum poum tchiii compris !)

[Sans-Nom]

Au fait, ça sera testé sur quel SGBD? Juste du SQL 3 ?

[Mandarounet]

Au fait, ça sera testé sur quel SGBD? Juste du SQL 3 ?

Je sais pas du tout.
Comme ça, je dirais qu’il faut que ce soit compatible ANSI-92.

[deltree]

re-re-édité: peut-être la virgule.
Vous faites un spectacle? (pour le pidoum poum tchiii) )

[Mandarounet]

re-re-édité: peut-être la virgule.
Vous faites un spectacle? (pour le pidoum poum tchiii) )

J’avais supprimé la virgule aussi.

Ouep mais on va arrêter de flooder ici parce que le sujet va redevenir sérieux dans une p’tite demi heure :ane:
Je finis mon schéma relationnel et j’le poste.

[Mandarounet]

Alors pour la question 1 de la partie suivante, j’ai un schéma tout simple :

Par contre je ne me souviens plus du tout comment on gère la navigabilité des associations. Dois-je rajouter des flèches ? Si oui, où ?

Edit : j’avais oublié les cardinalités, c’est corrigé.

[Mandarounet]

Bon, j’ai rajouté les flèches.
Il me semble qu’elles partent, lorsqu’elles sont nécessaires, des entités ayant pour cardinalités 0,1 ou 1,1.

[deltree]

Tu ne confondrait pas avec le modèle physique, sans association?

je ne met pas de flèche sur les entité association, et je ne sais pas si c’est permis (les normes évoluent et mes cours sont loins)

[Mandarounet]

Il y en a sur leur corrigé des annales 2005… du coup je vais en mettre.

[Mandarounet]

Je clos le topic, merci pour votre aide.
J’en ai marre de réviser