第三章 sql编程 练习

--打印直角三角形(法1)
                  --声明列,赋值
                  declare @row int
					set @row=1
					--声明行,赋值
					declare @low int 
					set @low=1
					--声明符号,赋值
					declare @xx varchar(10)
					set @xx='*'
					
					while(@row<=4)
					begin 
					
					while(@low<=@row)
					begin 
					
					print @xx
					
					select @xx=@xx+'*'
				    set @low=@low+1
				    
					end
					
					set @row=@row+1
					
					end
--打印直角三角形(法2)
declare @str2 nvarchar(200)
 declare @count int
 declare @result nvarchar(200)
 set @str2='*'
 set @count=0
 set @result=''0
 while(@count<4)
	begin
		set @result+=@str2
		print @result
		set @count+=1
	end
					
	--1--100的偶数和
	 declare @sum int
        set @sum=0
    	declare @num int
    	set @num=1
    	
	  while(@num<=100)begin
		  if(@num%2=0)
		  begin
			 set @sum=@num+@sum
		  end
		set  @num=@num+1
	  end
	  print @sum	
	  
	  --case end 案例
	create table sf(shengfu varchar(10),date datetime)
	insert into sf values('胜','2017-1-2')
    insert into sf values('胜','2017-1-1')
    insert into sf values('胜','2017-1-1')
    insert into sf values('胜','2017-1-2')
    insert into sf values('负','2017-1-2') 
    insert into sf values('负','2017-1-2')
    insert into sf values('负','2017-1-1')
    insert into sf values('负','2017-1-1')
    select * from sf
    
   select date as 日期,
   sum(case  when shengfu='胜' then 1 else 0 end)as 胜,
   sum(case  when shengfu='负' then 1 else 0 end)as 负    from sf           
       group by date
    

	  
  create table tb(LocTion varchar(10),iwork varchar(10),imoney int,itime int,age int)
   insert into tb values('广州', '程序员', 1000 , 5  , 22 )
   insert into tb values('广州', '教师'  , 1700 , 10 , 22 )
 insert into tb values('广州', '警察'  , 1300 , 15 , 22 )
  insert into tb values('广州', '警察'  , 800  , 5  , 22 )
   insert into tb values('上海', '程序员', 1600 , 5  , 21 )
   insert into tb values('上海', '司机'  , 1200 , 15 , 21 )
   insert into tb values('北京', '程序员', 1400 , 5  , 29 )

  select * from tb
   select loction , iwork, age,
   sum(case itime when 5 then imoney else 0 end),
   sum(case itime when 10 then imoney else 0 end),
   sum(case itime when 15 then imoney else 0 end) from tb
       group by loction , iwork ,age

                                       

--Case End 经典练习**************************************
--采用美国ABCDE五级打分制显示学生'走进Java编程世界'课程最近一次考试成绩（姓名等级）
--A级:   90分以上
--B级：  80－分
--C级:   70－分
--D级：60－分
--E级：60分以下
--01.找一个变量存储科目编号 
declare @subid int 
select @subid=subjectid from subject
where subjectname='走进Java编程世界'


--02.找到一个变量 ，存储最近一次考试时间 

declare @maxdate datetime 
select @maxdate= max(examdate) from result
where subjectid=@subid
 
  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 student,result
  where student.StudentNo=result.StudentNo
  and subjectid=@subid
  and examdate=@maxdate
  


--经典while循环加分题目***************************************
--检查学生参加“oop”课最近一次考试是否有不及格（60分及格）的学生。
--如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格

--①
--01.找一个变量存储o科目编号 
declare @subid2 int 
select @subid2=subjectid from subject
where subjectname='走进Java编程世界'
--02.找到一个变量 ，存储最近一次考试时间 
declare @maxdate2 datetime 
select @maxdate2= max(examdate) from result
where subjectid=@subid2

select * from result
where subjectid=@subid2
and  examdate=@maxdate2


declare @su int
select @su=COUNT(Studentno) from Result
where subjectid=@subid2 
and  examdate=@maxdate2
and studentresult<70

while(@su>0)
begin
   --每人+2分
   update result set studentresult+=2
   where subjectid=@subid2 
	and  examdate=@maxdate2
    and studentresult<95
    
    select @su=COUNT(Studentno) from Result
where subjectid=@subid2
and  examdate=@maxdate2
and studentresult<95

end
--②
declare @maxdate2 datetime --找到一个变量 ，存储最近一次考试时间 
declare @subid2 int   --找一个变量存储o科目编号
select @subid2=subjectid from subject
where subjectname='走进Java编程世界'

select @maxdate2= max(examdate) from result
where subjectid=@subid2


declare @n int
while(@n>0)
begin
   select @n=COUNT(Studentno) from Result
   where SubjectId=@subid2 and ExamDate=@maxdate2 and StudentResult<60
   if(@n>0)
   update result set studentresult+=2 from Result--每人+2分
   where subjectid=@subid2 
	and  examdate=@maxdate2
    and studentresult<95
    else
    break
    end
    print '加分后的成绩如下:' select StudentNo,studentresult from Result
    where SubjectId=@subid2 and ExamDate=@maxdate2