Q:

Obter elementos de uma coluna dependente de outra

Eu tenho que fazer uma  Tabela onde indique o "name2", "local" e "name" dos animais em maior número em cada "name".
SELECT DISTINCT OP.nome , OP.localizacao , AD.nome_cientifico   
FROM  C AD , A OP, B CP
WHERE OP.id_aquario = CP.id_aquario AND 
      CP.id_especie = AD.id_especie AND
      NOT EXISTS(SELECT *
                 FROM  C AD2
                 WHERE AD2.id_aquario = CP.id_aquario) 
GROUP BY OP.nome;

Tabelas 
-- A (name,Num2, local)
    insert into A values ('Favela',1,'WE');
    insert into A values ('Lamosa',2,'NA');
    insert into A values ('Luz',3,'S0');

-- B (Num1,name,Num2, Num3) 
insert into B values (1,'Maria',1,1);
insert into B values (2,'Jorge',2,1);
insert into B values (3,'Teresa',2,1);
insert into B values (4,'Rui',3,1);
insert into B values (1,'Fran',3,3);
insert into B values (2,'Juliett',3,3);

-- C (num5,num3,name2,num4)
insert into C values (2,1,'Cao',9);
insert into C values (1,2,'Gato',8);
insert into C values (1,3,'Golfinho',7);
insert into C values (3,4,'Peixe',11);

A:

Você pode utilizar uma subquery para obter a quantidade de cada espécie por aquário. Porém para mostrar apenas um registro por aquário você terá que utilizar o NOT EXISTS comparando a quantidade usando HAVING. O resultado é o seguinte:
SELECT base.nome as aquario,
       base.localizacao,
       base.nome_cientifico
  FROM (SELECT a.id_aquario,
               a.nome,
               a.localizacao,
               e.id_especie,
               e.nome_cientifico,
               count(1) as quantidade
          FROM aquario a
               INNER JOIN especime ep ON ep.id_aquario = a.id_aquario
               INNER JOIN especie e ON e.id_especie = ep.id_especie
         GROUP BY a.id_aquario, e.id_especie) base
  WHERE NOT EXISTS(SELECT 1
                     FROM especime ep
                          INNER JOIN especie e ON e.id_especie = ep.id_especie
                    WHERE ep.id_aquario = base.id_aquario
                      AND e.id_especie <> base.id_especie
                    GROUP BY e.id_especie
                   HAVING count(1) > base.quantidade)

Subqueries com EXISTS or NOT EXISTS
Se a subquery retornar alguma linha, EXISTS é TRUE, e NOT EXISTS é FALSE

Cláusula HAVING
A cláusula HAVING foi adicionada à SQL porque o WHERE não pode ser usada com funções agregadas (GROUP BY)

Um modo de simplificar a sua query seria criar uma view com os dados cruciais para a operação da seguinte forma:
CREATE VIEW view_quantidade_aquario AS
SELECT a.id_aquario,
       a.nome,
       a.localizacao,
       e.id_especie,
       e.nome_cientifico,
       count(1) as quantidade
  FROM aquario a
       INNER JOIN especime ep ON ep.id_aquario = a.id_aquario
       INNER JOIN especie e ON e.id_especie = ep.id_especie
 GROUP BY a.id_aquario, e.id_especie

E utilizá-la com a seguinte query:
SELECT vw.nome as aquario,
       vw.localizacao,
       vw.nome_cientifico
  FROM view_quantidade_aquario vw
  WHERE NOT EXISTS(SELECT 1
                     FROM view_quantidade_aquario vw2
                    WHERE vw2.id_aquario = vw.id_aquario
                      AND vw2.id_especie <> vw.id_especie
                      AND vw2.quantidade > vw.quantidade)

Nos dois casos o resultado será:
 ______________________________________________________
| aquario             | localizacao | nome_cientifico  |
| _____________________________________________________|
| Vasco da Gama       | NO          | Lutra sumatrana  |
| Bartolomeu Dias     | N           | Lutra sumatrana  |
| Infante D. Henrique | S           | Torpedo torpedo  |
|______________________________________________________|

