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);
select sc.s_ID from sc where sc.C_ID='C2'
select s.* from s where s.S_ID in (select sc.s_ID from sc where sc.C_ID='C2')
select sc.S_ID from sc where sc.C_ID='C5'
select s.* from s where s.S_ID not in (select sc.s_ID from sc where sc.C_ID='C5')
--
select S_ID,COUNT(sc.C_ID) a from sc group by sc.S_ID
select s.* from (select S_ID,COUNT(sc.C_ID) a from sc group by sc.S_ID) t,s exists t.a>=5 where t.S_ID=s.S_ID
select S_ID,COUNT(sc.C_ID) a from sc group by sc.S_ID HAVING a>5
select * from s where exists(select S_ID,COUNT(sc.C_ID) a from sc group by sc.S_ID HAVING a>5 and s.S_ID=sc.S_ID)