优化MySchool SQL编程 第三章

 

/****** Script for SelectTopNRows command from SSMS  ******/SELECT TOP 1000 [StudentNo]      ,[LoginPwd]      ,[StudentName]      ,[Gender]      ,[GradeId]      ,[Phone]      ,[Address]      ,[Birthday]      ,[Email]      ,[MyTT]  FROM [MySchool].[dbo].[Student]    select * from dbo.Subject    --查询oop课程  declare  @sbjec  int  select  @sbjec=SubjectId from dbo.Subject  where SubjectName='oop'    --最近一次考试时间  --select * from dbo.Result  declare @datetime  datetime  select @datetime=MAX(ExamDate) from dbo.Result where SubjectId=@sbjec      select * from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime    --投影出符合的人数  declare @sum  int  select @sum=COUNT(StudentNo) from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<60        while(@sum>0)  --每人加两分  begin   update dbo.Result set StudentResult=StudentResult+2 where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<95    select  @sum=COUNT(StudentNo) from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<70  end      --1到100之间的偶数之和    --(第一种方法)   declare @sun int    set @sun=1   declare  @num int    set @num=0 while(@sun<=100)beginif(@sun%2=0)beginset@num=@num+@sunendset  @sun=@sun+1      endprint @num--(第二种方法)declare @num1 intset @num1=0    declare @sum1 int    set @sum1=2    while(@sum1<=100)    beginif(@sum1%2=0)beginset@num1=@num1+@sum1endset @sum1=@sum1+1endprint @num1---------------***************------------------------------declare @row  intset @row=1declare @i int set @i=1declare @chr nvarchar(32)    set @chr=''    while(@row<=5)    begin     while(@i<=@row)    begin    set  @chr+='★'    set  @i+=1    end    print @chr    set  @row+=1    end  -------------(2)-------------    declare @j int    set  @j=1    declare @xing nvarchar(32)    set @xing='★'    while( @j<=5)    begin    print  @xing    set @xing+='★'    set  @j+=1    end                  --------------------------CASE  end---多分支语句--------------------------------查询oop课程  declare  @sbjecc  int  select  @sbjecc=SubjectId from dbo.Subject  where SubjectName='oop'    --最近一次考试时间  --select * from dbo.Result  declare @datet  datetime  select @datet=MAX(ExamDate) from dbo.Result where SubjectId=@sbjecc    select StudentName,等级=  case  when StudentResult>90 then 'A'  when StudentResult>80 then 'B'  when StudentResult>=70 then 'C'  when StudentResult>60 then 'D'  else      'E'    end  from dbo.Student S,dbo.Result F where S.StudentNo=F.StudentNo  and ExamDate=@datet and SubjectId=@sbjecc    --------------------------SQL语句面试题，关于group by-------------------------------   create table tmp(rq varchar(10),shengfu nchar(1))  select * from tmp  insert into tmp values('2005-05-09','胜')  insert into tmp values('2005-05-09','胜')  insert into tmp values('2005-05-09','负')  insert into tmp values('2005-05-09','负')  insert into tmp values('2005-05-10','胜')  insert into tmp values('2005-05-10','负')  insert into tmp values('2005-05-10','负')  select rq as 时间 ,  sum(  case   when shengfu='胜' then 1  else 0  end) as 胜,  sum(  case  when shengfu='负' then 1  else 0  end) as 负  from tmp  group by rq

--课后简答

--九九乘法表

DECLARE @i intDECLARE @j intDECLARE @str varchar(110)SET  @i=1WHILE  @i<10  BEGIN     SET  @j=1     SET @str=''     WHILE @j<=@i        BEGIN          SET @str= @str+CAST(@i AS varchar(1))+' x '+CAST(@j AS varchar(1))+'='+CAST(@i*@j AS varchar(2))+'    '          SET  @j=@j+1        END    print @str    SET  @i=@i+1  END

--查询罚款

/*数据库表见：建库建表.sql*/Print '罚款记录表情况如下：'Select RID ,BID ,PDate,Amount, 罚款类型=case                  When PType=1 then '损坏'                  When Ptype=2 then '延期'                  When Ptype=3 then  '丢失'End from Penalty

--借书记录

/*数据库表见：建库建表.sql*/declare @count intdeclare @TDay datetimeset @TDay=DateAdd(dd,1,getDate()) --获取明天的日期select @count=count(*) from Borrow where willDate<@TDayIF(@count=0)    BEGIN       print '明天没有应归还的图书'    EndElseIF(@count<10)BEGIN  Update Borrow set WillDate=DateAdd(dd,2,WillDate) WHERE willDate<@TDayENDElse BEGIN print '还书总数量：'SELECT book.BName, reader.RName, borrow.LendDate  FROM Book as book,Reader as reader,Borrow as borrow WHERE book.BID=borrow.BID and reader.RID=borrow.RID and borrow.willDate<@TDayprint @countEnd

--查询图书总额

/*数据库表见：建库建表.sql*/DECLARE @count int,@totalMoney moneySELECT @count=sum(BCount) FROM bookSELECT @totalMoney=sum(BCount*Price) FROM bookprint  '现存数量'+convert(varchar(10),@count)print  '总金额'+convert(varchar(10),@totalMoney)IF @count<10000   print '现有图书不足一万本，还需要继续购置书籍' ELSE   print '现有图书在一万本以上，需要管理员加强图书管理'