会电审计习题库

1、-1.查询主营业务收入的凭证select a.*from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where b.ccode='501' and b.mc<>0-2.查询现金收入大于的凭证select a.*from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhe

2、re b.ccode='101' and b.md>10000-3.查询现金收入整千元的凭证select a.*from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.ccode='101' and b.md%10000=0 and b.md<>0-4.检索出所有赊销收入明细账记录select a.*from GL_accvouch a join GL_accvouch bon a.

3、iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere a.ccode='501' and a.mc<>0 and b.ccode='113' and b.md<>0-5.检索出所有赊销收入凭证select a.*from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idjoin GL_accvouch c on a.ip

4、eriod=c.iperiod and a.csign=c.csign and a.ino_id=c.ino_idwhere b.ccode='501' and b.mc<>0 and c.ccode='113' and c.md<>0-6.计算各月的赊销收入select a.iperiod,SUM(a.mc)from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere a.ccode=&

5、#39;501' and a.mc<>0 and b.ccode='113' and b.md<>0group by a.iperiod -7.计算各月的现销收入select a.iperiod,SUM(a.mc)from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere a.ccode='501' and a.mc<>0 and b.ccode in ('

6、101','10201') and b.md<>0group by a.iperiod -8.计算各月收回的销售欠款（应收账款）select a.iperiod,SUM(a.mc)from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere a.ccode='113' and a.mc<>0 and b.ccode in ('101','10201'

7、)group by a.iperiod -9.检查所有确认收入时，未同时提取应交税金的销售收入明细账记录。create view v1 as select a.*from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.ccode='501' and b.mc<>0create view v2 asselect a.* from GL_accvouch a join GL_accvouch bon a.ipe

8、riod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idjoin GL_accvouch c on a.iperiod=c.iperiod and a.csign=c.csign and a.ino_id=c.ino_idwhere b.ccode='501' and b.mc<>0 and c.ccode like '221%' and c.mc<>0select a.*from v1 a left join v2 bon a.iperiod=b.iperiod and a.csi

9、gn=b.csign and a.ino_id=b.ino_idwhere a.ccode='501' and a.mc<>0 and b.iperiod is null -10.检查所有确认收入时，未同时提取应交税金的销售收入凭证。create view v1 as select a.*from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.ccode='501' and b.mc<

10、>0create view v2 asselect a.* from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idjoin GL_accvouch c on a.iperiod=c.iperiod and a.csign=c.csign and a.ino_id=c.ino_idwhere b.ccode='501' and b.mc<>0 and c.ccode like '221%' and c

11、.mc<>0select a.*from v1 a left join v2 bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.ccode is null -11.审计人员检查销售发票所列商品的品名、数量、金额与发货单中所列商品的品名、数量、金额是否一致create view v3 as select a.SBVID,cInvCode,sum(iQuantity)fpsl,SUM(iNatMoney)fpjefrom SaleBillVouch a join SaleBillVouchs

12、 bon a.SBVID=b.SBVID group by a.SBVID,cInvCodecreate view v4 asselect SBVID,cInvCode,SUM(iQuantity)fhsl,SUM(iNatMoney)hfjefrom DispatchList a join DispatchLists bon a.DLID=b.DLID group by SBVID,cInvCodeselect a.cInvCode,fpsl,fpje,b.cInvCode,fhsl,hfjefrom v3 a full join v4 bon a.sbvid=b.sbvidwhere a.

13、cInvCode is null or b.cInvCode is null or ISNULL(fpsl,0)<>ISNULL(fhsl,0) or ISNULL(fpje,0)<>ISNULL(hfje,0)-12.检索出销售收入明细账与发票不一致的地方create view v5 as select coutbillsign,coutid,SUM(mc) pzjefrom GL_accvouch where ccode='501' and mc<>0group by coutbillsign,coutidcreate view v6 a

14、sselect cVouchType,cSBVCode,SUM(iNatMoney)fpjefrom SaleBillVouch a join SaleBillVouchs bon a.SBVID=b.SBVID group by cVouchType,cSBVCodeselect *from v5 a full join v6 bon coutbillsign=cVouchType and coutid=cSBVCodewhere coutbillsign is null or coutid is null or isnull(pzje,0)<>ISNULL(fpje,0)-13

