What would be the best SQL query to get the next set of results

Question

First of all, this is homework. I’ve already thought it over and wrote a solution, but I am a little discouraged by the fact that I cannot successfully profile it or get a second opinion as to whether it is really good.

Let's say I have a simple table of information about participation in the film (person, film, people's attitude to the film), for example:

create table film
(
    person_name varchar(48) not null,
    film_title varchar(128) not null,
    relation varchar(48) not null
);

-- { 'Mel Gibson', 'Braveheart', 'director' }
-- { 'Mel Gibson', 'Braveheart', 'cast' }
-- { 'Steven Spielberg', 'A.I.' , 'director' }
-- { 'Hilary Swank', 'Million Dollar Baby', 'cast' }
-- etc

The database and table are not created and are not supported by me, I just request information from it.

, ( ) , . , , , , . , , , . , " , , ".

(, , ), :

(   
    select  person_name 
    from    film 
    where   relation = 'director'
)
except 
(
    select person_name
    from 
    (   
        (
            select  person_name, film_title 
            from    film 
            where   relation = 'director'
        ) 
        except 
        (   
            select  person_name, film_title 
            from    film 
            where   relation = 'cast'
        )
    ) as director_behind_camera_for_film
)

, , ? , ?

, ( ) - , .

Answer 1

SELECT tmp.person_name FROM
(
   SELECT person_name, film_title, COUNT(relationship) as cnt
   FROM film
   WHERE relationship IN ('cast', 'director')
   GROUP BY person_name, film_title
) as tmp
GROUP BY person_name
HAVING SUM(cnt) = COUNT(cnt)*2

SELECT tmp.person_name FROM
(
   SELECT person_name, film_title, COUNT(DISTINCT(relationship)) as cnt
   FROM film
   WHERE relationship IN ('cast', 'director')
   GROUP BY person_name, film_title
) as tmp
GROUP BY person_name
HAVING SUM(cnt) = COUNT(cnt)*2

Answer 2

" , ( ) , . , , , , ".

( , ) .

X , X , , , ( , ), ( ...) "true".

, X , , X " , ", , , X . ( , .)

, ( ) SQL- Relational Algebra, Domain .

( ), : X, Z ALL Y.

, "" : " Y" "" , X. , . , GROUP() , ( , , GROUP(), ).

, .

Answer 3

, , SQL:

select director.person_name 
from film director 
full outer join film actor 
    on director.person_name = actor.Person_name
    and director.film_title= actor.film_title
where actor.relationship = 'cast'
and director.relationship = 'director'
and actor.person_name is not null
and director.person_name is not null