第三章课后作业第三课后作业.doc

第三章习题 2013312055 王洁 习题3.2 (1) 检索年龄小于17岁的男学生的学号和姓名。 SELECT SNO SNAME FROM S WHERE AGE17 AND SEX=M (2) 检索男学生所学课程的课程号和课程名。 SELECT CNO,CNAME FROM C WHERE CNO IN(SELECT CNO FROM SC WHERE SNO IN(SELECT SNO FROM S WHERE SEX=M) ); (3) 检索男学生所学课程的任课老师的工号和姓名 SELECT TNO,TNAME FROM T WHERE TNO IN(SELECT TNO FROM C WHERE CNO IN(SELECT CNO FROM SC WHERE SNO IN(SELECT SNO FROM S WHERE SEX=M))); (4) 检索至少选修两门课程的学生学号。 SELECT DISTINCT X.SNO FROM SC AS X, SC AS Y WHERE X.SNO = Y.SNO AND X.CNO != Y.CNO （5）至少有学号选修的课程的课程号SELECT DISTINCT X.CNO FROM SC AS X, SC AS Y WHERE X.CNO = Y.CNO AND X.SNO = 01 AND Y.SNO = 02; （6）小红同学不学的课程的课程号SELECT CNO FROM C WHERE NOT EXISTS(SELECT * FROM S,SC WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO AND SNAME = 小红); ）SELECT CNO,CNAME FROM C WHERE NOT EXISTS(SELECT * FROM S WHERE NOT EXISTS(SELECT * FROM SC WHERE SC.SNO = S.SNO AND SC.CNO = C.CNO)); （8）检索选修课程中包含马艳老师所授全部课程的学生学号 SELECT DISTINCT SNO FROM SC AS X WHERE NOT EXISTS (SELECT* FROM C,T WHERE C.TNO=T.TNO AND Tname=杨红 AND NOT EXISTS (SELECT* FROM SC AS Y WHERE Y.SNO=X.SNO AND Y.CNO=X.CNO)); 习题3.4 设有两个基本表R(A,B,C)和S(A,B,C),试用SQL查询语句表达下列关系代数表达式： ①?(SELECT?*?FROM?R)???UNION? ??(SELECT?*?FROM?S)；? ②?(SELECT?*?FROM?R)???INTERSECT? ?(SELECT?*?FROM?S)； ?③?(SELECT?*?FROM?R)???MINUS? ?(SELECT?*?FROM?S)； ?④??SELECT?*? FROM?R,?S；? ⑤??SELECT?R.A,?R.B,?S.C? FROM?R,?S? WHERE?R.B=S.B；? ⑥??SELECT?R.A,?S.C? FROM?R,?S? WHERE?R.C=S.A；? ⑦??SELECT?R.*???（R.*表示R中全部属性）? FROM?R,?S? WHERE?R.C=S.C； ⑧SELECT?A,?B? FROM?R??RX? WHERE?NOT?EXISTS? (?SELECT?*?FROM?S? WHERE?NOT?EXISTS? (?SELECT?*?FROM?R??RY? WHERE?RY.A=RX.A?AND?RY.B=RX.B?AND?RY.C=S.C))； 习题3.7 试用SQL查询语句表达对的教学数据中基本表查询： 有学生选修的课程门数 FROM?C； ?求选修的女学生的平均年龄 FROM?S,?SC