Où est-ce que je me trompe dans cette modification de ce code SQL?

Dans votre requête, il y a une sous-requête dans la clause SELECT . Pour chaque ligne de résultat, il sélectionne le nombre d'instructeurs pour le département en question.

Vous êtes maintenant censé déplacer cela vers la clause FROM . Il n'y a donc plus un service avec lequel vous traitez. Vous voulez le décompte par département:

with i as
(
  select dept_name, count(*) as instructors
  from instructor
  group by dept_name
)
select d.dept_name, coalesce(i.instructors, 0) as num_instructors
from department d
left outer join i on i.dept_name = d.dept_name;

Si vous voyez qu'il y a au moins un instructeur par département, ou si vous décidez de ne pas montrer départements sans formateurs, vous utiliserez alors une INNER JOIN , sinon vous utiliserez une LEFT OUTER JOIN .

Jointure interne:

select d.dept_name, coalesce(i.instructors, 0) as num_instructors
from department d
left outer join
(
  select dept_name, count(*) as instructors
  from instructor
  group by dept_name
) i on i.dept_name = d.dept_name;

(Dans cet exemple particulier où la table de l'instructeur contient déjà tout ce dont vous avez besoin, il suffirait même de réduire l'ensemble de la requête à la sous-requête. Elle contient déjà le résultat final. Pas besoin de rejoindre la table department.)

Jointure externe:

select d.dept_name, i.num_instructors
from department d
inner join
(
  select dept_name, count(*) as num_instructors
  from instructor
  group by dept_name
) i on i.dept_name = d.dept_name;

Comme vous le voyez, nous doit gérer la possibilité que la sous-requête ne renvoie aucune ligne pour un département, nous devons donc transformer le nombre nul résultant en zéro.

Utiliser une clause WITH signifie simplement déplacer une sous-requête de la clause FROM au début de la requête, soit pour la lisibilité, soit pour l'utiliser plus d'une fois dans la requête:

select dept_name, count(*)
from instructor
group by dept_name;

J'ajoute ceci pour expliquer ce qui ne va pas avec votre propre requête.

Tout d'abord

from dept, dept_count

est l'ancienne syntaxe de

select count(*) 
from department, instructor 
where department.dept_name = instructor.dept_name

(Vous combinez chaque ligne d'instructeur avec sa ligne de département ici.)

Et cela seul

from dept
cross join dept_count

est l'ancienne syntaxe pour

from dept, dept_count

(Vous créez ici toutes les combinaisons possibles de départements et de nombres de départements. Avec 20 départements et 20 départements, vous créez par exemple 20 x 20 = 400 lignes de résultats. )

Votre sous-requête

from department
inner join instructor on department.dept_name = instructor.dept_name

ajoute d'abord la ligne du département à chaque instructeur (avec 20 départements et 50 enseignants, vous obtiendrez 50 lignes de résultats), puis vous les comptez. Le résultat est une seule ligne avec le nombre total d'instructeurs (50 dans l'exemple). Ceci est dû au fait que vous dites "compter les instructeurs" dans cette requête, plutôt que "compter les instructeurs par département", ce qui se traduirait par GROUP BY nom_dept . >

Dans votre requête principale, vous faites ceci:

from department, instructor 
where department.dept_name = instructor.dept_name

ce qui signifie que vous combinez chaque ligne de département avec la ligne qui contient le nombre total.

p>

Vous pouvez utiliser une clause with pour cela, mais ce n'est pas nécessaire.

La requête la plus simple serait

;with counts as (
    select department.dept_name, count(instructor.dept_name) as num_instructors
    from department
    left join instructor on department.dept_name = instructor.dept_name 
) 
select * from counts

Si vous voulez ajouter une clause with vous pourrait ajouter:

select department.dept_name, count(instructor.dept_name) as num_instructors
from department
left join instructor on department.dept_name = instructor.dept_name