数据库第一次纸质作业答案.docx

1、数据库第一次纸质作业答案作业(一)答案：1.按要求完成下列SQL语句答案：（1）建立数据库代码：USE masterGO/* 对象: Database 1140120125DB 脚本日期: 03/25/2012 16:18:40 */CREATE DATABASE 1140120125DB ON PRIMARY ( NAME = N1140120125DB, FILENAME = ND:数据库1140120125DB.mdf , SIZE = 3072KB , MAXSIZE = 102400KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N1140120

2、125DB_log, FILENAME = ND:数据库1140120125DB_log.ldf , SIZE = 1024KB , MAXSIZE = 81920KB , FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_ASGOEXEC dbo.sp_dbcmptlevel dbname=N1140120125DB, new_cmptlevel=90GOIF (1 = FULLTEXTSERVICEPROPERTY(IsFullTextInstalled)beginEXEC 1140120125DB.dbo.sp_fulltext_database acti

3、on = disableendGOALTER DATABASE 1140120125DB SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE 1140120125DB SET ANSI_NULLS OFF GOALTER DATABASE 1140120125DB SET ANSI_PADDING OFF GOALTER DATABASE 1140120125DB SET ANSI_WARNINGS OFF GOALTER DATABASE 1140120125DB SET ARITHABORT OFF GOALTER DATABASE 1140120125D

4、B SET AUTO_CLOSE OFF GOALTER DATABASE 1140120125DB SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE 1140120125DB SET AUTO_SHRINK OFF GOALTER DATABASE 1140120125DB SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE 1140120125DB SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE 1140120125DB SET CURSOR_DEFAULT

5、 GLOBAL GOALTER DATABASE 1140120125DB SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE 1140120125DB SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE 1140120125DB SET QUOTED_IDENTIFIER OFF GOALTER DATABASE 1140120125DB SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE 1140120125DB SET ENABLE_BROKER GOALTER DATA

6、BASE 1140120125DB SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE 1140120125DB SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE 1140120125DB SET TRUSTWORTHY OFF GOALTER DATABASE 1140120125DB SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE 1140120125DB SET PARAMETERIZATION SIMPLE GOALTE

7、R DATABASE 1140120125DB SET READ_WRITE GOALTER DATABASE 1140120125DB SET RECOVERY FULL GOALTER DATABASE 1140120125DB SET MULTI_USER GOALTER DATABASE 1140120125DB SET PAGE_VERIFY CHECKSUM GOALTER DATABASE 1140120125DB SET DB_CHAINING OFFOrders表代码：USE 1140120125DBGO/* 对象: Table dbo.Orders 脚本日期: 03/25/

8、2012 16:21:33 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.Orders( customerid char(10) COLLATE Chinese_PRC_CI_AS NOT NULL, order_date datetime NULL, item char(10) COLLATE Chinese_PRC_CI_AS NULL, quantity tinyint NULL, amount money NULL) ON PRIMARYGOSET ANSI_PA

9、DDING OFFCustomer表代码：USE 1140120125DBGO/* 对象: Table dbo.Customers 脚本日期: 03/25/2012 16:23:47 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.Customers( customerid char(10) COLLATE Chinese_PRC_CI_AS NOT NULL, firstname char(10) COLLATE Chinese_PRC_CI_AS NULL, lastn

10、ame char(10) COLLATE Chinese_PRC_CI_AS NULL, city char(10) COLLATE Chinese_PRC_CI_AS NULL, state char(20) COLLATE Chinese_PRC_CI_AS NULL) ON PRIMARYGOSET ANSI_PADDING OFF（2）代码：select customerid, item, amount from Orderswhere customerid = 10459（3）代码：select * from Orderswhere item = pencil（4）代码：select

11、 customerid, order_date, item from Orderswhere item like w%（5）代码：select count(distinct item) from orders（6）代码：select max(amount) from orders（7）代码：select avg(amount) as 平均值from orderswhere order_date like %9%（8）代码：select count(quantity) from orders（9）代码：select min(amount) from orders（10）代码：select sta

12、te 所在州, count(customerid) 顾客人数 from Customersgroup by state（11）代码：select item, max(amount) as 最大金额, min(amount) as 最小金额from ordersgroup by item（12）代码：select quantity, count( amount) as 总金额from ordersgroup by quantity（13）代码：select distinct item 商品, sum(amount)/sum(quantity) 单价from ordersgroup by item

13、（14）代码：select order_date, item, amount from orderswhere amount between 100 and 800（15）代码：select firstname, city, state from Customerswhere state in (Arizona, Washington, Oklahoma,Colorado) or state = Hawaii（16）代码：select state, count(customerid) 顾客数,state 所在州from customersgroup by state having count(

14、customerid) 1（17）代码：select item,max(amount) 最大金额, min(amount) 最小金额from ordersgroup by itemhaving max(amount) 190（18）代码：select customerid 顾客编号, count(customerid) 购物次数, sum(amount) 总金额from ordersgroup by customeridhaving count(customerid) 12.简答题答案：（1）：数据库管理员，数据库应用开发人员，数据库商业智能开发人员（2）：1.SQL SERVER实例：实际上

15、就是一个服务，在管理工具的服务里面能看到。2. 在电脑E103上安装默认实例，实例名称是E103，跟机器名相同。3. 如果再安装一个命名实例GZ，那么实例名称是GZ，跟机器名相同。（3）：1.master系统数据库；作用：用于记录SQL Server系统中所有系统级信息。如果该数据库损坏，则SQL Serve将无法正常工作。2.msdb系统数据库；作用：供SQL Serve代理服务调度报警和作业以及记录操作员时使用，保存关于调度报警，作业，操作员等信息，作业是在SQL Serve中定义的自动执行的一系列操作集合，作业的执行不需要任何人干预。3.model系统数据库；作用：是在SQL Serve

16、中创建用户数据库的模板，其中包含所有用户数据库的共享信息。当用户创建一个数据库时，系统自动将model数据库中得全部内容复制到新建数据库中。4.tempdb系统数据库；作用：是临时数据库，用于储存用户创建的临时表，用户声明的变量以及用户低昂一的游标数据等，并为数据的排序等操作提供一个临时工作空间。（4）：文件组是数据文件的逻辑集合，它使管理员能够将文件组中所有文件作为单独的一项进行管理。文件组可以控制数据库中各个对象的物理布局，这可提供大量可管理性和性能方面的好处。例如：可使用多个文件组，对数据库中数据的存储设备中得物理存储方式进行控制，并将读写数据与只读数据进行分离管理，这可以显著提高读写数

17、据的性能。SQL Serve 2005 有一个主文件组，另外还可以有多个用户定义的文件组，即为文件组。（5）：SQL Serve 2005中数据库由数据文件和日志文件组成，分为主数据文件，通常使用.mdf扩展名，辅助数据文件，通常使用.ndf为扩展名。其中数据文件可以通过文件来组织，而日志文件不适用文件组。SQL Serve 2005数据库包括如下数据库：master, tempdb, msdb, model是系统数据库，还有一个可选的示例数据库Adventureworks。3.解答题答案：（1）第一种思路代码：USE masterGO/* 对象: Database 1140120125_DB

18、 脚本日期: 03/27/2012 11:06:18 */CREATE DATABASE 1140120125_DB ON PRIMARY ( NAME = NDB_data1, FILENAME = ND:数据库DB_data1.mdf , SIZE = 51200KB , MAXSIZE = 409600KB , FILEGROWTH = 20%), FILEGROUP secondary ( NAME = NDB_data2, FILENAME = ND:数据库DB_data2 , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0)

19、 LOG ON ( NAME = NDB_log, FILENAME = ND:数据库DB_log.ldf , SIZE = 6144KB , MAXSIZE = 2048GB , FILEGROWTH = 0) COLLATE Chinese_PRC_CI_ASGOALTER DATABASE 1140120125_DB ADD FILEGROUP secondary1GOEXEC dbo.sp_dbcmptlevel dbname=N1140120125_DB, new_cmptlevel=90GOIF (1 = FULLTEXTSERVICEPROPERTY(IsFullTextInst

20、alled)beginEXEC 1140120125_DB.dbo.sp_fulltext_database action = disableendGOALTER DATABASE 1140120125_DB SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE 1140120125_DB SET ANSI_NULLS OFF GOALTER DATABASE 1140120125_DB SET ANSI_PADDING OFF GOALTER DATABASE 1140120125_DB SET ANSI_WARNINGS OFF GOALTER DATABA

21、SE 1140120125_DB SET ARITHABORT OFF GOALTER DATABASE 1140120125_DB SET AUTO_CLOSE OFF GOALTER DATABASE 1140120125_DB SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE 1140120125_DB SET AUTO_SHRINK OFF GOALTER DATABASE 1140120125_DB SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE 1140120125_DB SET CURSOR

22、_CLOSE_ON_COMMIT OFF GOALTER DATABASE 1140120125_DB SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE 1140120125_DB SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE 1140120125_DB SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE 1140120125_DB SET QUOTED_IDENTIFIER OFF GOALTER DATABASE 1140120125_DB SET RECURSIVE

23、_TRIGGERS OFF GOALTER DATABASE 1140120125_DB SET ENABLE_BROKER GOALTER DATABASE 1140120125_DB SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE 1140120125_DB SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE 1140120125_DB SET TRUSTWORTHY OFF GOALTER DATABASE 1140120125_DB SET ALLOW_SNAPSHOT

24、_ISOLATION OFF GOALTER DATABASE 1140120125_DB SET PARAMETERIZATION SIMPLE GOALTER DATABASE 1140120125_DB SET READ_WRITE GOALTER DATABASE 1140120125_DB SET RECOVERY FULL GOALTER DATABASE 1140120125_DB SET MULTI_USER GOALTER DATABASE 1140120125_DB SET PAGE_VERIFY CHECKSUM GOALTER DATABASE 1140120125_D

25、B SET DB_CHAINING OFF（2）第二种思路代码：USE masterGO/* 对象: Database 1140120125DB1 脚本日期: 03/27/2012 12:18:10 */CREATE DATABASE 1140120125DB1 ON PRIMARY ( NAME = NDB1_data1, FILENAME = ND:数据库DB1_data1.mdf , SIZE = 51200KB , MAXSIZE = 409600KB , FILEGROWTH = 20%), FILEGROUP secondary ( NAME = NDB1_data2, FILEN

26、AME = ND:数据库DB1_data2.ndf , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0) LOG ON ( NAME = NDB1_log, FILENAME = ND:数据库DB1_log.ldf , SIZE = 6144KB , MAXSIZE = 2048GB , FILEGROWTH = 0) COLLATE Chinese_PRC_CI_ASGOEXEC dbo.sp_dbcmptlevel dbname=N1140120125DB1, new_cmptlevel=90GOIF (1 = FULLTEXTSE

27、RVICEPROPERTY(IsFullTextInstalled)beginEXEC 1140120125DB1.dbo.sp_fulltext_database action = enableendGOALTER DATABASE 1140120125DB1 SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE 1140120125DB1 SET ANSI_NULLS OFF GOALTER DATABASE 1140120125DB1 SET ANSI_PADDING OFF GOALTER DATABASE 1140120125DB1 SET ANSI_

28、WARNINGS OFF GOALTER DATABASE 1140120125DB1 SET ARITHABORT OFF GOALTER DATABASE 1140120125DB1 SET AUTO_CLOSE OFF GOALTER DATABASE 1140120125DB1 SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE 1140120125DB1 SET AUTO_SHRINK OFF GOALTER DATABASE 1140120125DB1 SET AUTO_UPDATE_STATISTICS ON GOALTER DATABA

29、SE 1140120125DB1 SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE 1140120125DB1 SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE 1140120125DB1 SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE 1140120125DB1 SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE 1140120125DB1 SET QUOTED_IDENTIFIER OFF GOALTER DATABASE 1140120125DB1 SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE 1140120125DB1 SET ENABLE_BROKER GOALTER DATABASE 1140120125DB1 SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE 1140120125DB1 SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE 1140120125DB1 SET TRUSTWORTHY OFF GO