您的位置:自考365 > 复习指导 > 历年试题 > 北大“数据库原理”上机实践题目总结(11)

北大“数据库原理”上机实践题目总结(11)

2007-07-18 14:47   【 】【我要纠错

  /*所在地是北京,余额大于10的会员*/

  select NID,NName,NAdd,NCash

  from Numbers

  where NAdd = 'address1' and NCash >= 2

  /*获胜次数最多的马的产地*/

  select HAdd

  from Horses

  where Horses.HID in

  (Select HID

  from RRecord

  group by HID

  having count(*) >= all

  (Select count(*)

  from RRecord

  group by HID)

  )

  /*投注次数最多的会员*/

  select NID

  from CRecord

  group by NID

  having count(*) >= all

  (Select count(*)

  from CRecord

  group by NID)

  /*U001投注*/

  insert into CRecord

  VALUES ('U001','H003',3)

  update Numbers

  set NCash = NCash - 2

  where NID = 'U001'

  update Numbers

  set NCash = NCash + 10

  where NID = 'U001' and exists

  (select *

  from RRecord,CRecord

  where RRecord.SerNum = CRecord.SerNum and RRecord.HID = CRecord.HID and NID = 'U001'

  )

  /*得钱最多的会员(只考虑奖励)*/

  declare @NumID char(5)

  declare @MAX_NumID char(5)

  declare @Earning int

  declare @MAX_Earning int

  set @MAX_Earning = 0

  declare CurNum cursor for

  select distinct NID,count(*)

  from RRecord,CRecord

  where RRecord.HID = CRecord.HID

  group by NID

  open CurNum

  fetch from CurNum into @NumID,@Earning

  while @@fetch_status = 0

  begin

  if @Earning > @MAX_Earning

  begin

  set @MAX_Earning = @Earning

  set @MAX_NumID = @NumID

  end

  fetch from CurNum into @NumID,@Earning

  end

  close CurNum

  deallocate CurNum

  print '得到奖厉最多的会员'

  print @MAX_NumID + CONVERT(Varchar(20),@MAX_Earning * 10)

  /*赚钱最多的会员(包括投注的钱)*/

  /*=======================================================*/

本文转载链接:北大“数据库原理”上机实践题目总结(11)

分享到:
  • 站内搜索
  • 课程搜索
  • 试题搜索

热门搜索:教材 报名 查分 免考 考试计划