hc学习平台

 找回密码
 立即注册
搜索
热搜: 活动 交友 javakc
 › henry-J › 日志

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

2021-01-08 15:38:12 查看(233) 回复(0)

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);


 SELECT  C.S_ID,S.* FROM(SELECT SC.S_ID,x.C_ID   from c x,sc sc WHERE x.C_ID=sc.C_ID and CN='税收基础')c,s s  WHERE C.S_ID=S.S_ID
 
 
 SELECT  C.S_ID,S.* FROM(SELECT SC.S_ID  from sc sc  WHERE C_ID='C2')c,s s  WHERE C.S_ID=S.S_ID
 
 
  SELECT  C.S_ID,S.* FROM(SELECT SC.S_ID  from sc sc  WHERE C_ID='C5')c,s s  WHERE C.S_ID!=S.S_ID and
s.S_ID ='s6' LIMIT 1


SELECT *,COUNT(x.C_ID)   from c x,sc sc WHERE  x.C_ID=sc.C_ID  and  exists (SELECT * from c ) GROUP BY S_ID
HAVING COUNT(x.C_ID)=5




SELECT count(s.sn)  from sc sc,s s  WHERE sc.S_ID=s.S_ID  GROUP BY C_ID  ORDER BY count(s.sn) DESC LIMIT 1




 SELECT count(s.sn),s.*  from sc sc,s s  WHERE sc.S_ID=s.S_ID  GROUP BY sn  HAVING count(s.sn)>5

评论 (0 个评论)

facelist

全部作者的其他最新日志



站点统计|举报|Archiver|手机版|小黑屋|Comsenz Inc.

GMT+8, , Processed in 0.195171 second(s), 9 queries .

Powered by javakc! X1.0

© 2004-2014 javakc

f1208.com 备案号:京ICP备14030918号-1

返回顶部