数据库第三章作业讲评.ppt

《数据库第三章作业讲评.ppt》由会员分享，可在线阅读，更多相关《数据库第三章作业讲评.ppt（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第三章作业讲评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 SaS2.S#and S1.SaS2.Sa and S2.Sn=wangS1，S2 均为表别名第三章作业讲评(2)检索选修C4 课程的女生的平均年龄(3)检索年龄大于所有女同学年龄的男同学的姓名和年龄Select avg(Sa)from

2、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

3、#=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#)

4、=2Select 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

5、 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)统计开设课程的教师人数Se

6、lect C#,Cn from C where Cteacher=LiSelect 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

7、,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=北京)Sel

8、ect 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*fr

9、om 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 习题11Create 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