Aide pour faire une requette SQL

Version imprimable

29/05/2010, 23h32
ghiles82

Aide pour faire une requette SQL

Bonjour

Bonjour tout le monde

Voila j ai un petit souci pour faire une requette SQL d'oracle

On a 2 tables Jobs et Employees comme suit:

Dans la table Employee on a les champs: Employee_id , Job_id
Dans la table Jobs on a le champs Job_Id

Chaque employé a un job évidement.

La question est de trouver le job qui a le plus d'employées autrement dit le job le plus occupé

SVP si vous pouvez m'aider

Merci beaucoup

Bonjour,

L'idée est de récupérer le nombre d'employés par job, comme ceci :
Code:

1 2 3 SELECT Job_id, COUNT(*) as employeesByJob FROM Employee GROUP BY Job_id;
Pour récupérer le employeesByJob le plus grand, il y a plusieurs solutions. Je ne travaille pas avec Oracle, mais j'ai entendu parler de rownum qui pourrait être intéressant, mais je ne suis pas exactement certain de son fonctionnement. Je crois que ça serait comme ceci :
Code:

1 2 3 4 5 6 7 8 9 SELECT Job_id, employeesByJob FROM ( SELECT Job_id, COUNT(*) as employeesByJob FROM Employee GROUP BY Job_id ORDER BY employeesByJob DESC ) a WHERE rownum = 1
Sinon, tu peux utiliser les fonctions analytiques mais à priori, rownum fonctionne de la même façon que rank() dans ce cas-ci (et devrait être plus rapide logiquement).

Citation:
Envoyé par Jihnn
Bonjour,

L'idée est de récupérer le nombre d'employés par job, comme ceci :
Code:

1 2 3 SELECT Job_id, COUNT(*) as employeesByJob FROM Employee GROUP BY Job_id;
Pour récupérer le employeesByJob le plus grand, il y a plusieurs solutions. Je ne travaille pas avec Oracle, mais j'ai entendu parler de rownum qui pourrait être intéressant, mais je ne suis pas exactement certain de son fonctionnement. Je crois que ça serait comme ceci :
Code:

1 2 3 4 5 6 7 8 9 SELECT Job_id, employeesByJob FROM ( SELECT Job_id, COUNT(*) as employeesByJob FROM Employee GROUP BY Job_id ORDER BY employeesByJob DESC ) a WHERE rownum = 1
Sinon, tu peux utiliser les fonctions analytiques mais à priori, rownum fonctionne de la même façon que rank() dans ce cas-ci (et devrait être plus rapide logiquement).
Le soucis est que je souhaite faire les 2 en une seule requête donc compter le nombre d employés par job ensuite avoir le job qui a plus grand nombre d employé tout en une seule requête.

Merci

30/05/2010, 00h50
Jihnn

C'est en une seule requête, j'ai simplement expliqué la démarche pour arriver à la requête pour que la prochaine fois, tu sois en mesure de trouver la requête par toi-même.

Citation:
Envoyé par Jihnn
...
Code:

1 2 3 4 5 6 7 8 9 SELECT Job_id, employeesByJob FROM ( SELECT Job_id, COUNT(*) as employeesByJob FROM Employee GROUP BY Job_id ORDER BY employeesByJob DESC ) a WHERE rownum = 1
Cette requête ne correspond à aucun élément connu de SQL. Elle est donc fausse. D'ou tirez vous rownum ?
Sachez en outre que la claude ORDER BY est illicite dans les sous requêtes.
Enfin même dans le cas fortuit ou elle marcherait dans un SGBDR faisant du grand n'importe quoi, elle est susceptible de donner de faux résultats.
Bref, une requête particulièrement imbécile, preuve que vous n'avez rien compris aux principes ensembliste qui sont le fondement des bases de données relationnelles.

Une solution simple, parmi d'autres est la suivante :
Code:

1 2 3 4 5 6 7 8 9 SELECT Job_id FROM Employee GROUP BY Job_id HAVING COUNT(*) = (SELECT MAX(N) FROM (SELECT COUNT(*) AS N FROM Employee GROUP BY Job_id ) AS T )
Autre façon de faire, avec le quantificateur ALL :
Code:

