P127第3章SQL练习答案.docVIP

第2章第5题 Select SNO From SPJ Where JNO=’J1’ Select SNO From SPJ Where JNO=’J1’ And PNO=’P1’ Select SNO From SPJ,P Where SPJ.PNO=P.PNO And JNO=’J1’ And Color=’红’ select distinct jno from spj Where JNO not in (select distinct jno from spj,s,p where spj.sno=s.sno and p.pno=spj.pno and city=天津and color=红) select distinct jno from spj spjx where not exists(select * from spj spjy where spjy.sno=S1 and not exists (select * from spj spjz where spjz.jno=spjx.jno and spjz.pno=spjy.pno)) 第3章第5题 Select Sname,City From S Select Pname,Color,Weight From P Select Distinct JNO From SPJ Where SNO= S1 Select PNAME,QTY From SPJ,P Where SPJ.PNO=P.PNO And JNO= J2 Select Distinct PNO From SPJ ,S Where SPJ.SNO=S.SNO And City= 上海 Select Distinct Jname From Spj,J,S Where SPJ.JNO=J.JNO And SPJ.SNO=S.SNO And S.City= 上海 Select Distinct Jno From Spj Where Jno Not In (Select Distinct Jno From Spj,S Where Spj.Sno=S.Sno And City=天津) Update P Set Color=’蓝’ Where Color=红 Update Spj Set Sno=S3 Where Sno=S5 And Jno=J4 And Pno=P6 Delete From SPJ Where SNO=S2 Delete From S Where SNO=S2 Insert into SPJ Values (S2, J6, P4,200) 图书管理关系数据模型练习题答案： Select 单位 From 读者 Where 姓名=’张平’ Select 书名,作者 From 图书 Where 出版单位=‘人民出版社’ or出版单位=‘科学出版社’ Select 书名 From 图书 Where 单价 Between 10 And 15 Order By 单价 Desc Select 姓名，借阅日期 From 读者, 借阅 Where 借阅.借书证号=读者.借书证号 Select 图书.* From 图书,借阅 Where 图书.总编号=借阅.总编号 and 单价20 Order By 单价 Desc Select 图书.* From 图书 Where 出版单位= (Select 出版单位 From 图书 Where 书名=’数据库原理’ ) Select Count(*) From 读者 Where 单位=’计算机科学系’ Group By 单位 Select Max(单价), Min(单价), Avg(单价) From 图书 Group By 出版单位 Select 姓名,单位 From 读者 Where 借书证号 Not In (Select Distinct 借书证号 From 借阅 ) Select 姓名,单位 From 图书,读者,借阅 Where 图书.总编号=借阅.总编号 And 借阅.借书证号=读者.借书证号 And 书名=’数据结构’ Select 姓名,单位