数据库第三章作业讲评.ppt
第三章作业讲评,1.对于下述三个关系：S(S#,Sn,Sa,Sex),C(C#,Cn,Cteacher,Credit),SC(S#,C#,Grade)使用SQL语句表达下列查询请求：(1)检索学号比wang同学大，而年龄比他小的学生姓名,Select Sn from S where S#(select S#from S where S.Sn=wang)and Sa(select Sa from S where S.Sn=wang),Select S1.Sn from S S1,S S2 where S1.S#S2.S#and S1.SaS2.Sa and S2.Sn=wang,S1，S2均为表别名,第三章作业讲评,(2)检索选修C4课程的女生的平均年龄,(3)检索年龄大于所有女同学年龄的男同学的姓名和年龄,Select avg(Sa)from S,SC where S.S#=SC.S#and C#=C4 and Sex=女,Select avg(Sa)from S where S#in(select S#from SC where C#=C4)and Sex=女,Select Sn,Sa from S where Saall(select Sa from S where Sex=女)and Sex=男,第三章作业讲评,(4)查询1984-1986年出生的学生的学号、总平均成绩及已修学分数。,(5)检索全部学生都选修的课程号和课程名,Select S.S#,avg(Grade),sum(Credit)from S,C,SC where SC.C#=C.C#and SC.S#=S.S#and Year(Getdate()-Sa between 1984 and 1986 group by S.S#,Select C#,Cn from C where not exits(select*from S where not exists(select*from SC where SC.S#=S.S#and SC.C#=C.C#),第三章作业讲评,(6)检索至少选修两门课程的学生姓名,(7)检索王同学不学的课程的课程号和课程名,Select Sn from S,SC where S.S#=SC.S#group by Sn having count(C#)=2,Select Sn from S where S#in(select S#from SC group by Sn having count(C#)=2),Select C#,Cn from C where C#not in(select C#from SC where S#in(Select S#from S where Sn=wang),Select C#,Cn from C where not exists(select*from SC,S where SC.C#=C.C#and SC.S#=S.S#,S.Sn=wang),(Select C#,Cn from C)except(select SC.C#,Cn from SC,C,S where SC.C#=C.C#and SC.S#=S.S#and S.Sn=wang),第三章作业讲评,Select C#,Cn from C where not exists(select*from SC,S where SC.C#=C.C#and SC.S#=S.S#and S.Sn=wang),Select C#,Cn from C where C#not in(select C#from SC,S where SC.S#=S.S#and S.Sn=wang),第三章作业讲评,(8)检索Li老师所授课程的课程号和课程名,(9)统计开设课程的教师人数,Select C#,Cn from C where Cteacher=Li,Select count(distinct Cteacher)from C,第三章作业讲评,(10)检索姓名以L开头的所有学生的姓名和年龄,(11)求每个学生选修课程(已有成绩)的门数和平均成绩,Select Sn,Sa from S where Sn like L%,Select count(C#),avg(grade)from SC where grade is not null group by S#,第三章作业讲评,(12)查询所学每一门课程成绩均不低于该课程平均成绩的学生姓名及相应课程号。,Select Sn,C#from S,SC X where S.S#=X.S#and grade=(select avg(grade)from SC Y where X.C#=Y.C#),第三章作业讲评,2.针对教材P75的SPJ数据库，完成以下查询：,（1）找出北京的任何工程都不购买的零件号,Select PNO from P where PNO not in(select PNO from SPJ,J where SPJ.JNO=J.JNO and City=北京),(Select PNO from P)except(select PNO from SPJ,J where SPJ.JNO=J.JNO and City=北京),Select PNO from P where not exists(select*from SPJ,J where SPJ.JNO=J.JNO and City=北京 and SPJ.PNO=P.PNO),第三章作业讲评,（2）按工程号递增顺序列出每个工程购买的零件总量,（3）列出供应了全部红色零件的供应商名和其所在城市,Select sum(QTY)from SPJ group by JNO order by JNO,Select SNAME,CITY from S where not exists(select*from P where color=红 and not exists(select*from SPJ where SPJ.PNO=P.PNO and SPJ.SNO=S.SNO),第三章作业讲评,（4）列出供应红色零件的供应商名称,Select SNMAE from S,SPJ,P where S.SNO=SPJ.SNO and SPJ.PNO=P.PNO and color=红,Select SNAME from S where SNO in(select SNO from SPJ where PNO in(select PNO from P where color=红),P128 习题11,Create view SanJianAs select SNO,PNO,QTY from J,SPJWhere J.JNO=SPJ.JNO and JNAME=三建（1）select distinct PNO,QTY from SanJian（2）select SanJian.*from SanJian where SanJian.SNO=S1,
