- --上机题1
- --参加oop最近一次考试时间的最高分数和最低分数
- select MAX(studentresult), MIN(studentresult) from Result
- where ExamDate=
- (
- select MAX(ExamDate) from Result
- where SubjectId=
- (
- select SubjectId from Subject where SubjectName='oop'
- )
- )
- and SubjectId=
- (
- select SubjectId from Subject where SubjectName='oop'
- )
- --上机题2
- --使用in关键字的子查询来查询S1学期开设的课程
- select SubjectName from Subject
- where GradeId in
- (
- select GradeId from Grade where GradeName='S1'
- )
- --上机题3
- --查询某课程最近一次考试缺考的学生名单
- select studentno, StudentName from Student
- where StudentNo not in
- (
- select StudentNo from Result
- where SubjectId=
- (
- select SubjectId from Subject where SubjectName='oop'
- )
- and ExamDate=
- (
- select MAX(ExamDate) from Result
- where SubjectId=
- (
- select SubjectId from Subject where SubjectName='oop'
- )
- )
- )
- and GradeId=
- (
- select GradeId from Subject where SubjectName='oop'
- )
- --上机题4
- --检查并更新S1的学生为S2
- if exists(
- select studentno,StudentName from Student
- where StudentNo in
- (
- select StudentNo from Student where
- GradeId=
- (
MySchool 04 高级查询 上机题
最新推荐文章于 2021-02-19 05:27:03 发布
本文提供了一系列的SQL上机题,涉及高级查询技巧,如使用子查询查询最高分、最低分,查询特定学期课程,查找缺考学生,以及复杂的成绩更新策略,确保考试通过率不低于60%。
摘要由CSDN通过智能技术生成