VFP实验作业9-13答案

实验9（1）字段：xm,jg表：xs筛选：ALLT(jg)=”江苏”ANDxb=”女”（2）字段xm,YEAR(DATE())-YEAR(csrq)AS年龄表：js筛选：xb=”男”排序：YEAR(DATE())-YEAR(csrq)AS年龄杂项：排在前面的1个（3）字段：LEFT(xh,2)AS年级,COUNT(*)AS人数表：xs分组：LEFT(xh,2)AS年级排序：LEFT(xh,2)AS年级（4）字段：xh,COUNT(*)AS选课门数,AVG(cj)AS平均成绩表：cj分组：xh输出去向：表qdata（5）字段：kcdh,AVG(cj)AS平均分表：cj分组：kcdh，满足条件：平均分=60;输出去向：临时表tnpb（6）字段：xs.xh表：cj筛选：cj60杂项：无重复记录（7）字段：xs.xh,xm,kcdh,cj表：xs与cj内联接，联接条件：xs.xh=cj.xh筛选：xb=”男”（8）字段：xim.xdh,ximing,zc,COUNT(*)AS人数,AVG(YEAR(DATE())-YEAR(csrq))AS平均年龄表：xim与js内联接、js与zc内联接，联接条件：zc.zcdh=js.zcdh、js.xdh=xim.xdh分组：xim.xdh，zc排序：xim.xdh,COUNT(*)AS人数降序（9）字段：ximing,COUNT(*)AS人数,SUM(IIF(zc=”教授”,1,0))AS教授人数表：xim与js内联接、js与zc内联接，联接条件：zc.zcdh=js.zcdh、js.xdh=xim.xdh分组：ximing，满足条件：SUM(IIF(zc=”教授”,1,0))/COUNT(*)=0.25排序：SUM(IIF(zc=”教授”,1,0))AS教授人数、降序（10）字段：kcm,MAX(cj)AS最高分,MIN(cj)AS最低分,AVG(cj)AS平均分表：cj与kc内联接，联接条件：kc.kcdh=cj.kcdh分组：kcm，满足条件：平均分=75（11）字段：xs.xh,xm,COUNT(cj.xh)AS选课门数表：xs与cj左联接，联接条件：xs.xh=cj.xh分组：xs.xh（12）字段：xs.xh,xm,COUNT(*)AS补考门数表：xs与cj内联接，联接条件：xs.xh=cj.xh筛选：cj60分组：xs.xh排序：xs.xh实验10(1)字段：js.gh,xm,kc.kcdh,kcmc,kss联接：jsJOINrk条件js.gh=rk.ghrkJOINkc条件rk.kcdh=kc.kcdh更新：kc.kcdh关键字kcmc,kss更新字段发送SQL更新（2）筛选：xs.xh=?实验11（1）①SELECTxm,jg;FROMxs;WHEREALLT(jg)=”江苏”ANDxb=”女”②SELECTTOP1xm,YEAR(DATE())-YEAR(csrq)AS年龄;FROMjs;WHERExb=”男”;ORDERBY2或者SELECTMIN(YEAR(DATE())-YEAR(csrq))AS最小年龄;FROMjs;WHERExb=”男”③SELECTLEFT(xh,2)AS年级,COUNT(*)AS人数;FROMxs;GROUPBY1;ORDERBY1④SELECTxh,COUNT(*)AS选课门数,AVG(cj)AS平均成绩;FROMcj;GROUPBY1;INTODEFqdata⑤SELECTkcdh,AVG(cj)AS平均分;FROMcj;GROUPBY1HAVING平均分=60;TOCURStnpb⑥SELECTDISTINCTxs.xh;FROMcj;WHEREcj60⑦SELECTxs.xh,xm,kcdh,cj;FROMxsINNERJOINcjONxs.xh=cj.xh;WHERExb=”男”⑧SELECTxim.xdh,ximing,zc,COUNT(*)AS人数,;AVG(YEAR(DATE())-YEAR(csrq))AS平均年龄;FROMximJOINjsJOINzcONzc.zcdh=js.zcdhANDjs.xdh=xim.xdh;GROUPBY1,3;ORDERBY1,4DESC⑨SELECTximing,COUNT(*)AS人数,SUM(IIF(zc=”教授”,1,0))AS教授人数;FROMximJOINjsJOINzcONzc.zcdh=js.zcdhANDjs.xdh=xim.xdh;GROUPBY1HAVINGSUM(IIF(zc=”教授”,1,0))/COUNT(*)=0.25;ORDERBY3DESC⑩SELECTkcm,MAX(cj)AS最高分,MIN(cj)AS最低分,AVG(cj)AS平均分;FROMcjINNERJOINkcONkc.kcdh=cj.kcdh;GROUPBY1HAVING平均分=75⑾SELECTxs.xh,xm,COUNT(cj.xh)AS选课门数;FROMxsLEFTJOINcjONxs.xh=cj.xh;GROUPBY1或者：SELECTxs.xh,xm,COUNT(*)AS选课门数;FROMxsJOINcjONxs.xh=cj.xh;GROUPBY1;UNION;SELECTxs.xh,xm,0AS选课门数;FROMxsLEFTJOINcjONxs.xh=cj.xhHAVINGcj.xhISNULL⑿SELECTxs.xh,xm,COUNT(*)AS补考门数;FROMxsINNERJOINcjONxs.xh=cj.xh;WHEREcj60GROUPBY1ORDERBY1(2)SELECTxm;FROMxs;WHERExs.xhNOTIN(SELECTxhFROMcj)SELECTxm;FROMxs;WHERENOTEXISTS(SELECT*FROMcjWHERExs.xh=cj.xh)（3）SELECTximing,COUNT(*)AS人数;FROMjsJOINximONjs.xdh=xim.xdh;GROUPBY1HAVING人数(SELECTCOUNT(*)FROMjsJOINximONjs.xdh=xim.xdhWHEREximing=”数学系”)（4）SELECTkcmc,kss;FROMkc;WHEREbxkANDkssANY(SELECTkssFROMkcWHERENOTbxk);ORDERBY2DESC（5）SELECTxm,”教师”AS身份;FORMjs;UNION;SELECTxm,”学生”AS身份;FORMxs;ORDERBY2(6)SELECTkcm,COUNT(*)AS人数；FROMkcJOINcjONkc.kcdh=cj.kcdh;GROUPBY1;UNION;SELECT“选课学生人数为”+SPACE(4),COUNT(DISTINCTcj.xh)AS人数;FROMcj;ORDERBY2实验12（1）NOTEZY1.PRGINPUT“请输入行驶里程：”TOsIFs=3f=9ELSEf=9+(s-3)*2.4ENDIFf=f+2?f(2)NOTEZY2.PRGACCEPT“请输入一个字符：”TOcIFASC(c)=65ANDASC(c)=90ORASC(c)=97ANDASC(c)=122IFASC(c)=65ANDASC(c)=90??c+”是一个大写字母。”ELSE??c+”是一个小写字母。”ENDIFELSE??c+”不是一个字母。”ENDIF或者：ACCEPT“请输入一个字符：”TOcDOACSECASEASC(c)=65ANDASC(c)=90??c+”是一个大写字母。”CASEASC(c)=97ANDASC(c)=122??c+”是一个小写字母。”OTHERWISE??c+”不是一个字母。”ENDCASE(3)NOTEZY3.PRGs=0FORi=1TO100s=s+i/(i+1)ENDFOR?s(4)NOTEZY4.PRGINPUT“请输入一个字符串：”TOstr1N=LEN(str1)str2=””?str1+”的反向字符串是：”DOWHILEn0c=LEFT(str1,1)IFASC(c)=127str2=c+str2str1=SUBSTR(str1,2)ELSEstr2=LEFT(str1,2)+str2str1=SUBSTR(str1,3)ENDIFn=LEN(str1)ENDDO??str2(5)NOTEZY5.PRGn=0FORi=10TO10000d1=MOD(i,10)d2=MOD(INT(i/10),10)d3=MOD(INT(i/100),10)d4=MOD(INT(i/1000),10)DOCASECASEi100IFd1=d2n=n+1ENDIFCASEi1000IFd1=d3n=n+1ENDIFOTHERWISEIFd1=d4ANDd2=d3n=n+1ENDIFENDCASENEXT?n(6)NOTEZY6.PRGsumcj=0USEcjACCEPT“请输入学号：”TOxh1LOCATEFORxh=xh1IFEOF()??”无”+xh1+”的成绩。”ELSESCANFORxh=xh1?kcdh,cjsumcj=sumcj+cjENDSCAN?”总成绩为：”,sumcjENDIFUSE(7)NOTEZY7.PRGSELECTjs.gh,jbgz,ximing;FROMximJOINjsJOINgzONjs.gh=gz.ghONjs.xdh=xim.xdh;WHEREximing”医学院”;INTODBFgz1UPDATEgz1SETjbgz=jbgz*1.05SELECT*FROMgz1a;WHEREjbgz(SELECTAVG(jbgz)FROMgz1bWHEREa.ximing=b.ximing);ORDERBYximing,jbgz;INTOTABLEnewgz(8)NOTEZY8_1.PRGcString=”室内装饰DIY”nCount=0DOWHILELEN(cString)0IFASC(LEFT(cString,1))=127cString=SUBSTR(cString,2)LOOPENDIFnCount=nCount+1cString=SUBSTR(cString,3)ENDDOWAITWINDOWS‘汉字个数为’+STR(nCount)NOTEZY8_2.PRGCLEARm=2STORE0TOsFORi=1TO20s=s+m/im=m+iENDFOR?sNOTEZY8_3.PRGcText=visualbasiccResult=SPACE(0)DOWHILENOTEMPTY(cText)nSpacePos=AT(SPACE(1),cText)IFnSpacePos0cWord=LEFT(cText,nSpacePos)ELSEcWord=cTextENDIFcWord=UPPER(LEFT(cWord,1))+SUBSTR(cWord,2)cResult=cResult+cWordcText=LTRIM(SUBSTR(cText,nSpacePos))ENDDOWAITWINDOWcResult实验13（1）FUNCTIONntozPARAnstr1=”零一二三四五六七八九”RETURNSUBSTR(str1,2*n+1,2)ENDFUNC(2)NOTEZY1.PRGINPUT“请输入一个数字字符串：”TOl?”字符串”+l+”的中文表示是：”m=LEN(l)FORi=1TOm??ntoz(VAL(SUBSTR(l,i,1)))ENDFOR(3)NOTEZY2.PRGDIMENSIONa(100)n=0FORi=1TO100a(i)=INT(RAND()*10000+1)IFhs(a(i))n=n+1ENDIFNEXT?nFUNCTIONhsPARAmFORj=2TOm-1IFm%j=0RETURN.T.ENDIFNEXTRE