SQL DML Oracle query

Question

I have tried to do this query: What are the name of hospitals in Spain where work more than 2 doctors that only work for that hospital. But the result isn't what I expected.

I have these tables:

CREATE TABLE Hospital (
    hid INT PRIMARY KEY,
    name VARCHAR(127) UNIQUE,
    country VARCHAR(127),
    area INT
);
CREATE TABLE Doctor (
    ic INT PRIMARY KEY,
    name VARCHAR(127),
    date_of_birth INT,
);
CREATE TABLE Work (
    hid INT,
    ic INT,
    since INT,
    FOREIGN KEY (hid) REFERENCES Hospital (hid),
    FOREIGN KEY (ic) REFERENCES Doctor (ic),
    PRIMARY KEY (hid,ic)
);

I tried with this:

SELECT DISTINCT H.name 
FROM Doctor D, Work W, Hospital H 
WHERE D.bi = W.bi AND H.country = 'Spain' AND H.hid = W.hid AND W.ic = D.ic 
AND NOT EXISTS(
              SELECT *
              FROM Hospital H2
              WHERE H2.hid = W.hid
)
GROUP BY (H.name)
HAVING COUNT(D.ic) > 2
;    

Thanks.

Answer 1

this should work for you assuming you mean hospitals that have > 2 dedicated doctors.

SQL> select * from hospital;

       HID NAME                 COUNTRY            AREA
---------- -------------------- ------------ ----------
         1 General              Spain                 1
         2 Hospital 2           Spain                 1
         3 Hospital 3           Spain                 1

SQL> select * from doctor;

        IC NAME                 DATE_OF_BIRTH
---------- -------------------- -------------
         1 Gregory House                    1
         2 Dougie Howser                    1
         3 Marie Curie                      1
         4 Dr Who                           1
         5 Dr Zeuss                         1

SQL> select * from work;

       HID         IC      SINCE
---------- ---------- ----------
         1          1          1
         1          2          1
         2          3          1
         2          4          1
         3          4          1
         1          5          1

6 rows selected.

SQL> select h.hid, h.name
  2    from hospital h
  3         inner join work w
  4                 on w.hid = h.hid
  5         inner join doctor d
  6                 on d.ic = w.ic
  7   where not exists (select null
  8                       from work w2
  9                      where w2.hid != h.hid
 10                        and w2.ic = w.ic)
 11   group by h.hid, h.name
 12   having count(*) > 2;

       HID NAME
---------- --------------------
         1 General