Orcle数据库查询练习复习:二
一、题目
1.找出所有成绩均低于80的学生姓名
select sname from student where sid in( select sid from mark group by sid having max(cmark)<80) select sname from student where sid in( select sid from mark where sid not in( select sid from mark where cmark>=80))
2.找出和张三同学所选课程完全相同的学生姓名
Step1:select sid from student where sname='张三' step2:select cid from mark where sid=(step1) step3:select sid from mark where cid not in (step2) step4:select sid from mark where sid not in(step3)group by sid having count(*)=(select count(*)from mark where sid=(step1))
3.显示张三同学的数学和英语成绩,要求显示姓名、数学成绩、英语成绩
select sname ,( select cmark from mark where sid=(select sid from student where sname='张三') and cid=(select cid from course where cname='数学') ) 数学成绩, ( select cmark from mark where sid=(select sid from student where sname='张三') and cid=(select cid from course where cname='英语') ) 英语成绩 from studen
至少选修了学生10002选修的全部课程的学生学号
m studenm nt s leftjoin mark m on s.sid=m.sid where cmark<60
5.查询出所有存在不及格课程的学生的姓名
select sname from studenm nt s left join mark m on s.sid=m.sid where cmark<60
6.至少选修了学生10002选修的全部课程的学生学号
select sid from mark sc1 where not exists( select * from mark sc2 where sid=10002 and not exists (select *from mark where sid=sc1.sid and cid=sc2.cid ) ) and sid !=10002 group by sid
7.至少选修了学生张三选修的全部课程的学生姓名
select sid from mark sc1 where not exists( select * from mark sc2 where sid=(select sid from student where sname='张三') and not exists (select *from mark where sid=sc1.sid and cid=sc2.cid ) ) and sid !=10002 group by sid
本文来自博客园,作者:HuTiger,转载请注明原文链接:https://www.cnblogs.com/huguodong/p/5846653.html,欢迎加入qq群927460240学习讨论