SQLServer面试题及答案.doc

为管理岗位业务培训信息，建立3个表:S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄C (C#,CN ) C#,CN 分别代表课程编号、课程名称SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩1. 使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名 -实现代码:Select SN,SD FROM SWhere S# IN(Select S# FROM C,SCWhere C.C#=SC.C#AND CN=N税收基础) 2. 使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位-实现代码:Select S.SN,S.SD FROM S,SCWhere S.S#=SC.S#AND SC.C#=C23. 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位-实现代码:Select SN,SD FROM SWhere S# NOT IN(Select S# FROM SCWhere C#=C5)4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位-实现代码:Select SN,SD FROM SWhere S# IN(Select S# FROM SCRIGHT JOINC ON SC.C#=C.C# GROUP BY S#HAVING COUNT(*)=COUNT(S#)5. 查询选修了课程的学员人数-实现代码:Select 学员人数=COUNT(DISTINCT S#) FROM SC6. 查询选修课程超过5门的学员学号和所属单位-实现代码:Select SN,SD FROM SWhere S# IN(Select S# FROM SCGROUP BY S#HAVING COUNT(DISTINCT C#)5)题目2问题描述:已知关系模式:S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩1. 找出没有选修过“李明”老师讲授课程的所有学生姓名-实现代码:Select SNAME FROM SWhere NOT EXISTS(Select * FROM SC,CWhere SC.CNO=C.CNOAND CNAME=李明AND SC.SNO=S.SNO)2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩-实现代码:Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)FROM S,SC,(Select SNOFROM SCWhere SCGRADE=2)A Where S.SNO=A.SNO AND SC.SNO=A.SNOGROUP BY S.SNO,S.SNAME3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名-实现代码:Select S.SNO,S.SNAMEFROM S,(Select SC.SNOFROM SC,CWhere SC.CNO=C.CNOAND C.CNAME IN(1,2)GROUP BY SNOHAVING COUNT(DISTINCT CNO)=2)SC Where S.SNO=SC.SNO4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号-实现代码:Select S.SNO,S.SNAMEFROM S,(Select SC1.SNOFROM SC SC1,C C1,SC SC2,C C2Where SC1.CNO=C1.CNO AND C1.NAME=1AND SC2.CNO=C2.CNO AND C2.NAME=2AND SC1.SCGRADESC2.SCGRADE)SC Where S.SNO=SC.SNO 5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩-实现代码:Select S.SNO,S.SNAME,SC.1号课成绩,SC.2号课成绩FROM S,(Select SC1.SNO,1号课成绩=SC1.SCGRADE,2号课成绩=SC2.SCGRADEFROM SC SC1,C C1,SC SC2,C C2Where SC1.CNO=C1.CNO AND C1.NAME=1AND SC2.CNO=C2.CNO AND C2.NAME=2AND SC1.SCGRADESC2.SCGRADE)SC Where S.SNO=SC.SNOoracle Certification Program (OCP认证)的题目(1) A 表中有100条记录.Select* FROM A Where A.COLUMN1 = A.COLUMN1这个语句返回几条记录? (简单吧,似乎1秒钟就有答案了:)(2) Create SEQUENCE PEAK_NO Select PEAK_NO.NEXTVAL FROM DUAL - 假设返回110秒中后,再次做 Select PEAK_NO.NEXTVAL FROM DUAL - 返回多少?(3) SQL connect sys as sysdbaConnected. SQL insert into dual values ( Y);1 row created.SQL commit;Commit complete.SQL select count(*) from dual;COUNT(*)-2SQL delete from dual; commit;-DUAL里还剩几条记录?JUST TRY IT 问：怎样在一个Update语句中使用表B的三个列更新表A中的三个列？答：对这个问题，您可以使用强大的关系代数。本页中的代码说明了如何组合使用FROM子句和JOIN操作，以达到用其他表中数据更新指定列的目的。在设计关系表达式时，您需要决定是否需要单一行匹配多个行（一对多关系），或者需要多个行匹配被联接表中的单一行以更新所有行（多对一关系）。在一对多关系中，SQL Server始终使用它找到的最后一行更新数据。但是，您无法控制最后一行所在的位置。在多处理器的计算机上，查询可能是同步进行的，相同的查询最后一行的位置可能会不同。因此，Microsoft建议尽量不要使用一对多关系。如果待更新的表与FROM子句中的表相同，并且FROM子句只包含对该表的一个引用，别名可能未被指定。如果待更新的表在FROM子句中出现了多次，只有该表的一个引用可以省略表的别名，对该表的所有其他引用必须包含一个表别名。USE tempdbGOCreate TABLE #t1 (c1 int NOT NULL, c2 char(5), c3 char(5),c4 char(5)GOCreate TABLE #t2 (c1 int NOT NULL, c2 char(5), c3 char(5), c4 char(5)GO-数据赋值Insert #t1 values (1,hello,there,fred)Insert #t2 values (1,how,are,you?)-更新数据Update #t1 SET #t1.c2 = #t2.c2, #t1.c3 = #t2.c3, #t1.c4 = #t2.c4 FROM #t2 Where #t1.c1 = #t2.c1-检查结果Select * FROM #t1 Microsoft SQL Server开发团队-回滚事务日志文件中的事务更新日期: 2002年7月22日 本技巧摘自SQL Server杂志的一期。如需了解更多的技巧，请访问SQL Server的使用技巧中心。问：怎样使用Transact-SQL回滚某个位于事务日志文件中的事务（例如，ID 0000:0010a183）？答：出于预防数据错误的考虑，SQL Server并不支持个别事务的回滚。举例来说，假设两个事务T1和T2使用现金余额域。T1添加了500美金，T2使用更新后的值进行了某个操作。如果回滚T1，则T2可能是错误的。但是，您可以使用时间戳或事务日志标记将日志恢复至预定义的标记或时间点。以下两个例子说明了如何使用SQL Server 2000语法。例1：使用时间戳将日志进行时点恢复使用以前的完全备份恢复数据库，并使其为日志恢复做好准备。RESTORE DATABASE pubs FROM DISK = NC:BackupsFullbackup.bak WITH NORECOVERY现在您可以将日志前滚到合适的时间点，并使数据库可供使用。请注意，STOPAT在数据库正在执行大容量日志时禁止执行。RESTORE LOG pubs FROM DISK=NC:BackupsLogbackup.bak WITH RECOVERY,STOPAT=02/11/2002 17:35:00例2：使用数据库标记将日志恢复到预定义时间点的语句在事务日志中置入一个标记。请注意，被标记的事务至少须提交一个更新，以标记该日志。BEGIN TRAN MyMark WITH MARKUpdate pubs.dbo.LastLogMark SET MarkTime = GETDATE()COMMIT TRAN MyMark按照您常用的方法备份事务日志。BACKUP LOG pubs TO DISK=C:BackupsFullbackup.bak WITH INIT现在您可以将数据库恢复至日志标记点。首先恢复数据库，并使其为接受日志恢复做好准备。RESTORE DATABASE pubs FROM DISK=NC:BackupsFullbackup.bak WITH NORECOVERY现在将日志恢复至包含该标记的时间点，并使其可供使用。请注意，STOPAT在数据库正在执行大容量日志时禁止执行。RESTORE LOG pubs FROM DISK=NC:BackupsLogbackup.bak WITH RECOVERY,STOPAT=02/11/2002 17:35:00Microsoft SQL Server 开发团队-打开UDP端口1434以浏览命名实例更新日期: 2003年5月12日 本技巧摘自SQL Server杂志的一期。如需了解更多的技巧，请访问SQL Server的使用技巧中心。问：本地的一个ISP托管了我们的SQL Server，但是我不能看到或连接到运行在该计算机上的命名实例。我知道SQL Server 2000命名实例不使用TCP/IP 1433端口，并且确定该SQL Server实例所使用的TCP/IP端口是打开的。您有什么建议吗？答：许多人知道如何使用SQL Server网络实用工具或SQL Server错误日志来确定SQL Server实例的监听端口。但许多人似乎忘记或者根本不知道UDP 1434端口也必须是打开的，以确保您能够正确地访问和浏览命名实例。我建议您仔细研究一下这一要求。在SQL Server在线书籍中搜索“1434”，您将得到三个颇有研究价值的搜索结果。在SQL Server杂志网站2001年7月刊我的SQL Server实用技巧专栏中，您还可以查找到有关访问命名实例的信息（InstantDoc ID 21127）。在此我也简单解释一下。SQL Server在UDP 1434端口上建立一个监听服务，让客户端从服务器上查询到命名实例及其网络配置信息的一个列表。该监听服务始终运行在UDP 1434端口上，并且用户无法更改该运行端口。Brian Moran-如何使用系统管理员帐户管理多个开发人员？ 更新日期: 2002年12月16日 本技巧摘自SQL Server杂志的一期。如需了解更多的技巧，请访问SQL Server的使用技巧中心。问：在我们的开发环境下，每个人都有系统管理员（sa）密码，5个组使用一个数据库。在某个开发人员不小心删除了数据后，我没有办法确定谁删除哪一块数据。当开发人员更新数据库中的数据时，如何审核工作站或登录ID？答：因为您工作于开发环境下，使用SQL事件探查器和服务器跟踪所带来的性能损失并不会影响生产环境，所以您可以设置一个服务器跟踪，在后台将信息记录在一个表中。然后，您可以搜索该表以确定谁删除了数据。显然，一次为多个用户分配sa账户会带来许多问题。以下是您应该采取的一些措施：为每个开发团队创建该数据库的一份拷贝，为每个团队提供一个仅数据库拥有者才有访问权的账户。这样的话，他们就不会互相影响了。 如果每个开发人员都需要sa访问权（例如运行SQL事件探查器跟踪），并且您运行的是SQL Server 2000 Enterprise或Developer Edition，您可以为每个团队提供SQL Server的一个实例及其专用的sa密码。 考虑为每个项目设置一个开发环境，并以一个集成环境的方式由一个版本数据库管理员对所有项目间冲突进行管理。这样的话，因为架构改变脚本在进入集成环境之前已经经过了测试，生产版本的质量将有所改善。 The Microsoft SQL Server开发团队-防止锁定升级 更新日期: 2001年9月17日 本技巧摘自SQL Server杂志的一期。如需了解更多的技巧，请访问SQL Server的使用技巧中心。问：如何防止SQL Server将锁升级？答：SQL Server使用锁来保持事务在数据库内部的完整性和一致性。SQL Server支持具有不同粒度的多种锁类型。例如，行锁是一个高级锁，而页锁是一个低级锁。SQL Server自动将大量的高级锁升级为少量的低级锁，从而降低系统开销，但同时也可能导致应用程序内不可预测的阻塞。升级在锁的数量超过锁升级极限时自动进行。这一极限是一个内部设置，设计用于保护系统资源。该极限值不能由用户设置，但在以跟踪标记1211（无文档记载）启动SQL Server时，该锁升级将被禁用。请注意，如果禁用了锁升级，管理系统的职责将由您承担。如果SQL Server内存不足，系统可能会崩溃。一个更好的解决方案为，如果肯定需要行级锁，可以执行较短的事务。-SQL Server MVPs -使用SQL Server导入和索引 Microsoft Word 文档 更新日期: 2003年7月25日 本技巧摘自SQL Server杂志的一期。如需了解更多的技巧，请访问SQL Server的使用技巧中心。问：我需要将Microsoft Word文档导入至SQL Server并索引这些文档，以便在关系查询中使用这些文档。怎样导入和索引文档呢？答：SQL Server允许您以多种方式导入Word文档。让我们看看几种最常用的方法。请注意，在将文档导入至SQL Server之前，您需要创建一个image数据类型列，用于存放数据。然后，您可以使用textcopy.exe命令行工具将image文件读入数据库，从而完成文档的导入操作。如果需要该工具的基本说明文档，请在命令提示状态下键入textcopy /?。将Word文档导入SQL Server的另一个方法是使用Microsoft ActiveX Directory Object（ADO）Stream接口编写导入代码。您可以在Microsoft产品支持服务（PSS）的通过使用ADO Stream对象访问和修改SQL Server BLOB数据一文中找到示例代码。此外，您也可以将二进制数据移到SQL Server中。有关这一方法的详细说明，请参阅PSS的使用ADO检索和更新SQL Server文本域一文。移动二进制数据允许您将数据的一部分存放在数据库中，这在需要控制数据格式时尤其有用。例如，如果只需要数据中1,000到1,010之间的字节，导入二进制数据的速度远远高于使用ADO Stream接口的速度，这是由于SQL Server从磁盘上检索数据的量大为减少。人们通常使用这一技术来存储位掩码，用于表示应用程序的开或关标志位。SQL Server 2000自带了说明了如何移动二进制数据的示例代码。如需查看该代码，只需在安装有SQL Server 2000光盘上代码示例的驱动器上选择 Program FilesMicrosoft SQL Server80ToolsDevToolsSamplesado路径。展开该可执行文件，在Visual Basic目录下查找Samples子目录。在Employee示例下，注意一下该代码是如何使用FillDataFields()函数的。如需索引Word文档，SQL Server 7.0和SQL Server 2000都提供了全文搜索组件。该组件混合使用了多种技术，用以索引大型文本和image列。在执行全文搜索时，您需要指定image列所含的文件类型，以及从二进制数据中析取信息所需的筛选（filter）。有关使用全文索引的详细信息，请参阅SQL Server在线书籍中的相关主题，并阅读David Jones 2000年7月发表在SQL Server杂志网站上的名为构建更好的搜索引擎一文。请注意，索引Word文档并不会自动生成包含文档中关键字的一组关系表。但是，索引文件让您将这些Word文档包含在您的搜索中。以下是从数据中析取关键字的可行方式：使用OLE自动处理从文档中读取用户定义的关键字。在装载该文档时将这些关键字保存在关系表中。 使用OLE自动处理打开文档并将其保存为文本（.txt）格式。为了析取重要的词语，您可以使用自己的“词语分析器”遍历该文本文件。文字分析器查看文档中的每个词语，去除一些无用文字，并将每个唯一的词语与其出现的次数一道存储起来。 在新的全文索引文档中搜索特定的关键字，并将这些关键字输入到关系表中。 SQL Server 2000提供了多个强大的工具和接口，用于帮助用户实现二进制Microsoft Office文档的快速装载、搜索和检索。Ghost记录清理和602错误问：最近，在使用SQL事件探查器监视我的SQL Server数据库时，我定期收到“Error: 602, Severity: 21, State: 13”信息。在SQL Server企业管理器的进程信息窗口中，我找到了一个名为“Ghost Record Cleanup”（幻影记录清除）的后台进程，并且该命令由用户系统所引发。我使用DBCC CHECKDB检查我的数据库，但是没有发现任何异常。请问什么是Ghost Record Cleanup进程？什么是602错误消息？答：在从数据库中删除行、页或扩展盘区时，SQL Server会将这些对象标记为“幻影”（表示删除操作有待执行），并在稍后使用后台任务清除这些对象，该进程就是Ghost Record Cleanup。Ghost Record Cleanup改善了Delete命令的性能，因为SQL Server无需立即执行物理清除操作。如需了解出现602错误的原因，您可以在SQL Server错误日志中查找提供ObjectID的一行信息。然后，您可以使用该ObjectID跟踪到导致该错误的数据库或对象。如果该对象仍然存在（例如，它不是诸如临时表的临时对象），您可以对其运行DBCC CHECKTABLE，并通过查询sysindexes验证该对象并未受损。如果对象未受损，则SQL Server错误地发出了602错误消息。多数情况下，SQL Server发布了错误的消息。如需了解602错误所代表的意思，请访问Microsoft产品支持服务网站并搜索“错误消息”和602。搜索将返回一系列说明如何解决602错误及其含义的文章。SQL Server 开发团队 -使用扩展属性创建数据字典工具 问：我正在寻找可以帮助我维护一个SQL Server数据字典的共享软件或低成本工具。我使用过具备该功能的高级数据建模工具。但是，我现在的公司有许多SQL Server数据库，但是没有数据字典，并且也没有购买价格不菲的工具的打算。您知道有哪些给“穷人”用的数据字典工具吗？答：尽管可能有免费或低成本的共享软件工具，但我对它们不是很熟悉。我建议您尝试使用SQL Server 2000的扩展属性。在SQL Server 2000中，Microsoft添加了扩展属性，用于帮助用户在多个数据库对象上定义和操作用户定义的属性。您可以使用这些用户定义的属性向您的数据库添加元数据，并使用该数据库创建一个直接集成在SQL Server中的“土制”数据字典。您可以使用系统存储过程sp_addextendedproperty、sp_updateextendedproperty及sp_dropextendedproperty来管理这些属性。此外，您可以使用系统函数fn_listextendedproperty()检索现有的属性值。Microsoft使用扩展属性来写入和管理描述值，它与SQL Server企业管理器表设计视图中的列相关联。请注意，使用这些存储过程和fn_listextendedproperty()函数创建和管理扩展属性并不是一件轻而易举的事。SQL Server在线书籍中名为“属性管理”的主题提供了有关扩展属性的基本信息。幸运的是，SQL Server杂志上有几篇非常不错的相互文章。有关使用扩展属性的详细信息，请参阅Michelle A. Poolet的“一个商业元数据知识库”，William Vaughn的“管理扩展属性”，以及“技巧：管理基于SQL Server 2000扩展属性的数据字典”。这三篇文章都解释了如何使用扩展属性，并且每篇文章都提供了一个解释器，供您编写自己的“穷人”数据字典之用。SQL Server MVP Brian Moran -确保所有的非空（Non-NULL）值都是唯一的 问：我的SQL Server表的一列允许NULL值。我希望在其值为非NULL时，该列是唯一的。怎样才能以编程的方式实现这一行为？如果在该列上设置一个UNIQUE 约束，我只能包含一个值为NULL的记录。我正在使用触发器实现这一约束，您可以推荐一个更简单的方法以保证所有的非NULL值唯一吗？答：SQL Server没有实现非NULL值唯一性的内建机制，因此您需要通过自定义的CHECK约束来实现这一机制。例如，以下一段编码实现了您所需要的功能：USE tempdb Create table t1 (c1 int NULL, c2 char(5) NULL) Create trigger mytrigger on t1 for insert, update as BEGIN IF (select max(cnt) from (select count(i.c1)as cnt from t1, inserted i where t1.c1=i.c1 group by i.c1) x) 1 ROLLBACK TRAN END 在SQL Server 2000中，您还可以使用INSTEAD OF触发器来实现这一功能。有关INSTEAD OF触发器的详细信息，请参阅以下文章。如需访问这些文章，请访问SQL Server 杂志网站，在InstantDoc框中输入InstantDoc号，然后点击Go。相关文章如下：Tricks with INSTEAD OF Triggers; InstantDoc number 15828 INSTEAD OF Triggers on Views; InstantDoc number 15791 INSTEAD OF Triggers; InstantDoc number 15524 使用视图控制用户对数据访问 问：我的Microsoft Access 2000应用程序由后端的SQL Server 2000数据库写入数据。为防止Access的用户看到SQL Server 2000表中的全部数据，我想使用一种只允许用户浏览授权数据行的视图。可以创建一种限制用户访问SQL Server数据的视图吗？答：可以。如果每位用户以唯一的用户ID登录到Access，您就可以创建一种限制用户访问SQL Server数据的视图。以下的示例语句就可以创建这样一种视图：Create VIEW v_data ASSelect FROM dbo.mytable AS aINNER JOIN dbo.authtable AS bON (a.Pkey = b.DataKey AND b.userid = suser_sname() 该视图按userid限制用户的访问权。它要求您保存一份与数据表（mytable）中特定主键相匹配的用户名的表（authtable）。如果您的情况相对比较简单您无需管理多个用户的行访问权，则您可以将userid列插入到数据表中，如下列代码所示：Create VIEW v_data ASSelect FROM dbo.mytable AS aWhere a.userid = suser_sname() Microsoft SQL Server 开发团队-检查端口号 问：我创建了一个使用TCP/IP网络图书馆的服务器别名。客户端服务器别名被配置为动态确定所使用的端口。怎样才能找出客户所使用的端口以便我设置自己的防火墙？答：客户端用于联络SQL Server实例的端口与该实例用于监听客户端请求实例的端口是一样的。您有几种方法来确定正被使用的端口号。请记住，当服务器首次被安装时，SQL Server 动态地选择端口。以后每次服务器暂停工作和重新启动时使用的端口号都是一样的。动态端口分配是一次性的事件。要查看正被SQL Server实例使用的端口号，可以选择以下的任何一种方法：打开Server网络实用工具，点击启用的协议列表中TCP/IP条目的属性。查看特定实例的错误日志。您会在错误日志中看到与下面相似的条目：SQL Server listening on : 1362.冒号后的四位数是SQL Server正用于监听IP地址的端口，冒号左边是详细的信息。查看注册表。根据您安装和升级SQL Server的方式，具体的注册表子键会有所不同，但您会看到和下面相似的键： HKEY_LOCAL_MACHINESOFTWAREMicrosoftInstanceNameMSSQLServerSuperSocketNetLibTcpInstanceName是您正在使用的SQL Server实例的名称（对于默认实例，InstanceName为MSSQLServer）。该键有一个名为TcpPort的条目，其中包含了正在被使用的端口号。 您需要这个端口号来设置客户端和服务器之间的防火墙。客户端向SQL Server的UDP 1434端口发送特殊的请求，以确定使用哪一个端口。SQL Server返回可用的服务器实例的列表，以及每个实例正在使用的端口。如果屏蔽了UDP 1434端口，则通过防火墙与SQL Server实例的连接将无法实现。 -如何识别真实和自动创建的索引？ 问：我发现sys