ger7 incomplete

CREATE TABLE empl (
    name VARCHAR2(64),
    ssn NUMBER(1,0) primary key
);
CREATE TABLE depen (
    name VARCHAR2(64),
    gender NUMBER(1,0),
    essn NUMBER(1,0),
    constraint depen_essnname_pk PRIMARY KEY(essn, name),
    constraint depen_essn_fk foreign key (essn) references empl (ssn)
);

--
INSERT INTO empl VALUES ('klaus', 1);
INSERT INTO empl VALUES ('heidi', 2);
INSERT INTO empl VALUES ('hans', 3);
--
INSERT INTO depen VALUES ('hans', 1, 1);
INSERT INTO depen VALUES ('werner', 2, 1);
INSERT INTO depen VALUES ('chihiro', 1, 3);
INSERT INTO depen VALUES ('hinazuki', 2, 2);

-- correct
SELECT DISTINCT e.name, e.ssn
FROM empl e join depen d ON (e.ssn = d.essn)
WHERE EXISTS(SELECT * FROM depen d1 WHERE d1.essn = e.ssn AND d1.gender = 1)
AND EXISTS(SELECT * FROM depen d1 WHERE d1.essn = e.ssn AND d1.gender = 2);

-- fancy correct
SELECT d1.essn
 FROM depen d1 JOIN empl e1 ON d1.essn = e1.ssn
 WHERE d1.gender = 1
INTERSECT
 SELECT d2.essn
 FROM depen d2 JOIN empl e2 ON d2.essn = e2.ssn
 WHERE d2.gender = 1

-- real fancy solution
SELECT d1.essn
FROM depen d1 inner join depen d2 ON d1.essn = d2.essn
WHERE d1.gender = 1 AND d2.gender = 2

---
SELECT d.essn
FROM depen d
WHERE EXISTS (SELECT d1.essn FROM depen d1 WHERE d1.gender = 2)
AND EXISTS (SELECT d2.essn FROM depen d2 WHERE d2.gender = 1)
--minus (select DISTINCT d3.essn from depen d3 where d3.gender = 1 and d3.gender = 2)