第5章Transact.pdf

《第5章Transact.pdf》由会员分享，可在线阅读，更多相关《第5章Transact.pdf（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 1 第 5 章 Transact-SQL Transact-SQL 5.1 练习题 5 及参考答案 1从功能上划分，SQL 语言分为哪 4 类？答：SQL 语句通常分成 4 类：（1）数据查询语言（2）数据操纵语言（3）数据定义语言（4）数据控制语言 2 NULL 代表什么含义？将其与其他值进行比较会产生什么结果？如果数值型列中存在 NULL，会产生什么结果？答：在数据库中，NULL 是一个特殊值，表示数值未知。NULL 不同于空字符或数字 0，也不同于零长度字符串。比较两个空值或将空值与任何其他数值相比均返回未知，这是因为每个空值均为未知。空值通常表示未知、不可用或以后添加数据。如果某个列

2、上的空值属性为 NULL，表示接受空值；空值属性为 NOT NULL，表示拒绝空值。如果数值型列中存在 NULL 则在进行数据统计时就会产生不正确的结果。3使用 T-SQL 语句向表中插入数据应注意什么？答：在使用 T-SQL 语句向表中插入数据时要注意以下几点：（1）当向表中所有列都插入新数据时，可以省略列表名，但是必须保证 VALUES 后的各数据项位置同表定义时的顺序一致。（2）要保证表定义时的非空列必须有值，即使这个非空列没有出现在插入语句中，也必须如此。（3）插入字符型和日期型值时，要加入单引号。（4）没有列出的数据类型应该具有以下属性之一：identity 属性、timestamp

3、 数据类型、具有 NULL 属性或者有一个缺省值。对于具有 identity 属性的列，其值由系统给出，用户不必往表中插入数据。第 章 5 2 第 5 章 Transact-SQL 4LIKE 匹配字符有哪几种？如果要检索的字符中包含匹配字符，那么该如何处理？答：在 SQL Server 2005 中，共提供了 4 个通配符：%：代表任意多个字符。_（下划线）：代表一个任意字符。：代表方括号内的任意一个字符。：表示任意一个在方括号内没有的字符。如果要检索的字符中包含匹配字符，可使用上述通配符，例如，以下语句查询 student表中所有姓王的学生记录：USE school SELECT*FROM

4、 student WHERE sname LIKE 王%其执行结果如下：sno sname ssex sbirthday sclass-107 王丽 女 1976-01-23 00:00:00.000 95033 109 王芳 女 1975-02-10 00:00:00.000 95031 5在 SELECT 语句中 DISTINCT、ORDER BY、GROUP BY 和 HAVING 子句的功能各是什么？答：各子句的功能如下：DISTINCT：查询唯一结果。ORDER BY：使查询结果有序显示。GROUP BY：对查询结果进行分组。HAVING 筛选分组结果。6.在一个 SELECT 语句

5、中，当 WHERE 子句、GROUP BY 子句和 HAVING 子句同时出现在一个查询中时，SQL 的执行顺序如何？答：其执行顺序如下：（1）执行 WHERE 子句，从表中选取行。（2）由 GROUP BY 对选取的行进行分组。（3）执行聚合函数。（4）执行 HAVING 子句选取满足条件的分组。7什么是局部变量？什么是全局变量？如何标识它们？答：局部变量是在一个批处理中被声明、定义、赋值和引用的变量，批处理结束后，该变量也就消失了。全局变量是用来记录 SQL Server 服务器活动状态的变量，它预先被定义，用户只可以使用，不可以重新定义和赋值。局部变量是用户定义的变量，用 DECLARE

6、 语句声明，在声明时它被初始化为 NULL,用户可用 SET 语句为其赋值，局部变量的使用范围是定义它的批、存储过程和触发器。它 3 第 5 章 Transact-SQL 必须以开始，遵循 SQL Server 的标识符和对象的命名规范，而且名字不能使用保留字。全局变量是 SQL Server 系统所提供并赋值的变量。用户不能建立全局变量，也不能使用 SET 语句去修改全局变量的值。全局变量的名字以开头。大多数全局变量的值是报告本次 SQL Server 启动后发生的系统活动。8什么是批处理？使用批处理有何限制？答：批处理是指一个 SQL 语句集，这些语句一起提交并作为一个组来执行。批处理结束

7、的符号是 GO。由于批处理中的多个语句是一起提交给 SQL Server 的，所以可以节省系统开销。使用批时有很多限制：（1）并不是所有的 SQL 语句都可以和其他语句在一起组合成批处理。下述语句就不能组合在同一个批处理中：CREATE PROCEDURE CREATE RULE CREATE DEFAULT CREATE TRIGGER CREATE VIEW（2）不能在同一个批处理中既绑定又使用规则和缺省。（3）不能在同一个批处理中既定义又使用 CHECK 约束。（4）在同一个批处理中不能既删除对象又重建它。（5）用 SET 语句改变的选项在批处理结束时生效。（6）在同一个批处理中不能改变

