S2第三章

       --变量
--字符串类型
declare @num nvarchar(32)
set @num='席洪丽'
print @num
--数字类型    numeric(18,2)   
DECLARE @money numeric(18,2)
set @money=70.00
print @money
--decimal(18,2)
declare @balance decimal(18,2)
set @balance=90.00
print @balance
--日期类型
declare @brithday datetime
set @brithday='1997-02-02'
print @brithday

         --cast convert 用法
declare @number int
set @number=5
print 'num value is ' + cast(@number as nvarchar(32))
print 'num value is ' + convert(nvarchar(32),@number)
--练习
--统计2013-08-09的oop的考试平均分
--如果成绩在70分以上输出'成绩优秀'并显示前三名学生分考试成绩
declare  @SId int
select @SId=SubjectId from Subject
where SubjectName='oop'
    

declare @pinjun int
select @pinjun =avg(StudentResult) from Result
where ExamDate>='2013-08-09' and ExamDate<'2013-08-10' 
and SubjectId=@SId


if(@pinjun>70)
begin
print '考试成绩优秀'
select  top 3 * from result
where ExamDate>='2013-08-09' and ExamDate<'2013-08-10'
and SubjectId=@SId
order by StudentResult desc
end
else
begin
print '不合格'
select top 3 * from Result
where Examdate>='2013-0809' and ExamDate<'2013-08-10'
and SubjectId=@SId
order by StudentResult asc
end