Get the largest elements of an SQL column

Question

I have 3 tables and I have the following question to solve: "Get the "name" with higher "Perce" related to "num2". Enter "num2" through "Name2"

 -- 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');


-- 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);

-- D (Num5, Num2, perce)
insert into D values(1,1,100);
insert into D  values(2,1,100);
insert into D values(1,3,10);
insert into D values(3,3,20);
insert into D values(2,2,60);
insert into D values(3,1,50);
insert into D values(2,3,70);
insert into D values(3,2,40);
insert into D values(2,1,0);

Answer 1

You can use the clause EXISTS in the WHERE to test if there is any aquarium with higher suitability and only show the result if there is no:

SELECT e.nome_cientifico as especie,
       a.nome as aquario
  FROM especie e
       INNER JOIN aquario_habitat ah ON ah.id_habitat = e.id_habitat
       INNER JOIN aquario a ON a.id_aquario = ah.id_aquario
 WHERE NOT EXISTS(SELECT 1
                     FROM aquario_habitat ah2
                    WHERE ah2.id_habitat = ah.id_habitat -- O que importa é o habitat
                      AND ah2.id_aquario <> ah.id_aquario -- Deve desconsiderar o mesmo aquário
                      AND ah2.adequacao > ah.adequacao); -- Caso existe de adequação maior, este deve ser descartado

Subqueries with EXISTS or NOT EXISTS

If the subquery return some line, EXISTS is TRUE, and NOT EXISTS is FALSE

We get the result (Based on your image data):

 ________________________________________________
| especie                  | aquario             |
|________________________________________________|
| Lutra sumatrana          | Vasco da Gama       |
| Hexanchus griseus        | Infante D. Henrique |
| Torpedo torpedo          | Infante D. Henrique |
| Echinaster brasiliensis  | Bartolomeu Dias     |
|________________________________________________|

Using the following creation of schema:

CREATE TABLE aquario(id_aquario  INTEGER,
                     localizacao VARCHAR(100),
                     nome        VARCHAR(100));

CREATE TABLE especie(id_especie      INTEGER,
                     nome_cientifico VARCHAR(100),
                     id_cat          INTEGER,
                     id_habitat      INTEGER);

CREATE TABLE aquario_habitat(id_aquario INTEGER,
                             id_habitat INTEGER,
                             adequacao  INTEGER);

insert into aquario(id_aquario, localizacao, nome)
values(1, 'NO', 'Vasco da Gama'),
      (2, 'N', 'Bartolomeu Dias'),
      (3, 'S', 'Infante D. Henrique');

insert into especie(id_especie, nome_cientifico, id_cat, id_habitat)
values(1, 'Lutra sumatrana', 9, 2),
      (2, 'Hexanchus griseus', 8, 1),
      (3, 'Torpedo torpedo', 7, 1),
      (4, 'Echinaster brasiliensis', 11, 3);

insert into aquario_habitat(id_aquario, id_habitat, adequacao)
values(1, 1, 10),
      (2, 1, 0),
      (3, 1, 100),
      (1, 2, 100),
      (2, 2, 90),
      (3, 2, 90),
      (1, 3, 0),
      (2, 3, 100),
      (3, 3, 0);

EDIT

Or as requested, adding to query which was presented (The FROM with multiple tables is equal to INNER JOIN only the visualization is more difficult):

SELECT DISTINCT A.nome,
                E.nome_cientifico
  FROM Aquario A,
       Especie E,
       Aquario_Habitat AH
 WHERE AH.id_habitat = E.id_habitat
   AND A.id_aquario = AH.id_aquario
   AND NOT EXISTS(SELECT 1
                    FROM aquario_habitat ah2
                   WHERE ah2.id_habitat = ah.id_habitat -- O que importa é o habitat
                     AND ah2.id_aquario <> ah.id_aquario -- Deve desconsiderar o mesmo aquário
                     AND ah2.adequacao > ah.adequacao); -- Caso existe de adequação maior, este deve ser descartado;