实验八实验内容(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 sfgz5．利用所建立的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 ;cno=(sele cno from course where cname=”计算机基础”) and grade<60(10) 求每个系的学生数而不是求学生总数。
希望得到类似下面的输出：dno count(*)d01 120d02 65sele dno,count(*) from student group by dno(11) 查询每个学生已获得的学分（注：成绩及格即取得相应课程的学分）Sele sno,sum(credit) from sc,course where o=o group by sno(12) 查学分大于30分的学生（只输出学号）Sele sno from sc,course where o=o group by sno having sum(credit)>30(13) 查询从未被选修的课程sele cname from course where cno not in (sele cno from sc)实验九参考答案二、实验内容与步骤1．SQL数据修改命令练习（1）向zgqk表中插入一条记录，zgbh, xm, xb, xl, xw, zc等字段的值分别为。
Insert into zgqk(zgbh, xm, xb, xl, xw, zc) ;values(“199009”,”和红”,”女”,”研究生”,”博士”,”助教”)（2）向zgqk表中插入3条记录，其zgbh字段的第一个字母均设置为”A”，如“A00001”。
Insert into zgqk values("A00001","和红1", "女",{^1969/12/21},{^1999/12/21}, ;"研究生","博士","助教",.t.,"aa","A","105")Insert into zgqk values("A00002","和红2", "女",{^1969/12/21},{^1999/12/21}, ;"研究生","博士","助教",.t.,"aa","A ","105")Insert into zgqk values("A00003","和红3", "女",{^1969/12/21},{^1999/12/21}, ;"研究生","博士","助教",.t.,"aa","A","105")（3）删除上题中插入的3条记录。