חיתוך ציונים

CREATE DATABASE class
GO 
 
USE class
GO 
 
DROP TABLE Class_grades
 
CREATE TABLE Class_grades
(name varchar(20),
 prof varchar(20),
 grade int)
 
INSERT INTO Class_grades (name, prof, grade)
VALUES          ('Yossi', 'Math', 100),
                ('Tami', 'Math' ,80),
                ('Yossi', 'Physics', 100),
                ('Tami', 'Math' ,100),
                ('Oren', 'Math', 100),
                ('Oren', 'Physics', 80),
                ('Dor', 'History',70)

Select t1.name
From class_grades t1
Where t1.prof='math' and t1.grade=100
And exists 
(
Select 1
From class_grades t2
Where t1.name=t2.name
And t2.prof='phisics'
And t2.grade=100
) p