数据库第三章习题参考答案.doc

3 -2 对于教务管理数据库的三个基本表 S(SNO,SNAME, SEX, AGE,SDEPT) SC(O,GRADE) AME,CDEPT,TNAME) 试用 SQL 的查询语句表达下列查询: ⑴检索 LIU 老师所授课程的课程号和课程名。⑵检索年龄大于 23岁的男学生的学号和姓名。⑶检索学号为 200915146 的学生所学课程的课程名和任课教师名。⑷检索至少选修 LIU 老师所授课程中一门课程的女学生姓名。⑸检索 WANG 同学不学的课程的课程号。⑹检索至少选修两门课程的学生学号。⑺检索全部学生都选修的课程的课程号与课程名。⑻检索选修课程包含 LIU 老师所授课程的学生学号。解: ⑴SELECT C#,CNAME FROM CWHERE TEACHER= ’LIU ’; ⑵SELECT S#,SNAME FROM SWHERE AGE>23 AND SEX= ’M’; ⑶AME ,TEACHER FROM SC,C WHERE #=# AND S#= ’200915146 ’⑷SELECT SNAME (连接查询方式) FROM S,SC,C WHERE #=# AND #=# AND SEX= ’F’AND TEACHER= ’LIU ’;或: SELECT SNAME (嵌套查询方式) FROM SWHERE SEX= ’F’AND S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM CWHERE TEACHER= ’LIU ’)) 或: SELECT SNAME (存在量词方式) FROM SWHERE SEX= ’F’AND EXISTS(SELECT* FROM SC WHERE #=# AND EXISTS(SELECT * FROM CWHERE #=# AND TEACHER= ’LIU ’)) ⑸SELECT C# FROM CWHERE NOT EXISTS (SELECT *FROM S,SC WHERE #=# AND #=# AND SNAME= ’WANG)); ⑹SELECT DISTINCT # FROM SC AS X,SC AS Y WHERE #=# AND #!=#; ⑺SELECT C#.CNAME FROM CWHERE NOT EXISTS (SELECT * FROM SWHERE NOT EXISTS (SELECT *FROM SC WHERE S#=# AND C#=#)); ⑻SELECT DISTINCT S# FROM SC AS XWHERE NOT EXISTIS (SELECT *FROM CWHERE TEACHER= ’LIU ’AND NOT EXISTS (SELECT *FROM SC AS YWHERE #=# AND #=#)); 3 -3试用 SQL 查询语句表达下列对 题中教务管理数据库的三个基本表 S、 SC 、 C查询: ⑴统计有学生选修的课程门数。⑵求选修 4号课程的学生的平均年龄。⑶求 LIU 老师所授课程的每门课程的学生平均成绩