实验五TSQL编程

1、实验五 T-SQL编程实验目的：（1）进一步巩固第2章第4章所学内容；（2）掌握用户自定义类型的使用；（3）掌握变量的分类及其使用；（4）掌握各种控制语句的使用；（5）掌握系统函数及用户自定义函数的使用。实验内容：(1)在yggl数据库中创建Employees3表，表结构与Employees类似，只是EmployeeID列使用的数据类型为用户自定义数据类型ID_typeexec sp_addtype 'id_type','char(6)','not null'-自定义一数据类型id_type，描述员工编号if exists(select name

2、 from sysobjectswhere name='employees3')drop table employees3create table employees3(employeeid id_type,name char(10) not null,education char(4) not null,birthday datetime not null,sex bit not null default 1,workyear tinyint null,address varchar(40) null,phonenumber char(12) null,departmenti

3、d char(3) not null,primary key(employeeid)(2) 定义一个变量，用于描述YGGL数据库中的Salary表中员工的实际收入，然后查询该变量。declare gongzi char(10)Set gongzi=(select income-outcome as realcome from salary where EmployeeID='000001'); Select gongzi as realcome; (3)判断姓名为"王林"的员工实际收入是否高于，如果是则显示其收入，否则显示"收入不高于"。

4、if exists(select income-outcome from salary,employees where salary.employeeid=employees.employeeid and income-outcome>3000 and name='王林')select income-outcome from salary,employees where salary.employeeid=employees.employeeid and income-outcome>3000 and name='王林'elseselect name

5、,'收入不高于' from employees where name='王林'(4)用循环输出一个用"*"组成的三角形。实心三角形：declare lines int,i intset lines=10set i=1while i<=lines begin print space(lines-i)+replicate('*',2*i-1) set i=i+1end空心三角形：declare lines int,i intset lines=10set i=1print space(lines)+'*'wh

6、ile i<lines begin print space(lines-i)+'*'+space(2*i-1) +'*' set i=i+1endprint replicate('* ',lines+1)(5)自定义一个函数，能够计算一个数的阶乘create function factorial(num int)returns bigint as begindeclare result bigint;declare i int;select result=1;select i=1;if(num<1)beginselect result

7、=1endelse beginwhile(i<=num)beginselect result=result*i;select i=i+1;endendreturn result;endselect dbo.factorial(5);select dbo.factorial(7);select dbo.factorial(9);(6)求财务部收入最高的员工姓名select name from departments,employees e1,salary s1 where e1.employeeid=s1.employeeid and e1.departmentid=departments

8、.departmentidand departmentname='财务部' and income=(select max(income) from employees e2,salary s2where e2.employeeid=s2.employeeid and e2.departmentid=e1.departmentid)(7)查询员工收入的平均数select avg(income) as 员工收入的平均数 from salary (8)查询员工收入的平均数select left('abcdef',3)(9)使用day()函数返回指定日期的天数select day('2013-12-31')(10)列举出其他的时间日期函数。select getdate()select datename(yyyy,'2013-11-11')select datediff(dd,'2012-11-11','2013-11-11')sele