优化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