6.T-SQL编程

 use YGGL

 --创建一个名为female的用户变量，并在select语句中使用该
 --局部变量查找表中所有女员工的编号和姓名
declare @female bit
set @female=0
select EmpID,EmpName from Employees
where EmpSex=@female

--定义一个变量，用于获取号码为102201的员工的电话号码
declare @phone char(12)
set @phone=(select PhoneNum from Employees where EmpID=102201)
select @phone

--定义一个变量，用于描述Salary表中000001号员工的实际收入
declare @income float
set @income=(select InCome from Salary where EmpID=000001)
select @income 

--流程控制语句 begin...end,if...else,case,goto,while,continue,break,return,waitfor
--判断Employees表中是否存在编号为111006的员工，如果存在，则显示该员工信息；若不存在，则
--显示“查无此人”
if (select EmpName from Employees where EmpID=111006)is not null
   select * from Employees where EmpID=111006
else (select '查无此人')

--判断姓名为“王林”的员工实际收入是否高于3000，如果是，显示其收入，否则显示‘收入不高于3000’
if(select InCome from Salary where EmpID in (Select EmpID from Employees where EmpName='王林'))>3000
 select InCome from Salary where EmpID in (Select EmpID from Employees where EmpName='王林')
else select '收入不高于3000'

--假设变量X的初始值为0，每次加1，直至x变为5
declare @x int
set @x=2
while(@x<10)
begin
set @x=@x+1
end
select @x

--使用循环输出一个用“*”组成的三角形
declare @count int
set @count=1
declare @i int
set @i=1
while(@i<6)
begin
while @count<=@i
begin
print Replicate('*',@count)
set @count=@count+1
end
set @i=@i+1
end

use YGGL
--使用if语句对Employee表按部分进行分类
select * from Employees,Departments
--该if有问题
if(select DepID from Employees)=1
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'财务部' from Employees
if(select DepID from Employees)=2
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'人力资源部' from Employees
if(select DepID from Employees)=3
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'经理办公室' from Employees
if(select DepID from Employees)=4
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'研发部' from Employees
if(select DepID from Employees)=5
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'市场部' from Employees

--使用case语句对Employee表按部分进行分类
select EmpID,EmpName,Addres,DepID =
case DepID 
   when 1 then '财务部'
   when 2 then '人力资源部'
   when 3 then '经理办公室'
   when 4 then '研发部'
   when 5 then '市场部'
end
from Employees