优化Myschool数据库设计第四章 使用子查询

USE MySchool
go

--示例1：使用子查询查看年龄比“李斯文”小的学生
SELECT StudentNo, StudentName, Sex, BornDate, Address FROM Student
WHERE BornDate>(SELECT BornDate FROM Student WHERE StudentName = '李斯文')

--示例2：查询“Java Logic”课程等于60分的学生姓名
SELECT StudentName FROM Student Where StudentNo =
(
SELECT StudentNo From Result WHERE StudentResult=60 AND SubjectNo =
(
SELECT SubjectNo From Subject where SubjectName = 'java logic'
)
)

--练习1：查询java logic最近一次开始的最高分和最低分
SELECT MAX(StudentResult),MIN(StudentResult) --最高分，最低分
FROM Result
WHERE SubjectNo =
(
SELECT SubjectNo FROM Subject WHERE SubjectName = 'java logic'--java logic的课程编号
)
AND ExamDate =
(
SELECT MAX(ExamDate) FROM Result WHERE SubjectNo =
(
SELECT SubjectNo FROM Subject WHERE SubjectName = 'java logic'--java logic最近考试日期
)
)

--示例3：使用IN子查询参加java logic最近一次考试的学生姓名
SELECT StudentName
FROM Student WHERE StudentNo IN
(
SELECT StudentNo FROM Result WHERE SubjectNo =
(
SELECT SubjectNo FROM Subject Where SubjectName = 'java logic'
)
AND ExamDate=
(
SELECT MAX(ExamDate) FROM Result WHERE SubjectNo =
(
SELECT SubjectNo From Subject where SubjectName = 'java logic'
)
)
)

--练习2：查询y2学期以外其他学期开设的课程
SELECT SubjectName From Subject
WHERE GradeId IN
(
SELECT GradeId From Grade WHERE GradeName != 'Y2'
)

--示例4：查询未参加“Java Logic”课程最近一次考试的学生名单
SELECT StudentName
FROM Student WHERE StudentNo NOT IN
(
SELECT StudentNo FROM Result WHERE SubjectNo =
(
SELECT SubjectNo FROM Subject Where SubjectName = 'java logic'
)
AND ExamDate=
(
SELECT MAX(ExamDate) FROM Result WHERE SubjectNo =
(
SELECT SubjectNo From Subject where SubjectName = 'java logic'
)
)
)
AND GradeId =
(
SELECT GradeId FROM Subject WHERE SubjectName = 'java logic'
)

--示例5：使用Exist子查询检查“Java Logic”课程最近一次考试。
--如果有 80分以上的成绩，则每人提2分；否则，每人提5分。
--最终的成绩不得大于100分

SELECT '本次Java Logic课程考试学生原始成绩是：'
SELECT * FROM Result WHERE SubjectNo =
(
--查询java logic课程的课程编号
SELECT SubjectNo From Subject WHERE SubjectName = 'java logic'
)
AND ExamDate =
(
--查询java logic课程最后一次考试日期
SELECT MAX(ExamDate) FROM Result WHERE SubjectNo =
(
--查询java logic课程的课程编号
SELECT SubjectNo From Subject WHERE SubjectName = 'java logic'
)
)

IF EXISTS
(
SELECT * FROM Result WHERE SubjectNo =
(
--查询java logic课程的课程编号
SELECT SubjectNo From Subject WHERE SubjectName = 'java logic'
)
AND ExamDate =
(
--查询java logic课程最后一次考试日期
SELECT MAX(ExamDate) FROM Result WHERE SubjectNo =
(
--查询java logic课程的课程编号
SELECT SubjectNo From Subject WHERE SubjectName = 'java logic'
)
)
AND StudentResult>80
)
BEGIN
--修改成绩：对于java logic课程最后一次考试低于98分的学生加2分
UPDATE Result SET StudentResult = StudentResult + 2
WHERE SubjectNo =
(
--查询java logic课程的学号
SELECT SubjectNo FROM Subject WHERE SubjectName = 'java logic'
)
AND ExamDate =
(
--查询java logic课程最近考试时间
SELECT MAX(ExamDate) FROM Result WHERE SubjectNo = (SELECT SubjectNo FROM Subject WHERE SubjectName = 'java logic')
)
AND StudentResult<=98
PRINT '本次Java Logic课程考试部分学生成绩高于80分，每人只加2分，加分后的成绩是：'
END
ELSE
BEGIN
--修改成绩：对于java logic课程最后一次考试低于95分的学生加5分
UPDATE Result SET StudentResult = StudentResult + 5
WHERE SubjectNo =
(
--查询java logic课程的学号
SELECT SubjectNo FROM Subject WHERE SubjectName = 'java logic'
)
AND ExamDate =
(
--查询java logic课程最近考试时间
SELECT MAX(ExamDate) FROM Result WHERE SubjectNo = (SELECT SubjectNo FROM Subject WHERE SubjectName = 'java logic')
)
AND StudentResult<=95
PRINT '本次Java Logic课程考试没有学生成绩高于80分，每人可以加5分，加分后的成绩是：'
END
--输出加分后的结果
SELECT * FROM Result WHERE SubjectNo =
(
--查询java logic课程的课程编号
SELECT SubjectNo From Subject WHERE SubjectName = 'java logic'
)
AND ExamDate =
(
--查询java logic课程最后一次考试日期
SELECT MAX(ExamDate) FROM Result WHERE SubjectNo =
(
--查询java logic课程的课程编号
SELECT SubjectNo From Subject WHERE SubjectName = 'java logic'
)
)

--练习3：--如果有S1的学生，就将他在读年级更新为S2
IF EXISTS
(
--查询S1的学生
SELECT * FROM Student WHERE GradeId =
(
--查询S1的年级编号
SELECT GradeId FROM Grade WHERE GradeName = 'S1'
)
)
BEGIN
--如果存在，则修改为S2
UPDATE Student SET GradeId =
(
--查询S2的年级编号
SELECT GradeId FROM Grade WHERE GradeName = 'S2'
)
WHERE GradeId =
(
SELECT GradeId FROM Grade WHERE GradeName = 'S1'
)
END

--示例6：出现在select子句和from子句中的子查询
--输出成绩单（学生姓名、课程所属的年级名称、课程名称、考试日期、考试成绩）
SELECT StudentName AS 姓名,subjectName AS 课程名称,
(SELECT GradeName FROM Grade Where GradeID = Subject.GradeId) AS 所在年级,
ExamDate AS 考试日期,StudentResult AS 考试成绩
FROM Result INNER JOIN Student ON Result.StudentNo = Student.StudentNo
INNER JOIN Subject ON Result.SubjectNo = Subject.SubjectNo

SELECT temp.StudentName,temp.LoginPwd
FROM (select * from Student) temp