To find all students that have at least one non-zero mark, I would use an EXISTS condition:
select stu.* from student stu where exists (select * from mark mrk where mrk.student_id = stu.id and mrk.mark <> 0);
Finding students that only have zero marks is a bit more tricky and requires aggregation.
select stu.* from student stu where exists (select mrk.student_id from mark mrk where mrk.student_id = stu.id group by mrk.student_id having bool_and(mrk.mark = 0));
group by is not needed in the sub-query as it will only deal with a single student, but the SQL syntax rules require it.
This could also be expressed as a join against a derived table which might actually be a bit more efficient:
select stu.* from student stu join ( select mrk.student_id, bool_and(mrk.mark = 0) as all_marks_are_zero from mark mrk group by mrk.student_id ) m on m.student_id = stu.id and m.all_marks_are_zero
* Be the first to Make Comment