第二章 SQL编程 , 类型转换 , ifelse , 上机练习

    xiaoxiao2021-03-26  9

    --字符串类型 declare @name nvarchar(32) -- 声明 set @name ='库里' -- 输出语句 print @name -- 数字类型 declare @balance decimal(18,2) set @balance=16.23 print @balance --日期类型 存储‘火火’出生日期 declare @birthday datetime set @birthday ='1998-02-18' print @birthday -- 数据类型转换 cast as declare @num int set @num = 500000 print '这个数是:'+ cast(@num as nvarchar(32)) -- 数据类型转换 convert declare @num1 int set @num1 = 3000 print '这个数是:::'+ convert(nvarchar(32),@num1) -- if else declare @age int set @age = 15 if(@age>=18) begin print'可以观看' end else begin print'不可以观看!再长几年吧' end --练习 --统计并显示2013-08-09的oop考试的平均分 --如果平均分在70分以上,显示“考试成绩优秀”,并显示前三名学生的考试信息 --如果在以下,显示“考试成绩较差”,并显示后三名学生的考试成绩 select * from result order by examdate --1.求出oop课程对应的课程编号 declare @subid int select @subid=SubjectId from Subject where subjectname='oop' --2.查询平均分 declare @avg int select @avg=avg(Studentresult) from result where examdate>='2013-08-09' and examdate<'2013-08-10' and subjectid=@subid if(@avg>=70) begin print '成绩优秀' --打印前三名的成绩 select top 3 * from result where examdate>='2013-08-09' and examdate<'2013-08-10' and subjectid=@subid order by studentresult desc end else begin print '成绩较差' --打印前三名的成绩 select top 3 * from result where examdate>='2013-08-09' and examdate<'2013-08-10' and subjectid=@subid order by studentresult end -- 上机一 declare @tu nvarchar(32) set @tu ='☆' print @tu print @tu+@tu print @tu+@tu+@tu print @tu+@tu+@tu+@tu print @tu+@tu+@tu+@tu+@tu -- 上机二 select studentno as '学号',StudentName as '姓名',FLOOR(DATEDIFF(dy,BornDate,getdate())/365) as '年龄' from dbo.Student where studentno='S1101001' declare @yy int select @yy=DATEPART(YY,BornDate) from student where studentno='S1101001' select * from student where DATEPART(YY,BornDate)=@yy+1 or DATEPART(YY,BornDate)=@yy-1 go -- 上机三 -- 1 查询课程id declare @id2 int select @id2=subjectid from Subject WHERE subjectname='java' -- 2 查询该学生的成绩 学号为 23219 declare @cheng int select @cheng=Studentresult from result where subjectid=@id2 and studentno=23219 --print @chengji if(@cheng>=85) begin Print '优秀!' end else if(@cheng>=70) begin Print '良好!' end else if(@cheng>=60) begin Print '中等!' end else begin Print '差!' end
    转载请注明原文地址: https://ju.6miu.com/read-500079.html

    最新回复(0)