15、.代码级次为，生成一个新的科目代码表，表中包含两个字段（科目代码、科目全称）select * from codeorder by ccodeselect kmdm= case when LEN(a.ccode)=3 then b.ccode when LEN(a.ccode)=5 then b.ccode+'0'+RIGHT(c.ccode,2) when LEN(a.ccode)=7 then b.ccode+'0'+RIGHT(d.ccode,4) end,kmqc= case when LEN(a.ccode)=3 then b.ccode_name wh

16、en LEN(a.ccode)=5 then b.ccode_name+'/'+c.ccode_name when LEN(a.ccode)=7 then b.ccode_name+'/'+c.ccode_name+'/'+d.ccode_name endfrom code a join code b on LEFT(a.ccode,3)=b.ccode join code c on LEFT(a.ccode,5)=c.ccode join code d on LEFT(a.ccode,7)=d.ccode order by kmdm -或简单转

17、换（看题目是如何要求）：select a.ccode,kmqc=b.ccode_name+ case when LEN(a.ccode)=6 then '-'+c.ccode_name when LEN(a.ccode)=8 then '-'+c.ccode_name+'-'+d.ccode_name else '' end,a.bendfrom code a join code b on left(a.ccode,3)=b.ccode join code c on LEFT(a.ccode,6)=c.ccode join cod

18、e d on LEFT(a.ccode,8)=d.ccodeorder by a.ccode-14.将主营业务收入明细账记录从开始到最大值分层,统计每层业务笔数金额以及占总业务笔数,金额的比率select SUM(mc),MAX(mc)/10,COUNT(*)from GL_accvouch where ccode='501' and mc<>0select ceiling(mc/40800.0) ceng,COUNT(*),COUNT(*)/27.0,SUM(mc),SUM(mc)/4737700from GL_accvouch where ccode='

19、501' and mc<>0group by ceiling(mc/40800.0)-15.将主营业务收入明细账记录从正的最小值开始到最大值分层,统计每层业务笔数,金额,-以及占总业务笔数、金额的比率select MAX(mc),MIN(mc),(MAX(mc)-MIN(mc)/10,COUNT(*),SUM(mc)from GL_accvouch where ccode='501' and mc<>0select ceng=case when ceiling(mc-6000)/40800.0)=0 then 1 else ceiling(mc-

20、6000)/40800.0)end,COUNT(*),count(*)/27.0,SUM(mc),SUM(mc)/473770from GL_accvouch where ccode='501' and mc<>0group by ceiling(mc-6000)/40800.0)-16.将主营业务收入明细账记录分为层,包括万元以下,2万-3万元,3-4万元,4万元以上,统计每层业务笔数,金额,-以及占总业务笔数、金额的比率select SUM(mc),COUNT(*)from GL_accvouch where ccode='501' and mc

21、<>0select ceng= case when mc<=20000 then 1 when mc>20000 and mc<=30000 then 2 when mc>30000 and mc<=40000 then 3 when mc>40000 then 4 end,COUNT(*),COUNT(*)/27.0,SUM(mc),SUM(mc)/4733700from GL_accvouch where ccode='501' and mc<>0group by case when mc<=20000 th

22、en 1 when mc>20000 and mc<=30000 then 2 when mc>30000 and mc<=40000 then 3 when mc>40000 then 4 end -17.检查销售发票断号重号select cVouchType,MAX(cSBVCode),MIN(cSBVCode),COUNT(*)from SaleBillVouch group by cVouchTypeselect cVouchType,cSBVCode,COUNT(*)from SaleBillVouch group by cVouchType,cSBVC

23、odehaving COUNT(*)>1 -18、检查销售发票副联是否附有发运凭证（或提货单)及顾客订货单/*检查销售发票副联是否附有发运凭证*/select a.*from SaleBillVouch a left join DispatchList bon a.SBVID=b.SBVID where b.SBVID is null /*检查销售发票副联是否附有顾客订货单*/select a.*from SaleBillVouch a left join SO_SOMain bon a.cSOCode=b.cSOCode where b.cSOCode is null/*追查主营业务收

24、入的明细账中的分录销售发票*/select a.*from GL_accvouch a left join SaleBillVouch bon coutbillsign=cVouchType and coutid=cSBVCode where ccode='501' and mc<>0 and cVouchType is null-19.检查顾客的赊购是否经过授权批准-应用逻辑访问控制检查-信息系统审计-检查授权批准人的名称-列出用户账号-检查账号密码-检查执行情况-检查伪造可能性-20.发票与收入明细账的核对，所有的发票已记入收入!-所有字段都在左连接的表上，所以

