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

第三章作业讲评 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)查询所学每一门课程成绩均不低于该课程平均成绩的学生姓