Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TRAINER (SSN PRIMARY KEY, NameT, Surname) AS
SELECT 1, 'Alice', 'Abbot' FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Baron' FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Count' FROM DUAL;
3 rows affected

CREATE TABLE GYM (CodG PRIMARY KEY, NameG, City) AS
SELECT 10, 'GymA', 'Adelaide' FROM DUAL UNION ALL
SELECT 11, 'GymB', 'Berlin'   FROM DUAL UNION ALL
SELECT 12, 'GymC', 'Caracas'  FROM DUAL UNION ALL
SELECT 13, 'GymD', 'Dubai'    FROM DUAL;
4 rows affected

CREATE TABLE SPECIALTY (CodS PRIMARY KEY, NameS, Description) AS
SELECT 20, 'Yoga',      'Descr1' FROM DUAL UNION ALL
SELECT 21, 'Not Yoga',  'Descr2' FROM DUAL;
2 rows affected

CREATE TABLE LESSON (SSN, CodG, "DATE", CodS, ParticipantsNumber,
  PRIMARY KEY (SSN, CodG, "DATE")
) AS
SELECT 1, 10, DATE '1970-01-01' + LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT 1, 11, DATE '1970-01-01' + LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 2, 10, DATE '1970-01-01' + LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 2, 12, DATE '1970-01-01' + LEVEL - 1, 21, 1 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 3, 10, DATE '1970-01-01' + LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 3, 13, DATE '1970-01-01' + LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 3;
14 rows affected

ALTER TABLE lesson ADD FOREIGN KEY (ssn) REFERENCES trainer(ssn);
ALTER TABLE lesson ADD FOREIGN KEY (CodG) REFERENCES gym(CodG);
ALTER TABLE lesson ADD FOREIGN KEY (CodS) REFERENCES specialty(CodS);
SELECT namet, surname, city
FROM   (
  SELECT l.ssn,
         MAX(namet) AS namet,
         MAX(surname) AS surname,
         g.codg,
         MAX(city) AS city,
         SUM(COUNT(CASE WHEN s.NameS  <> 'Yoga' THEN 1 END))
           OVER (PARTITION BY l.ssn)
           AS num_other_lessons,
         RANK() OVER (PARTITION BY l.ssn ORDER BY COUNT(*) DESC) AS gym_rank
  FROM   lesson l
         INNER JOIN trainer t   ON (l.ssn = t.ssn)
         INNER JOIN gym g       ON (l.codg = g.codg)
         INNER JOIN specialty s ON (l.cods = s.cods)
  GROUP BY
         l.ssn,
         g.codg
)
WHERE  num_other_lessons = 0
AND    gym_rank = 1


NAMET
SURNAME
CITY


Alice
Abbot
Adelaide

Carol
Count
Dubai