Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Students (
  Student_id int,
  Student_name varchar(100)
  );
  
  create table Grades
  (
    student_id numeric,
    Profession varchar(10),
    Grade numeric
  );
  
  insert into Students(Student_id,Student_name) values (1,'avi'),(2,'david'), (3,'mosh'), (4,'adir');
  
  
  insert into Grades values (2,'math',95), (3,'sport',25), (4,'english',30);
  
  insert into Grades(student_id,Profession) values (1,'math');
4 rows affected

3 rows affected

1 rows affected

SELECT t.student_name
FROM (SELECT s.Student_id, s.student_name
           , COUNT(s.Student_id) OVER (PARTITION BY Profession) no_of_std
      FROM Students s
      inner join Grades g
              on s.Student_id = g.Student_id) t
where t.no_of_std > 1 


student_name


avi

david


SELECT s.student_name
FROM Students s
inner join Grades g
        on s.Student_id = g.Student_id
inner join (SELECT Profession
                 , COUNT(1) no_of_std
            FROM Grades
            GROUP BY Profession) t
        on g.Profession = t.Profession
       AND t.no_of_std > 1
order by s.student_id 


student_name


avi

david