SQL高级 第四章 高级查询

作业：

------------------------------------------------------------------------
--关键字 " in "  返回多条记录

select StudentName from student where LoginPwd in
(select LoginPwd from student  where LoginPwd='111111')

--关键字 " = "  返回单条记录
select StudentName from student where LoginPwd =
(select LoginPwd from student  where LoginPwd='123456')


-----------------------------------------------------------------------------

--查询  最近一次 参加‘opp’ 考试的   最高分 and  最低分

select max([StudentResult])as 最高分,min([StudentResult]) AS 最低分
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 studentName ,studentNo 
  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”课程最近一次考试。--如果有分以上的成绩，则每人提分；
	--否则，每人提分。最终的成绩不得大于95分
	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
	   --有，每人提分 99
	   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
	   --没有，整体+5
	   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

---------------------------------------------------------------------------------------------

---分页：双Top 双Order by  每页显示3条记录,我想要第二页数据 4-6条
select top 3 * from Student
where StudentNo not in
(
   select top 0 StudentNo from Student
)


----------------------------------------------------------------------------------------------
          上机练习：
--------------------------------------
上机练习一：

--查询  最近一次 参加‘opp’ 考试的   最高分 and  最低分

select max([StudentResult])as 最高分,min([StudentResult]) AS 最低分
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')

-------------------------------------------------------------------------
上机练习二：

--使用 in  关键字  查询S1 学期开设的课程

select SubjectName   from  subject 
where gradeid in
(
  select gradeid from grade 
  where Gradeid='1'
)

--------------------------------------------------------------------------
上机练习三：

--查询某课程  最近一次考试 缺考的 学生名单

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'
	)
  )
) 

---------------------------------------------------------------------------
上机练习四：

--如果有 S1 的学生 就将 年级更新为 S2 

if exists (
select * from  student
where gradeid in
     (
         select gradeid from student 
		 where gradeid='1'
     )	
 )
 begin 
   update student set Gradeid='2'
   where Gradeid in
   (
      select gradeid from student 
		 where gradeid='1'
   )
 end 
 Go

-----------------------------------------------------------------------------

DECLARE @subjectName varchar(50)
DECLARE @date datetime  --最近考试时间
DECLARE @subjectNo int  --科目编号
SET  @subjectName='java logic'
SELECT  @date=max(ExamDate) FROM Result INNER JOIN  Subject
ON Result.SubjectNo=Subject.SubjectNo
WHERE SubjectName= @subjectName
SELECT @subjectNo=subjectNo 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

IF (@avg<60)  --判断平均分是否低于60分。如果低于60分，设置平均分为60分
 SET @avg=60

WHILE (1=1) --循环加分，最高分不能超过97分
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--查看更新IsPass列后的成绩和通过情况，可用于调试

/*--------------显示考试最终通过情况--------------*/
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
----------------------------------------------------------------------------------------

--关键字 " in "  返回多条记录

select StudentName from student where LoginPwd in
(select LoginPwd from student  where LoginPwd='111111')

--关键字 " = "  返回单条记录
select StudentName from student where LoginPwd =
(select LoginPwd from student  where LoginPwd='123456')


-----------------------------------------------------------------------------

--查询  最近一次 参加‘opp’ 考试的   最高分 and  最低分

select max([StudentResult])as 最高分,min([StudentResult]) AS 最低分
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 studentName ,studentNo 
  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
	   --有，每人提分 99
	   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
	   --没有，整体+5
	   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

---------------------------------------------------------------------------------------------

---分页：双Top 双Order by  每页显示3条记录,我想要第二页数据 4-6条
select top 3 * from Student
   where StudentNo not in
(
   select top 3 StudentNo from Student
)

--01.分页的第二种方式：  row_number() over(order by xxx) 分页

select * from 
(
 select *,row_number() over(order by studentno) as myid
 from Student
)as temp
where myid between 4 and 6

--------------------------------------------------------------------------------------------- 

--02.go关键字2
select * from student
 go

create table s2226goodperson
(
  sid int identity(1,1) primary key not null,
  sname nvarchar(32)
)
go

--DDL(Data Definition Language) 数据定义语言    建库 ，建表，建约束  go必须另起一行
--DML(Data Manipulation Language) 数据操作语言   Update，insert 。delete
--DQL(Data query language)数据查询语言       select 

---------------------------------------------------------------------------------------------

--03.第三范式
--如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF） 

---------------------------------------------------------------------------------------------

--04.convert(参数1，参数2，参数3) 针对日期类型设定格式
--SQL 获取系统时间
select GETDATE()
select CONVERT(nvarchar(32),GETDATE(),21)