为管理岗位业务培训信息，建立3个表:

S (S_ID,SN,SD,SA)   S_ID,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C_ID,CN)         C_ID,CN       分别代表课程编号、课程名称
SC (S_ID,C_ID,G)      S_ID,C_ID,G     分别代表学号、所选修的课程编号、学习成绩
 
要求实现如下6个处理：
1.查询选修课程名称为'税收基础'的学员信息
2.查询选修课程编号为'C2'的学员信息
3.查询没有选修课程编号为'C5'的学员信息
4.查询选修全部课程的学员信息
5.查询选修了课程的学员人数

6.查询选修课程超过5门的学员信息

--创建测试数据
create table S(S_ID varchar(10),SN varchar(10),SD varchar(10),SA DATE);
insert into s values('S1' , '赵三' , '税务局' , '1981-01-01');
insert into s values('S2' , '钱四' , '司法局' , '1982-02-02');
insert into s values('S3' , '孙五' , '农业局' , '1983-03-03');
insert into s values('S4' , '李六' , '矿务局' , '1984-04-04');
insert into s values('S5' , '周七' , '工商局' , '1985-05-05');
insert into s values('S6' , '王八' , '财政局' , '1986-06-06');
create table C(C_ID varchar(10),CN varchar(10));
insert into c values('C1' , '税收基础');
insert into c values('C2' , '会计基础');
insert into c values('C3' , '法律基础');
insert into c values('C4' , '农业基础');
insert into c values('C5' , '工业基础');
insert into c values('C6' , '管理基础');
create table SC(S_ID varchar(10),C_ID varchar(10),G int);
insert into sc values('S1' , 'C1' , 11);
insert into sc values('S1' , 'C2' , 12);
insert into sc values('S1' , 'C3' , 13);
insert into sc values('S1' , 'C4' , 14);
insert into sc values('S1' , 'C5' , 15);
insert into sc values('S1' , 'C6' , 16);
insert into sc values('S2' , 'C1' , 61);
insert into sc values('S2' , 'C2' , 62);
insert into sc values('S2' , 'C3' , 63);
insert into sc values('S2' , 'C4' , 64);
insert into sc values('S2' , 'C5' , 65);
insert into sc values('S2' , 'C6' , 66);
insert into sc values('S3' , 'C1' , 71);
insert into sc values('S3' , 'C2' , 72);
insert into sc values('S3' , 'C3' , 73);
insert into sc values('S3' , 'C4' , 74);
insert into sc values('S3' , 'C5' , 75);
insert into sc values('S3' , 'C6' , 76);
insert into sc values('S4' , 'C1' , 91);
insert into sc values('S4' , 'C2' , 92);
insert into sc values('S4' , 'C3' , 93);
insert into sc values('S4' , 'C4' , 94);
insert into sc values('S4' , 'C5' , 95);
insert into sc values('S4' , 'C6' , 96);
insert into sc values('S5' , 'C1' , 81);
insert into sc values('S5' , 'C3' , 83);
insert into sc values('S5' , 'C5' , 85);

-- 1

select s.*,c.CN from s, sc,c where s.S_ID=sc.S_ID and c.C_ID=sc.C_ID and c.CN='税收基础'

-- 2

select s.* ,sc.C_ID from s,sc where s.S_ID=sc.S_ID  and sc.C_ID='c2'

-- 3

select s.*,sc.C_ID from s left join sc  on s.S_ID=sc.S_ID  and  sc.C_ID !='c5'

-- 4

select * from s where S_ID

 in (select S_ID from (select sc.S_ID, count(1) x from sc group by sc.S_ID having x>5) c)

select * from (select S_ID from (select sc.S_ID, count(1) x from sc group by sc.S_ID having x>5) c) y,s where s.S_ID=y.S_ID

select * from s where exists

(select * from (select sc.S_ID, count(1) x from sc group by sc.S_ID having x>5) c  where c.S_ID=s.S_ID)

-- 5

select S_ID,COUNT(1) from sc group by S_ID

SELECT count(1) from (select S_ID,COUNT(1) from sc group by S_ID) c

-- 6

select * from s where S_ID

  in (select S_ID from (select sc.S_ID, count(1) x from sc group by sc.S_ID having x>5) c)