Anuar  Maratkhan Anuar Maratkhan - 1 month ago 10
SQL Question

SQL Query: Employee Database

I have such SQL Database where there are employees and supervisors assigned to them. For supervisors who have no male employees working for them, I need to find the SSN and the total number of employees working for them.

This is how my table in database looks: enter image description here

And here is my code for the solution:

SELECT supervisors.ssn, COUNT(e.ssn)
FROM employee AS supervisors, employee AS e
WHERE e.ssn NOT IN (SELECT e.ssn FROM employee e WHERE e.sex = 'M') AND supervisors.ssn = e.superssn
GROUP BY supervisors.ssn;


But the code above finds the supervisors who has at least one Female employee. However, I need to find supervisors who have only Female employees.

Answer

Many of the Answers will exclude results if the Supervisor is Male

This will not

SELECT employee.lname, count(employee.lname) as NumberOfEmployees 
FROM Employee
INNER JOIN (

SELECT Employee.SuperSSN
FROM Employee
WHERE Employee.SuperSSN NOT IN
(SELECT Employee.SuperSSN FROM Employee e
INNER JOIN Employee
ON e.SSN = Employee.SuperSSN
WHERE Employee.SEX = 'M')
)
AS e
ON Employee.ssn = e.superssn
GROUP BY employee.lname
Comments