Question

SQL语言查询题目求助

已知教学管理数据库（jxgl.dbc)中有三张表，定义如下
学生表 xs(XH C(9),XM C(8),XB C(2),CSRQ D)
课程表 kc(KCDH C(2),KCM C(16),XF N(5,1))
成绩表cj(XH C(9),KCDH C(2),CJ N(4,1),BKCJ N(4,1))
写出视线一下数据查询操作的SELECT-SQL命令：
1）查询成绩在70分至80分之间的学生的学号、课程号和成绩，结果按成绩降序排列
2）查询选修课程号为C2的学生的学号和姓名，结果按学号升序排列。
3）查询所有学生的学号、姓名、选课名称和成绩
4）查询所有姓方的学生的学号、姓名和性别
5）查询方华同学所学课程的课程号及成绩

Answer 1

字段名bkcj是什么意思？

1）查询成绩在70分至80分之间的学生的学号、课程号和成绩，结果按成绩降序排列。
select xh,kcdh,cj from cj where cj>=70 and cj<=80 order by 3 desc

2）查询选修课程号为C2的学生的学号和姓名，结果按学号升序排列。
select xh,xm from xs,cj where kcdh="c2" and xs.xh=cj.xh order by 1

3）查询所有学生的学号、姓名、选课名称和成绩
select xh,xm,kcm,cj from xs,kc,cj where xs.xh=cj.xh and kc.kcdh=cj.kcdh

4）查询所有姓方的学生的学号、姓名和性别
select xh,xm,xb from xs where xm like "方%"

5）查询方华同学所学课程的课程号及成绩
select kcdh,cj from cj ,xs where xs.xh=cj.xh and xm="方华"

Answer 2

1.select xh,kcdh,cj from cj where cj>=70 and cj<=80 order by cj desc
2.select xs.xh,xm from xs,cj where kcdh="C2" and xs.xh=cj.xh order by xs.xh
3. select xs.xh,xm,kcm,cj from xs,kc,cj where xs.xh=cj.xh and kc.kcdh=cj.kcdh
4.select xh,xm,xb from xs where xm like "方%"
5.select kcdh,cj from xs,cj where xm="方华"

Answer 3

1
select xs.xh,kc.kcdh,cj.cj
from xs,kc,cj
where xs.xh=cj.xh and kc.kcdh = cj.kcdh
and cj.cj between 70 and 80
order by cj.cj desc
2
select xs.xh,xs.xm
from xs,kc,cj
where xs.xh=cj.xh and kc.kcdh = cj.kcdh
and kc.kcm='c2'
order by xs.xh
3
select xs.xh,xs.xm,kc.kcdh,cj.cj
from xs,kc,cj
where xs.xh=cj.xh and kc.kcdh = cj.kcdh
4
select xh,xm,xb from xs where xm like '方%'
5
select cj.kcdh,cj.cj
from xs,cj
where xs.xh=cj.xh
and xs.xm='方华'

--既然课本上讲了第三范式，为什么不按第三范式的要求出题呢？误导！

Answer 4

1
select xs.xh,kc.kcdh,cj.cj
from xs,kc,cj
where xs.xh=cj.xh and kc.kcdh = cj.kcdh
and cj.cj between 70 and 80
order by cj.cj desc
----
select xs.xh,kc.kcdh,cj.cj
from xs,kc,cj
where xs.xh=cj.xh and kc.kcdh = cj.kcdh
and cj.cj > 70 and cj.cj <80
order by cj.cj desc

2
select xs.xh,xs.xm
from xs,kc,cj
where xs.xh=cj.xh and kc.kcdh = cj.kcdh
and kc.kcm='c2'
order by xs.xh
3
select xs.xh,xs.xm,kc.kcdh,cj.cj
from xs,kc,cj
where xs.xh=cj.xh and kc.kcdh = cj.kcdh
4
select xh,xm,xb from xs where xm like '方%'
5
select cj.kcdh,cj.cj
from xs,cj
where xs.xh=cj.xh
and xs.xm='方华'