试题"/>
经典试题
题目1:问题描述:
S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
1.(1)找出选修过“李明”老师讲授课程的所有学生姓名
--实现代码:法一
Select *
FROM SC,C,S
Where SC.CNO=C.CNO AND SC.SNO=S.SNO AND CTEACHER='李明'
---法二:-------------
Select *
FROM S
Where Sno IN( Select Sno
FROM C,SC
Where C.[Cno]=SC.[Cno] AND CTEACHER='李明')
(2)找出没有选修过“李明”老师讲授课程的所有学生姓名
--实现代码:法一
Select SName
FROM S
Where [Sno] NOT IN( Select SC.[Sno]
FROM SC,C
Where SC.CNO=C.CNO AND CTEACHER='李明')
(3)找出没有选修过课程号为"1"的所有学生姓名
--实现代码
Select SName
FROM S
Where [Sno] NOT IN( Select [Sno] FROM SC Where CNO='1')
------------------------------------------------------------------------------------------------------------------------------------------
2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
--实现代码:
Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,( Select SNO
FROM SC
Where SCGRADE<60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)>=2 )A
Where S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
--------------------------------------------------------------------------------------------------------------------------------------
3. (1)列出既学过“语文”课程,又学过“数学”课程的所有学生姓名
--实现代码:法一
Select S.SNO,S.SNAME
FROM S,(
Select SC.SNO
FROM SC,C
Where SC.CNO=C.CNO AND C.CNAME IN('语文','数学')
GROUP BY SNO
HAVING COUNT(DISTINCT c.CNO)=2 )SC
Where S.SNO=SC.SNO
---法二:--------------------------
select *
from (select s.sno from s,sc,c where s.sno=sc.sno and sco=co and came='语文')t1,
(select s.sno from s,sc,c where s.sno=sc.sno and sco=co and came='数学')t2,
s
where t1.sno=t2.sno and t1.sno=s.sno
------------------
(2)使用标准SQL嵌套语句查询选修全部课程的学员姓名
法一:一句SQL语句即可。注意有下划线的部分!
Select S.SNO,S.SNAME
FROM S,(
Select SNO
FROM SC
GROUP BY SNO
HAVING COUNT(*)=(select count(*) from c) )SC
Where S.SNO=SC.SNO
(3)查询选修课程超过2门的学员学号
--实现代码:
Select Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)>2
查询选修课程超过2门的学员学号及姓名
--实现代码:
Select SNo,SNAME
FROM S
Where Sno IN(Select Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)>2)
(4)列出人数大于3的各科最高成绩的列表,显示科目号、成绩两个字段
---正解如下:(科目号为2、3的选课人数大于3人)
Select cno,max(scgrade)as'最高分'
FROM SC
Where cno IN (Select cno FROM SC GROUP BY cno HAVING COUNT(sno)>3)
group by cno
order by cno
-----------------------------------------------------------------------------------------------------------------------------------
4. 列出“语文”课比“数学”课同学该门课成绩高的所有学生的学号、姓名及分数
--实现代码:法一
select *
from (select s.sno,s.sname,sc.SCGRADE from s,sc,c
where s.sno=sc.sno and sco=co and came='语文' )t1,
(select s.sno,s.sname,sc.SCGRADE from s,sc,c
where s.sno=sc.sno and sco=co and came='数学' )t2
where t1.sno=t2.sno and t1.SCGRADE>t2.SCGRADE
------------------------------------------------------------------------------------------------------------------------------------
5. 查询选修了课程的学员人数
--实现代码:
Select 学员人数=COUNT(DISTINCT [Sno]) FROM SC
总结:
1、select * from a,b where a.id=b.id 和
select * from a inner join b on a.id=b.id 结果集相同;
2、在作“找出没有选修过“李明”老师讲授课程的所有学生姓名”这种题时,
--用Where [Sno] NOT IN(在此可以是SQL语句生成的一列值)比较好。
Select SName
FROM S
Where [Sno] NOT IN( Select SC.[Sno]
FROM SC,C
Where SC.CNO=C.CNO AND CTEACHER='李明')
3、可以将select count(*) from c直接代入SQL语句,而不是非要写成存储过程;
Select S.SNO,S.SNAME
FROM S,(
Select SC.SNO
FROM SC,C
Where SC.CNO=C.CNO
GROUP BY SNO
HAVING COUNT(*)=(select count(*) from c) )SC
Where S.SNO=SC.SNO
题名是这样的:
studentname classname grade
mike english 65
mike math 75
Jerry english 80
Jerry math 68
Lida english 88
Lida chinese 77
Lida math 90
求出各科成绩都大于75分的学生。
即得到如下结果
studentname classname grade
Lida english 88
Lida chinese 77
Lida math 90
select t1.studentname,classname,grade
from # t1,(select studentname
from #
where grade>75
group by studentname
having count(*)=(select count(*) from (select distinct classname from #)A)) t2
where t1.studentname=t2.studentname
注:
1、from (select distinct classname from #)A 中的"A"一定要有,将(select distinct classname from #)得到的记录集看成一个新的表A。
2、在SQL语句中,用group by 语句时,一般都会用到count(),sum(),avg(),max(),min()等函数;
但,当不使用group by语句,而直接使用这些函数时,则表示将所有数据集看成一个组来处理。
第二题:
已知一个关系数据库的模式如下:
职工EMP(职工号,姓名,工资,所在部门)
部门DEPT(部门号,部门名,部门经理的职工号)
(1)、使用查询分析器建表,要求如下:
A、每个表的主外码。
B、职工姓名和部门名不能为空。
C、工资取800到5000这间整数。
(2)、插入如下数据:
职工(E01 赵 2500 D02
E02 钱 3000 D02
E03 孙 6000 D01
E04 李 5500 D01
E05 周 3500 D03
E06 吴 2000 D04
E07 郑 3000 D04 )
部门(D01,人事,E04
D02,信息,E01
D03,销售,E05
D04,财务,E06)
(3)、用SQL表达以下的查询:找出那些工资高于其所在部门经理工资的职工的姓名及其工资。
select *
from emp A,(select emp.姓名,emp.工资,dept.*
from emp,dept
where emp.职工号=dept.部门经理的职工号)B
where A.所在部门=B.部门号and A.工资>B.工资 (4)、用SQL完成:将D01号部门经理的工资改为该部门所有职工的平均工资。
update emp
set工资=(select avg(工资) from emp where 所在部门='D01')
from emp,dept
where emp.职工号=dept.部门经理的职工号AND所在部门='D01'
(5)、新增加一个广告部门,编号为D05。 insert into dept values('D05','广告','E08') 小结:不要急于答题,先审好题看清表结构,再下笔! 在(3)题中,职工号(E01..)是主键,是标识字段。要先确定标识字段,再按照标识字段去理思路,用(E01..)将两个表相连即可得到部门表中各经理的姓名、工资等情况数据集B,此数据集的记录数=部门(dept)表的记录数。然后再将数据集B与职工(emp)表相连,即可得到“出那些工资高于其所在部门经理工资的职工的姓名及其工资”。
第一题:创建如下三个基本表,表结构如下:
borrower:
借书证号 姓名 系名 班级
03001 李垒 信息系 03-1
03002 赵 信息 03-1
03003 钱 计算机 03-2
03004 孙 计算机 03-3
......
loans:
借书证号 图书登记号 借书日期
03001 t01 2004
03001 t02 2004
03001 t03 2004
03002 t01 2005
03002 t02 2005
03003 t03 2006
03003 t04 2006
03004 t05 2007
.......
BOOKS:
索书号 书名 作者 图书登记号 出版社 价格
TP311.13 数据结构 李卫 T01 科学 19.00
TP311.13 数据结构 李卫 T02 科学 19.00
TP.065 数据结构导论李卫 T03 北航 16.50
TP.1599 数据通信 杨志 T04 清华 28.50 (1)、检索借了3本书以下的学生的借书证号,姓名,系名和借书数量。
---正解如下------------
select *
from borrower B,(select count(*) as 借书数量,借书证号 as 借书证号
from loans
group by 借书证号
having count(*)<3) A
where B.借书证号=A.借书证号 (2)、检索借书和赵垒同学所借书中的任意一本相同的学生的姓名,系名,书名,借书日期。
---正解如下------------
select姓名,系名,图书登记号,借书日期
from borrower bb,loans ll
where bb.借书证号=ll.借书证号 and 姓名<>'李垒'
and图书登记号 in (select 图书登记号
from borrower b,loans l
where b.借书证号=l.借书证号 and b.姓名='李垒') (3)、建立信管系学生借书的视图SB,该视图的属性列由借书证号,姓名,班级,图书登记号,书号,出版社和借书日期组成
第二题:
现有一个学生选修课程的数据库,其中存放以下三个表:
学生(学号,姓名,性别,年龄,系别)
课程(课程号,课程名,任课教师);
选修(学号,课程号,分数);
请用SQL完成以下功能:
(1)、建表,在定义中要求声明:
A、每个表的主外码。
B、学生的年龄介于16到30。
C、学生的姓名和课程名不能为空。
D、选课成绩要么为空值,要么取0到100的整数。
(2)、插入如下数据:
学生(101,张三,男,16,数学
102,李四,男,18,计算机
103,王玲,女,17,中文
105,李飞,男,19,计算机
109,赵四,女,18,历史
110,李平,男,20,化学)
课程(203,操作系统,程羽
279,高等数学,王备
210,现代文学,王林
243,有机化学,沈同
204,数据结构,张青)
选修(101,203,82
105,203,59
102,279,90
101,279,88
105,279,82
110,279,68
109,210,72
103,210,90
110,243,92
101,204,85
105,204,91
102,204,56
101,210,77
101,243,88)
(3)、用SQL语然完成下列查询:
1、列出张三同学选修的所有课程的名称和成绩
------正解如下----------------
select课程名,分数
from学生,选修,课程
where学生.学号=选修.学号and选修.课程号=课程.课程号and姓名='张三' 2、列出所有课程都及格了的同学的名字
------正解如下----------------
select t1.姓名
from学生t1,(select 学号
from 选修
where 分数>=60
group by 学号
having count(*)=(select count(*) from (select distinct 课程号from课程)A)) t2
where t1.学号=t2.学号 3、列出在选修张青老师所教授课程的学生中,成绩最高的学生姓名和成绩
------正解如下----------------
select top 1 姓名,分数
from学生,选修
where学生.学号=选修.学号 and 学生.学号 in (select 学号 from 课程,选修 where 选修.课程号=课程.课程号 and 任课教师='张青')
order by 分数 desc
(4)、删除所有成绩不及格的选课记录
delete from 选修where分数<60 (5)、将105号同学选修203号课程的成绩改为该门课程的平均成绩
update选修
set分数=(select avg(分数) from 选修where课程号='203')
from选修
where学号='105' and 课程号='203'
更多推荐
经典试题
发布评论