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

转载 2013年12月03日 11:06:32

为管理岗位业务培训信息,建立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 


较常见的SQL面/笔试题

日志     关于我 Andy 一枚流星,在想像的高空逡巡,期待的眼波,寂寞而执着   加博友   关注他 ...
  • chennade1
  • chennade1
  • 2017年01月06日 10:17
  • 788

SQL三表连接查询与集合的并、交、差运算查询

use db_sqlserver2   select 姓名, 工资, 面积, 金额, (工资+金额/1000) as 实发工资 from 职工,仓库, 订购单       where 职工.职工号...
  • u011854789
  • u011854789
  • 2016年08月15日 11:16
  • 8783

几道经典的SQL笔试题目(有答案)

几道经典的SQL笔试题目(有答案)   (1)表名:购物信息 购物人      商品名称     数量 A            甲          2 B            乙   ...
  • Rebirth_Love
  • Rebirth_Love
  • 2016年10月17日 22:35
  • 2321

sql语句查询(经典笔试题)

题目1: 问题描述:  S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名  C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME 为课...
  • lizhengnanhua
  • lizhengnanhua
  • 2013年08月16日 21:51
  • 5509

【笔试/面试】SQL 经典面试题

基本概念 (1)any/all,构成 where 子句的条件判断,any:表示或(or)的概念,all:则表示与(and)的概念,这两个关键字的出现是为了语句的简化; (2)先分组再做聚合,逻辑上也应...
  • lanchunhui
  • lanchunhui
  • 2016年04月05日 23:17
  • 8257

MySQL之一道关于GROUP BY的经典面试题

六月的大师兄 2017-05-02 06:31 有一张shop表如下,有三个字段article,author,price。选出每个author的price最高的记录(要包含所有字段)。 ...
  • u011277123
  • u011277123
  • 2017年05月03日 08:58
  • 580

sql常见面试题(2)

在整理准备数据库面试的过程中,先是在网上一顿海搜,找到历史面试题,然后一个骨头一个骨头的啃完,现在基本上这些问题(或者说叫做实践)都没有问题了。遇到的困难是:PL/SQL居多,T-SQL太少,所以需要...
  • LGTTTT
  • LGTTTT
  • 2016年06月08日 09:13
  • 1878

sql常见面试题 (我觉得太好了)

Sql常见面试题 受用了 1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名 name   kecheng   fenshu 张三     语文       81 张三    ...
  • u012467492
  • u012467492
  • 2015年07月07日 15:29
  • 88161

经典SQL面试题

以下题目都在MySQL上测试可行,有疏漏或有更优化的解决方法的话欢迎大家提出,我会持续更新的:) 有三个表,如果学生缺考,那么在成绩表中就不存在这个学生的这门课程成绩的记录,写一段SQL语句,检...
  • dl0914791011
  • dl0914791011
  • 2013年11月25日 09:48
  • 12083

哈希表查找、哈希冲突-面试题

哈希查找是面试中常见的问题。本文为自己梳理一下知识点。 对于大多数查找算法,其查找效率取决于查找过程的比较次数。比如二叉查找树,二分查找。而有一种查找不需要经过元素间的比较,而直接让表中数据元素的关...
  • joson793847469
  • joson793847469
  • 2016年08月30日 00:13
  • 1568
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sql试题--经典三表问题----4----
举报原因:
原因补充:

(最多只允许输入30个字)