04高级查询

-上机题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,(select GradeName from Grade)as Gradename,subjectname,examdate,studentresult from  Student,Subject,Result  where result.StudentNo in  (     select MAX(ExamDate) from Result     group by SubjectId  )      --上机题6  --SQL语句的综合运用    --赋值科目名称为oop  declare @subjectName varchar(50)  SET @subjectName='oop'  --科目为oop最近考试时间  declare @date datetime  select @date=MAX(examdate) from Result  inner join Subject on Result.SubjectId=Subject.SubjectId  where SubjectName=@subjectName  --课程oop的编号  declare @subjectNo int  select @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 TempResult    SELECT  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                          ) R  ON Student.StudentNo=R.StudentNo  WHERE 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<97  END       --因为提分，所以需要更新IsPass（是否通过）列的数据  UPDATE TempResult     SET IsPass=CASE                 WHEN StudentResult>=60  THEN 1                 ELSE  0              END    SELECT * 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  [sql] view plain copy 在CODE上查看代码片派生到我的代码片//经典案例      --参加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'  )    ----参加oop最近一次考试时间的学生姓名和学生编号    select studentno, studentname from Student  where 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    )    begin  update 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>98    update 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<=98  end    else    begin  update 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