有如下一张表(student)
姓名(name) 课程(course) 分数(score)
A a 60 A b 80 A c B a 30 B c C a D b 70
1.查询出选修了两门课以上的学生名单
2.删除只选修了一门课的学生
3.查询出有不及格的课程的学生名单(没有分数也算不及格)
姓名(name) 课程(course) 分数(score)
A a 60 A b 80 A c B a 30 B c C a D b 70
1.查询出选修了两门课以上的学生名单
2.删除只选修了一门课的学生
3.查询出有不及格的课程的学生名单(没有分数也算不及格)
2 delete from student a where exists ( select 1 from (select name from student group by name having count(course)=1 ) b where b.name=a.name);
3 select distinct(name) from student where score is null or score <60
第二个sql逻辑貌似不对啊这样写就可以
delete from student where student.name in (select name from student
group by name having count(course)=1)
insert student
select'A','a',60 union all
select'A','b',80 union all
select'B','a',null union all
select'B','c',null union all
select'C','a',null union all
select'D','b',70
1 select name from student group by name having count(course)>=2
2 delete from student where student.name in (select name from student group by name having count(course)=1)
3 select distinct name from student where score<60 or score is null