码农

USE MySchool2
GO


SELECT * FROM Student
SELECT * FROM Result
SELECT * FROM Subject2


--编写T-SQL语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息 
SELECT * FROM Student WHERE BornDate>(SELECT  BornDate  FROM Student WHERE StudentName='李斯文')


--查询“Java Logic”课程至少一次考试刚好等于60分的学生
select StudentName from Student where StudentNo in (select StudentNo from Result where SubjectNo in (SELECT SubjectNo from Subject2 where SubjectName= 'Java Logic ')  AND   StudentResult=60 ) 


--指导——查询指定学生成绩2-1


--查询参加最近一次“Java Logic”考试成绩最高分和最低分
--1.查询获得“Java Logic”课程的课程编号
SELECT SubjectNo from Subject2 where SubjectName='Java Logic'
--2.查询获得“Java Logic”课程最近一次的考试日期
select MAX(ExamData) from Result where SubjectNo=(SELECT SubjectNo from Subject2 where SubjectName='Java Logic')
--3.根据课程编号查询考试成绩的最高分和最低分
SELECT MAX(StudentResult) as 最高分, MIN(StudentResult) 
from Result 
where SubjectNo =(SELECT SubjectNo from Subject2 where SubjectName='Java Logic')  
and ExamData =(select MAX(ExamData) from Result)


--指导——查询某学期开设的课程2-2


--1.查询获得年级名称是S1的所有课程的课程编号
select GradeID from Grade1 where  GradeName='S1'
--2.根据课程编号查询课程表得到课程名称
SELECT SubjectName 
from Subject2 
WHERE GradeID = (select GradeID from Grade1 where  GradeName='S1')


--查询未参加“Java Logic”课程最近一次考试的在读学生名单
--1.获得Java Logic课程的课程编号
SELECT SubjectNo from  Subject2 WHERE SubjectName='Java Logic'
--2.根据课程编号查询得到Java Logic课程最近一次的考试日期
select MAX(ExamData) from Result 
where SubjectNo=(SELECT SubjectNo from  Subject2 WHERE SubjectName='Java Logic')
--3.根据课程编号和最近一次的考试日期查询出学生名单
--4.通过NOT IN关键字查出没有参加最近一次考试的在读学生名单
--5.限定Java Logic课程所在学期
select StudentNo ,StudentName 
from Student 
where StudentNo not in( select StudentNo from Result where SubjectNo=(SELECT SubjectNo from  Subject2 WHERE SubjectName='Java Logic') 
  and ExamData=(select MAX(ExamData) from Result) )   
AND GradeId=(SELECT GradeID FROM Subject2 where SubjectName='Java Logic')




--检查“Java Logic”课程最近一次考试。
--如果有 80分以上的成绩，则每人提2分；否则，每人提5分。最终的成绩不得大于100分
--第一步：采用EXISTS检测是否有人考试成绩达到80分以上
select StudentNo ,studentResult from Result where StudentResult>80 AND SubjectNo=(select SubjectNo from Subject2 where SubjectName='Java Logic')
--IF exists (select * from Result where StudentResult>80 AND SubjectNo=(select SubjectNo from Subject2 where SubjectName='Java Logic'))
--第二步：如果成绩有80分以上的，使用UPDATE语句为参加本次考试的每名学生加2分；否则加5分
IF exists (
select * 
from Result 
where SubjectNo=(
select SubjectNo 
from Subject2 
where SubjectName='Java Logic') 
and ExamData=(
select MAX(ExamData) 
from Result)
and StudentResult>80)
BEGIN
 PRINT '考试成绩还可以'
 UPDATE  Result
 SET StudentResult+=3
where SubjectNo=(
select SubjectNo 
from Subject2 
where SubjectName='Java Logic') 
and ExamData=(
select MAX(ExamData) 
from Result)
and StudentResult<=98
 END
 ELSE
 BEGIN
 PRINT '考试成绩不行'
 UPDATE  Result
 SET StudentResult+=5
where SubjectNo=(
select SubjectNo 
from Subject2 
where SubjectName='Java Logic') 
and ExamData=(
select MAX(ExamData) 
from Result)
and StudentResult<=95
 END
 select StudentNo ,studentResult from Result where StudentResult>80 AND SubjectNo=(select SubjectNo from Subject2 where SubjectName='Java Logic')


 
 --检查“Java Logic”课程最近一次考试。
 --如果全部没有通过考试（即：60分及格），
 --则试题偏难，每人加3分，否则，每人只加1分
 select StudentNo ,studentResult from Result WHERE StudentResult>=60 AND SubjectNo=(SELECT SubjectNo FROM Subject2 WHERE SubjectName ='Java Logic') and ExamData=(SELECT MAX(ExamData) FROM Result) 
 --NOT EXISTS
  IF NOT EXISTS ( SELECT * FROM Result WHERE StudentResult>=60 AND SubjectNo=(SELECT SubjectNo FROM Subject2 WHERE SubjectName ='Java Logic') and ExamData=(SELECT MAX(ExamData) FROM Result))
  BEGIN
  PRINT '试题偏难'
  UPDATE Result 
  SET StudentResult+=3 
  WHERE StudentResult>=60 
  AND SubjectNo=(SELECT SubjectNo FROM Subject2 WHERE SubjectName ='Java Logic') and ExamData=(SELECT MAX(ExamData) FROM Result) AND StudentResult<=97
  END
  
 ELSE
 BEGIN
 PRINT'还可以'
 UPDATE Result 
 SET StudentResult+=1 
 WHERE StudentResult>=60 
 AND SubjectNo=(SELECT SubjectNo FROM Subject2 WHERE SubjectName ='Java Logic') and ExamData=(SELECT MAX(ExamData) FROM Result) AND StudentResult<=99
 END
  select StudentNo ,studentResult from Result WHERE StudentResult>=60 AND SubjectNo=(SELECT SubjectNo FROM Subject2 WHERE SubjectName ='Java Logic') and ExamData=(SELECT MAX(ExamData) FROM Result)


--如果有S1的学生，就将他在读年级更新为S2
--检测是否有S1的学生记录
SELECT * FROM Student WHERE GradeId=(select GradeID from Grade1 where GradeName='S1')
--用UPDATE语句将学生表中S1 对应的年级编号更新为S2的年级编号
IF EXISTS (SELECT * FROM Student WHERE GradeId=(select GradeID from Grade1 where GradeName='S1') )
  BEGIN
  UPDATE Student SET GradeId=(select GradeID from Grade1 where GradeName='S2')
  END
ELSE
  PRINT'完美，没有说明要修改的.'
  SELECT * FROM Student WHERE GradeId=(select GradeID from Grade1 where GradeName='S2')