Topic: there is a student table, which records the courses and scores of all students in a class. Each student has multiple courses. Different students may take different courses. Try to write an SQL statement to find out the names of all students whose course scores are greater than 80.
Table structure and data are as follows:

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `score` double NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `course` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '80', 'Zhang Fei', 'Chinese');
INSERT INTO `student` VALUES ('2', '76', 'Zhang Fei', 'Mathematics');
INSERT INTO `student` VALUES ('3', '88', 'Liu Bei', 'Chinese');
INSERT INTO `student` VALUES ('4', '95', 'Liu Bei', 'Mathematics');
INSERT INTO `student` VALUES ('5', '86', 'Guan Yu', 'Chinese');
INSERT INTO `student` VALUES ('6', '72', 'Guan Yu', 'Mathematics');
INSERT INTO `student` VALUES ('7', '78', 'Guan Yu', 'English?');
INSERT INTO `student` VALUES ('8', '80', 'Zhu Geliang', 'Chinese');
INSERT INTO `student` VALUES ('9', '85', 'Zhu Geliang', 'Physics');
INSERT INTO `student` VALUES ('10', '88', 'Zhu Geliang', 'Chemistry');

Method 1: reverse thinking solution
First, find out the names of students whose course scores are less than 80, and then use not in or not exists method to exclude them.

//not in
SELECT DISTINCT a.name
FROM student a
WHERE a.name NOT IN(SELECT DISTINCT b.name FROM student b WHERE b.score < 80)

//not exists
SELECT DISTINCT a.name
FROM student a
WHERE NOT EXISTS(SELECT 1 FROM student b WHERE b.score < 80 
AND a.name = b.name)

Query results

name
 Liu Bei
 Zhu Geliang

Explain:
exists (sql returns result set, true)
Not exists (SQL does not return result set, true)
The biggest difference between exists and in is that the clause guided by in can only return one field

Method 2: Using Having

SELECT s.name
FROM student s
GROUP BY s.name
Having MIN(s.score)>=80

Query results

name
 Liu Bei
 Zhu Geliang

Explain:
SQL uses the HAVING clause to specify a set of filter conditions for a row or aggregate.
The HAVING clause is usually used with the GROUP BY clause to filter the groups according to the specified criteria. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.
The HAVING clause applies filter conditions to each group of rows, while the WHERE clause applies filter conditions to each individual row.