二级VF真题答案

-1-二级VF答案***1套***2.2sele会员号，姓名，年龄fromcustomerwhere年龄=30orderby年龄desetofilecut_ab3.在文本框VALUE属性窗口输入：=date()ab=thisform.textl.valueselecustomer会员号,姓名sum(article.单价*orderitem.数量)as总金额fromcustomer，orderitem，articlewherecustomer.会员号=orderitem.会员号andorderitem.商品号=article.商品号and日期=abgroupby1order3intotaledbfa***2套***2.1双击customer表→【表】→追加记录→选择“customer1”表并确定Sele*fromcustomerwhere客户号in(sele客户号fromorder_list)orderby1intotableresults***3套***（1）将order_list添加到报表数据环境中（右键→数据环境）（2）右键→数据分组→选择“客户号”为分组表达式：将“客户号”字段拖到组标头带区；将“总金额”字段拖到组注脚带区，双击“总金额”→计算→选择“求和”并确定（3）【报表】→标题/总结→选择“标题带区”及“总结带区”（4）打开数据环境→双击表→输入setorderto客户号***4套***3.updatescoreset学分=iif(物理=60，3,0）+iif(英语=60,4,0）Sele学号，学分fromscoreorderby2,1descintotablexf***5套***3.updatescore_fsset平均分=（iif(物理#-1，物理，0)+iif(高数#-1，高数，0)+iif(英语#-1，英语，0)）/（iif(物理#-1,1,0)+iif(高数#-1,1，0)+iif(英语3-1,1,0)）Sele学号，平均分fromscore_fsorderby2desc,1intotablepjf***6套***2.2selestudent2.*fromstudent2,sc,course2wherestudent2.学号=sc.学号andsc.课程号=course2.课程号and课程名=“数据库”and成绩=85orderbystuden2.学号intotablenine3.updatescorel1set总成绩=考试成绩+iif(少数名族=.t.,5,0)+iif(优秀干部，10,0)+iif(三好生，20,0)Sele学号，总成绩fromscore1orderby2,1,intotablezcj***7套***2.1SELECTStudent.学号，姓名，课程名，成绩FROMstudent,sc,coursewhereSc.课程号=（course.课程号andstudent.学号=Sc.学号ORDERBY课程名，成绩DESCINTOIABLEsclist3.createviewsviewasselestudent.学号，姓名，avg(成绩)as平均成绩，min(成绩)as最低分，count(*)as选课门数fromStudent,scwherestudent.学号=sc.学号groupbystudent.学号havingcount(*)3orderby平均成绩desc浏览：opendatasdb和sele*fromsview打印：reportformp_studentpreview***8套***3.closedataallSele股票代码，（买入价—现价）*持有数量as浮亏金额fromstock_slwhere现价买入价intotablenewusestock_fkzapappendfromnewselesum(浮亏金额)as浮亏金额fromstock_fkintotablestock_zaltertablestock_zalter浮亏金额n(11,2)***9套***1.4updateproductsset销售价格=进货价格*1.18983.closedataallSele股票代码，count(*)as买入次数，manx(单价)as最高价fromstockmmwhere买卖标记groupby1intotablenew-2-usestock_scZapAppefromnewSeletop1*fromstock_csorderby买入次数descintotablestock_x***10套***2.1seleorder_detail.1*fromcustomer,order_list,order_detailwherecustomer.客户号=order_list.客户号andorder_list.订单号=order_detail.订单号and客户名=“三一贸易公司”orderby1,4descintotableresults3.updatetoorder_detailset新单价=单价*0.9where订单号in(sele订单号fromorder_listwhereyear(订购日期)=2001)Updateorder_detailset新单价=单价*1.1where订单号in(sele订单号fromorder_listwhereyear(订购日期)=2002)***11套***2.1sele*fromorder_listwhere总金额(seleavg(总金额)fromorder_list)orderby1intotableresults3.sele*fromorder_detailintotableod_bakSeleright(订单号，1)as订单号，器件号，器件名，min(单价)as单价，sum（数量）as数量fromod_bakgroupby1,2orderby1,2intotableod_new***12套***ClosedateallSele股票代码，sum（本次数量）asmjfromstock_mmwhere买卖标记=.t.groupby1intotablemjSele股票代码，sum（本次数量）asmcfromstock_mmwhere买卖标记=.f.groupby1intotablemcSelemj.股票代码，mj—mcas持仓数量frommj,mcwheremj.股票代码=mc.股票代码intotablenewUsestock_ccZapAppendfromnewSeletop1*fromstock_ccorderby2intotablestock_x***13套***ClosedataallSele*fromchengjiorderby课程号，成绩deseintotablenewIndexon课程号tag课程号Sele0UsekechengSetrelationto课程号intonewReplace最高分withnew.成绩，学号for课程号=new.课程号Sele课程名，最高分fromkechengorderby2deseintotablejiangli***14套***2.2数据表格式输入：opendatabasestsc/usestudent/browse/closedatabase退出：setsysmenutodefault3.closedataallSele学号，avg（成绩）as平均分fromchengjigroupby1intotablenewIndexon学号tag学号Sele0UsexueshengSetrelationto学号intonewReplace平均分withnew.平均分forxuesheng.学号=new.学号Sele学号，平均分fromxueshengorderby2,1intotablepingjun***15套***2.1selestudent.*fromstudentscore,coursewherestudent.学号=score.学号andscore.课程号=course.课程号and课程名=”C++”orderby1intotablecplus-3-3.closedataallUsezhichengIndexon职称代码tag职称代码Sele0UseyuangongSetrelationto职称代码intozhichengReplace新工资with工资*（1+zhicheng.增加百分比/100）for职称代码=zhicheng.职称代码***16套***2.1sele商品表.商品号，商品名,销售价.单价，sum(销售数量)as销售数量，sum（销售表.单价*销售数量）as销售金额from商品表，销售表where商品表.商品号=销售表.商品号groupby1orderby5deseintotablesee_a3.sele*from商品表intotable商品表备份update商品表set单价=出厂单价*0.1whereleft(商品号，2)=”10”调整:dochange_c***17套***sele*fromsalarysintotablebak_salarysclosedataallusec_sallaryssetorderto雇员号sele0usesalaryssetrelato雇员号intoc_salary1replace工资withc_salary1.工资for雇员号=c_salary1.雇员号调整:dochange_c***18套***closedataallsele订单号，sum(单价*数量)as总金额fromorder_detailgroupby1intotablenewindexon订单号tag订单号sele0useorder_listsetrelationto订单号intonewrepl总金额withnew.总金额for订单号=new.订单号sele*fromorder_listorderby总金额descintotableod_new***19套***sele订单号，sum(单价*数量)as总金额fromorder_detailgroupby1intotablenewsele客户号，order_list.订单号，订购日期，new.总金额fromorder_list,newwhereorder_list.订单号=new.订单号andorder_list.总金额#new.总金额orderby4intotableod_modaltertableod_modalter总金额f(15,2)***20套***2.1createviewsviewasselect部门号，雇员号，姓名，工资，补贴，奖励，失业保险，医疗统筹，工资+补贴+奖励-失业保险-医疗统筹as实发工资fromsalarysorderby部门号desc3.ab=thisform.optiongroup1.valuedocasecaseab=1opendatasalary_dbsele*fromsviewintotablegz1caseab=2-4-sele*fromdeptintotablebm1caseab=3seledept.部门号,部门名,sum(工资)as工资,sum(补贴)as补贴,sum(奖励)as奖励,sum(失业保险)as失业保险,sum(医疗统筹)as医疗统筹fromdept,salaryswheredept.部门号=salarys.部门号groupbydept.部门号intotablehz1endcase***21套***1.3update雇员setemall=部门号+雇员号[e-mail]2.1updeta雇员set日期=date()***22套***1.1sele*fromstock_slintotablestock_bk1.2insertintostock_bkvalues(600028,4.36,4.60,5500)1.3updatestock_bkset现价=8.88where股