sql运用：查询各科分数都 > 80 分的三种写法

查询各科分数都 > 80 分的三种写法

#创建表
create table student_grade (
    s_name varchar (100),
    course varchar (50),
    grade int
);

#插入模拟数据
insert into student_grade value ('a', '数学', 100);
insert into student_grade value ('a', '语文', 60);
insert into student_grade value ('a', '英语', 40);
insert into student_grade value ('b', '数学', 10);
insert into student_grade value ('b', '语文', 10);
insert into student_grade value ('b', '英语', 50);
insert into student_grade value ('v', '数学', 10);
insert into student_grade value ('v', '语文', 100);
insert into student_grade value ('v', '英语', 40);
insert into student_grade value ('c', '数学', 20);
insert into student_grade value ('c', '语文', 30);
insert into student_grade value ('c', '英语', 90);
insert into student_grade value ('d', '数学', 90);
insert into student_grade value ('d', '语文', 90);
insert into student_grade value ('d', '英语', 95);
insert into student_grade value ('e', '数学', 99);
insert into student_grade value ('e', '语文', 97);
insert into student_grade value ('e', '英语', 95);

select * from student_grade;

#查询各科分数都 > 80 分的写法
#方法一
select s_name from student_grade group by s_name having min(grade) > 80;
方法二
select distinct(s_name) from student_grade where s_name not in (select s_name from student_grade where grade <= 80);