25、要把收入的条件放在join里！select a.*from SaleBillVouch a left join GL_accvouch bon cVouchType=coutbillsign and cSBVCode=coutid and ccode='501' and mc<>0where coutbillsign is null-明确主营业务收入与发票记录之间的关系，-结论：一张会计凭证里的的MC对应一张发票表中的两条子表发票记录-所以对发票的主子表进行操作时都应将相关的金额sumcreate view v_fp asselect a.cvouchtype,a.

26、csbvcode,fpje=sum(b.inatmoney)-汇总发票子表的金额from salebillvouch ajoin salebillvouchs bon a.sbvid=b.sbvidgroup by a.cvouchtype,a.csbvcodeselect a.iperiod,a.csign,a.ino_id,b.cvouchtype,b.csbvcode,a.mc,b.fpjefrom gl_accvouch a join v_fp bon a.coutbillsign=b.cvouchtype and a.coutid=b.csbvcodewhere a.ccode li

27、ke '501%'and a.mc<>b.fpje-21.检索出所有确认收入时，未提取应交增值税的收入明细账记录。create view sr1 as select a.*from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where b.ccode='501' and b.mc<>0create view sr2 as select a.*from GL_accvouch a join

28、GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where b.ccode like '221%' and b.mc<>0select a.*from sr1 a left join sr2 b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode='501' and a.mc<>0 and b.ccode is null-22

29、.对凭证表和余额表的发生额进行一致性检查。（只对比总账，并且已记账的记录。）create view pz as select iperiod,left(ccode,3) ccode,sum(md) 借方发生,SUM(mc) 贷方发生 from GL_accvouch where ibook=1 group by iperiod,LEFT(ccode,3) create view ye as select iperiod,ccode,SUM(md) 借方发生,SUM(mc) 贷方发生 from GL_accsum where LEN(ccode)=3 group by iperiod,ccode

30、 select *from pz a full join ye bon a.iperiod=b.iperiod and a.ccode=b.ccodewhere isnull(a.借方发生,0)<>isnull(b.借方发生,0) or isnull(a.贷方发生,0)<>isnull(b.贷方发生,0)order by a.iperiod,a.ccode-23.计算各月的收入，成本，税金，毛利率select iperiod,SUM(case when ccode='501' and mc<>0 then isnull(mc,0) end)s

