Another interesting question one of my students has sent me recently. This question tests your understanding of SQL SELF JOIN. Given the following Class Grades table:

Create a query to display all students who received a grade of 100 in Math and Physics.
CREATE DATABASE class
GO
USE class
GO
CREATE TABLE Class_grades
(name varchar(20),
class varchar(20),
grade int)
INSERT INTO Class_grades (name, class, grade)
VALUES ('John', 'Math', 100),
('Tim', 'Math' ,80),
('John', 'Physics', 100),
('Tim', 'Math' ,100),
('Owen', 'Math', 100),
('Owen', 'Physics', 80),
('Dan', 'History',70)
SELECT DISTINCT cg1.name FROM Class_grades cg1, Class_grades cg2 WHERE cg1.name = cg2.name AND cg1.class = 'Math' AND cg1.grade = 100 AND cg2.class = 'Physics' AND cg2.grade = 100