1 2 3 4 5 6 7 SELECT Job_id FROM Employee GROUP BY Job_id HAVING COUNT(*) >= ALL (SELECT COUNT(*) AS N FROM Employee GROUP BY Job_id ) AS T
Plus sophistiqué avec les fonctions de ranking (norme SQL:2003)
Code:

1 2 3 4 5 SELECT Job_id FROM (SELECT Job_id, RANK() OVER (ORDER BY COUNT(*) DESC PARTITION BY Job_id) AS N FROM Employee) AS T WHERE N = 1
Enfin, pour expliquer pourquoi la requête qui vous a été donné est fausse, il suffit de prendre un exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 INSERT INTO Employee (Employee_id , Job_id) VALUES (1, 100); INSERT INTO Employee (Employee_id , Job_id) VALUES (2, 100); INSERT INTO Employee (Employee_id , Job_id) VALUES (3, 200); INSERT INTO Employee (Employee_id , Job_id) VALUES (4, 200); INSERT INTO Employee (Employee_id , Job_id) VALUES (5, 300); INSERT INTO Employee (Employee_id , Job_id) VALUES (6, 400);
Dans votre cas la requête pondue donnera arbitrairement le job id 100 ou 200 (indéterminisme) puisqu'ils ont le même nombre d'employé et que c'est bien le maximum.
Or en matière de SGBDR l'arbitraire ne peut exister !

Conclusion : apprenez le langage SQL et les principes des SGBRD. Mon site web, comme mes livres peuvent vous y aider !

A +

30/05/2010, 18h45
Jihnn

Citation:

Envoyé par SQLpro

Cette requête ne correspond à aucun élément connu de SQL. Elle est donc fausse. D'ou tirez vous rownum ?

