For each department headed by a female manager, list the department name, and the number of employees who work for that department.

My Answer:

SELECT E.SEX
FROM EMPLOYEE AS E
WHERE E.Ssn IN (SELECT Essn
                FROM DEPENDENT AS D
                WHERE E.Sex = D.Sex);

enter image description here

CodePudding user response：

I think your answer is probably wrong. My solution is below:

select aa.name,
count(*) as cnt

(select a.dname
,dnumber 

from department as a 

inner join employee as b
on a.mgr_ssn = b.ssn

where a.sex = female
) as aa

inner join empoyee as bb
on aa.dnumber = bb.dno

group by aa.name

The logic is following:

1, find all departments which are headed by a female manager. the code is follwing

select a.dname
,dnumber 

from department as a 

inner join employee as b
on a.mgr_ssn = b.ssn

where a.sex = female

2, group by the department name and count the number of employees who work for that department.

CodePudding user response：

You'll want to study up on it inner joins, aliases/correlation names, and groups/aggregates.

select d.dname, count(*)
from dept d inner join emp em on em.ssn = dept.mgr_ssn
inner join emp e on e.dno = d.dnumber
where em.sex = 'F'
group by d.dname