第4题:
在教学管理系统中,有教师关系T(T#,NAME),学生关系S(S#,NAME),学生成绩C(S#,NU)。其中T#表示教师工号,S#表示学生学号,则T和S存在的关系为()。
A.1:1
B.1:N
C.M:N
D.无联系
①检索年龄小于17岁的女学生的学号和姓名。 SELECT S#.SNAME FROM S WHERE AGE<17 AND SEX=’F’ ②检索男学生所学课程的课程号和课程名。 SELECT C.C#,CNAME (连接查询方式) FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# AND SEX=’M’; ③检索男学生所学课程的任课老师的工号和姓名。 SELECT T.T#, TNAME FROM S,SC,C,T WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# AND SEX=’M’; ④检索至少选修两门课程的学生学号。 SELECT DISTINCT X.S# FROM SC AS X, SC AS Y WHERE X.S#=Y.S# AND X.C#!=Y.C#; ⑤检索至少有学号为S2和S4的学生选修的课程的课程号。 SELECT DISTINCT X.C# FROM SC AS X, SC AS Y WHERE X.S#=’S2’ AND Y.S#=’S4’ AND X.C#=Y.C#; ⑥检索WANG同学不学的课程的课程号。 SELECT C# FROM C WHERE NOT EXISTS (SELECT * FROM S, SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=’WANG’); ⑦检索全部学生都选修的课程的课程号与课程名。 SELECT C#,CNAME FROM C WHERE NOT EXISTS (SELECT * FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C.C#)); ⑧检索选修课程包含LIU老师所授全部课程的学生学号。 法一: SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTS (SELECT * FROM C,T WHERE C.T#=T.T# AND TNAME=’LIU’ AND NOT EXISTS (SELECT{ FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#)); 法二: SELECT DISTINCT S# FROM SC X WHERE NOT EXISTS ((SELECT C# FROM C,T WHERE C.T#=T.T# AND TNAME='LIU’) EXCEPT (SELECT C# FROM SC Y WHERE Y.S#=X.S#));