T-SQL上机练习习题和答案

1.在查询分析器中分别调试如下程序，先修正错误再将结果以注释语句的形式写在相应程序的下方。

(1)declare @n decimal(5,2),@m varchar(10)go@n=99.995@m='n 的值为'print @m+@ngo改为：declare @n decimal(5,2),@m varchar(10)set @n=99.995set @m='n 的值为'PRINT @m+str(@n)Go(2)exec sp_addtype cy1,varchar(24)declare @a cy1set @a='abcd'select @a,datalength(@a)改为：exec sp_addtype cy1,'varchar(24)'godeclare @a cy1set @a='abcd'select @a,datalength(@a)(3)Use Studentgoselect 学号,成绩,等级=casewhen 成绩<=100 then 'A'when 成绩<90 then 'B'when 成绩<80 t hen 'C‟when 成绩<70 then …D‟when 成绩<60 then 'E'else …成绩输入有误!‟endfrom 课程注册go改为：Use Studentgoselect 学号,成绩,等级=casewhen 成绩<60 then 'E'when 成绩<70 then 'D'when 成绩<80 then 'C'when 成绩<90 then 'B'when 成绩<=100 then 'A'else '成绩输入有误!'endfrom 课程注册go(4)--本题的功能是求100以内的自然数之和declare @i int;@s intset @i=1set @s=0while @i<=100@s=@s+@i@i=@i+1print '100以内的自然数之和为: '+ltrim(str(@s))改为：declare @i int,@s intset @i=1set @s=0while @i<=100beginset @s=@s+@iset @i=@i+1endprint '100以内的自然数之和为: '+ltrim(str(@s))(5)/*本程序的功能是统计随机产生的1000个0到1之间小数以0.5为分界出现的次数,用以说明随机数的均匀性*/declare @r numeric(10,2),@m int,declare @n1 int,@n2 intset @n1=0set @n2=0while @m<=1000beginset @r=rand()if @r<=0.5@n1=@n1+1else@n2=@n2+1@m=@m+1endprint '小于等于0.5的随机数出现了'+ @n1+‟次‟print '小大于0.5的随机数出现了'+@n2‟+‟次‟改为：declare @r numeric(10,2),@m intdeclare @n1 int,@n2 intset @m=1set @n1=0set @n2=0while @m<=1000beginset @r=rand()if @r<=0.5set @n1=@n1+1elseset @n2=@n2+1set @m=@m+1endprint '小于等于.5的随机数出现了'+ str(@n1)+'次' print '小大于.5的随机数出现了'+str(@n2)+'次'2.计算1到10之间的奇数之和declare @i tinyint,@sum intset @sum=0set @i=0while @i>=0beginif(@i>=10)beginselect '总和'=@sumendelsebeginset @i=@i+1if(@i % 2) = 0continueelseset @sum=@sum+@iendend3.利用GOTO语句求出从1加到5的总和。

1.HAVING字句中应后跟（B ）A．行条件表达式B．分组条件表达式C．试图序列D．列名序列2.数据定义语言的缩写词为（A ）A.DDLB.DCLC.DMLD.DBL3.在SQL语言中，建立存储过程的命令为（A ）A.CREATE PROCEDUREB.CREATE RULEC.CREATE DURED.CREATE FILE4.在SQL中，建立视图的命令为（C ）A.CREATE SCHEMAB.CREATE TABLEC.CREATE VIEWD.CREATE INDEX5.SQL的视图的数据是从（A ）中产生的A.基本表B.视图C.基本表或视图D.数据库6.从“产品”表里查询出价格高于产品名为“一次性纸杯”的产品的记录，此SQL语句为（D ）A.Select * from 产品where 价格>’一次性纸杯’B.Select * from 产品where 价格>(select * from 产品where 产品名称>’一次性纸杯’)C.Select * from 产品where exists (产品名称=’一次性纸杯’)D.Select * from 产品where 价格>(select 价格from 产品where 产品名称=’一次性纸杯’)7.假设订单表orders用来存储的订单信息，cid代表客户编号，money代表单次订购额，现要查询每个客户的订购次数和每个客户的订购总金额，下面（A ）sql语句可以返回正确结果。

