数据库作业二参考的答案.docVIP

1．查询所有姓“张”的男同学信息； Select * From student Where ssex=’男’ and sname like ‘张%’ 2．查询选修了“张三”老师的“数据结构”课程的学生姓名； Select student.sname From student,sc,course Where student.sno=sc.sno And sc.cno=course.cno And course.cname=’数据结构’ And course.teacher=’张三’ Select sname From student Where sno in (Select sno From sc Where cno in (select cno from sc Where cname=’数据结构’ And course.teacher=’张三’)) 3．查询所有同学均及格的课程名、最低成绩、最高成绩和平均成绩； Select course.cname,Min(sc.score),Max(sc.score),Avg(sc.score) From course,sc Where course.cno=sc.cno Group By course.cno，course.cname Having Min(score)=60 Select course.cname,Min(sc.score),Max(sc.score),Avg(sc.score) From course,sc Where course.cno=sc.cno And sc.cno Not in (Select cno From sc Where score 60) Group By course.cname 4．查询选修了“李四”老师教授 “王红”同学的所有课程的同学姓名； Select sname From student Where sno in (Select sno From sc sc1 Where Not Exists (Select * From sc sc2 Where cno in (Select cno From course Where teacher=’李四’) And sno in (Select sno From student Where sname=’王红’) And Not Exists (Select * From sc sc3 Where sc3.sno=sc1.sno And sc3.cno=sc2.cno) ) ) Select sname From student Where sno in (Select sno From sc sc1 Where Not Exists (Select * From sc sc2,course c1,student s1 Where sc2.cno=c1.cno And sc2.sno=s1.sno And s1.sname=王红 And c1.teacher=李四 And Not Exists (Select * From sc sc3 Where sc3.sno=sc1.sno And sc3.cno=sc2.cno) ) ) 5．若某同学存在次高课程成绩，查询输出学号、姓名及次高成绩； Select sc1.sno,student.sname,Max(sc1.score) From sc sc1,student Where sc1.sno=student.sno And score (Select Max(score) From sc Where sno = sc1.sno) Group By sc1.sno,student.sname Select sc1.sno,student.sname,Max(sc1.score) From sc sc1,student Where sc1.sno=student.sno And score Any (Select score From sc Where sno = sc1.sno) Group By sc1.sno,student.sname Select sc1.sno,student.sname,Max(sc1.score) From student,sc sc1,sc sc2 Where sc1.sno=student.sno And sc1.sno=sc2.sno And sc1.score sc2.score Group By sc1.sno,student.sname Top K查询 top k [percent] [with ties] top k：取查询结果的前k行；