MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE students (
  student_id int,
  teacher_id int );
  
INSERT INTO students values 
(1,2),
(2,1),
(3,2),
(4,3),
(5,3),
(6,4);

CREATE TABLE teachers (
  teacher_id int,
  full_name varchar(50) );
  
INSERT INTO teachers values 
(1,'NameA'),
(2,'NameB'),
(3,'NameC'),
(4,'NameD');

CREATE TABLE scores (
  student_id int,
  score int );
  
INSERT INTO scores values 
(1,6),
(2,4),
(3,6),
(4,3),
(5,4),
(6,5);



Records: 6  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0


SELECT * FROM students;
SELECT * FROM teachers;
SELECT * FROM scores;


student_id
teacher_id


1
2

2
1

3
2

4
3

5
3

6
4




teacher_id
full_name


1
NameA

2
NameB

3
NameC

4
NameD




student_id
score


1
6

2
4

3
6

4
3

5
4

6
5


select distinct t.full_name
from teachers t
inner join students s on t.teacher_id=s.teacher_id
inner join scores sc on sc.student_id=s.student_id
group by t.full_name
HAVING NOT SUM(sc.score <> 6)
;


full_name


NameB