A.Select cid,count(distinct(cid)),sum(money) from orders group by cidB.Select cid,count(distinct(cid)),sum(money) from orders order by cidC.Select cid count(cid),sum(money) from orders order by cidD.Select cid count(cid),sum(money) from orders group by cid8.实体关系中学生与任课教师之间具有（D ）联系A.一对一B.一对多C.多对一D.多对多9.在SQL中，建立索引用（C ）命名A.CREATE SCHEMAB.CREATE TABLEC.CREATE INDEXD.CREATE VIEW10.在SQL Server 2008中，声明一个最多可以存储10个字符的变量pwd，正确的代码是（B ）A.DECLARE pwd VARCHAR(10)B.DECLARE @pwd VACHAR(10)C.pwd VARCHAR(10)D.@pwd VARCHAR(10)11.E-R图中，关系用下面（C ）来表示A.矩形B.椭圆形C.菱形D.圆形12.下面变量中属于T-SQL中用户自定义的变量是（B ）A.@@errorB.@numberC.AbcD.num113.在数据库中查询数据，一般根据（C ）查询速度较快A.设置默认值的列B.设置为外键的列C.设置为主键的列D.设置检查约束的列14.分析下面的代码段，说法错误的是（D ）（选择一项）1 CREATE PROCEDURE proc_stuInfo2 @stuName VARCHAR(10),3 @n INT OUTPUT,4 @stuAge=18 INT5 AS6 ….代码略7 GO89 DECLARE @s INT10 EXEC proc_stuInfo ‘张三’,@s,3011 EXEC proc_stuInfo @stuAge=22,@stuName=’李四’，@n=@s OUTPUTA.此代码段创建了一个存储过程，并测试调用此存储过程B.第4行代码有误C.第10行代码有误D.第11行代码有误15.关于存储过程，以下说法正确的是（A ）A.不能在存储过程中使用CREATE VIEW命令B.T-SQL批代码的执行速度要快于存储过程C.存储过程必须带有参数D.存储过程不能返回结果集16.现有书目表book，包含字段：price（float）；现在查询一条书价最高的书目的详细信息，以下语句正确的是（BC ）（选两项）A.Select top1 * from book order by price ascB.Select top1 * from book order by price descC.Select top1 * from book where price=(select max(price) from book)D.Select top1 * from book where price=max(price)17.现有订单表orders，包含数据如下表。

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. 本次笔试成绩较差，假定要提分，确保每人笔试都通过。

24.写出下列每条语句或程序段的功能假设存在名为AAA的数据库，包括Students（学号 char(8)，姓名varchar(8)，年龄 int，专业 varchar(20)，入学日期 DateTime）和Score （学号 char(8)，课程名 varchar(10),成绩 numeric(5,2)）两张表。

（1）SELECT *FROM StudentsWHERE DATEPART(year,入学日期) =DATEPART(year,GETDATE())(（1）从Students表中查询出所有当年（系统时间）入学的学生记录。

)（2）DECLARE @MyNO CHAR(8)SET @MyNO='20030001'IF (SELECT 专业FROM Students WHERE 学号=@MyNO)='计算机软件'BEGINSELECT AVG(成绩) AS 平均成绩FROM ScoreWHERE 学号=@MyNOENDELSEPRINT '学号为' +@MyNO+'的学生不存在或不属于软件专业' GO(（2）首先定义一个名为@MyNo的局部变量，并给它赋初值，如果@MyNo属于计算机软件专业，则显示出平均成绩，否则显示“学号为@MyNo 的学生不存在或不属于软件专业”)（3）declare @a numeric(5,2)set @a=(select avg(成绩) from score)select *from scorewhere 成绩>=@a(从score表中查询出大于等于平均成绩的所有记录。

)（4）declare @a numeric(5,2),@b numeric(5,2) set @a=(select max(成绩) from score)set @b=(select min(成绩) from score)print @a-@b(求出score表中最高成绩与最低成绩的分数之差。

《SQL Server实用教程》教案实验7 T-SQL编程授课教师：课时：2学时●实验目的掌握索引的使用方法掌握数据完整性实现方法●实验重点索引的使用方法●实验难点索引的使用方法●实验内容与步骤1、建立索引（1）对YGGL数据库的Employees表中的DepartmantID列建立索引。

（2）对XSCJ数据库的KC表中的课程号列建立索引。

（3）对XSCJ数据库的XS_KC表中的学号列和课程号列建立复合索引。

2、数据完整性1、创建一个表EmployeeID、Name、Sex和Education列。

将Name设为主键，作为列Name的约束。

对EmployeeID列进行unique约束。

2、删除上题中创建的unique约束。

3、创建一个表cjb2包含学号，课程号，成绩将学号和课程号使用复合列作为主键创建约束。

3、创建新表student,只考虑号码和性别两列，性别只能包含男或女。

4、创建新表salary2，完成后修改salary2表，增加“departmentid”字段且添加约束departmentid 的值在1-5之间。

a、测试check约束的有效性。

●实验小结（要求学生写）答案下页1.对YGGL数据库的Employees表中的DepartmantID列建立索引。

use ygglgocreate index kc_name_indon dbo.Departments(DepartmentID)2.对XSCJ数据库的KC表中的课程号列建立索引。

use PXSCJgocreate index kch_nameon dbo.KCB(CourseId)3.对XSCJ数据库的XS_KC表中的学号列和课程号列建立复合索引。

use PXSCJgocreate index xh_kc_nameon {dbo.XSB(StudentId) dbo.KCB(CourseId)};with (drop_existing=on)4.创建一个表EmployeeID、Name、Sex和Education列。

一、根据要求用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语句完成如下操作。

1下列说法中正确的是：（ D ）A 、 SQL 中局部变量可以不声明就使用B 、 SQL 中全局变量必须先声明再使用C 、 SQL 中所有变量都必须先声明后使用D 、 SQL 中只有局部变量先声明后使用；全局变量是由系统提供的用户不能自己建立。

2.哪个关键字用于测试跟随的子查询中的行是否存在（B）。

A．MOV B．EXISTSC．UNION D．HAVING3 .下列哪些语句用于创建存储过程（A）？A、CREATE PROCEDUREB、CREATE TABLEC、DROP PROCEDURED、其他5.在SQL中，SELECT语句的“SELECT DISTINCT”表示查询结果中 ( C )。

A.属性名都不相同B.去掉了重复的列C.行都不相同D.属性值都不相同6.SQL 语言集数据查询、数据操作、数据定义和数据控制功能于一体，语句INSERT、DELETE、UPDATA实现下列哪类功___A_____。

A. 数据查询B. 数据操纵C. 数据定义D. 数据控制7.SQL Server 2000 采用的身份验证模式有（D）。

（A）仅Windows身份验证模式（B）仅SQL Server身份验证模式（C）仅混合模式（D）Windows身份验证模式和混合模式10.SQL Server 2000 企业版可以安装在操作系统上。

( C )（A）Microsoft Windows 98（B）Microsoft Windows 2000 Professional（C）Microsoft Windows 2000 Server（D）Microsoft Windows XP11.SQL Server是一个（C）的数据库系统。

（A）网状型（B）层次型（C）关系型（D）以上都不是12.SQL语言中，删除一个视图的命令是( B )。

A. DELETEB. DROPC. CLEARD. REMOVE13.SQL语言中，删除记录的命令是（A）。

T-SQL综合练习（参考答案）1.USE AdventureWorksSELECT CardNumber AS [Credit Card Number]FROM Sales.CreditCard2.SELECT categoryid '产品类别代号',productname,unitprice '单价'FROM NorthWind.dbo.productsORDER BY categoryid,unitprice DESC3.-- 使用DISTINCT 关键字列出不重复的Title 数据行SELECT DISTINCT TitleFROM AdventureWorks.HumanResources.EmployeeORDER BY Title4.---透过GETDATE() 系统函数取得当前执行的日期与时间，并通过运算符示范加减日期。

SELECT GETDATE()'今天',GETDATE()+1 '明天',GETDATE()-2 '前天'5.--加号「+」运用在字串与数值表达式中的执行结果。

USE AdventureWorks;SELECT c.FirstName+'.'+stName FullName, VacationHours, SickLeaveHours,VacationHours + SickLeaveHours '休假总时数'FROM HumanResources.Employee AS eJOIN Person.Contact AS c ON e.ContactID = c.ContactID6.--通过CONVERT 转换函数，将数值数据转换为字串类型，则两个相同类型的字串即可相加SELECT ProductID,Name,convert(nvarchar,'04')+ProductNumberFROM AdventureWorks.Production.Product p或者SELECT ProductID,Name,'04'+ProductNumberFROM AdventureWorks.Production.Product p7.SELECT PhoneFROM AdventureWorks.Person.ContactWHERE Phone like'415%'8.select distinct City , PostalCode from Person.Addresswhere PostalCode LIKE'[A-E][A-Z][0-9][0-9]'SELECT FirstName, LastNameFROM Person.ContactWHERE FirstName LIKE'Al[^a]%'ORDER BY FirstNameSELECT CustomerID,NameFROM AdventureWorks.Sales.StoreWHERE CustomerID LIKE'1%'AND Name LIKE N'B%'9.SELECT EmployeeID, TitleFROM AdventureWorks.HumanResources.EmployeeWHERE Title NOT IN('Design Engineer','Tool Designer','Marketing Assistant')或者SELECT EmployeeID, TitleFROM AdventureWorks.HumanResources.EmployeeWHERE Title <>'Design Engineer'AND Title <>'Tool Designer'AND Title <>'Marketing Assistant'10.SELECT ProductID, ProductModelIDFROM AdventureWorks.Production.ProductWHERE ProductModelID = 20 OR ProductModelID = 21AND Color ='Red'SELECT ProductID, ProductModelIDFROM AdventureWorks.Production.ProductWHERE(ProductModelID = 20 OR ProductModelID = 21)AND Color ='Red'11.--取回指定范围内的数据(使用BETWEEN AND关键字)SELECT ProductID,Name,ListPriceFROM AdventureWorks.Production.ProductWHERE ListPrice BETWEEN 20 AND 25SELECT ProductID,NameFROM AdventureWorks.Production.ProductWHERE ListPrice NOT BETWEEN 15 AND 2512SELECT Title,AVG(VacationHours)as [平均休假时数],SUM(SickLeaveHours)as [病假总时数]FROM HumanResources.EmployeeGroup BY Title13a.CREATE TABLE dbo.EmployeeEmployeeID VARCHAR(10)NOT NULL PRIMARY KEY,Employee VARCHAR(30)NOT NULL);b.CREATE TABLE dbo.Orders(OrderID VARCHAR(10)NOT NULL,--PRIMARY KEY,EmployeeID VARCHAR(10)NULL,Price INT NULL);C.INSERT INTO dbo.Employee(EmployeeID, Employee)VALUES('1001','Sandy'); INSERT INTO dbo.Employee(EmployeeID, Employee)VALUES('1002','Byron'); INSERT INTO dbo.Employee(EmployeeID, Employee)VALUES('1003','Bill'); INSERT INTO dbo.Employee(EmployeeID, Employee)VALUES('1004','Lisa');INSERT INTO dbo.Orders(OrderID,EmployeeID,Price)VALUES('A013','1001', 150);INSERT INTO dbo.Orders(OrderID,EmployeeID,Price)VALUES('A014','1002', 50);INSERT INTO dbo.Orders(OrderID,EmployeeID,Price)VALUES('B103','1003', 370);INSERT INTO dbo.Orders(OrderID,EmployeeID,Price)VALUES('B140','1003', 110);INSERT INTO dbo.Orders(OrderID,EmployeeID,Price)VALUES('C239','1001', 1020);INSERT INTOdbo.Orders(OrderID,EmployeeID,Price)VALUES('E134','1002',200);d.SELECT Employee,count(OrderId)订单数量,sum(Price)业绩FROM Employee AS EINNER JOIN Orders AS OON O.EmployeeID = E.EmployeeIDGROUP BY Employeee.SELECT Employee,count(OrderId)订单数量,sum(Price)业绩FROM Employee AS ELEFT JOIN Orders AS OON O.EmployeeID = E.EmployeeIDGROUP BY Employee14.ALTER TABLE Orders ALTER COLUMN OrderID integer15.1)SELECT TempDate FROM tblTemp WHERE TempID%2=02)SELECT*FROM tblTemp WHERE TempID=(SELECT MAX(TempID)FROM tblTemp) 16.1）SELECT*FROM physicians WHERE state='NY'OR state='WA'OR state='VA' OR state='CA'或者SELECT*FROM physicians WHERE state in('NY','WA','VA','CA'）2)SELECT DISTINCT state FROM physicians3)SELECT physicians.*,'Amt_Due'=(co_pay+5)FROM physicians或者SELECT physicians.*,(co_pay+5)'Amt_Due'FROM physicians17.1）CREATE TABLE tblCustomerInfo(代号int IDENTITY(10001,5)NOT NULL,名称VARCHAR(80),电话VARCHAR(20),传真VARCHAR(20),备注VARCHAR(2000))2) ATLTER TABLE tblCustomerInfo ADD手机CHAR(11)。

实训131.使用T-SQL语句创建“Windows身份验证”登录名（对应的Windows用户为testwin）。

create login [stu1001\testwin]from windowsexec sp_addlogin 'aa' , '123' /*用存储过程创建sql登录名*/2.使用T-SQL语句查看所创建的登录名“testwin”的属性。

select loginname,sid from master.sys.syslogins3.使用T-SQL删除登录名“testwin”。

drop login [stu1001\testwin]4.使用SSMS创建与登录名“testsql”对应的数据库用户“sqluser”(对应于数据库WebShop)，并查看其属性5.使用SSMS删除数据库用户“sqluser”。

6.使用T-SQL语句创建与登录名“testwin”对应的数据库用户“winuser”。

use webshopcreate user [winuser] for login [stu1001\testwin]7.将数据库用户“winuser”修改为“win”。

use webshopalter user [winuser]with name=win/*Alter User的语法结构：ALTER USER userNameWITH <set_item> [ ,...n ]<set_item> ::=NAME = newUserName| DEFAULT_SCHEMA = { schemaName | NULL }| LOGIN = loginName| PASSWORD = 'password' [ OLD_PASSWORD ='oldpassword' ]| DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }*/1.使用SSMS或T-SQL语句查看WebShop数据库中数据库用户的信息。