优化MySchool 第四章 高级查询

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [StudentNo]
      ,[LoginPwd]
      ,[StudentName]
      ,[Gender]
      ,[GradeId]
      ,[Phone]
      ,[Address]
      ,[Birthday]
      ,[Email]
      ,[MyTT]
  FROM [MySchool].[dbo].[Student]
  
  
  --比较运算符后面的子查询只能返回单个数值
  
  --in子查询后面可跟随返回多条记录的子查询,用于检测某列的值是否存在于某个范围内
  
  
  
  declare @birthday datetime
  select @birthday=Birthday from Student where StudentName='微冷的雨'
  select * from Student where Birthday>@birthday
  
  
  
  --简单子查询
  select * from dbo.Student where Birthday>(select Birthday from Student  where  StudentName='微冷的雨')
  
  
  
  
  --内连接
  select StudentName from Student where StudentNo in
  (
  select StudentNo from Result R
  inner join Subject S on R.SubjectId=S.SubjectId
  where StudentResult>60 and SubjectName='oop'
  )
  
  select StudentName,StudentResult
  from dbo.Result R,dbo.Student S,dbo.Subject B
  where R.SubjectId=B.GradeId and S.StudentNo=R.StudentNo
  and StudentResult>60
  and SubjectName='oop'
 
 


--最近一次考试的学生名单(学生姓名,编号)考试科目为'oop'
      select StudentName,StudentNo from dbo.Student 
      where StudentNo in
      (
      select StudentNo from dbo.Result 
           where SubjectId=(
           select SubjectId from dbo.Subject 
           where SubjectName='oop'
                 )and ExamDate=(
           select MAX(ExamDate) from dbo.Result 
           where SubjectId=(
           select SubjectId from dbo.Subject
           where SubjectName='oop'
       )
     )
   )
   
   
   
   
--**********************************************************   
   
 --查询oop的最高和最低成绩
 --上机一
 select MAX(StudentResult),MIN(StudentResult) from dbo.Result 
  where ExamDate=( 
        select MAX(ExamDate) from dbo.Result 
        where SubjectId=(
         select SubjectId from dbo.Subject where SubjectName='oop'      
          )
      )
      
      
      
      
 --***************************************************
 --上机练习二
 --(1)查询年级为S1的课程编号
  select GradeId from dbo.Subject where GradeId in
  (
  select GradeId from dbo.Grade where GradeName='S1'
  )
 --(2)根据课课程编号查询课程表的课程名称
  select SubjectName  from dbo.Subject S , dbo.Grade  G where S.GradeId=G.GradeId 
  and  G.GradeId in(
  select GradeId from dbo.Grade where GradeName='S1'
  )
  



 --****************************************************************$$$
 --上机练习三
 
  select StudentName from Student
  where Studentno not in(
  select Studentno from result where examdate=(
  select max(ExamDate)
  from result 
  where SubjectId=(
  select SubjectId from Subject where Subjectname='java' 
  )
  )
  )and Gradeid=1

 
 
 
 
 --**************************************************************
 --上机练习四
if exists(
 select Gradeid from Grade 
 where Gradeid in (Select Gradeid from Grade where Gradename='S1' or Gradename='S2')
 )
 begin
 update Student 
 set Gradeid='2'
 where Gradeid='1'
 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  






--检查“oop”课程最近一次考试。--如果有分以上的成绩，则每人提分；
--否则，每人提分。最终的成绩不得大于分


   if exists( 
   select StudentResult from dbo.Result 
   where SubjectId=(
   select SubjectId from dbo.Subject  where SubjectName='oop'
   )and ExamDate=(
   select MAX(ExamDate) from dbo.Result 
   where SubjectId=(
   select SubjectId from dbo.Subject where SubjectName='oop'
   )
   )
and StudentResult>80
 )
 begin
 update dbo.Result set StudentResult=100
 where SubjectId=(
 select SubjectId from dbo.Subject where SubjectName='oop'
 )and ExamDate=(
   select MAX(ExamDate) from dbo.Result 
   where SubjectId=(
   select SubjectId from dbo.Subject where SubjectName='oop'
   )
   )
 and StudentResult>98
 update dbo.Result set StudentResult+=2 
 where SubjectId=(
 select SubjectId from dbo.Subject where SubjectName='oop'
 )and ExamDate=(
 select MAX(ExamDate) from dbo.Result where SubjectId=(
 select SubjectId from dbo.Subject where SubjectName='oop'
 )
 )
 and StudentResult<=98
 end 
 else
 begin
 update dbo.Result set StudentResult+=5
 where SubjectId=(
 select SubjectId from dbo.Subject where SubjectName='oop'
 ) and ExamDate=(
 select MAX(ExamDate) from dbo.Result where SubjectId=(
  select SubjectId from dbo.Subject where SubjectName='oop'
 )
 )
 end

 
 --分页查询 top  ,order by
 select top 2 * from dbo.Student
 where StudentNo not in(
 select top 3 StudentNo from dbo.Student
 )