SQL Server 基础练习题1

1、任意给定两个整数，求这两个数之和

源码：

declare @x int,@y int, @z int
set @x=1
set @y=2
set @z=@x+@y
print 'x+y='+ltrim(str(@z))

2、使用单分支的条件语句编程，计算并输出两个整数的最大值

源码：

declare @a as int,@b as int,@max as int
set @a=3
set @b=5
if @a>@b
   set  @max=@a
if @a<=@b
   set @max=@b
print 'max='+cast(@max as char(3))

3、使用双分支的条件语句编程，计算交输出两个整数的最大值

源码：

declare @a as int,@b as int,@max as int
set @a=3
set @b=5
if @a>@b
   set @max=@a
else
   set @max=@b
print 'max='+convert(char(3),@max)

4、有一函数

①当x<0时,y=-1；

②当x=0时，y=0；

③当x>0时，y=1。编写程序，输入一个x值，输出y值

算法一：

源码：

declare @x as int,@y as int
set @x=3
if @x<0
   set @y=-1
else
   if @x=0
      set @y=0
   else
      set @y=1
print @y

算法二：

源码：

declare @x as int,@y as int
set @x=3
set @y=case  
         when @x<0 then  -1
         when @x=0 then  0
         else 1
end
print @y

5、将学生百分制成绩输出等级制成绩，百分制分数段[90，100]为优；[80，90）为良；[70，80）为中；[60，70）为及格；[0，60）为不及格

源码：

declare @bcj real,@dcj varchar(18)
set @bcj=86.8
set @dcj=case  
          when @bcj >=90 and @bcj<=100 then '优'
          when @bcj >=80 and @bcj<90 then '良'
          when @bcj >=70 and @bcj<80 then '中'
          when @bcj >=60 and @bcj<70 then '及格'
          when @bcj >=0 and @bcj<60 then '不及格'
          else '这不是百分制成绩！'
       end
print @dcj

6、根据自然数字，输出相应的星期的英文单词

declare @a  int,@y  varchar(20)
set @a=6
set @y=case  
          when @a =1 then 'Monday'
          when @a =2 then 'Tuesday'
          when @a =3 then 'Wednesday'
          when @a =4 then 'Thursday'
          when @a =5 then 'Fridday'
          when @a =6 then 'Saturday'
          when @a =7 then 'Sunday'
          else 'No such day'
       end
print @y
此程序还可以这样写
declare @a  int,@y  varchar(20)
set @a=6
set @y=case  @a
          when 1 then 'Monday'
          when 2 then 'Tuesday'
          when 3 then 'Wednesday'
          when 4 then 'Thursday'
          when 5 then 'Fridday'
          when 6 then 'Saturday'
          when 7 then 'Sunday'
          else 'No such day'
       end
print @y

7、计算1+2+……+100

源码：

declare @sum int,@i int
set @sum=0
set @i=1
while @i<=100
      begin
         set @sum=@sum+@i
         set @i=@i+1
      end
print @sum

8、找出100至999之间的所有“水仙花数”。所谓“水仙花数”是指一个三位数，其各位数字的立方和等于该数本身（如153＝13+53+33）。请编程计算并输出所有的水仙花数

源码：

declare @n as int,@b as int,@s as int,@g as int
set @n=100
while @n<1000
     begin
         set @b=cast(substring(cast(@n as char(3)),1, 1) as int) 
         set @s=cast(substring(cast(@n as char(3)),2,1) as int)
         set @g=cast(substring(cast(@n as char(3)),3,1) as int)
         if power(@b,3)+power(@s,3)+power(@g,3)=@n
            begin
               print @n
            end

9、编程输出如下九九乘法表

源码：

/*i表示被乘数，又表示打印列数；j表示乘数，又表示打印行数*/
declare @i tinyint,@j tinyint,@s varchar(100)
set @j=1
while @j<=9
   begin
      set @i=1
      set @s=space(0)
      while @i<=@j
         begin
            set @s=@s+cast(@i as char(1))+'×'+cast(@j as char(1))+'='+cast((@i*@j) as char(2))+space(1)
            set @i=@i+1
         end
      print @s
      set @j=@j+1
   end