在Student-Course数据库中实现以下查询:
(1)使用子查询完成
① 查询教师年龄大于“李明”的教师信息。
② 查询学号为191245学生选修课程的授课老师的姓名。
③ 查询年龄大于20岁的男生的选课信息。
④ 使用ANY查询选修了课程号是“C101”课程的学生姓名和所在系。
⑤ 查询没有选修“C201”课程的学生的学号,姓名,性别和所在系。
(2)使用WHERE子句完成
① 查询学号为191245学生选修课程的课程号、课程名和授课老师的姓名。
② 查询机械系学生的学号、姓名、性别、课程号、成绩,并按照成绩的降序排列。
③ 查询讲授“数据库原理”课程的教师的姓名、职称、所在系。
④ 查询先修课的先修课是“程序设计语言”课程的课程号,课程名。
(1)
SELECT *
FROM TEACHER
WHERE AGE >ANY
(
SELECT DISTINCT year(getdate())-year(BIR)
FROM STUDENT
WHERE SN='李明'
)
(2)代码
SELECT TN
FROM TEACHER
WHERE TNO IN
(
SELECT TNO
FROM TC
WHERE CNO IN
(
SELECT CNO
FROM SC
WHERE SNO='191245'
)
)
(3)
SELECT DISTINCT COURSE.*
FROM STUDENT,COURSE,SC
WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND (getdate())-year(STUDENT.BIR)>20 AND STUDENT.SEX='男'
(4)
SELECT SN,DEPT
FROM STUDENT
WHERE SNO=ANY
(
SELECT SNO
FROM SC
WHERE CNO='C101'
)
(5)
SELECT SNO,SN,SEX,DEPT
FROM STUDENT
WHERE SNO NOT IN
(
SELECT SNO
FROM SC
WHERE CNO='C201'
)
2.(1)
SELECT SC.CNO,COURSE.CN,TEACHER.TN
FROM SC,TEACHER,COURSE,TC
WHERE SC.CNO=COURSE.CNO AND TEACHER.TNO=TC.TNO AND SNO='191245'
(2)
SELECT STUDENT.SNO,STUDENT.SN,STUDENT.SEX,SC.CNO ,SC.SCORE
FROM STUDENT,SC
WHERE SC.SNO=STUDENT.SNO
ORDER BY SCORE DESC
(3)
SELECT TEACHER.TN,TEACHER.TITLE,TEACHER.DEPT
FROM TEACHER,TC,COURSE
WHERE TEACHER.TNO=TC.TNO AND TC.CNO=COURSE.CNO
AND CN='数据库原理'
(4)
SELECT FIRST.CNO,SECOND.CN AS'先修课的先修课'
FROM COURSE FIRST,COURSE SECOND
WHERE FIRST.FIRSTCLASS=SECOND.CNO AND SECOND.CN='程序设计语言'