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

--上机题1--参加oop最近一次考试时间的最高分数和最低分数select MAX(studentresult), MIN(studentresult) from Resultwhere 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 Subjectwhere 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的学生为S2if exists(select studentno,StudentName from Student where StudentNo in(  select StudentNo from Student where  GradeId=  (  sleelct GradeId from grade where GradeName='S1'  )))beginupdate Student set GradeId=2where GradeId in(  select GradeId from Grade where GradeName='S1' )end--上机题5select studentname,(select GradeName from Grade)as Gradename,subjectname,examdate,studentresult from  Student,Subject,Resultwhere result.StudentNo in(   select MAX(ExamDate) from Result   group by SubjectId)--上机题6--SQL语句的综合运用--赋值科目名称为oopdeclare @subjectName varchar(50)SET @subjectName='oop'--科目为oop最近考试时间declare @date datetimeselect @date=MAX(examdate) from Resultinner join Subject on Result.SubjectId=Subject.SubjectIdwhere SubjectName=@subjectName--课程oop的编号declare @subjectNo intselect @subjectNo=SubjectId from Subject where SubjectName=@subjectName--统计考试缺考情况SELECT 应到人数=(                 SELECT COUNT(*)  FROM Student                  INNER JOIN Subject ON Subject.GradeId=Student.GradeId                  WHERE SubjectName= @subjectName                 ) ,         实到人数=(                 SELECT COUNT(*) FROM Result                 WHERE ExamDate=@date AND SubjectNo=@subjectNo               ),      缺考人数=(                 SELECT COUNT(*)  FROM Student                  INNER JOIN Subject ON Subject.GradeId=Student.GradeId                  WHERE SubjectName= @subjectName              ) -              (                 SELECT COUNT(*) FROM Result                  WHERE ExamDate=@date AND SubjectNo=@subjectNo              ) --统计考试通过情况，并将统计结果存放在新表TempResult中--IF EXISTS(SELECT * FROM sysobjects WHERE name='TempResult')  DROP TABLE TempResultSELECT  Student.StudentName,Student.StudentNo,StudentResult,        IsPass=CASE                  WHEN StudentResult>=60  THEN 1                 ELSE 0               END INTO TempResult FROM Student LEFT JOIN (                         SELECT * FROM Result  WHERE ExamDate=@date AND SubjectNo=@subjectNo                        ) RON Student.StudentNo=R.StudentNoWHERE GradeId=(SELECT GradeId FROM Subject WHERE SubjectName= @subjectName)       SELECT * FROM TempResult DECLARE @avg numeric(4,1) SELECT @avg=AVG(StudentResult) FROM TempResult WHERE StudentResult IS NOT NULL--判断平均分是否低于60分。如果低于60分，设置平均分为60分IF (@avg<60)   SET @avg=60 --循环加分，最高分不能超过97分WHILE (1=1) BEGIN     IF(NOT Exists(SELECT * FROM TempResult WHERE StudentResult<@avg))      BREAK   ELSE     UPDATE TempResult SET StudentResult=StudentResult+1     WHERE StudentResult<@avg AND StudentResult<97END --因为提分，所以需要更新IsPass（是否通过）列的数据UPDATE TempResult   SET IsPass=CASE               WHEN StudentResult>=60  THEN 1               ELSE  0            ENDSELECT * FROM newTable--显示考试最终通过情况SELECT 姓名=StudentName,学号=StudentNo,        成绩=CASE               WHEN StudentResult IS NULL THEN '缺考'              ELSE  CONVERT(varchar(5),StudentResult)            END,   是否通过=CASE               WHEN isPass=1 THEN '是'              ELSE  '否'           END FROM TempResult  --显示通过率及通过人数SELECT 总人数=COUNT(*) ,通过人数=SUM(IsPass),       通过率=(CONVERT(varchar(5),AVG(IsPass*100))+'%')  FROM TempResult GO

//经典案例--参加oop最近一次考试时间的最高分数和最低分数select MAX(studentresult), MIN(studentresult) from Resultwhere ExamDate=(select MAX(ExamDate) from Result where SubjectId=(select SubjectId from Subject where SubjectName='oop'))and SubjectId=(select SubjectId from Subject where SubjectName='oop')----参加oop最近一次考试时间的学生姓名和学生编号select studentno, studentname from Studentwhere StudentNo 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'     )  ))--案例：检查“oop”课程最近一次考试。--如果有分以上的成绩，则每人提分；--否则，每人提分。最终的成绩不得大于分if exists( select studentresult 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 StudentResult>80)beginupdate Result set StudentResult=100  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 StudentResult>98update Result set StudentResult+=2  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 StudentResult<=98endelsebeginupdate Result set StudentResult+=5  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'     )  )end