SQLServer练习题
一、填空题
1.数据库系统的特点分别是数据的结构化、数据共享、数据独立性和可控冗余度。2.在数据库的表中，主键是指表中的某一列或几列，它的值唯一标识一行。3.SQLServer2005的文件包括:数据文件（.mdf或.ndf）和日志文件。4.在SQLServer2005中，要访问某个数据库，除了需要有一个登录帐号外，还必须有一个该数据库的用户帐号。5.数据库管理系统，简称DBMS，它是指帮助用户建立、使用和管理数据库的软件系统。6.在T-SQL语言中，若要修改某张表的结构，应该使用的修改关键字是ALTER；若要创建一个数据库，应该使用的语句是CREATEDATABASE。5.命令TruncateTable的功能是清空数据库。6.数据完整性是指存储在数据库中的数据的一致性和准确性，，根据完整性机制所作用的数据库对象和范围不同，可以分为：实体完整性、域完整性、参照（或：引用）完整性和用户自定义完整性约束等四种，其中主键可以实现实体完整性。7.模糊查询符号%代表任意字符，查询条件e%im代表意思是查询以e开头中间任意字符（串）且以im结尾的字符串。8.使用索引可以减少检索时间，根据索引的存储结构不同将其分为两类：簇集索引和非簇集索引。9.SQLServer中的变量分为两种，全局变量和局部变量。其中全局变量的名称以@@字符开始，由系统定义和维护；局部变量以@字符开始，由用户自己定义和赋值。10.实现数据完整性的途径有默认值、约束、规则、存储过程、触发器等。11.访问SQLServer数据库对象时，需要经过身份验证和权限验证两个阶段，其中身份验证分为Windows验证模式和混合验证模式。12.SQLServer使用日志文件记载用户对数据库进行的所有操作。13.sp_help系统存储过程的功能是显示数据对象的相关属性。？14.SQL语言中行注释的符号为--；块注释的符号为/**/。15.备份是为了在数据库遭到破坏时，能够修复，数据库备份的类型有四种分别为：数据库备份、日志备份、差异备份、文件和文件组备份。16.在SQLServer2005中，其权限分为三类，即对象权限、语句权限和隐含权限。17.用户对数据进行添加、修改和删除时，自动执行的存储过程称为触发器。18.对数据进行统计时，求最大值的函数是MAX()。19.还原是指将数据库备份加载到服务器中的过程，在SQLServer中有三种数据库的还原模型，分别是简单还原、完全还原、批日志还原。20.DTS是指数据转换服务。21.在给数据库改名之前，必须设置数据库处于单用户状态。22.在一个表中主键的个数为：1。23.在SQLServer2005中，索引的顺序和数据表的物理顺序相同的索引是聚集索引。24.角色是一组用户所构成的组，可以分为服务器角色和数据库角色。25.触发器有3种类型，即INSERT类型、UPDATE类型和DELETE类型。26.如果要计算表中的行数，可以使用聚合函数COUNT（）。27.在SQLServer2005中修改表的结构应使用关键字ALTER,修改表中的数据应使用关键字UPDATE。28.SQLServer是作为Windows系统的一个服务运行的，用户可以启动、暂停或停止它。29.在SQLServer2005中，约束定义关于列中允许值的规则，是强制实施完整性的标准机制。使用约束优先于使用DML触发器、规则。约束有非空约束、缺省约束、主键约束、外键约束、检查约束和唯一约束等6种类型。30.对象权限是指用户基于数据库对象层次上的访问和操作权限，共有5种：SELECT、INSERT、DELETE、UPDATE（或：更新）和EXECUTE（或：执行）。
31．UNION可以把两个或多个SELECT语句的查询结果组合成一个结果集，使用时要求所有SELECT语句的列数应相同，对应列的数据类型相容。32．事务是指一个操作序列，这些操作序列要么都被执行，要么都不被执行。33．差异备份只记录自上次完整数据库备份后发生更改的数据。34.在SELECT语句的FROM子句中最多可以指定256个表或视图，相互之间要用逗号分隔；当所查询的表不在当前数据库时，可用数据库.所有者名称.对象名称格式来指出表或视图对象。35.删除表命令是：droptable。36.计算字段的累加和的函数是：Sum()。37.在SQLServer2005中，一个批处理语句是以GO结束的。38.如果要计算表中数据的平均值，可以使用的聚合函数是AVG()。39.在SQLServer2005中，每个数据表最多有1024个列；最多有1个簇式索引。40.在一个复合索引中最多可以有16个关键字。41.SQLServer使用各数据库的事务日志来恢复事务。42.在创建一个表的过程中，如果包含REFERENCES子句，则表示在表中建立参照性约束。43.可以通过INSERT语句，向表中添加新的纪录或在记录中插入部分字段的数据。44.可以在INSERT子句中指定列名，Values字句中的数据项与指定的列名相对应，没有列出的字段必须具有允许NULL、Timestamp、IDENTITY、Default四种定义的条件之一。45.T-SQL语言中数据的检索是通过SELECT语句及与其他一系列子句配合来完成的。46.在SELECT命令中，GROUPBY子句可依字段类别总计函数处理查询结果；而将查询结果以某字段或运算值数据排序条件的子句是ORDERBY。47.为了简便处理，我们可以为表名赋予别名，以减少代码量。使用别名时不能同时使用表名，字段名的格式。48.在UNION操作中，如果不指明ALL子句将删除重复行。49.UNION子句可以出现在INSERT、SELECT语句中。但是不能出现在CREATEVIEW语句内或子查询内。50.当向数据表中插入数据时，如果没有明确的提供输入值时，SQLServer自动为该列输入指定值，则约束的类型为DEFAULT。51.通过逻辑表达式判断限制插入到列中的值的强制约束类型为CHECK。52.允许主键上有空值，但不允许主键上有重复值的强制类型约束为UNIQUE。53.DEFAULT约束，只在执行INSERT语句时作用。54.在创建DEFAULT约束时，SQLServer将对表中现有的数据进行完整性验证。55.CHECK约束在执行INSERT语句或UPDATE语句时起作用。56.在向表中添加强制性约束时，应当在ALTERTABLE命令中使用ADDCONSTRAINT子句。57.必须是表的所有者，才能执行CREATEINDEX语句来创建索引。58.视图可以在不同数据库中的不同表上建立，一个视图最多可以引用1024个字段。59.视图隐蔽了数据库设计的复杂性，这使得开发者可以在不影响用户使用数据库的情况下改变数据库内容。60.建立视图之后，可以简化数据库管理，如可以通过GRANT命令为各种用户授予在视图上的操作权限。61.用户可以通过执行系统存储过程sp_helptext查看视图的定义信息。62.视图的修改和数据库中表的修改一样，视图的修改也是由ALTER语句来完成的；视图的删除也与表的删除类似，可以通过DROP语句来实现。63.存储过程在第一次执行时进行语法检查和编译。64.不能将CREATEPROCEDURE语句与其他SQL语句组合到单个批处理中，每一个批处理就是一个GO语句段。65.存储过程只能在当前数据库中创建。66.为了定义接受输入参数的存储过程，需要在CREATEPROCEDURE语句中生命一个或多个变量作为参数。67.创建存储过程时，参数的默认值必须是常量或NULL。68.在存储过程中，为了输出参数，需要在CREATEPROCEDURE语句中指定OUTPUT关键字。69.可以通过CreateTrigger语句来创建触发器。
70.在触发器被执行的同时，取消触发器的SQL语句的操作，需要使用INSTEADOF关键字实现。71.CHECK约束只能根据逻辑表达式或同一表中的另外一列来验证列值。如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器。72.SQLServer用DECLARE语句声明变量。73.使用T-SQL语句创建SQLServer账号，需要用到系统存储过程sp_addlogin。74.与权限管理相关的T-SQL语句有三个：GRANT、REVOKE和DENY。75.使用BACKUPDATABASE命令可以对数据库进行完全拷贝的备份。76.使用RESTOREDATABASE命令可以对数据库进行还原。
二、简答题
1、简述视图的概念和优点？（1）概念：视图是从一个或多个表（或视图）导出的虚表，是根据用户观点所定义的数据库结构。视图与表不同，它所对应的数据不进行实际存储，在数据库中只存储视图的定义（一组符合用户需求的SQLSELECT语句）。（2）优点：1）为用户集中数据，简化用户的数据查询和处理；2）屏蔽数据库的复杂性；3）简化用户权限的管理，增加了安全性；4）便于数据共享；5）为数据库重构提供一定的逻辑独立性。2、列出SQLServer2005中常见的数据库对象。数据库对象主要包括表、视图、索引、存储过程、触发器和约束等。3、什么是索引？索引分为哪两种？（1）索引是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标志这些值的数据页的逻辑指针清单。索引是依赖于表建立的,它提供了数据库中编排表中数据的内部方法。一个表的存储是由两部分组成的,一部分是用来存放表的数据页面,另一部分存放索引页面。从某种程度上,可以把数据库看作一本书,把索引看作书的目录,通过目录查找书中的信息,显然较没有目录的书方便、快捷。（2）聚集索引和非聚集索引。4、创建索引有什么优点和缺点？（1）优点：索引在数据库中的作用类似于目录在书籍中的作用，用来提高查找信息的速度。使用索引查找数据，无需对整表进行扫描，可以快速找到所需数据。另一方面，通过创建唯一性索引，可以保证每一行数据的唯一性。（2）缺点：索引需要占用数据表以外的物理存储空间。创建索引和维护索引要花费一定的时间。当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。5、修改视图中的数据会受到哪些限制？（1）必须有权限；（2）单表视图可直接修改；（3）多表可在视图中定义insteadof触发器；（4）对由多表连接成的视图修改数据时，不能同时影响一个以上的基表，也不能删除视图中的数据；（5）对视图上的某些列不能修改，这些列是：计算列、内置函数列和行集合函数列；（6）对具有NOTNULL的列进行修改时可能会出错；通过视图修改或者插入数据时，必须保证未显示的具有NOTNULL属性的列有值，可以是默认值、IDENTITY等，否则不能向视图中插入数据行；（7）如果某些列因为规则或者约束的限制不能接受从视图插入的数据时，则插入数据可能会失败；（8）删除基表并不删除视图，建议采用与表明显不同的名字命名规则。6、什么是数据完整性？主要作用是什么？数据完整性（DataIntegrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。主要作用是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息。7、数据完整性有哪几类？数据完整性分为四类：实体完整性（EntityIntegrity）、域完整性（DomainIntegrity）、参照完整性（ReferentialIntegrity）、用户定义的完整性（User-definedIntegrity）。8、什么是主键约束？什么是惟一约束？两者有什么区别？（1）主键约束：有一列或列的组合，其值能唯一的标识表中的每一行。（2）唯一约束：不允许数据库表在指定列上具有相同的值，但允许有空值。（3）区别：一个表可以定义多个唯一约束，只能定义一个主键约束；允许空值的列上
可以定义唯一约束，不能定义主键约束。9、什么是存储过程？存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，它是数据库中的一个重要对象。用户通过指定存储过程的名字并给出参数（如果带有参数）来执行它。10、什么是触发器？触发器有什么作用？触发器是数据库服务器中发生事件时自动执行的特种存储过程。触发器经常用于加强数据的完整性约束和业务规则等，利用触发器可以方便地保持数据库中数据的完整性。11、简述INSERT触发器的工作原理当向表中插入数据时，INSERT触发器触发执行。当INSERT触发器触发时，新的记录增加到触发器表中和inserted表中。inserted表是逻辑表，该保存了所插入记录的拷贝，允许用户参考INSERT语句中的数据。触发器可以检查inserted表，来确定该触发器的操作是否应该执行和如何执行。在inserted表中的那些记录，总是触发表中一行或多行记录的冗余。12、用户权限的种类有哪些？各自的作用？①对象权限、作用：决定用户对数据库对象所执行的操作，包括用户对数据库中的表、视图、列或存储过程等对象的操作权限②语句权限、作用：决定用户能否操作数据库和创建数据库对象③隐含权限、作用：控制那些只能由预定义系统角色的成员或数据库对象所有者执行的活动。
三、程序阅读题
1、阅读下面的程序段，然后简述该程序段所完成的功能。BeginWaitfortime’15:43’CreateViewmy_viewAsSelectorder_date，book_number，book_idFromorderformWherebook_number!<25End该程序段的功能是：。答：系统在15：为orderform表中订购数量book_number大于25本的订购信息的order_date、43book_number和book_id三个字段创建一个名为my_view的视图。2、下面程序段的功能是：在数据库中判断是否存在名为my_proc的存储过程，若存在，则删除之，然后创建同名的存储过程，该存储过程的功能是向book表的book_id、book_name、price和publisher字段插入数据。阅读并回答以下问题：UsebookdbGoIfExists(SelectnameFrom①Wherename=‘my_proc’AndType=‘②’)DropProcmy_procGoCreateProcmy_proc@aint,@bchar(50),@cfloat,@dpublisher③InsertIntostudent(book_id,book_name,price,publisher)Values(@a,@b,@c,@d)Go问题：（1）填写该程序段中空白处的内容：①Sysobjects②P③As（2）任写一条调用该存储过程的语句：。答：（1）①Sysobjects②P③As（2）Execmy_proc7,‘SQLServer2000教程’，38，‘北京科海集团公司’或Execmy_proc@a=7,@b=‘SQLServer2000教程’，@c=38,@d=‘北京科海集团公司’
四、编程题
（一）、有一个“学生－课程”数据库，数据库中包括三个表：(1)“学生”表Student由（Sno）、（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为：Student(Sno,Sname,Ssex,Sage,Sdept)Sno为关键字。(2)“课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为：Course(Cno,Cname,Cpno,Ccredit)Cno为关键字。(3)“学生选课”表SC由（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为：SC(Sno,Cno,Grade)(SNO,CNO)为关键字。完成下列操作：1、请把其中建立“学生”表Student的语句写下来，其中属性不能为空，并且其值是唯一的。2、在student表中查询Sdept是‘计算机’的学生的所有信息，并按Sno降序排列。3、在以上三个表中查询Ccredit为5并且Grade大于60的学生的、和性别。4、Course表创建一个名称为my_trig的触发器，为当用户成功删除该表中的一条或多条记录时，触发器自动删除SC表中与之有关的记录。（注：在创建触发器之前要判断是否有同名的触发器存在，若存在则删除之。）1、CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20),SsexCHAR(1),SageINT,SdeptCHAR(15));2、Select*FromstudentWhereSdept=’计算机’OrderBySnoDesc3、方法1：方法：Selectstudent.Sno,Sname,SsexFromstudent,course,scWhere(student.Sno=sc.Sno)And(course.Cno=sc.Cno)And(course.Ccredit=5)And(sc.Grade>60)方法2：：Selectstudent.Sno,Sname,SsexFromstudentInnerJoinscInnerJoincourseOncourse.Cno=sc.CnoOnstudent.Sno=sc.SnoWhere(course.Ccredit=5)And(sc.Grade>60)4、IfExists(SelectnameFromsysobjectsWherename=‘my_trig’AndType=‘tr’)DropTriggermy_trigGoCreateTriggermy_trigOnCourseForDeleteAsDeleteFromscWhereSnoIn(SelectSnoFromdeleted)Go（二）数据库中包括三个表student表（学生表）性别年龄系别120李明男计算机系219张然女经管系318许晴女电子系course表(课程表)课程号课程名1SQLServer2VB3专业英语sc表（选课表）学分432
课程号成绩11902185227633701、把course表中课程号为2的课程的学分修改为4。2、在student表中查询年龄大于18的学生的所有信息，并按降序排列。3、在以上三个表中查询选的课程的“学分”为4，并且成绩大于80的学生的、和性别。4、创建一存储过程GetDetailByName，通过输入参数学生（如“张然"），筛选出该学生的基本信息，对不存在此学生的输入值，必须作一检测，打印信息“不存在此学生”。1、UpdatecourseSet学分=4Where课程号=22、Select*FromstudentWhere年龄>18OrderByDesc3、方法1：方法：SelectDistinctstudent.,,性别Fromstudent,course,scWhere(student.=sc.)And(course.课程号=sc.课程号)And(course.学分=4)And(sc.成绩>80)方法2：：SelectDistinctstudent.,,性别FromstudentInnerJoinscInnerJoincourseOncourse.课程号=sc.课程号Onstudent.=sc.Where(course.学分=4)And(sc.成绩>80)4、CreateProcedureGetDetailByName@Namenvarchar(10)ASIF(SELECTCount(*)FROMstudentWHERE=@Name)>0Select*FromstudentWhere=@NameELSEPrint'警示：不存在为'+@Name+'的学生资料'(三)、查询设计某个学籍数据库有如下表结构：学生（，，性别，出生日期，班级）课程（课程号，课程名称，课程类别，课时）选课（，课程号，成绩）请完成下列查询要求。1．查询出“软件041”班的所有男生的和。2．查询出课程名称中含有“语言”一词的课程的所有信息。3．查询出“C语言”课程成绩前5名的学生的、和班级。4．查询出各班级学生人数。5．查询出“计算机应用基础”课程成绩高于张三同学此门课程成绩的学生的和。1．SELECT,FROM学生WHERE班级=‘软件041’AND性别=‘男’2．SELECT*FROM课程WHERE课程名称LIKE‘%语言%’3．SELECTTOP5学生.,,班级FROM学生JOIN选课ON学生.=选课.JOIN课程ON课程.课程号=选课.课程号WHERE课程名称=‘C语言’ORDERBY成绩DESC4．SELECT班级,COUNT(*)AS人数FROM学生GROUPBY班级
5．SELECT学生.,FROM学生JOIN选课ON学生.=选课.JOIN课程ON课程.课程号=选课.课程号WHERE课程名称=‘计算机应用基础’AND成绩>(SELECT成绩FROM学生JOIN选课ON学生.=选课.JOIN课程ON课程.课程号=选课.课程号WHERE课程名称=‘计算机应用基础’AND=‘张三’)
五、综合应用题
(一)、学生选课系统，主要表有：学生表（、、性别、专业、出生年月等），选课表（学号、课程号、分数），课程表（课程号、课程名、所属专业、学分）要求：1、写出SQL语句。1)查询所有选“SQL课程”的学生，和专业。2)删除所有选择“数学”的同学的选课记录。3)查询有哪些课程没有被任何同学报选。2、创建存储过程。1)、能够查询出选修任何课程的学生信息及相应的分数。2)、以表格的形式返回本课程的平均分数。3、在课程表中增加“No_id”字段，数据类型char(12)，不可空，并将其设定为UNIQUE约束，写出其命令语句。答：1、1)SELECT,,专业FROM学生表A,选课表B,课程表CWHEREA.=B.ANDB.课程号=C.课程号ANDC.课程名='SQL'或SELECT,,专业FROM学生表WHEREIN(SELECTFROM选课表WHERE课程号=(SELECT课程号FROM课程表WHERE课程名=‘SQL课程’))2)DELETEFROM选课表WHERE课程号IN(SELECT课程号FROM课程表WHERE课程名=‘数学’)3)SELECT*FROM课程表WHERE课程号NOTIN(SELECTDISTINCT课程号FROM选课表)2、（1）CREATEPROCEDURExc_info@knamechar(16)ASSELECTa.*,b.分数FROM学生表aINNERJOIN选课表bONa.=b.WHEREb.课程号=(SELECT课程号FROM课程表WHERE课程名=@kname)（2）CREATEPROCEDURExca_info@knamechar(16)ASSELECTAvg(分数)FROM选课表WHERE课程号=(SELECT课程号FROM课程表WHERE课程名=@kname)GROUPBY课程号3、ALTERTABLE课程表
ADDNo_idCHAR(12)NOTNULLCONSTRAINTtable_uniqueUNIQUE(二)、根据下面某教学管理数据库的表结构，完成下面的程序填空题。教师（职工号，，学历，职称）课程（课程号，课程名称，课程类别）任课（职工号，课程号，周课时）1．补填下面的存储过程的定义，使其被调用时，能根据调用程序提供的教师使用输出参数返回该教师任课的课程数。CREATEPROCEDURE按教师查询任课课程数@VARCHAR(10),@课程数INT_____________________ASSELECT_____________________FROM教师JOIN任课ON教师.职工号=任课.职工号WHERE_____________________2．完成下面的触发器的定义，当向任课表中插入记录时，触发器能判断若插入的记录使该教师的任课周课时总计超过20，则回滚插入操作。CREATETRIGGER添加教学任务ON任课AFTERINSERTASDECLARE@总课时INTSELECT@总课时=SUM(任课.周课时)FROM任课JOIN_____________________IF@总课时>20_____________________答：1．OUTPUT;@课程数=COUNT(*);=@2．insertedON任课.职工号=inserted.职工号；ROLLBACKTransaction（三）、根据所给的商品库和教学库，按照下列所给的每条SQL查询语句写出相应的功能，或者按照下列所给的每种功能写出相应的SQL查询语句。在名称为商品库的数据库中包含有商品表1和商品表2，它们的定义分别为：商品表1(商品代号char(8),分类名char(8),单价float,数量int)商品表2(商品代号char(8),产地char(6),品牌char(6),)在名称为教学库的数据库中包含有学生、课程和选课三个表，它们的定义分别为：学生(学生号char(7),char(6),性别char(2),出生日期datetime,专业char(10),年级int)课程(课程号char(4),课程名char(10),课程学分int选课(学生号char(7),课程号char(4),成绩int)1．SelectDistinct产地From商品表22．Select*From学生Where学生号In(Select学生号from选课GroupBy学生号HavingCount(*)=1)3．Select*From学生Where学生号In(Select学生号From选课GroupBy学生号HavingCount(*)<=2)ORNOTExists
(Select*From选课Where学生.学生号=选课.学生号)4．从商品库中查询出每类（即分类名相同）商品的最高单价。5．从教学库中查询出至少选修了为@m1学生所选课程中一门课的全部学生。答：1.从商品库中查询出所有商品的不同产地。2.从教学库中查询出只选修了一门课程的全部学生。3.从教学库中查询出最多选修了2门课程（含未选任何课程）的全部学生。4.Select分类名,Max(单价)As最高单价From商品表1GroupBy分类名5.SelectDistinct学生.*From学生,选课Where学生.学生号=选课.学生号And课程号=Any(Select课程号From学生,选课Where学生.学生号=选课.学生号And=@m1)
