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