T-SQL实训练习

实验14 SQL Server 2005T-SQL编程

实验14 T-SQL编程课堂练习一（视图）：目标综述掌握视图的创建方法与修改。

以下代码在AdventureWorks实验内容14.1)由于工作的需要，会经常查询员工的一些相关信息，会涉及到Person.Contact表的Title，FirstName，MiddleName，LastName，EmailAddress，Phone和HumanResources.Employee表的EmployeeID，Title等字段，请为这些字段在HumanResources架构下创建一个名为vEmployeeContact的视图，要求在视图中将Person.Contact表的Title字段名改为Salutation，将FirstName，MiddleName，LastName，三个字段名合并为Name。

14.2)在上次实验中，关于销售总监要进行的促销活动，对于促销数据的修改，现在作为数据管理员的你想到除了用临时表的另外一种方式，就是创建视图，请在上次的实验代码的基础上将其修改为视图的创建。

（销售总监在做产品销售情况统计的时候发现有些产品的销售情况不太理想，向上级申请对一些销售较差的产品进行打折优惠进行促销。

销售总监的定义是如果销售总量在500以下（不含500）的产品，在促销期间，价格下调15%。

此次操作涉及表有：Sales.SalesOrderDetail、Production.Product。

Sales.SalesOrderDetail表中的OrderQty字段保存订单的销售数量，Production.Product表中的ListPrice保存产品价格）14.3)对视图进行简单验证，使用select查询视图基本信息。

课堂练习二（存储过程）：目标综述掌握存储过程的创建，如果对存储过程进行管理（调用、查看源代码、修改）实验内容14.4)在Sales.SpecialOffer中保存了一些特价的产品信息。

顾客经常会通过这张表来查询某类产品的折扣信息。

T-SQL编程及练习题

T-SQL流程控制
• Try…catch语句：可进行结构化错误处理。
BEGIN TRY sql语句或语句块 END TRY BEGIN CATCH sql语句或语句块 END CATCH
• Retrun语句
终止目前T-SQL语句的执行，从查询或过程中无条件的退出来，并可返回整数值给调用程序。可以在任何时候从过程、批处理或语句块中退出，而不是跳出某个循环或某个位置。主要在存储过程或函数中使用。
多语句表值函数
• 示例：
CREATE FUNCTION f_test3(@dept_id int) RETURNS @emp TABLE(empid int NOT NULL, lname NVARCHAR(20) NOT NULL, sal money NOT NULL) BEGIN DECLARE @mgr_id int SELECT @mgr_id=manager_id from departments where department_id=@dept_id INSERT @emp SELECT employee_id,last_name,salary from employees where manager_id=@mgr_id RETURN END GO SELECT * from dbo.f_test3(10)
• 执行：
EXECUTE test4 @employee_id=100,@last_name=‘’ －－返回King 或 declare @name varchar(20) EXECUTE test4 100,@last_name=@name output －－返回King select @name －－返回King
用户定义函数
主要内容

用T-SQL语句完成创建与管理表操作练习

在SSMS中创建与管理表操作练习一1．创建TSJY数据库，并在TSJY数据库中创建如下三张表，表结构如下：（1）表名：XS，存放学生基本信息。

（10分）创建名为CK_XS的check约束，设置性别字段的值只能为‘男’或者‘女’。

（2）表名：TS，存放图书基本信息。

（10分）创建名为CK_TS的check约束，设置状态字段的值只能为‘在馆’或者‘借出’。

（3）表名：JY，存放学生借阅图书信息。

（10分）2.维护表操作：将XS表的JSRQ字段的默认值为当前系统时间getdate()3.在XS表中输入以下记录。

XH XM XB ZY CSRQ 95001 张三男计算机1990-2-1 95002 李四男电子商务1989-5-4 95003 陈红女电子商务1989-6-795004 倪娟女计算机网络技术1988-5-695005 李利男电子商务1990-8-9在TS表中输入如下记录SH SM CBS ZB DJ ZTj10098 国际贸易实务浙江科学出版社吴百福43在馆k20045 dephi7.0实用教程机械工业出版社张帆38借出w10003 撒哈拉的故事北京十月文艺出版社三毛20在馆s20005 改变一生的60个心理学效应中国青年出版社郑小兰22在馆在JY表中输入以下记录LSH XH SH JSRQ95003 w10003 2009-9-195003 s20005 2009-12-2395005 j10098 2009-8-7 4．JY表中再插入如下记录：5，20004，s0008,2010-12-23能不能完成插入操作，为什么？如果要插入上述记录，如何进行？5．删除XS表中学号为95001的记录。

6．删除XS表中学号为95005的记录，能否完成删除操作，为什么？如果要将这条记录删除，如何进行？3.创建school数据库中的表。

（1）、College表—学院（2）、Specialty表—专业5．表student中给enteryear设置4位数字的值的约束，score字段的值要求大于0,bankid的值不能有重复。

实验-T-SQL高级应用(答案)

实验六T-SQL高级应用一、实验目的：(1) 掌握T-SQL程序设计的控制结构及程序设计逻辑。

(2) 掌握自定义函数、存储过程、游标的使用。

二、实验内容(1) 从学生数据库school中查询所有同学选课成绩情况：姓名、课程名、成绩。

要求：小于60分的输出“不及格”；60—70分的输出“及格”；70—80分的输出“中等”；80—90分的输出“良好”；90—100分的输出“优秀”。

select sname as '姓名',cname as '课程名',casewhen degree<60 then '不及格'when degree<70 then '及格'when degree<80 then '中等'when degree<90 then '良好'else '优秀'end as '成绩'from student s,course c,scwhere s.sno=sc.sno and o=o(2) 创建一个自定义函数，根据学生姓名查询该生所有的选课信息，包括课程名、成绩。

--创建函数f1create function f1(@xm char(10))returns tableasreturn (select cname,degreefrom student s,course c,scwhere s.sno=sc.sno and o=o and sname=@xm) --调用函数f1select * from f1('刘晨')(3) 创建一个自定义函数，实现如下功能：判断一个数是否是素数。

--创建函数，返回1代表n是素数，返回0代表n不是素数create function sh(@n int)returns tinyintbegindeclare @i int,@f tinyintset @i=2set @f=1 --假定@n是素数while @i<=sqrt(@n)beginif @n%@i=0beginset @f=0breakendset @i=@i+1endreturn @fendgo--调用函数sh，判断5是否是素数if dbo.sh(5)=1print '是素数'elseprint '不是素数'(4)创建包含两个参数的存储过程stucred，一个输入参数（@sno）用于指定学生学号，一个输出参数（@s_cred）用于返回该生所修学分总和。

T-SQL练习
任务1：请使用T-SQL编写一个程序，定义两个整型变量，使用set 和select分别将其赋值5和-3，并将这两个变量进行加法运算，最后要求分别使用select和print输出计算结果。

（参考Page163，例9.2）请将你的T-SQL程序代码复制到这里：
任务2、请定义字符型变量，用于存放“中华人民共和国PRC”，要求分别使用合适的字符串函数，完成下列要求：（参考Page163，例
9.3）
①统计其字符个数（参考Page175-len()）
②返回字符串右边开始的3个字符（参考Page176-right()）
③将大写字母转换为小写字母（参考Page176-lower()）
请将你的T-SQL程序代码复制到这里：
任务3、请使用数学函数PI()，计算一个半径为5的圆的周长和面积。

（参考Page177-PI()）
请将你的T-SQL程序代码复制到这里：
任务4、请调整你的windows系统的时间至准确时间，然后使用日期函数，分别统计香港回归日（1997-07-1）和澳门回归日（1999-12-20）距离今天的年数、月数、天数？（参考Page178-例9.28）
请将你的T-SQL程序代码复制到这里：
任务5、请编写一个简单的循环程序，计算100的阶乘。

（参考Page171-例9.17）
请将你的T-SQL程序代码复制到这里：。

T-SQL练习题参考答案

T-SQL练习题参考答案一、StudentInfo数据库已发给大家。

其中包含两个表：stuInfo和stuMarks。

根据这个数据库完成下列各题：1．编写T-SQL查找李文才的左右同桌？分析：第一步，找出“李文才“的座位号；第二步，李文才的座位号加1或减1/*--查找李文才的信息--*/DECLARE @name varchar(8) --学员姓名 SET @name='李文才' --使用SET 赋值SELECT * FROM stuInfo WHERE stuName = @name /*--查找李文才的左右同桌--*/ DECLARE @seat int --座位号SELECT @seat=stuSeat FROM stuInfo --使用SELECT赋值 WHEREstuName=@name SELECT * FROM stuInfoWHERE (stuSeat = @seat+1) OR (stuSeat = @seat-1) GO2. 统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息。

分析：第一步，统计平均成绩存入临时变量；第二步，用IF－ELSE判断；declare @myavg floatselect @myavg=avg(writtenExam) from stuMarks --使用select赋值print '本班的平均分:'+convert(varchar(5),@myavg) if (@myavg>70) beginprint '本班笔试成绩优秀!'select top 3 * from stuMarks order by writtenExam desc end elsebeginprint '本班笔试成绩优秀!'select top 3 * from stuMarks order by writtenExam end go3. 本次笔试成绩较差，假定要提分，确保每人笔试都通过。

T-SQL语句练习题

一、根据要求用T-SQL语句创建数据库和表。

创建数据库“英才大学成绩管理”。

分别创建三个表，具体的表名、字段名如下：学生(学号,姓名,性别,出生年月,籍贯,班级) 其中性别字段只能为“男”或“女”课程(课程编号,课程名称,学分) 其中课程名称字段为唯一值约束成绩(学号,课程编号,成绩)带下划线的字段为主键，表之间的外键关联参考下图，各字段的数据类型应尽量合理。

CREATE DATABASE 英才大学成绩管理create table 学生(学号 char(6) primary key,姓名 char(6),性别 char(2) check(性别='男' or 性别='女'),出生年月 datetime,籍贯 varchar(50),班级 char(12),)create table 课程(课程编号 char(5) primary key,课程名称 char(20) unique,学分 smallint,)create table 成绩(学号 char(6) references 学生(学号),课程编号 char(5) references 课程(课程编号),成绩 decimal(5,2),primary key(学号,课程编号))二、根据T-SQL语句写出执行结果（20分，每题4分）对上题所创建的表添加记录后如图所示，详细写出下面的SQL语句的执行结果。

1、select * from 课程 where 课程编号='30442'1、2、select 姓名 from 学生where 性别='女' and 籍贯='辽宁'2、3、select 姓名,课程名称,成绩 from 学生,课程,成绩where 学生.学号=成绩.学号 and 课程.课程编号=成绩.课程编号and 学分='4'3、4、select max(成绩) as 最高分 from 课程,成绩where 课程.课程编号=成绩.课程编号 and 课程名称='C语言程序设计' 4、5、select 籍贯,count(*) as 人数from 学生where 班级='计ZG091' group by 籍贯5、三、对第一题中的表用T-SQL语句完成如下操作。

实验10用T-SQL创建和管理数据库答案

实验10 使用T-SQL语言管理数据库一、实验目的1.掌握查询分析器的使用方法。

2.掌握使用T-SQL语言如何创建数据库。

3.掌握使用T-SQL语言如何查询数据库4.掌握使用T-SQL语言如何删除数据库。

5.掌握如何使用T-SQL语言还原数据库。

6.掌握如何使用T-SQL语言修改数据库。

二、实验内容1.新建以学生本人学号（后两位）+姓名命名的文件夹，启动企业管理器，使用查询分析器，利用T-SQL语言创建以“高校管理系统”命名的包含多个数据文件和日志文件的SQL SERVER数据库，数据文本分别为：高校管理系统_DATA1、高校管理系统_DATA2。

其中高校管理系统_DATA1文件为主要数据库文件。

主要数据文件初始大小为20MB，最大尺寸为无限大，增长速度为10MB，次要数据名为“高校管理系统_DATA3”，初始大小为5MB，最大尺寸为无限大，增长速度为1MB，文件事务日志文件名为“高校管理系统_LOG1”，初始大小为3MB，最大尺寸为30MB，增长速度为10%。

高校管理系统_DATA2文件采用默认设置。

（操作提示：创建数据库时要注意数据文件和事务日志的存放位置，以下各题文件存储位置相同）。

SQL代码如下：CREATE DATABASE 高校管理系统ON(NAME = 高校管理系统_DATA1,FILENAME = 'D:\数据库练习\高校管理系统\高校管理系统_DATA1.MDF',SIZE = 20MB,MAXSIZE = UNLIMITED,FILEGROWTH = 1MB),(NAME = 高校管理系统_DATA2,FILENAME = 'D:\数据库练习\高校管理系统\高校管理系统_DATA2.MDF' ),(NAME = 高校管理系统_DATA3,FILENAME = 'D:\数据库练习\高校管理系统\高校管理系统_DATA3.NDF',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 1MB)LOG ON(NAME = 高校管理系统_LOG1,FILENAME = 'D:\数据库练习\高校管理系统\高校管理系统_LOG1.LDF',SIZE = 3MB,MAXSIZE = 30MB,FILEGROWTH = 10%)2.使用企业管理器完全备份高校管理系统。

T-SQL实训

T-SQL实训题目一、用T—SQL语句查询数据（一）简单查询（用T—SQL语句实现）1.检索“客房信息”表中有哪几种客房状态？（DISTINCT）2.检索“教师档案表”中教师有那几种学历？3.检索“教师档案表”中教师有那几种职称？4.检索“教师档案表”中教师有那些不同学历职称组合？5.检索“项目属性表”表中有哪几种项目大类和小类？6.检索“报名表2010”表中有哪些参赛队报了男子“五项全能”项目？7.检索“入住单”表中的前10条记录？（TOP 10 *）8.检索“入住单”表中的前10%条记录？（TOP 10 PERCENT）9.检索“学生档案表”中前8条记录？10.检索学生档案表中前30%条记录？11.检索学生档案表中前4条女生记录？12.检索教师档案表中前25%条党员教师记录？13.检索“总积分排名表”中的前6条记录？14.检索“总积分排名表”中类别为“教工”的前6条记录？15.检索“总积分排名表”中类别为“学生”的前3名的记录？16.检索“总积分排名表”中类别为“教工”中的排名在前25%的记录？17.检索入住单号为RZ00000002的记录？18.检索教师档案表中学历为研究生的教师的信息？19.检索学生档案表中1988年1月30日以前出生的学生信息？（convert(varchar(10),出生日期,126)< '1988-1-30' 或datediff(day, 出生日期, '1988-1-30')>0）20.检索教师档案表中为“党员”“教授”的教师信息？21.检索“24”班的学生学号和姓名？22.检索所有教师的姓名、学历和职称？23.检索所有教师的姓名和工龄？（datediff函数）24.检索所有学生的姓名、性别和年龄？25.通过“客房信息”表统计各类型编号客房的数目？26.通过“客房信息”表统计各类型编号客房的床的总数量？27.通过“课程名表”统计各种类别课程的平均学分？28.通过“学生成绩表”统计各学生（按学号）的平均成绩，最高分和最低分？29.通过“学生成绩表”统计各门课程（按课程编号）在各学期的平均成绩，最高分和最低分？30.通过“教师档案表”统计不同学历与职称的教师的人数？31.检索非党员中姓“张”和姓“王”的学生信息？32.检索选修了“A01”课程且成绩在80-90之间的学生学号、课程编号和成绩？33.检索课程名中含有“原理”的课程的所有信息；34.检索出学生的学号，姓氏（计算字段），名字（计算字段），性别的信息（其中所有学生都是单姓）；35.通过“报名表2010”统计各类别、组别、项目的预赛分组数（字段：分组数1）和报名人数。

实验3-练习

实验三T-SQL语言基础实验目的1、掌握T-SQL语言的基本概念2、掌握T-SQL语言的对象命名规则3、掌握T-SQL的数据类型和表达式4、掌握在T-SQL中使用变量、函数5、学习在T-SQL中编写基本的流程控语句。

实验环境硬件环境：PC机一台软件环境：SQL Server 2008/2005实验内容1.设置语句“SELECT * FROM student”延迟30秒执行。

2.输出100~200之间能被7整除的数，并且统计这些数的个数。

3.使用CASE语句实现，对学生选课表的学生学号、课程号、课程成绩进行查询，如果成绩为空，则显示“未考”；若成绩大于60，则显示“及格”；若成绩小于60，则显示“不及格”相关练习1．下面哪个关键字不是Transact-SQL的函数？（）A．DAY( ) B．YEAR( ) C．MONTH( ) D．SECOND( )2．声明了变量：DECLARE @i int，@c char(4)，现在为@i赋值为10,为@c赋值’abcd’，正确的语句是（）。

A．SET @i＝10,@c＝’abcd’B．SET @i＝10,SET @c＝’abcd’C．SELECT @i＝10,@c＝’abcd’D．SELECT @i＝10,SELECT @c＝’abcd’3．在以下代码中的_____位置分别为BREAK,CONTINUE或RETURN时，输出的值为（）。

DECLARE @n intSET @n=3WHILE @n>0BEGINSET @n=@n-1IF @n=1 _____ENDPRINT @nA．1，0，不输出B．1，1，1 C．0，0，0 D．0，1，2相关知识SQL Server中较常用的全局变量1.数据统计函数数据统计函数可以对一系列数值进行运算，并返回一个数值型的计算结果，这种函数最常见的情况就是在使用了GROUP BY子句的语句中，但是，在非GROUP BY子句的语句中也可以看到这些函数。