第四章高级查询--上机题和经典案例

最新推荐文章于 2021-12-22 15:52:57 发布

倩倩之

最新推荐文章于 2021-12-22 15:52:57 发布

阅读量2.6k

点赞数

分类专栏：数据库设计 SQL

本文链接：https://blog.csdn.net/qq_36074113/article/details/54408581

版权

本文展示了多个SQL查询实例，包括查找特定课程的最高分和最低分，查询特定学期开设的课程，获取考试缺考学生名单，检查并更新学生年级，统计考试缺考和通过情况，以及调整考试分数等。所有查询都围绕'oop'课程展开，涉及子查询、联接和条件操作。

摘要由CSDN通过智能技术生成

--上机题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=
  (
  sleelct GradeId from grade where GradeName='S1'
  )
)
)
begin

update Student set GradeId=2
where GradeId in
(
  select GradeId from Grade where GradeName='S1' 
)
end


--上机题5


select studentname,(sele