13课后习题答案

第二章习题答案一、选择题1．A2．A3．A4．C5．D6．A7．D8．B二、判断题1．对2．错3．错4．对三、填空题1．数据结构数据操作完整性约束关系模型层次模型网状模型2．数据结构数据操作3．层次一对多4．网状指针5．1:n6．关系模型关系数据库系统7．实体完整性参照完整性用户定义的完整性四、名词解释1-8略五、简答题1-4略第三章习题答案1－6(略)7．8．RS=AB(R)-AB(AB(R)S-R)R∪SABC123416324271R∩SABC416R－SABC123324RSR.AR.BR.CS.AS.BS.C123416123271416416416271324416324271R⋈R.aS.bSR.AR.BR.CS.AS.BS.C123271416271324271RSABabck-336-9．(1)SNAME(σTEACHER=’Liu’(S⋈SC⋈C))(2)C#(C)—C#(σSNAME=’Wang’(S⋈SC))(3)GRADE(σSNAME=’Wang’∧CNAME=’Math’(S⋈SC⋈C))(4)SNAME(σCNAME=’Database’(S⋈SC⋈C))(5)C#,CNAME((S#,C#(SC)÷ΠS#(S))⋈C)第四章习题答案一、选择题1．B2．C3．B4．C5．C6．A7．A8．B9．C10．D11．A12．A13．D14．D15．D16．C17．B18．A19．B20．C21．D22．A23．C24．A25．B26．B27．ECDBDA二、填空题1．结构化查询语言2．数据定义数据控制3．SELECT4．基本表视图5．基本表或视图定义6．宿主语言7．操作数据库控制程序流程8．集合记录游标9．数据缓冲区10．命名块匿名块定义部分执行部分异常处理部分三、简答题1-3答案略4．(1)SELECTA#，ANAMEFROMAWHEREWQTY=100ORCITY=’长沙’;(2)SELECTA.ANAMEFROMA，B，ABWHEREA.A#=AB.A#ANDB.B#=AB.B#ANDB.BNAME=’书包’;5．(1)SELECTSN，AGE，SEXFROMSWHEREAGE(SELECTAGEFROMSWHERESN=’王华’);(2)SELECTS#FROMSCWHEREC#=’C2’ANDGRADE=ALL(SELECTGRADEFROMSCWHEREC#=’C2’);(3)SELECTS.SN,SC.C#,SC.GRADEFROMS,SCWHERES.S#=SC.S#(4)SELECTS#,SUM(GRADE)FROMSCWHEREGRADE=60GROUPBYS#HAVINGCOUNT(*)=4ORDERBY2DESC6．(1)SELECT*FROMJB(2)SELECT*FROMJBWHERECITY=“上海”(3)SELECTPNFROMPBWHEREWEIGHT=(SELECTMIN(WEIGHT)FROMPB)(4)SELECTSNFROMSPJBWHEREJN=’J1’;(5)SELECTSNFROMSPJBWHEREJN=‘J1’ANDPN=’P1’;(6)SELECTJB.JNAMEFROMJB,SPJBWHEREJB.JN=SPJB.JNANDSPJB.SN=’S1’;(7)SELECTDISTINCTPB.COLORFROMPB,SPJBWHEREPB.PN=SPJB.PNANDSPJB.SN=’S1’;(8)SELECTDISTINCTSNFROMSPJBWHEREJN=’J1’ORJN=’J2’;(9)SELECTDISTINCTSPJB.SNFROMSPJB,PBWHEREPB.PN=SPJB.PNANDSPJB.JN=’J1’ANDPB.COLOR=’红’;(10)SELECTDISTINCTSPJB.SNFROMSPJB,JBWHERESPJB.JN=JB.JNANDJB.CITY=’上海’;(11)SELECTSPJB.SNFROMPB,JB,SPJBWHERESPJB.PN=PB.PNANDJB.JN=SPJB.JNANDPB.COLOR=’红’AND(JB.CITY=’上海’ORJB.CITY=’北京’);(12)SELECTDISTINCTSPJB.PNFROMSB,JB,SPJBWHERESB.SN=SPJB.SNANDJB.JN=SPJB.JNANDSB.CITY=JB.CITY;(13)SELECTSPJB.PNFROMSB,JB,SPJBFWHERESB.SN=SPJB.SNANDJB.JN=SPJB.JNANDSB.CITY=’上海’ANDJB.CITY=’上海’;(14)SELECTDISTINCTSPJB.JNFROMSB,JB,SPJBWHERESB.SN=SPJB.SNANDJB.JN=SPJB.JNANDSB.CITY!=JB.CITY;(15)SELECTDISTINCTJNFROMSPJBWHEREJNNOTIN(SELECTDISTINCTSPJB.JNFROMSB,SPJBWHERESB.SN=SPJB.SNANDSB.CITY=’上海’);(16)SELECTDISTINCTSPJB.SNFROMPB,SPJBWHERESPJB.PNIN(SELECTSPJB.PNFROMSPJB,SB,PBWHERESB.SN=SPJB.SNANDPB.PN=SPJB.PNANDPB.COLOR=’红’);(17)SELECTDISTINCTSPJB.JNFROMSB,PB,SPJBWHERESB.SN=SPJB.SNANDPB.PN=SPJB.PNANDSB.SN=’S1’;(18)SELECTDISTINCTSB.CITY,JB,CITYFROMSB,JB,SPJBWHERESB.SN=SPJB.SNANDJB.JN=SPJB.JN;(19)SELECTDISTINCTSB.CITY,SPJB.PN,JB.CITYFROMSB,JB,SPJBWHERESB.SN=SPJB.SNANDJB.JN=SPJB.JN;(20)SELECTDISTINCTSB.CITY,SPJB.PN,JB.CITYFROMSB,JB,SPJBWHERESB.SN=SPJB.SNANDJB.JN=SPJB.JNANDSB.CITY!=JB.CITY;7．(1)SELECTDISTINCT公司名、地址、电话FROM客户WHERE城市=’上海’;(2)SELECTA.公司名，B.订货日期，B.预付订金，B.发货日期FROM客户A，订单.BWHEREA.客户号=B.客户号;(3)SELECTDISTINCTA.订单号，B.公司名，A.预付订金FROM订单A，客户.BWHEREA.客户号=B.客户号ORDERBYA.预付订金DESC(4)SELECTA.订单号，B.公司名，A.预付订金FROM订单A，客户.B-338-WHEREA.预付订金=(SELECTMAX(A.预付订金)FROM订单A)ANDA.客户号=B.客户号(5)SELECTSUM(预付订金)FROM订单(6)SELECTA.订单号，B.公司名，A.订货日期，A.发货日期，A.交通FROM订单A，客户BWHEREA.客户号=B.客户号GROUPBYA.客户号8．(1)INSERTINTORVALUES(25，’李明’，21，’95031’)(2)INSERTINTOR(NO,NAME,CLASS)VALUES(30,’郑和’，’95031’)(3)UPDATERSETNAME=’王华’WHERENO=10(4)UPDATERSETCLASS=’95091’WHERECLASS=’95101’(5)DELETEFROMRWHERENO=20(6)DELETEFROMRWHERENAMELIKE‘王%’9．(1)SELECTA#，ANAMEFROMAWHEREWQTY=100ORCITY=’长沙’；(2)SELECTA。ANAMEFROMA，B，ABWHEREA.A#=AB.A#ANDB.B#=AB.B#ANDB.BNAME=’书包’(3)SELECTA.NAME,A.CITYFROMA,BWHEREA.A#=AB.A#ANDAB.B#IN(SELECTAB.B#FROMABWHEREA#=’256’);10．(1)CREATESQLVIEWRSTASSELECTR.A，B，C，S.D，E，FFROMR，S，TWHERER.A=S.AANDS.D=T.D；(2)SELECTAVG(C)，AVG(E)FROMRSTGROUPBYA11．(1)CREATTABLE职工(职工号CHAR(10)NOTNULLUNIQUE，姓名CHAR(8)NOTNULL，年龄SMALLINT，性别CHAR(2)，CONSTRAINTC1CHECK(性别IN(‘男’，‘女’)))；CREATTABLE社会团体(编号CHAR(8)NOTNULLUNIQUE，名称CHAR(12)NOTNULL，负责人CHAR(8)，活动地点VARCHAR(50)，CONSTRAINTC2FOREIGNKEY(负责人)REFERENCES职工(职工号))；CREATTABLE参加(职工号CHAR(8)，编号CHAR(8)，参加日期DATA，CONSTRAINTC3PRIMARYKEY(职工号，编号)，CONSTRAINC3FOREIGNKEY(职工号)REFERENCES职工(职工号))；(2)CREATVIEW社团负责人(编号，名称，负责人职工号，负责人姓名，负责人性别)ASSELECT编号，名称，负责人，姓名，性别FROM社会团体，职工WHERE社会团体.负责人＝职工.职工号;CREATVIEW参加人情况(职工号，姓名，社团编号，参加日期)ASSELECT参加.职员号,姓名，社会团体.编号，名称，参加日期FROM职工，社会团体，参加WHERE职工.职工号＝参加.职工号AND参加.编号＝社会团体.编号;(3)SELECT职工号，姓名FROM职工，社会团体，参加WHERE职工.职工号＝参加.职工号AND参加.编号＝社会团体.编号AND社会团体.名称IN(‘唱歌队’，‘篮球队’)；(4)SELECT*FROM职工WHERENOTEXISTS(SELECT*FORM参加WHERE参加.职工号＝职工.职工号)；(5)SELECT*FROM职工WHERENOTEXISTS(SELECT*FROM参加WHERENOTEXISTS(SELECT*FROM社会团体WHERE参加.职工号＝职工.职工号AND参加.编号＝社会团体.编))；(6)SELECT职工号FROM职工WHERENOTEXISTS(SELECT*FROM参加参加1WHERE参加1.职工号＝‘1001’ANDNOTEXISTS(SELECT*FROM参加参加2WHERE参加2.编号＝参加1.编号AND参加2.职工号＝职工.职工号))；(7)SELECTCOUNT(职员号)FROM参加GROUPBY编号；(8)SELECT社会团体.名称，COUNT(参加.职工号)FROM社会团体参加WHERE社会团体.编号＝参加.编号GROUPBY参加编号HAVINGMAX(COUNT(参加.职工号))＝COUNT(参加.职工号))；(9)SELECT社会团体.名称，职工.姓名FROM职工，社会团体，参加WHERE社会团体.编号＝参加.编号AND社会团体.负责人＝职工.职工号GROUPBY参加.编号HAVINGCOUNT(参加.编号)＞100；12．参考需补充的程序段1：execsqldeclarescxcursorforselects#,c#,gradefromscwheres#=:givensno;需补充的程序段2：execsqlfetchfromscxinto:sno,:cno,:g;需补充的程序段3：execsqlupdatescsetgrade=7