数据库上机练习题

期末上机练习一、项目、数据库和表操作打开素材中的项目文件TEST，在该项目中已有一数据库SJK。

1、按如下要求修改SJK中学生（XS）表的结构：（1）为XS表增加一个备注型字段，其字段名为bz；（2）为备注（bz）字段设置标题：备注；（3）对学号（xh）字段设置输入掩码：接受8个字节的字符，且只接受数字；（4）在XS表中增加一条记录，记录内容为：xh xm xdh Bz学生自己的学号自己的姓名05 信息管理系学生（5）创建一个普通索引xmrq，要求先按xm字段排序，xm相同时再按出生日期（csrq）字段排序。

（6）为xs表增加一个字段nj（年级），该字段长度为4个字符，该字段的值由当前世纪与bjbh字段的前2位构成，例如：bjbh为“030404051”，则nj字段的值为“2003”update xs set nj = "20"+left(bjbh,2)browse（7）为XS表设置删除触发器：不允许删除。

（8）xs表设置xb字段的有效性（验证）规则，要求性别必须为“男”或“女”；2、为XS表添加编辑说明：学生基本信息表在项目管理器中，选中XS右击有编辑说明3、把素材中的表文件GZB.DBF添加为TEST项目中的自由表。

（1）调整GZB表中所有讲师的综合津贴（zhjt），要求1996年（含1996年）以前参加工作的讲师每人为280元，其余的讲师为150元。

update gzb set zhjt = iif(year(gzrq)<=1996,280,150) where zc="讲师"或者 use gzb replace zhjt with iif(year(gzrq)<=1996,280,150) for zc="讲师"（2）把表文件GZB.DBF添加为TEST项目中的数据库表。

把GZB表中所有职称(zc)为助教的综合津贴（zhjt）增加１００元。

use gzbreplace zhjt with zhjt+100 for zc="助教"brow（3）为gzb表设置更新触发器：仅允许修改２００１年（含２００１年）以后参加工作的教师记录.4、把素材中的表文件GZC.DBF添加为TEST项目中的自由表。

调整GZC表中所有讲师的其他（qt）字段，要求设置为150元。

update gzc set qt=150 where zc="讲师"5、为RK表设置插入触发器：工号以“A”或“B”字母开头的记录允许插入。

Left(gh,1)="A" or Left(gh,1)="B"6、设置参照完整性：(要清理数据环境)（1）JS表已存在主索引jsgh，索引表达式为gh，RK表已存在普通索引rkgh，索引表达式为gh。

以js表为主表、RK表为子表，按gh建立永久关系，并设置JS表和RK表之间的参照完整性：更新限制, 删除级联。

（2）JS表已存在主索引jsgh，索引表达式为gh；GZ表已存在普通索引gzgh，索引表达式为gh，以JS表为主表、GZ表为子表，按gh建立永久关系，并设置JS表和GZ表之间的参照完整性：更新级联，删除限制。

（3）SJK中KC表已存在主索引kcdh,索引表达式为kcdh，CJ表已存在普通索引cjkcdh,索引表达式为kcdh。

以KC表为主表，CJ表为子表按kcdh建立永久关系，并设置KC表和CJ 表之间的参照完整性：更新级联。

7、按如下要求修改SJK中工资(gz)表的结构:（1）为个人所得税（grsds）字段设置标题：个人所得税。

（2）设置个人所得税（grsds）字段的注释：基本工资1000元以上征税；（3）设置gz表的记录有效性(验证)规则：如果jbgz小于1000元，grsds的值为0，否则grsds的值为jbgz的5%（该规则对表中已有的数据不做验证）；（4）设置gz表的记录有效性信息：个人所得税按基本工资的5%收取；（5）创建一个普通索引gzsy，要求按jbgz排列。

(6) 为GZ表设置删除触发器：不允许删除任何记录。

.f.8、设置test项目信息：作者为学生本人，单位为”南京审计学院”。

9、向kc表中添加二条记录，记录内容为：Kcdh Kcm Bxk Kss xf26 计算机图形学.f. 3 327 人工智能.t. 3 310、按如下要求修改SJK中教师（JS）表的结构：（1）设置工作日期（gzrq）字段的有效性（验证）规则：工作日期的年份必须小于等于当前系统日期的年份，并且大于等于1960年；（2）设置（gzrq）字段的有效性（验证）信息：工作日期必须在1960年以后；（3）将简历（ji）字段标题设置为：简历；（4）为工号（gh）字段设置格式与输入掩码：接受5个字节的字符，且第一个字符必须为大写英文字母，后4个字符只接受数字字符；（5）创建一个普通索引xdhzc，要求先按系代号（xdh）字段排序，相同时按职称代号（zcdh）字段排序。

创建一个普通索引zcgzrq，要求先按职称代码(zcdh)字段排序，职称相同的再按工作日期(gzrq)字段排序。

（6）为JS表添加注释：教师基本信息表（7）修改js表中简历（jl）字段的值，要求：所有xdh为”05”的记录的简历都改为”信息管理系的教师”。

update js set jl="信息管理系的教师" where xdh="05"（8）设置js表的插入触发器：gh字段的值必须为5个字节的字符串（除去首尾空格），否则不允许插入Len(alltrim(gh))=5（9）为JS表设置有效性（验证）规则：工作日期（gzrq）与出生日期（csrq）之间至少相差20年；设置JS表的记录有效性（验证）信息：教师参加工作时至少为20岁。

