数据库作业2答案.doc

《数据库作业2答案.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、SQL作业：第三章：课后习题2：(1)selectsno,snamefromstudentwhereheight>1.80(2) selectcno,creditfromcoursewheresemester='秋'andcnolike'CS%';(3)selectsname,c.cno,credit,gradefromstudents,coursec,scwheres.sno=sc.snoandc.cno=sc.cnoandc.cnolike'CS%'andsex='男'andsemester='秋'andgradeisn

2、otnull(4)selectsnamefromstudents,scwheres.sno=sc.snoandcnolike'EE%'andsex='女'or: selectsnamefromstudents,scwheres.sno=sc.snoandcnolike'EE%'andsex='女'groupbys.sno,snamehavingcount(cno)>=1(5)selectsno,count(cno),avg(grade)fromscwheregradeisnotnullgroupbysno(6)selectc

3、no,count(sno),max(grade),min(grade),avg(grade)fromscwheregradeisnotnullgroupbycno(7)selects.sno,snamefromstudents,scwheres.sno=sc.snoands.snonotin(selectsnofromscwheregradeisnull)andgradeisnotnullgroupbys.sno,snamehavingmin(grade)>=80(8)selectsname,sc.cno,creditfro

4、mstudents,coursec,scwheres.sno=sc.snoandc.cno=sc.cnoandgradeisnull(9)selectsnamefromstudents,coursec,scwheres.sno=sc.snoandsc.cno=c.cnoandcredit>=3andgrade<70or:selectsnamefromstudents,coursec,scwheres.sno=sc.snoandsc.cno=c.cnoandcredit>=3groupbysc.sno,snamehavingm

5、in(grade)<70(10)selectsname,avg(grade),sum(credit)fromstudents,coursec,scwheres.sno=sc.snoandsc.cno=c.cnoandyear(bdate)between1974and1976groupbysc.sno,sname习题3：deletefromStudentswhereSNOLIKE'91%'deletefromSCwhereSNOLIKE'91%'习题4：INSERTINTOStudents(SNO,SNAME,SEX,BDAT

6、E,HEIGHT)　VALUES('9409101','何平','女','1977-03-02',1.62)INSERTINTOStudents(SNO,SNAME,SEX,BDATE,HEIGHT)　VALUES('9408130','向阳','男','1976-12-11',1.75)习题5：UPDATECourses　SETCREDIT=3,LHOUR=60　WHERECNAME='CS-221'补充题：试写出下述查询的SELECT命令1.1查c4比c16成绩好,但比c9成绩差的课程及其成绩;selectcourse-

7、name,gradefromstudents4,scsc4,coursecou4,students16,scsc16,coursecou16,students9,scsc9,coursecou9wheres4.sno=sc4.snoandcou4.cno=sc4.cnoands4.sname='c4'ands16.sno=sc16.snoandcou16.cno=sc16.cnoands16.sname='c16'ands9.sno=sc9.snoandcou9.cno=sc9.cnoands9.sname='c9'anda

8、ndsc4.cno=sc16.cnoandsc4.cno=sc9.cnosc4.grade>sc16.gradeandsc4.grade