关闭

sql试题--经典三表问题----4----

798人阅读 评论(0) 收藏 举报
分类:

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

S(S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

C (C#,CN) C#,CN 分别代表课程编号、课程名称

cs (S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

1.      使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名


create table s 
(sid varchar(22),sn varchar(22),sd varchar(22),sa varchar(22));
insert into s values('1','zhangsan','cn','219');
insert into s values('2','zhangsan2','cn','129');
insert into s values('3','zhangsan3','cn','290');
insert into s values('4','zhangsan4','cn','329');

create table c(cid varchar(22), cn varchar(22));
insert into c values('1','yuwen');
insert into c values('2','yuwen');
insert into c values('3','yuwen');
insert into c values('4','税收基础');
insert into c values('4','yuwen2');


create table cs (sid varchar(22),cid varchar(22),g float);
insert into cs values('1','1','473.8');
insert into cs values('1','2','447.8');
insert into cs values('2','1','147.8');
insert into cs values('2','2','247.8');
insert into cs values('3','1','477.8');

insert into cs values('1','4','4522.8');
insert into cs values('2','4','45.8');
insert into cs values('3','4','37.8');
insert into cs values('1','4','87.8');


select s1.sid, s1.sn from s s1 where s1.sid in ( select cs1.sid from cs cs1,c c1 where c1.cid = cs1.cid and c1.cn= '税收基础');


比较复杂的第二种方法:

select s1.sid, s1.sn from s s1 where s1.sid in(
select cs1.sid 
from cs cs1 
	where cs1.cid = 
		(
			select c1.cid from c c1 
			where c1.cn='税收基础'
		)
);

2       使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

select s1.sn, s1.sd from s s1 where s1.sid in ( select cs1.sid from cs cs1,c c1 where c1.cid = cs1.cid);
3   使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
select s1.sn, s1.sd from s s1 where s1.sid not in ( select cs1.sid from cs cs1,c c1 where c1.cid = cs1.cid);

4

使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

select sn,sd
from s
where not exists(
select *
from c
where not exists(
select *
from cs
where sid=s.sid
and cid=c.cid))

SQL 语言中exists,代表‘存在’,带有exists的子查询不返回任何数据,只返回代表‘不’的true和代表‘肯定’的false.not exists表示不存在。选修全部课程的学员姓名和所属单位 用逻辑来表达就是‘任意’课程‘他都选’,转换之后,就变成‘没有’一门课程‘他不选’。第一个not exists代表‘没有’,中间的‘select *
  from c’表示‘一门课程’,因为是从c中选的嘛,第二个not exists 表示‘不’,下面的select *
from sc
where s#=s.s#
and c#=c.c#表示他选,合起来就是‘他不选’

注:参见王珊的数据库系统概论 第三版 P113



查询选修了课程的学员人数


select count(distinct(sid)) from cs;
6 查询选修课程超过5门的学员学号和所属单位


select count(*) from s where sid in(
select sid from cs group by sid having count(sid)=(select count(cid) from c)
) ; 
与第四个非常类似
自己看别人写也不理解,但是自己写了就明白了



稍微变下条件

S(SNO,SNAME) 学生关系。SNO 为学号,SNAME为姓名

C(CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME为课程名,CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE)选课关系。SCGRADE 为成绩

7      找出没有选修过“李明”老师讲授课程的所有学生姓名


第一种方法:

select sn from s where sid not in
(select cs1.sid from cs cs1 where cs1.cid not in
(select c1.cid from c c1 where c1.ct ='laoshi2')
)

第二种方法:
select sn from s where not exists (select * from c,cs where s.sid=cs.sid and cs.cid = c.cid and 
c.ct='laoshi2');

8 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

select s.sid,s.sn,(select avg(g) from cs  where cs.sid=s.sid) from s where s.sid in (select sid from cs where cs.g< '200' group by sid having count(*) >=2);

列出既学过“1”号课程,又学过“2”号课程的所有学生姓名

select s.sn from s where 
s.sid in (select cs.sid from cs ,c  where cs.cid = c.cid and c.cn in ('yuwen','yuwen2') group by cs.sid having count(cs.sid) =2 ) 

第二种方法:非常类似

select s.sn from s where 
s.sid in (select cs.sid from cs ,c  where cs.cid = c.cid and (c.cn ='yuwen' or c.cn='yuwen2') group by cs.sid having count(cs.sid) =2 )

小结

mysql from 表名不能用select代替,
防止出现笛卡尔积就用相对做条件。

having后面不能接字句,只能是函数

10 


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1227529次
    • 积分:19709
    • 等级:
    • 排名:第435名
    • 原创:658篇
    • 转载:660篇
    • 译文:0篇
    • 评论:78条
    最新评论