database - improving the written sql query

Question

I want to write a SQL query for the problem as defined below, my answer is as below, but I am not sure about the answer, can anyone help me? Is the answer correct, or if not, how can I improve it?

Let us consider the following relational schema about physicians and departments:

• PHYSICIAN (PhysicianId, Name, Surname, Specialization, Gender, BirthDate, Department);

  Let every physician be univocally identified by a code and characterized by a name, a surname, a specialization (we assume to record exactly one specialization for each physician), a gender, a birth date, and the relative department (each physician is assigned to one and only one department).

• DEPARTMENT (Name, Building, Floor, Chief)

  Let every department be univocally identified by a name and characterized by its location (building and floor) and a chief.

Let us assume that a physician can be the chief of at most one department (the department he/she belongs to). We do not exclude the possibility for two distinct departments to be located at the same floor of the same building.

Define preliminarily primary keys, other candidate keys (if any), and foreign keys (if any). Then, formulate an SQL query to compute the following data (exploiting aggregate functions only if they are strictly necessary):

• the departments with both male and female physicians, whose physicians are all born after 1955, that is, they are all at most 64 years old.

My answer is as below:

create view table X as {
select d.Name
from department d inner join PHYSICIAN p on 
d.department=f.name
where gender="Female" and gender="Male and birthdate>1955

select *
from X

Answer 1

Your query will not return any records because 'gender' field cannot be equal to Female and Male at the same time. My suggestion is something like:

select distinct d.*
  from departments d
  inner join physicians m on d.name = m.department and m.gender = 'Male'
  inner join physicians f on d.name = f.department and f.gender = 'Female'
  left join physicians o on d.name = o.department and p.birthdate < '1955-01-01'
  where o.id is null

The idea is to find at least one male and one female for each department. After that find one old person for each department and filter out all departments that have such an old person.