select d.idprepod, d.fio
from (
  select r.idprepod, r.fio, r.idgr, count(distinct r.mark)marks, iif(sum(iif(r.mark=5,r.qty_stud,0))>1,1,0) qty5
  from (
    select p.idprepod, p.fio, s.idgr, m.mark, count(distinct s.nzach) qty_stud
    from PREPOD p
    join MARKS m on m.idprepod=p.idprepod
    join STUD s on s.nzach=m.nzach
    group by p.idprepod, p.fio, s.idgr, m.mark
    )r
  group by r.idprepod, r.fio, r.idgr
  ) d
group by d.idprepod, d.fio
having count(*)=sum(d.qty5)

select g.idprepod, p.fio
from (
  select q.idprepod, q.idgr, 1<sum(case q.mark when 5 then q.qty_stud else 0 end) more1x5
  from (
    select m.idprepod, s.idgr, m.mark, count(distinct concat(m.nzach,'-',m.idpredmet))qty_stud
    from MARKS m
    join STUD s on s.nzach=m.nzach
    group by m.idprepod, s.idgr, m.mark
    )q
  group by q.idprepod, q.idgr
  )g
join PREPOD p on p.idprepod=g.idprepod
group by g.idprepod, p.fio
having count(*)=sum(g.more1x5)

select g.idprepod, p.fio
from (
  select idprepod, idgr, sum(mark5*qty_stud) qty5
  from (
    select m.idprepod, s.idgr, iif(m.mark=5,1,0) mark5, count(distinct concat(m.nzach,'-',m.idpredmet))qty_stud
    from MARKS m
    join STUD s on s.nzach=m.nzach
    group by m.idprepod, s.idgr, iif(m.mark=5,1,0)
    ) q
  group by idprepod, idgr
  ) g
join PREPOD p on p.idprepod=g.idprepod
group by g.idprepod, p.fio
having min(qty5)>1

SELECT  M.idprepod,S.idgr,SUM(CASE WHEN m.mark>=5 THEN 1 END)
  FROM marks M,stud S
  WHERE M.nzach=S.nzach
  GROUP BY idgr,idprepod ORDER BY idprepod ASC

select idprepod, fio, g.name group_name, qty5
from (
  select g.idprepod, p.fio, g.idgr, g.qty5, min(g.qty5)over(partition by g.idprepod)min_qty5
  from (
    select idprepod, idgr, sum(mark5*qty_stud) qty5
    from (
      select m.idprepod, s.idgr, iif(m.mark=5,1,0) mark5, count(distinct concat(m.nzach,'-',m.idpredmet))qty_stud
      from MARKS m
      join STUD s on s.nzach=m.nzach
      group by m.idprepod, s.idgr, iif(m.mark=5,1,0)
      ) q
    group by idprepod, idgr
    ) g
  join PREPOD p on p.idprepod=g.idprepod
  )n
join GRUPS g on g.idgr=n.idgr
where n.min_qty5>1
order by 2,3