实验03-利用查询分析器查询(二).docVIP

实验三 利用查询分析器查询（二） 【实验目的】 1.学会利用查询分析器进行复杂查询和操作； 【实验内容】 使用样本（实验一）中的数据库。 学会TRANSACT-SQL语言的使用。 实行如下要求的查询和操作： 求出有一门课成绩在85分上的学生的学号、姓名 SELECT sno,sname FROM student WHERE sno IN (SELECT sno FROM sc WHEgrade85) 求出各门课程成绩均在85分以上学生的学号、姓名 select sno,sname from student where sno in (select sno from sc group by sno having min(grade)85) 查询没有选修 “030303”号课程的学生姓名和年龄 select sname,age from student where not exists (select * from sc where cno=030303) 查询各门课程取得最高成绩的学生姓名和成绩 select sname,grade from student,sc y where grade = (select max(grade) from sc where cno=y.cno) and student.sno=y.sno 检索学习了课程“操作系统”或“数据库系统原理”或“编译原理”其中一门课的学生学号。 select sno from student where sno in (select sno from sc where cno in (select cno from course where cname=操作系统or cname=数据库系统原理or cname=编译原理)) 检索已经学习课程“操作系统”和 “数据库系统原理”二门课的学生学号。 select sno from student where sno in (select sno from sc where cno in (select cno from course where cname=操作系统))and sno in(select sno from sc where cno in (select cno from course where cname=数据库系统原理)) 查询至少选修了学生130803101选修的全部课程的学生学号 详见课本P111 select distinct sno from sc x where not exists (select * from sc y where y.sno=130803101 and not exists (select * from sc z where z.sno=x.sno and z.cno=y.cno)) 列出所有同学的姓名、课程名和成绩 select sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno 查询与学生‘王丽’在同一班级学习的学生(用“IN”谓词表示) select sno from student where cls in (select cls from student where sname=李梅梅) 列出李伟同学所有未选修课程的课程号和课程名（用存在量词表示） select cno,cname from course where not exists (select cno from sc where sno in (select sno from student where sname=李伟)) 列出成绩比该课程平均成绩低的同学的成绩表 select sno,cno,grade from sc x where grade (select avg(grade)from sc where cno=x.cno) 列出所有选修‘操作系统’课程的‘男’同学的成绩表 select sno,cno,grade from sc x where sno in (select sno from sc where cno=(select cno from course where cname=操作系统))and sno in (select sno from student where sex=男) 8