8、一个表再立即引用其新列。9 在默认情况下，SQL 脚本文件的后缀是什么？SQL 脚本执行的结果有哪几种形式？答：SQL 脚本文件的默认后缀是.sql.，SQL 脚本执行的结果有三种形式：文本显示形式、表格显示形式及文件保存形式。10.编写一个程序，输出所有学生的学号和平均分，并以平均分递增排序。解：对应的程序如下：USE school GO SELECT sno AS 学号,AVG(degree)AS 平均分 FROM score GROUP BY sno ORDER BY AVG(degree)GO 程序执行结果如下：4 第 5 章 Transact-SQL 11.编写一个程序，判断 sch

9、ool 数据库是否存在 student 表。解：对应的程序如下：USE school GO IF EXISTS(SELECT*FROM sysobjects WHERE name=student AND type=U)PRINT 存在 student 表 ELSE PRINT 不存在 student 表 GO 12.编写一个程序，输出所有学生成绩对应的等级。解：对应的程序如下：USE school GO SELECT sno AS 学号,cno AS 课程号,CASE WHEN degree=90 THEN 优 WHEN degree=80 THEN 良 WHEN degree=70 THEN

10、 中 WHEN degree=60 THEN 及格 WHEN degree(SELECT AVG(degree)FROM score b WHERE o=o)ORDER BY cno GO 程序执行结果如下：15.创建一个自定义函数 maxscore，用于计算给定课程号的最高分，并用相关数据进行测试。解：对应的程序如下：USE school GO CREATE FUNCTION maxscore(no char(5)-建立函数 maxscore RETURNS st TABLE -返回表st,下面定义其表结构 (sno char(5),cno char(5),maxs float )AS BE

11、GIN 6 第 5 章 Transact-SQL INSERT st(sno,cno,maxs)-向st 中插入满足条件的记录 SELECT sno,cno,degree FROM score WHERE cno=no AND degree=(SELECT MAX(degree)FROM score WHERE cno=no)RETURN END GO SELECT*FROM maxscore(3-105)GO 程序执行结果如下：5.2 上机实验题 4 及操作过程 在上机实验题 3 建立的 factory 数据库上，完成如下各题的程序，要求以文本格式显示结果：（1）显示所有职工的年龄，并按职工

12、号递增排序。（2）求出各部门的党员人数。（3）显示所有职工的姓名和 2004 年 1 月份工资数。（4）显示所有职工的职工号、姓名和平均工资。（5）显示所有职工的职工号、姓名、部门名和 2004 年 2 月份工资，并按部门名顺序排列。（6）显示各部门名和该部门的所有职工平均工资。（7）显示所有平均工资高于 1200 的部门名和对应的平均工资。（8）显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属管理部门，市场部属市场部门。（9）若存在职工号为 10 的职工，则显示其工作部门名称，否则显示相应提示信息。（10）求出男女职工的平均工资，若男职工平均工资高出女职工平均工资 50%，则显示“

13、男职工比女职工的工资高多了”的信息；若男职工平均工资与女职工平均工资比率在1.50.8 之间，则显示“男职工跟女职工的工资差不多”的信息；否则，显示“女职工比男职工的工资高多了”的信息。操作过程（1）对应的程序如下：USE factory GO SELECT 姓名,YEAR(GETDATE()-YEAR(出生日期)AS 年龄 FROM worker ORDER BY 职工号 7 第 5 章 Transact-SQL GO 执行结果如下：姓名 年龄-孙华 56 陈涛 50 刘欣 56 李涵 43 王小燕 44 李艺 45 魏君 38 孙天奇 43 陈明 63 李华 52 余慧 28 欧阳少兵 3

14、7 程西 28 张旗 28 刘夫文 66（2）对应的程序如下：USE factory GO SELECT depart.部门名,COUNT(*)AS 党员人数 FROM worker,depart WHERE worker.党员否=是 AND worker.部门号=depart.部门号 GROUP BY depart.部门名 GO 执行结果如下：部门名 党员人数 -财务部 2 人事部 1 市场部 2（3）对应的程序如下：USE factory GO SELECT worker.姓名,salary.工资 FROM worker,salary WHERE worker.职 工 号=salary.职

15、 工 号AND YEAR(salary.日 期)=2004 AND MONTH(salary.日期)=1 GO 执行结果如下：姓名 工资 8 第 5 章 Transact-SQL-孙华 1201.5 陈明 1350.6 程西 750.8 孙天奇 900 刘夫文 2006.8 刘欣 1250 余慧 725 张旗 728 王小燕 1200 李华 1500.5 陈涛 1245.8 李艺 1000.6 李涵 1345 魏君 1100 欧阳少兵 1085（4）对应的程序如下：USE factory GO SELECT worker.职工号,worker.姓名,AVG(salary.工资)AS 平均工资

16、FROM worker,salary WHERE worker.职工号=salary.职工号 GROUP BY worker.职工号,worker.姓名 GO 执行结果如下：职工号 姓名 平均工资-1 孙华 1204 10 陈涛 1248.3 11 刘欣 1252.5 12 李涵 1347.5 13 王小燕 1202.5 14 李艺 1003.1 15 魏君 1102.5 2 孙天奇 902.5 3 陈明 1353.1 4 李华 1503 5 余慧 727.5 6 欧阳少兵 1085 7 程西 753.3 8 张旗 730.5 9 刘夫文 2009.3（5）对应的程序如下：USE factor

17、y GO SELECT worker.职工号,worker.姓名,depart.部门名,9 第 5 章 Transact-SQL salary.工资 AS 2004 年月工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=2 ORDER BY worker.部门号 GO 执行结果如下：职工号 姓名 部门名 2004 年月工资 -1 孙华 财务部 1206.5 7 程西 财务部 755.8 1

18、1 刘欣 财务部 1255 13 王小燕 财务部 1205 2 孙天奇 人事部 905 9 刘夫文 人事部 2011.8 3 陈明 人事部 1355.6 10 陈涛 人事部 1250.8 8 张旗 人事部 733 14 李艺 市场部 1005.6 12 李涵 市场部 1350 15 魏君 市场部 1105 6 欧阳少兵 市场部 1085 4 李华 市场部 1505.5 5 余慧 市场部 730（6）对应的程序如下：USE factory GO SELECT depart.部门名,AVG(salary.工资)AS 平均工资 FROM worker,depart,salary WHERE work

19、er.部门号=depart.部门号 AND worker.职工号=salary.职工号 GROUP BY depart.部门名 GO 执行结果如下：部门名 平均工资 -财务部 1103.075 人事部 1248.74 市场部 1128.1（7）对应的程序如下：USE factory GO SELECT depart.部门名,AVG(salary.工资)AS 平均工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 10 第 5 章 Transact-SQL GROUP BY depa

20、rt.部门名 HAVING AVG(salary.工资)1200 GO 执行结果如下：部门名 平均工资 -人事部 1248.74（8）对应的程序如下：USE factory GO SELECT worker.职工号,worker.姓名,CASE depart.部门名 WHEN 财务部THEN 管理部门 WHEN 人事部THEN 管理部门 WHEN 市场部THEN 市场部门 END AS 部门类型 FROM worker,depart WHERE worker.部门号=depart.部门号 GO 执行结果如下：职工号 姓名 部门类型-1 孙华 管理部门 10 陈涛 管理部门 11 刘欣 管理部门

21、 12 李涵 市场部门 13 王小燕 管理部门 14 李艺 市场部门 15 魏君 市场部门 2 孙天奇 管理部门 3 陈明 管理部门 4 李华 市场部门 5 余慧 市场部门 6 欧阳少兵 市场部门 7 程西 管理部门 8 张旗 管理部门 9 刘夫文 管理部门（9）对应的程序如下：USE factory GO DECLARE no int,dep char(10)SET no=10 IF EXISTS(SELECT*FROM worker WHERE 职工号=no)BEGIN 11 第 5 章 Transact-SQL SELECT dep=depart.部门名 FROM worker,depa

22、rt WHERE worker.职工号=no AND worker.部门号=depart.部门号 PRINT 职工号为+CAST(no AS CHAR(2)+的职工在+CAST(dep AS char(6)+工作 END ELSE PRINT 不存在该职工号的记录 GO 执行结果如下：职工号为的职工在人事部工作（10）对应的程序如下：USE factory GO DECLARE avg1 float,avg2 float,ratio float-计算男职工平均工资 SELECT avg1=AVG(salary.工资)FROM worker,salary WHERE worker.职工号=salary.职工号 AND worker.性别=男-计算女职工平均工资 SELECT avg2=AVG(salary.工资)FROM worker,salary WHERE worker.职工号=salary.职工号 AND worker.性别=女 SET ratio=avg1/avg2 IF ratio1.5 PRINT 男职工比女职工的工资高多了 ELSE IF ratio=0.8 PRINT 男职工跟女职工的工资差不多 ELSE PRINT 女职工比男职工的工资高多了 GO 执行结果如下：男职工跟女职工的工资差不多