31、r,SUM(case when ccode='502' and md<>0 then isnull(md,0) end)cb,SUM(case when ccode='504' and md<>0 then isnull(md,0) end)sj,mll=(SUM(case when ccode='501' and mc<>0 then isnull(mc,0) end)- SUM(case when ccode='502' and md<>0 then isnull(md,0) e

32、nd)- SUM(case when ccode='504' and md<>0 then isnull(md,0) end)/SUM(case when ccode='501' and mc<>0 then mc end)from GL_accvouch group by iperiod create view sr3 asselect iperiod,SUM(mc) srfrom GL_accvouch where ccode='501' and mc<>0group by iperiodcreate vi

33、ew cb asselect iperiod,SUM(md) cbfrom GL_accvouch where ccode='502' and md<>0group by iperiod create view sj asselect iperiod,SUM(mc) sjfrom GL_accvouch where ccode like '504' and mc<>0group by iperiod select a.iperiod,isnull(sr,0),isnull(cb,0),isnull(sj,0) ,mll=(isnull(s

34、r,0)-isnull(cb,0)-isnull(sj,0)/isnull(sr,0.0001)from sr3 a full join cb b on a.iperiod=b.iperiodfull join sj c on a.iperiod=c.iperiod-24.汇总计算出各总账科目的年初余额和年末余额，查询结果中应包括：-科目代码、年初余额、年末余额，其中余额的方向通过金额的正负来表示。-(在年初余额的视图里要放cbegind_c，以便进行年末余额时的判断计算。)create view ye3 as select ccode,cbegind_c,ncye= case when cb

35、egind_c='借' then mb else -mb end from GL_accsumwhere iperiod=1 and LEN(ccode)=3create view ye4 as select left(ccode,3) ccode,SUM(md) jfje,SUM(mc) dfjefrom GL_accvouch group by LEFT(ccode,3)select a.ccode,ccode_name,ncye,nmye= case when b.cbegind_c='借' then ncye+jfje-dfje else -ncye+d

36、fje-jfje endfrom code a join ye3 b on a.ccode=b.ccodejoin ye4 c on a.ccode=c.ccode-以下为历年练习题：-1、请检索出采购现付的记账凭证（材料采购科目）。*/select * from code where ccode_name like '材料采购'select a.*from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id join GL_accvou

37、ch c on a.iperiod=c.iperiod and a.csign=c.csign and a.ino_id=c.ino_idwhere b.ccode='121' and b.md<>0 and c.ccode='101' and c.mc<>0-2、cname字段记录了业务经手人，请以该字段为分组依据，计算各位经手人的赊销业务笔数和赊销金额。select ame,COUNT(*),SUM(a.mc)from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and

38、 a.csign=b.csign and a.ino_id=b.ino_id where a.ccode='501' and a.mc<>0 and b.ccode='113' and b.md<>0group by ame -3、请追查发票单至应收款明细账，要求列出未记入应收款明细账的发票记录。create view fp1 as select cVouchType,cSBVCode,SUM(iNatSum)fpjefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID

39、 group by cVouchType,cSBVCodecreate view ys1 asselect a.coutbillsign,a.coutid,SUM(a.md) pzjefrom GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode='113' and a.md<>0 and b.ccode='501' and b.mc<>0group by a.coutbi

40、llsign,a.coutidselect a.*from fp1 a left join ys1 bon cVouchType=coutbillsign and cSBVCode=coutidwhere coutbillsign is null/*4、请统计出月份当月发生当月收回的应收款金额，假设该企业从月份起开始建账，并且一笔应收款总是一次收回，要求列出金额。*/create view sh1 as -当月收回 select a.* from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csig

41、n and a.ino_id=b.ino_id where a.ccode='113' and a.mc<>0 and (b.ccode like '101' or b.ccode like '102%') and a.iperiod=10 create view sh2 as -当月发生 select * from GL_accvouch where iperiod=10 and ccode='113' and md<>0 select SUM(a.mc) -当月发生并收回from sh1 a join

42、sh2 b on a.iperiod=b.iperiodwhere a.mc=b.md/*2检索出各月现销、赊销占主营业务收入的比重，包括：期间、主营业务收入总金额、现销金额、现销占比、赊销金额、赊销占比。*/-一步法：select a.iperiod,SUM(a.mc) zje,xsje=SUM(case when b.ccode like '101' OR b.ccode like '102%' and b.md<>0 then a.mc end),xxzb=SUM(case when b.ccode like '101' OR

43、b.ccode like '102%' then a.mc end)/SUM(a.mc) ,sxje=SUM(case when b.ccode like '113%' and b.md<>0 then a.mc end),sxzb=SUM(case when b.ccode like '113%' and b.md<>0 then a.mc end)/SUM(a.mc) from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.cs

44、ign and a.ino_id=b.ino_id where a.ccode='501' and a.mc<>0 and b.ccode in ('101','10201','113') and b.md<>0group by a.iperiod-分步法：create view d5 as select a.iperiod,SUM(a.mc) xxje from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.cs

45、ign and a.ino_id=b.ino_id where a.ccode='501' and a.mc<>0 and (b.ccode like '101' or b.ccode like '102%') group by a.iperiod create view d6 as select a.iperiod,SUM(a.mc) sxje from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b

46、.ino_id where a.ccode='501' and a.mc <>0 and b.ccode='113' group by a.iperiod create view d7 as select iperiod,SUM(mc) zje from GL_accvouch where ccode='501' and mc<>0 group by iperiod select b.iperiod,zje,xxje,xxje/zje,sxje,sxje/zjefrom d5 a full join d6 b on a.i

47、period=b.iperiodfull join d7 c on b.iperiod=c.iperiod/*2、审计人员需要通过重新计算发票中的无税金额、税额、含税金额，再和发票中对应的金额进行核对，并计算对应金额的差值。(计算所得无税金额与发票中无税金额之差，计算所得税额与发票中的金额之差，计算所得金额与发票中的含税金额之差)。请查询发票信息如下信息：计算获得的无税金额，计算获得的税额，计算获得的含税金额，发票无税金额，发票税额，发票含税金额，无税金额之差、税额之差、含税金额之差。(15分)*/-重算：create view fpcs11 asselect a.SBVID,SUM(iUni

48、tPrice*iQuantity) csws,SUM(iTaxUnitPrice*iQuantity) cshs ,csse=SUM(iTaxUnitPrice*iQuantity)-SUM(iUnitPrice*iQuantity)from SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.SBVIDselect iUnitPrice,iTaxUnitPrice from SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID -原发票create

49、view yfp as select a.SBVID ,SUM(iNatMoney) fpws,SUM(iNatTax) fpse,SUM(iNatSum) fphsfrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.SBVIDselect sum(csws) 无税金额,sum(csse) 税额, sum(cshs) 含税金额 ,sum(fpws)发票无税金额,sum(fpse) 发票税额,sum(fphs) 发票含税金额, 无税金额之差=sum(csws)-sum(fpws),税额之差=sum(cs

50、se)-sum(fpse),含税金额之差=sum(cshs)-sum(fphs) from fpcs11 a join yfp b on a.sbvid=b.sbvid-3、资产负债率为负债总额除以资产总额的百分比，请查询资产负债率。(15分)-一步法：select iperiod,sum(case when cclass='负债' then me end)/(SUM(case when cclass='资产' and cendd_c='借' then me end)-SUM(case when cclass='资产' and c

51、endd_c='贷' then me end)from GL_accsum a join code bon a.ccode=b.ccode group by iperiod -分步法：create view zc1 as select iperiod,SUM(me) jzcfrom GL_accsum a join code b on a.ccode=b.ccode where LEN(a.ccode)=3 and cclass='资产' and cendd_c='借'group by iperiodcreate view zc2 as sele

52、ct iperiod,SUM(me) bdfrom GL_accsum a join code b on a.ccode=b.ccode where LEN(a.ccode)=3 and cclass='资产' and cendd_c='贷'group by iperiodcreate view zc3 as select iperiod,SUM(me) fzfrom GL_accsum a join code b on a.ccode=b.ccode where LEN(a.ccode)=3 and cclass='负债'group by ip

53、eriodselect a.iperiod,zcfzl=fz/(jzc-bd)from zc1 a join zc2 b on a.iperiod=b.iperiodjoin zc3 c on a.iperiod=c.iperiod-2、为了检查应收账款收回情况，审计人员将发票与主营业务收入明细账进行比较，找今年新增但未核销的应收账款明细账。（分）-今年新增应收款create view xzys asselect a.*from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.i

54、no_id=b.ino_id where a.ccode='113' and a.md<>0 and b.ccode='501' and b.mc<>0-今年已经收回create view ysh as select a.*from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode='113' and a.mc<>0 and b.ccode

55、 in ('101','10201') and b.md<>0-今年新增未核销select a.*from xzys a left join ysh b on a.coutbillsign=b.coutbillsign and a.coutid=b.coutidwhere b.coutid is null /*3、为了检查发票账龄情况，审计人员将发票与应收账款明细账进行比较，现场审计时间为年月日，请统计未全额收回的发票的账龄，将账龄从开始分为三层，统计各层数量，金额及各自占比。（分）*/create view wsh as select a.*fro

56、m xzys a left join ysh b on a.coutbillsign=b.coutbillsign and a.coutid=b.coutidwhere b.coutid is null select MAX(DATEDIFF(DAY,dbill_date,'2002-5-31')/3,SUM(md),COUNT(*)from wshselect case when ceiling(DATEDIFF(DAY,dbill_date,'2002-5-31')/70.0)=3 then 1 when ceiling(DATEDIFF(DAY,dbill

57、_date,'2002-5-31')/70.0)=4 then 2 end ceng,count(*),count(*)/17.0,SUM(md),SUM(md)/3741989.0 from wshgroup by ceiling(DATEDIFF(DAY,dbill_date,'2002-5-31')/70.0)/*1、请从凭证表中检索出现金支出大于万元的明细账记录，结果包括（会计期间、凭证号、科目代码、科目名称、摘要、借贷方向、金额、凭证审核人）。（分）*/select iperiod,ino_id,a.ccode,ccode_name,cdigest,借

58、贷方向= case when md<>0 then '借' when mc<>0 then '贷' end,je=mc+md,ccheck from GL_accvouch a join code b on a.ccode=b.ccode where a.ccode like '101' and a.mc>10000-2、检索出所有科目的年初余额，结果包含四个字段（科目代码、科目全称、年初借方余额、年初贷方余额）（分）create view km asselect a.ccode,kmqc= case when len(a.ccode)=3 then b.ccode_name when LEN(a.ccode)=5 then b.ccode_name+'/'+c.ccode_name when LEN(a.ccode)=7 then b.ccode_name+'/'+c.ccode_name+'/'+d.ccode_name endfr