Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE alumnos (
 i_alumn NUMBER GENERATED ALWAYS AS IDENTITY,
 name VARCHAR2(100) NOT NULL,
 CONSTRAINT pk_alumnos PRIMARY KEY (i_alumn)
);
INSERT INTO alumnos (name) 
SELECT 'Peter' as name FROM DUAL UNION ALL
SELECT 'Jessica' FROM DUAL UNION ALL
SELECT 'Bob' FROM DUAL;
3 rows affected

CREATE TABLE alumnos_materias (
 id NUMBER GENERATED ALWAYS AS IDENTITY,
 subject VARCHAR2(100) NOT NULL,
 grade NUMBER(10) NOT NULL,
 attempt NUMBER(2) NOT NULL,
 i_alumn NUMBER,
 CONSTRAINT pk_alumnos_materias PRIMARY KEY (id),
 CONSTRAINT fk_alumnos FOREIGN KEY (i_alumn) REFERENCES alumnos(i_alumn)
);
INSERT INTO alumnos_materias (i_alumn, grade, attempt, subject)
SELECT i_alumn, 6 as grade, 1 as attempt, 'math' as subject
FROM alumnos WHERE name = 'Peter' UNION ALL
SELECT i_alumn, 7, 2, 'math' FROM alumnos WHERE name = 'Peter' UNION ALL
SELECT i_alumn, 8, 1, 'chemistry' FROM alumnos WHERE name = 'Jessica' UNION ALL
SELECT i_alumn, 9, 1, 'spanish' FROM alumnos WHERE name = 'Jessica' UNION ALL
SELECT i_alumn, 6, 1, 'knitting' FROM alumnos WHERE name = 'Bob' UNION ALL
SELECT i_alumn, 5, 2, 'knitting' FROM alumnos WHERE name = 'Bob'
;
6 rows affected

SELECT 
 a.name, 
 COUNT(fails.subject) AS total_failed_subjects
FROM 
(
   SELECT i_alumn, subject
   FROM  alumnos_materias
   GROUP BY i_alumn, subject
   HAVING MAX(grade) <= 6
) fails
JOIN alumnos a ON a.i_alumn = fails.i_alumn
GROUP BY a.i_alumn, a.name
ORDER BY COUNT(fails.subject) DESC
FETCH FIRST 1 ROWS ONLY;


NAME
TOTAL_FAILED_SUBJECTS


Bob
1