要加引号11、按如下要求修改SJK中课程（kc）表的结构：（1）将必修课(bxk)字段的标题设置为“必修课”,默认值为.F.；（2）设置学分(xf)字段的有效性（验证）规则：大于等于0小于等于6；（3）为课程代号(kcdh)字段设置输入掩码：接受4个字节的字符，且只接受数字字符；（5）创建一个普通索引xfkcdh,要求先按xf字段排序，xf相同的再按kcdh字段排序；ALLTRIM(STR(xf))+kcdh（6）为KC表添加编辑说明为“课程基本信息表”。

二、设计查询1、TEST项目中已经存在查询chaxunA，且在SJK中包含一个名为JSST的视图。

该视图包括教师工号（gh）、姓名（xm）、出生日期（csrq）、职称（zc）和系代号（xdh）字段，在SJK的工资（GZ）表含有教师工号（gh）、基本工资（jbgz）和综合津贴（zhjt）字段。

按如下要求修改查询：基于JSST视图和GZ表查询每个系各类职称基本工资、岗位津贴和综合津贴三项之和的最大值、最小值、平均值、要求输出字段包括：xdh、zc，最大值、最小值、平均值，查询结果按xdh从高到低排序。

max(gz.jbgz+gz.gwjt+gz.zhjt)min(gz.jbgz+gz.gwjt+gz.zhjt)avg(gz.jbgz+gz.gwjt+gz.zhjt)2、在TEST项目中已存在查询xscj，按如下要求修改该查询：基于学生（XS）表和成绩(CJ)表查询2003级学生成绩总分前3名。

要求输出字段为：xh、xm、xb、总成绩，查询结果按总成绩降序排序。

Sum(cj.cj) as 总分（说明：xh字段的前两位表示年级，03级学生即xh以“03”开头的学生）select top 3 xs.xh,xs.xm,xs.xb,sum(cj.cj);from xs inner join cj on xs.xh=cj.xh;where left(xs.xh,2)="03";group by 1 ;order by 4 desc3、在TEST项目中已存在查询xschq，且在SJK中包含一个名为XSST的视图，该视图中包含有学生所在的系代号（xdh）、系名称(ximing)、专业代号(zydh),另外在SJK的专业（ZY）表中含有专业名称(zymc)字段。

按如下要求修改查询：基于XSST视图和ZY表查询每个系各专业学生的人数。

要求输出字段为：xdh、ximing、zydh、zymc、学生人数，查询结果按xdh的升序排序，如果xdh相同，则按zydh的升序排序。

select xsst.xdh,xsst.ximing,xsst.zydh,zy.zymc,count(*) as 人数;from xsst inner join zy on zy.zydh=xsst.zydh;group by xsst.ximing;order by xsst.xdh,xsst.zydh4、TEST项目中已经存在查询JSST_XIM.QPR，且在SJK中包含一个名为JSST的视图。

该视图包括教师工号（gh）姓名（xm）、出生日期（csrq）、职称（zc）和系代号（xdh）字段；在SJK的系名（XIM）表含有系代号（xdh）和系名称（ximing）字段。

按如下要求修改查询：基于JSST视图和XIM表查询每个系职称为“教授”的人数、平均年龄，要求输出字段包括：xdh、ximing、zc、人数、平均年龄、查询结果按人数从高到低排序。

人数：SUM(IIF(Jsst.zc="教授",1,0)) as 教授人数（一种方法）select jsst.xdh,xim.ximing,jsst.zc,count(*) as 人数,avg(year(date())-year(csrq)) as 平均年龄;from jsst inner join xim on jsst.xdh=xim.xdh;where zc="教授";group by xim.ximing;order by 4 desc5、在TEST项目中已存在查询chaxun_xszy，且在SJK中学生（xs）表包含学号（xh）、姓名（xm）、性别（xb）、班级编号（bjbh）、系代号（xdh）和专业代号（zydh）字段，专业（zy）表含有专业代号（zydh）和专业名称（zymc）字段，按如下要求修改查询：基于xs表和zy表查询“01”年级每个专业女生所占比例。

要求输出字段为：zydh,zymc,年级，女生人数和女生所占比例，查询结果按女生所占比例从高到低排序（假定bjbh字段值的前二位表示年级）。

select zy.zydh,zy.zymc,left(bjbh,2) as 年级, sum(iif(xb="女",1,0)) as 女生人数,sum(iif(xb="女",1,0))/count(*) as 女生所占比例;from zy inner join xs on zy.zydh=xs.zydh;where left(bjbh,2)="01";group by zy.zydh;order by 5 desc三、设计菜单（一）项目TEST中已存在菜单MenuM，利用菜单设计器按如下要求修改菜单：1、如图1所示，在“表操作”菜单栏下增加一个“删除记录”菜单项及其子菜单；2、为“表操作”菜单栏下的“记录定位”菜单项编写过程，其功能是：打开学生（XS）表，并将记录指针定位在最后一条记录；3、为“显示”菜单设置访问键，“显示（V）”4、为“文件”菜单下的子菜单“恢复系统菜单”设置相关命令，可以返回vfp系统菜单环境。