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.*
from s,c,sc
where CN='税收基础' and s.S_ID=sc.S_ID and c.C_ID=sc.C_ID
-- 2.查询选修课程编号为'C2'的学员信息
select s.*
from s,sc
where sc.C_ID ='C2' and s.S_ID = sc.S_ID
-- 3.查询没有选修课程编号为'C5'的学员信息
select s.*,sc.C_ID
from s,sc
where sc.C_ID !='C5' and s.S_ID = sc.S_ID
-- 4.查询选修全部课程的学员信息(使用exists查询)
select * from s where exists (select * from(select S_ID,count(C_ID) from sc GROUP BY S_ID HAVING count(C_ID)>5 )w where w.S_ID=s.S_ID)
-- 5.查询选修了课程的学员人数
select count(SN) from (select SN,count(1) from s,sc where s.S_ID = sc.S_ID and sc.C_ID is not null GROUP BY SN)a
-- 6.查询选修课程超过5门的学员信息
select * from s where exists (select * from(select S_ID,count(C_ID) from sc GROUP BY S_ID HAVING count(C_ID)>5 )w where w.S_ID=s.S_ID)