Du site d'Oracle (par exemple ici). Si j'ai bien compris (je n'ai malheureusement pas Oracle pour tester), c'est en gros l'équivalent de ROW_NUMBER() OVER(ORDER BY colonne quelconque). Ne sachant pas comment faire sans sous-requête pour récupérer le nombre d'employés par job en même temps que son rang, j'ai préféré procédé ainsi.
Je n'avais effectivement pas pensé au cas où deux jobs auraient le même nombre d'employés, donc ROW_NUMBER() ne serait pas aussi approprié que rank()

Citation:

Sachez en outre que la claude ORDER BY est illicite dans les sous requêtes.
Enfin même dans le cas fortuit ou elle marcherait dans un SGBDR faisant du grand n'importe quoi, elle est susceptible de donner de faux résultats.

Oui, je m'en doutais

Citation:

Bref, une requête particulièrement imbécile, preuve que vous n'avez rien compris aux principes ensembliste qui sont le fondement des bases de données relationnelles.

Un peu de respect n'a jamais tué personne. Nous n'avons pas tous eu la chance d'avoir des formations poussées dans ce domaine.

Citation:

Conclusion : apprenez le langage SQL et les principes des SGBRD. Mon site web, comme mes livres peuvent vous y aider !

J'ai cherché à acheter votre livre, il ne semble pas disponible dans les magasins au Québec. Il n'est plus disponible chez Amazon ni chez Fnac selon mes recherches.

http://www.amazon. fr/exec/obidos/ASIN/2744073180/wwwdeveloppec-21
http://livre.fnac.com/a1236888/Frede...=1&Mn=-1&Ra=-1

Citation:
Envoyé par SQLpro
Cette requête ne correspond à aucun élément connu de SQL. Elle est donc fausse. D'ou tirez vous rownum ?
Sachez en outre que la claude ORDER BY est illicite dans les sous requêtes.
Enfin même dans le cas fortuit ou elle marcherait dans un SGBDR faisant du grand n'importe quoi, elle est susceptible de donner de faux résultats.
Bref, une requête particulièrement imbécile, preuve que vous n'avez rien compris aux principes ensembliste qui sont le fondement des bases de données relationnelles.

Une solution simple, parmi d'autres est la suivante :
Code:

1 2 3 4 5 6 7 8 9 SELECT Job_id FROM Employee GROUP BY Job_id HAVING COUNT(*) = (SELECT MAX(N) FROM (SELECT COUNT(*) AS N FROM Employee GROUP BY Job_id ) AS T )
Autre façon de faire, avec le quantificateur ALL :
Code:

1 2 3 4 5 6 7 SELECT Job_id FROM Employee GROUP BY Job_id HAVING COUNT(*) >= ALL (SELECT COUNT(*) AS N FROM Employee GROUP BY Job_id ) AS T
Plus sophistiqué avec les fonctions de ranking (norme SQL:2003)
Code:

1 2 3 4 5 SELECT Job_id FROM (SELECT Job_id, RANK() OVER (ORDER BY COUNT(*) DESC PARTITION BY Job_id) AS N FROM Employee) AS T WHERE N = 1
Enfin, pour expliquer pourquoi la requête qui vous a été donné est fausse, il suffit de prendre un exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 INSERT INTO Employee (Employee_id , Job_id) VALUES (1, 100); INSERT INTO Employee (Employee_id , Job_id) VALUES (2, 100); INSERT INTO Employee (Employee_id , Job_id) VALUES (3, 200); INSERT INTO Employee (Employee_id , Job_id) VALUES (4, 200); INSERT INTO Employee (Employee_id , Job_id) VALUES (5, 300); INSERT INTO Employee (Employee_id , Job_id) VALUES (6, 400);
Dans votre cas la requête pondue donnera arbitrairement le job id 100 ou 200 (indéterminisme) puisqu'ils ont le même nombre d'employé et que c'est bien le maximum.
Or en matière de SGBDR l'arbitraire ne peut exister !

Conclusion : apprenez le langage SQL et les principes des SGBRD. Mon site web, comme mes livres peuvent vous y aider !

A +
Bonjour

Merci beaucoup de votre aide la requête marche parfaitement et j ai choisis votre 1ere proposition mais il me reste encore une chose a régler car en fait la table jobs contient job_id et job_title et je souhaite afficher aussi le job_title mais je n'arrive pas a faire une jointure.

Je vous remercie encore une fois.

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT J.*
FROM   Jobs AS J
WHERE  Job_Id IN (SELECT Job_id
                  FROM   Employee
                  GROUP  BY Job_id
                  HAVING COUNT(*) = (SELECT MAX(N)
                                     FROM   (SELECT COUNT(*) AS N
                                             FROM   Employee
                                             GROUP  BY Job_id
                                            ) AS T
                                   )

Par exemple !

Lisez ce que j'ai écrit au sujet des sous requêtes : http://sqlpro.developpez.com/cours/sqlaz/sousrequetes/

A +

31/05/2010, 10h00
Waldar
Citation:

Envoyé par SQLpro

Cette requête ne correspond à aucun élément connu de SQL. Elle est donc fausse. D'ou tirez vous rownum ?

Sachez en outre que la claude ORDER BY est illicite dans les sous requêtes.
Enfin même dans le cas fortuit ou elle marcherait dans un SGBDR faisant du grand n'importe quoi, elle est susceptible de donner de faux résultats.

La façon d'écrire cette requête sous Oracle est l'équivalent du TOP sous SQL Server, "verrues" issues du temps où la norme ne couvrait pas ces besoins qui existent pourtant depuis le début.
rownum est la pseudocolonne Oracle qui positionne le numéro de ligne du résultat, après le tri (ce qui explique l'utilisation d'une sous-requête).

C'est sémantiquement correct. Vos remarques au sujet du déterminisme de cette requête sont 100% valides.
Citation:
Envoyé par SQLpro

Plus sophistiqué avec les fonctions de ranking (norme SQL:2003)

Code:

1 2 3 4 5 SELECT Job_id FROM (SELECT Job_id, RANK() OVER (ORDER BY COUNT(*) DESC PARTITION BY Job_id) AS N FROM Employee) AS T WHERE N = 1
PARTITION BY avant le ORDER BY, et il manque le GROUP BY dans la sous-requête.
31/05/2010, 21h53
ghiles82

Merci tout le monde pour votre aide et particulièrement à SQLpro. :ccool: