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