数据库实验8-9-参考答案

实验八实验内容(2)

二、实验内容与步骤

1．SQL数据查询命令的应用

(9) 查询基础工资最高的教师姓名和职称

(10) 查询各部门基础工资与职务补贴之和的最高值、最低值

(11) 查询人数大于等于4人的部门名称和人数

(12) 按部门编号的降序显示教师姓名、部门编号和职称

(13) 查询工资表的数据，存放到数组中

(14) 查询工资表的数据，存放到临时表中

4．使用rsgl数据库，写出分组和统计计算查询的SQL命令

(1) sele count(*) 财政系人数from zgqk where bmbh=;

( sele bmbh from bm where bmmc="财政系")

或者

sele count(*) 财政系人数from zgqk,bm where;

bm.bmbh=zgqk.bmbh and bmmc="财政系"

或者

sele count(b mbh) 财政系人数from zgqk,bm where;

bm.bmbh=zgqk.bmbh and bmmc="财政系"

(2) sele bmmc, count(zgqk.bmbh) 人数from zgqk ;

right join bm on zgqk.bmbh=bm.bmbh group by bm.bmbh

注意：

①count(zgqk.bmbh)中必须为zgqk.bmbh，目的是不统计.NULL.内容的记录。可以

先查看右连接情况：sele * from zgqk right join bm on zgqk.bmbh=bm.bmbh

②此应该采用右连接right join，因为“统计每个系”，必须考虑bm表中的所有系

部。

③group by bm.bmbh，分组应该按照bm的bmbh分组，而不是zgqk的bmbh；运

行①中的sele * from zgqk right join bm on zgqk.bmbh=bm.bmbh，查看运行结果

就知道原因了。

(3) sele bmbh, count(*) 教授人数from zgqk where ;

zc=”教授” group by bmbh order by 教授人数desc

或者

sele bmmc, count(*) 教授人数from zgqk, bm;

where zgqk.bmbh=bm.bmbh and zc=”教授”;

group by zgqk.bmbh order by 教授人数desc

注意：因为该题有where zc=”教授”的限定，所以不需要再类似(2)题的设置。

(4) sele sum(jcgz+zwgz+zjgz) 全体职工工资from gz

(5) sele bmmc, count(*) jcgz 超过1800人数from zgqk, bm, gz;

where zgqk.bmbh=bm.bmbh and zgqk.zgbh=gz.zgbh and jcgz>=2500;

group by zgqk.bmbh

(6) sele top 1 xm, count(*) 数目from zgqk, kyqk where zgqk.zgbh=kyqk.zgbh;

group by kyqk.zgbh order by 数目desc

或者

sele top 1 xm, count(xm) 数目from zgqk, kyqk where zgqk.zgbh=kyqk.zgbh;

group by xm order by 数目desc

(7) sele top 1 bmmc, count(*) 数目from zgqk, bm, kyqk where zgqk.bmbh=bm.bmbh;

and zgqk.zgbh=kyqk.zgbh group by bmmc order by 数目desc

(8) sele zgqk.zgbh, xm, jcgz+zwgz+zjgz+fljj yfgz from zgqk, gz;

where zgqk.zgbh=gz.zgbh into table zggz

(9) sele bmmc, sum(jcgz+zwgz+zjgz+fljj-sdf-mqf) sfgz from zgqk, bm, gz;

where zgqk.bmbh=bm.bmbh and zgqk.zgbh=gz.zgbh;

group by bmmc order by sfgz desc into cursor sfgz

5．利用所建立的xsgl数据库，使用SQL_Select完成下列查询：

(1) 查询系号为"d01"的男学生信息

select * from student where dno="d01" and sex="男"

(2) 查询所有学生某门课（如"C001"）的成绩，并按成绩由高到低的顺序输出

sele sname,grade from student,sc where;

student.sno=sc.sno and cno="c001" order by grade desc

若不显示姓名，则命令为：sele grade from sc where cno="c001" order by grade desc

(3) 查询89、90两年出生的女同学的名单

sele sname from student where;

sex="女" and birthday between {^1989/01/01} and {^1990/12/31}

(4) 查询“计算机基础”课不及格的学生名单（输出学生的学号、姓名及成绩）

Sele sno,sname,grade from student,sc where student.sno=sc.sno and ;

sno=(sele sno from course where cname=”计算机基础”) and grade<60

(5) 查询同时选修了"C001"和"C002"的学生的学号

Sele a.sno from sc a, sc b where a.sno=b.sno;

and o="C001" and o="C002"

(6) 查询选修了"C001"、但没有选修"C002"的学生的学号

Sele sno from sc where ;

sno in (Sele sno from sc where cno="C001" ) and ;

sno not in (Sele sno from sc where cno="C002")

(7) 查询至少选修了"C001"和"C002"中一门课的学生

Sele sno from sc where cno="C001" union ;

sele sno from sc where cno="C002"

或者

Sele dist sno from sc where ;

sno in (Sele sno from sc where cno="C001" ) or ;

sno in (Sele sno from sc where cno="C002")

(8) 求女学生的学生总数

sele count(sno) from student where sex="女"

(9) 查询有多少名同学计算机基础课不及格

Sele count(*) from sc where ;