为管理业务培训信息,建立3个表:
S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号,学员姓名,所属单位,学员年龄
C(C#,CN)C#,CN分别代表课程编号,课程名称
SC(S#,C#,G) S#,C#,G分别代表学号,所选的课程编号,学习成绩
(1)使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
select S#,SN from S where S# in(select S# from SC where C# in(select C# from C where CN='税收基础'))
或
select S#,SN from S where S# in(select S# from C,SC where C.C#=SC.C# and CN='税收基础')
(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
select SN,SD from S where S# in(select ditinct S# from SC where C#='C2')
(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
select SN,SD from S where S# not in(select S# from SC where C='C5')
(4)查询选修了课程的学员人数
select count(distinct S#) from SC
(5) 查询选修课程超过5门的学员学号和所属单位?
select SN,SD from S where S# in(select S# from SC group by S# having count(distinct c#)>5)