实验五嵌套子查询设计

a)查询选修了数据结构与算法的学生学号和姓名。 select Student.S_ID,Student.S_Name from Student,Course,SC where

Student.S_ID=SC.S_ID

and

Course.C_ID=SC.C_ID

and

Student.S_ID=SC.S_ID and C_Name='数据结构与算法'

b)查询07294002课程的成绩低于孙云禄的学生学号和成绩 select Student.S_ID,SC.EXAM_Grade from Student,SC

where Student.S_ID=SC.S_ID and SC.EXAM_Grade<(select SC.EXAM_Grade from Student,SC

where Student.S_ID=SC.S_ID and Student.S_Name='孙云禄' and SC.C_ID='07294002')

c)查询和孙云禄同年出生的学生的姓名和出生年份。

select S_Name,Date_of_Birth from Student

where Date_of_Birth like (select left(Date_of_Birth,0) from Student where S_Name='孙云禄')

d)查询其他系中年龄小于数学与信息技术学院年龄最大者的学生。 select * from Student

where DEPT_ID<>'数学与信息技术学院' and year(getdate())-year(Date_of_Birth)<all (select

max(year(getdate())-year(Date_of_Birth)

)

as

年龄

from

Student,Department

where Student.DEPT_ID=Department.DEPT_ID and DEPT_Name='数学与信息技术学院')

e)查询其他系中比数学与信息技术学院学生年龄都小的学生。 select * from Student

where DEPT_ID<>'数学与信息技术学院' and year(getdate())-year(Date_of_Birth) >all

(select max(year(getdate())-year(Date_of_Birth) ) as 年龄 from Student where DEPT_ID='数学与信息技术学院')

f)查询同孙云禄数据库原理与应用课程分数相同的学生的学号和姓名。 select Student.S_ID,Student.S_Name from Student,Course,SC where Student.S_ID=SC.S_ID and Course.C_ID=SC.C_ID

and Student.S_ID=SC.S_ID and SC.EXAM_Grade=(select SC.EXAM_Grade from Student,Course,SC

where Student.S_ID=SC.S_ID and Course.C_ID=SC.C_ID

and Student.S_ID=SC.S_ID and Student.S_Name='孙云禄' and C_Name='数据库原理与应用')

g)查询选修了07294002课程的学生姓名。 select S_Name from Student,SC

where Student.S_ID=SC.S_ID and SC.C_ID='07294002'

h)查询没有选07294002课程的学生姓名。 select S_name from Student

where not EXISTS(select * from SC

where S_ID = Student.S_ID and C_ID='07294002')

i)查询同时选修了07295006和07295007课程的学生的学号。 select S_Name from Student,SC

where Student.S_ID=SC.S_ID and SC.C_ID ='07295006' and SC.C_ID ='07295007'

j)查询所有未授课的教师的工号、姓名和院系，结果按院系升序排列。 select

Teacher.T_ID,Teacher.T_Name,Department.DEPT_Name

from

Teacher,Department,TC

where Teacher.T_ID=TC.T_ID and Department.DEPT_ID=Teacher.DEPT_ID and TC.C_ID=NULL or

der by Department.DEPT_Name