SQL Sever 实用教程（第三版）实验五答案.doc

200901501116 刘玉佩T-SQL编程（1）自定义数据类型的使用在对象资源管理器中展开“数据库-PXSCJ-可编程性”，右击“类型”，选择“新建”选项，在新建数据类型“窗口中使用界面创建一个用户自定义数据类型（2）变量的使用定义一个变量，用于描述YGGL数据库的Salary表中000001号员工的实际收入，然后查询该变量DECLARE realincome floatSET realincome=(SELECT InCome-OutCome FROM Salary WHERE EmployeeID=000001)SELECT realincome（3）运算符的使用熟悉各种运算符的功能和用法，如LIKE、BETWEEN等查询收入做在2000-3000之间的员工的号码和姓名SELECT Employees.EmployeeID,NameFROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeIDAND InCome BETWEEN 2000 AND 3000查询电话号码以8开头和结尾的员工的号码和姓名SELECT EmployeeID,NameFROM EmployeesWHERE PhoneNumber LIKE 8%8（4）流程控制语句判断姓名为“王林”的一个的实际收入是否高于3000元，如果是，则显示其收入，否则显示“收入不高于3000”IF(SELECT InCome-OutCome FROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeID AND Name=王林)>3000SELECT InCome-OutCome FROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeID AND Name=王林ELSESELECT收入不高于使用循环输出一个用“*”组成的三角形DECLARE X INT,M INT,N INT,Y VARCHAR(30),Z VARCHAR(30)SET X=1WHILE X<=5BEGIN SET M=1 SET N=1 SET Y= SET Z= WHILE M<=5-X BEGIN SET Y=Y+ SET M=M+1 END WHILE N<=2*X-1 BEGIN SET Z=Z+* SET N=N+1 END PRINT Y+Z SET X=X+1END使用IF语句对Employees表按部门进行分类CREATE FUNCTION CHECK_IF(departmentid char(3)RETURNS CHAR(10)ASBEGIN DECLARE num CHAR(10)IF (SELECT DepartmentID FROM Departments WHERE departmentid=DepartmentID)=1SELECT num=财务部IF (SELECT DepartmentID FROM Departments WHERE departmentid=DepartmentID)=2SELECT num=人力资源部IF (SELECT DepartmentID FROM Departments WHERE departmentid=DepartmentID)=3SELECT num=经理部IF (SELECT DepartmentID FROM Departments WHERE departmentid=DepartmentID)=4SELECT num=研发部IF (SELECT DepartmentID FROM Departments WHERE departmentid=DepartmentID)=5SELECT num=市场部RETURN numENDGOSELECT EmployeeID,Name,Address,dbo.CHECK_IF(DepartmentID)AS DepartmentIDFROM Employees（5）自定义函数的使用自定义一个函数，计算一个数的阶乘CREATE FUNCTION JC(num int)RETURNS int ASBEGINDECLARE m int,n intSET m=1SET n=1WHILE n<=num BEGIN SET m=m*n SET n=n+1 ENDRETURN mENDGODECLARE X intSELECT X=dbo.JC(5)SELECT X AS 5的阶层（6）系统内置函数的使用使用RAND()函数产生一个0-1的随机值SELECT RAND(1)使用SQUARE()函数获得一个数的平方DECLARE num floatSET num=4.5SELECT SQUARE(num)使用SORT()函数返回一个数的平方根DECLARE num intSET num=16SELECT SQRT(num)求财务部收入最高的员工姓名SELECT NameFROM Employees,Salary,DepartmentsWHERE InCome>=ALL(SELECT InCome FROM Employees,Salary,Departments WHERE Employees.EmployeeID=Salary.EmployeeID AND Employees.DepartmentID=Departments.DepartmentID AND DepartmentName=财务部)AND Employees.EmployeeID=Salary.EmployeeID AND Employees.DepartmentID=Departments.DepartmentIDAND DepartmentName=财务部查询员工收入的平均数SELECT AVG(InCome)FROM SalaryWHERE EmployeeID IN(SELECT EmployeeID FROM Employees)聚合函数如何与GROUP BY函数一起使用？SELECT子句中的列表中只能包含GROUP BY中指出的列或聚合函数中指定的列按照员工的部门分组，排列出各部门的人数SELECT DepartmentID,count(EmployeeID)FROM EmployeesGROUP BY DepartmentID使用CHAR()函数将ASCII码代表的字符组成字符串SELECT CHAR(115)+CHAR(97)+CHAR(110)使用LEFT()函数返回从字符串abcdef左边开始的3个字符SELECT LEFT(abcdef,3)使用DAY()函数返回指定日期时间的天数SELECT DAY(2003-4-17)列举出其他的时间日期函数GETDATE()函数SELECT GETDATE()使用其他类型的系统内置函数