| 導購 | 订阅 | 在线投稿
分享
 
 
 

微軟認證最新考題70-029 SQL7.0實現

來源:互聯網  2006-12-17 09:47:41  評論

微軟認證最新考題70-029 SQL7.0實現

微軟認證最新考題70-029 SQL7.0實現 70-029 SQL7.0實現

1). Users report slow response times when they are modify data in your application. Response times are excellent when users are merely retrieving data. The search criteria used for modifying data are the same as the search criteria for retrieving data.

All transactions are short and follow standard guidelines for coding transactions. You monitor blocking locks, it』s ok.

What is the most likely cause?

A. The transaction log is placed on an otherwise busy disk drive.

B. The transaction log is nearly full

C. The checkpoint process is set too short

D. The tempdb database is too small

E. The tempdb database is on the same physical disk drive as the

database.

Answer: A

2). Database includes a table named experiments that is defined as follows:

CREATE TABLE experiments { experimentid char(32),

description text,

status integer,

results text}

You write the following:

SELECT* from experiments where contains(description, 'angina')

you are certain that there are matching rows, but you receive an empty result set when you run the query.

what should you do?(choose two)

A. ensure that there is a nonunique index on the description column of the experiments table.

B. ensure that there is a clustered index on the results column

experiments table.

C. create a full-text catalog that includes the experiments table.

D. create a scheduled job to populate the full-text catalog.

Answer: C,D

3). Database includes a job_cost table that typically holds 100000 rows but can grow or shrink by as much as 75000 rows at a time. the job_cost table is maintained by a batch job that runs at night.

during the day, the job_cost table is frequently joined to other tables by many different queries. Users report that their initial queries are very slow, but then response time improves for subsequent queries.

How should you improve the response time of the initial queries?

A. run the sp_updatestats stored procedure as part of the nightly batch

job.

B. run the sp_createstats stored procedure as part of the nightly batch

job.

C. set the auto create statistics database option to true.

D. set the auto update statistics database option to true.

Answer: A

4).you are the DBA. You receive reports that your sales application has very poor response times.

The database includes a table that is defined as follow:

create table dbo.orders {

OrderID int identity(1,1) NOT NULL,

SalespersonID int NOT NULL,

RegionID int NOT NULL,

Orderdate datetime NOT NULL,

Orderamount int NOT NULL,

CustomerID int NULL}

the orderid column is the primary key of the table. there are also indexes on the regionid and orderamount columns.

you decide to run a showplan on all queries in the application. The following query, which accesses this table is used to list total average sales by region:

select t1.regionid,avg(t1.salestotal) as regionaverage

from (select regionid,salespersonid,sum(orderamount) as

salestotal

from orders

group by regionid,salespersonid as t1

group by t1.regionid

you set the SHOWPLAN_TEXT option to ON execute the query.the

showplan output is as follows:

...

you suspect that this query is part of the problem because the showplan indicates that the query is performing a table scan operation.

what is the most likely reason that this query is performing a table scan?

A. there is no composite index on OrderID,RegionID,and Orderamount.

B. there is no where clause in the query.

C. the query contains a subquery.

D. the query is performing aggregate operations.

ANSWER: B

5). Users can create and submit their own ad hoc queries against any of The tables in DSS database. You users report that the responses time for some queries are too long. Response times for other queries are acceptable.

What should you use to identify long-running queries?

A. SQL Server Enterprise Manager

B. SQL Server Profiler

C SQL Server Analyzer

D. Microsoft Windows NT Performance Monitor.

ANSWER: B

6). You have an application that makes four connections to the SQL Server at the same time, The connections are used to Execute SELECT, INSERT, UPDATE, and DELETE statements.

The application occasionally stops responding when a user is trying to update or Delete rows, and then the user must close the application. The problem occurs When a user attempts to execute an UPDATE or DELETE statement after Submitting a SELECT statement that retrieves a result of more than 10,000 Rows

What can you do?

A. On the connection for the SELECT statement, set the deadlock priority

to low.

B. On the connections for the UPDATE and DELETE statement, set the

deadlock priority to low.

C. On the connection for the SELECT, set the transaction isolation

level to READ UNCOMMITTED.

D. Set the query wait configuration option of the SQL Server 5,000.

Answer: C

7). The policy table will be accessed and updated by several additional applications, in the policy table, you need to ensure that the value entered into the beginning_effective_date column is less than or equal to the value entered into The ending_effective_date column.

What should you do?

A. Program each application to compare the values before updating the

policy table.

B. Create a CHECK constraint on the policy table that compares the values.

C. Create a rule and bind the rule to the Beginning_effective_date

column.

D. Create INSERT and UPDATE triggers on the policy table that compare

the values.

ANSWER:B

8). The demographic table in the application contains more than 1,000 columns

Most of the reports relate to long response times when users are updating or retrieving data from the demographic table. Nearly 90 percent of the users search or update 20 of the columns .The remaining columns are seldom used, but there are important.

What should you do to?

A. Create a clustered index on the demographic table over the most

accessed columns.

B. Create a view based on the demographic table, which selects the

20 most accessed columns.

C. Divide the data in the demographic table into two new tables ,with

one table Containing 20 most accessed columns and the other

containing the remaining columns.

D. Create a series of stored procedures that select or update the

demographic table according to user needs.

Answer: C

9). You are implementing a transaction-based application for a Credit card company. More than 10 million vendors accept the Company』s credit card, and more than 100 million people Regularly use the credit card.

Vendors around the world must be able to authorize purchases in less than 30 Seconds, 24 hours a day, seven days a week, Additionally, the application must be able to accommodate more vendors in more locations in the future.

What should you do?

A. .. .

B. .. .

C. .. .

D. Implement an n-tier architecture in which vendor make calls to the geographically dispersed Microsoft Transaction Servers(MTS),which would then obtain an authorization code from geographically dispersed SQL Servers.

Answer: D

10). Two SQL Servers supporting two separate applications on your network. Each application uses stored procedures for all data manipulation. You need to integrate parts of the two applications. The changes are limited to a few stored procedures that need to make calls to remote stored procedures.

What should you do to implement calls to remote stored procedures?

A. Add the remote server as a linked server, Fully qualify the remote

procedures names.

B. Program each application to connect to both servers. Use the

sp_bindsession stored procedures to bind both connections before

running any stored procedures that start a transaction.

C. Program each application to issue the BEGIN DISTRIBUTED TRANSACTION

Statement before each transaction .

D. Configure the SQL Server by setting the remote proc trans option

to 1.

Answer: A

11). You need to produce a list of the five Highest revenue transactions from the Orders table in the Sales database.

The Orders table is defined as follows:

CREATE TABLE Orders{

Order ID int IDENTITY(1,1) NOT NULL,

SalePersonID int NOT NULL,

RegionID int NOT NULL,

Orderdate datetime NOT NULL,

OrderAmount int NOT NULL

}

Which statement will produce the report?

A. SELECT TOP 5 OrderAmount, SalePersonID FROM Orders ORDER BY

OrderAmount DESC.

B. SELECT TOP 5 OrderAmount, SalePersonID FROM Orders.

C. SELECT TOP 5 with TIES OrderAmount, SalePersonID FROM Orders

ORDER BY OrderAmount.

D. SELECT TOP 5 with TIES OrderAmount, SalePersonID FROM Orders

ORDER BY Order ID.

Answer: A

12).Your Sales database is accessed by a Micorosoft Visual Basic Client/Server application. The application is not using the Microsoft Windows NT Authentication security model.

You write the following stored procedure to be called by the Visual Basic application:

CREATE PROCEDURE InsertReinstatedOrder

...

...

A User named Andrew assigned to the Sales role. Andrew reports that he is receiving an error message indicating that he is having a permissions problem with the procedure.

What must you do to solve the problem?

A. Grant permission on the stored procedure specifically to Andrew.

B. Add Andrew to the Windows NT Sales group.

C. Add Andrew to the Windows NT Administrator group.

D. Add Andrew to the db_owner role.

Answer: D

13).You are designing a data model to track research projects.

You want to accomplish the following goals:

...

You design the logical model as shown in the exhibit(Click the exhibit

Button)

Institution

InstitutionID(PK)

InstitutionID1

Job

JobName(PK)

Project

ProjectID(PK)

Scientist

ScientistID(PK)

InstitutionID

JobName

ProjectDetail

ProjectID(PK)

InstitutionID(PK)

Which result or results does this model produce?(choose all that apply)

A. All the scientists conducting research for any specific project

can be reported.

B. A scientist』s job for a specific project in a specific institution

can be reported.

C. All the institution participating in any specific project can be

reported.

D. The institution at which a scientist is a staff member

can be tracked.

E. An institution can be identified as part of another

institution.

Answer: C,D,E

14)You work for a licensing agency that buys photographs and then sells

them to other companies for commercial use...

You want to accomplish the following goals:

...

You take the following actions:

...

Which result or results do these actions produce?(choose all that apply)

A. An individual can be identified as a customer, a supplier or an

employee, or any combination of the three.

B. There is no data redundancy.

c. No individual can have the same identification number as an

organization.

D. An individual can be associated with more than one organization

E. An individual can be associated with more than one individual.

Answer: B,D,E

15).You are designing an inventory database application for a national

automobile sales registry.

...

You want to be able to track information about each automobile.

You want to normalize your database.

...

Which tables should be included in the database application(choose all that apply)

A. a table containing the list of all dealerships along with the address

and identification number for each dealership.

B. a table containing the contact information for each automobile manufacturer along with the name of each model manufactured by each

manufacturer.

C. a table containing the name and address of each dealership along

with automobile information

D. a table containing identification number for each automobile,

the owning dealership』s identification number, and other information specific to each automobile

Answer: A,D

16).You are designing the data model to maintain information about

students living in a group home.

You want to accomplish the following goals:

...

You design the logical model as shown in the exhibit(click the exhibit)

Student

StudentID(PK)

StudentAlias

StudentID(PK)

Alias(PK)

StudentEvent

StudentID(PK)

EventName(PK)

FamilyRelationship

StudentID1(PK)

StudentID2(PK)

Address

AddressID(PK)

StudentID

Which result or results do this model produce?(choose all that apply)

A. Any kind of descriptor can be associated with a student.

A. Multiple addresses can be associated with multiple students, and

that address usage can be reported.

C. Any family relationship with another student can be reported.

D. All known aliases for a student can be reported.

E. Significant events in a student』s life can be reported.

Answer: C,D,E

17).You are creating a table named recruit to track the status of

potential new employees. The SocialSecurityNo column must not

allow null values. However, a value for a potential employee』s

Social Security number is not always known at the time of initial

Entry.

You want the database to populate the SocialSecurityNo column with a value of UNKNOWN when a recruiter enters the name of a new potential

Employee without a Social Security number.

How can you accomplish this task?

A. Create a rule on the SocialSecurityNo column.

B. Create a default definition on the SocialSecurityNo column.

C. Create a CHECK contraint on the SocialSecurityNo column.

D. Create a user-defined data type. apply it to the SocialSecurityNo

column, and bind a rule to the user-defined data type.

Answer: B

18).You are developing a Personnel database for your company.

This database includes an employee table that is defined as

Follows:

CREATE TABLE employee{

ID int IDENTITY NOT NULL,

Surname varchar(50) NOT NULL,

FirstName varchar(50) NOT NULL,

SocialSecurityNo char(10) NOT NULL,

Extention char(4) NOT NULL,

EmailAddress varchar(255) NOT NULL}

Each employee must have a unique telephone extension number and a unique e-mail address. In addition, you must prevent duplicate Social Security numbers from being entered into the database

How can you alter the table to meet all of the requirements?

A. ALTER TABLE employee

ADD CONSTRAINT u_nodups UNIQUE NONCLUSTERED (SocialSecurityNo,

Extention, EmailAddress).

B. ALTER TABLE employee

ADD CONSTRAINT u_nodups UNIQUE CLUSTERED (SocialSecurityNo,

Extention, EmailAddress).

C. ALTER TABLE employee

ADD CONSTRAINT u_nodupssn UNIQUE NONCLUSTERED (SocialSecurityNo ).

ALTER TABLE employee

ADD CONSTRAINT u_nodupext UNIQUE NONCLUSTERED (Extension)

ALTER TABLE employee

ADD CONSTRAINT u_nodupemail UNIQUE NONCLUSTERED (EmailAddress).

D. Alter table employee

Add constraint u_nodupssn check...

Answer: C

19).Your database is used to store information about each employee

department. An employee can work in only one department.

The database contains two tables, which are named Department and

Employee. The tables are modeled as shown in the exhibit(click the exhibit)

Department

DepartmentNo(PK)

DepartmentName

Employee

EmployeeID(PK)

FirstName

LastName

SocialsecurityNo

DepartmentNo

DepartmentName

Phone

You want to ensure that all data stored is dependent on the whole key

Of the table in which data is stored. What should you do ?

A. Add an EmployeeID column to the Department table.

B. Remove the SocialSecurityNo column from the Employee table.

C. Remove the Departmentname column from the Employee table.

D. Remove the Departmentname column from the Departmenttable.

Answer: C

20).You are designing a distributed data model for an international

importing and exporting company.

You take the following actions:

...

Which result or results does these actions produce?(choose all apply)

A. All sales record primary keys are unique throughout the

distributed database.

B. A sales record created in London or Nairobi includes a

value-added tax,but that a sales record created in Cairo does not.

C.Local sales amounts can be calculated in United States dollars at

the time of the sale.

D. Local sales amounts can be calculated in United States dollars at the end of the month.

E. The difference between the values of a sale United States dollars

at the time of the sale and the value at the end of the month can be calculated.

Answer: A,B

21).You must write a stored procedure to perform cascading deletes

on the HomeLoan database. The client application will pass a parameter containing the CustomerID of the customer to be deleted.

Customer

CustomerID(PK)

LastName

FirstName

AddressID

StatusID

Country

Loan

LoanID(PK)

LoanDate

LoanStatusID

AppraisalDate

AppraisalAmount

LoanAmount

CustomerID

Inspection

InspectionID(PK)

LoanID

InspectionResultID

InspectionDate

InspectionTypeID

Appraisal

AppraisalID(PK)

LoanID

AppraisalDate

AppraisalAmount

AppralsorID

Which stored procedure should you use?

A. CREATE PROCEDURE LoanCascadeDelete

@customerID int

AS

DELETE FROM Appraisal

FROM Appraisal

JOIN Loan ON Appraisal.LoanID=Loan.LoanID

WHERE customerID=@customerID

DELETE FROM Inspection

FROM Inspection

JOIN Loan ON Inspection.LoanID=Loan.LoanID

WHERE customerID=@customerID

DELETE FROM Loan

WHERE customerID=@customerID

DELETE FROM Customer

WHERE customerID=@customerID

B. ...

C. ...

D. ...

Answer: A (A是級聯刪除的標准寫法)

22).You have a database that is accessed by many different

applications complies with ANSI 92 written with many different development tools. Each application uses a different mechanism for accessing the database.

Transaction processing in not uniform across all your applications.

How Can you ensure that all applications handle transactions in the some Fashion?

A. Inside the database,create an application role,Program all

applications to activate the application role.

B. Program all applications to issue the SET ANSI_DEFAULTS ON command

Immediately after establishing a user connection.

C. Configure the SQL Server by using the sp_configure 『user options』

Stored procedure to turn on the following

options:implicit_transactions,cussor_close_on_commit,

ansi_warnings, ansi_padding, ansi_nulls,quoted_identifier,

ansi_null_dflt_on.

D. Create a stored procedure that sets all the users seesion properties

Run the sp_procoption stored procedure to mark your procedure for

startup.

Answer: B

23).Your database includes tables that are defined as follow:

create table SalesPerson(

SalesPersonID int identity(1,1) NOT NULL

PRIMARY KEY NONCLUSTERED,

RegionID int NOT NULL,

Lastname varchar(30) NULL,

Firstname varchar(30) NULL,

Middlename varchar(30) NULL,

AddressId int NULL)

create table Orders(

OrderID int identity(1,1) NOT NULL

PRIMARY KEY NONCLUSTERED,

SalePersonID int NOT NULL,

RegionID int NOT NULL,

Orderdate datetime NOT NULL,

OrderAmount money NOT NULL)

You need to produce a list of the highest sale for each salesperson on September 15,1998. The list id to be printed in the following:

LastName FirstName OrderDate OrderAmount

Which query will accurately produce the list?

A. SELECT s.LastName,s.FirstName,o.OrderDate,OrderAmount

FROM salesperson AS s

LEFT OUTER JOIN Orders AS o

ON o.SalePersonID=s. SalesPersonID

WHERE o.OrderDate=』09/15/1998』

and OrderAmount in (select MAX(OrderAmount) from Orders)

B. SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount)

FROM salesperson AS s

LEFT OUTER JOIN Orders AS o

ON o.SalePersonID=s.SalesPersonID AND o.OrderDate=』09/15/1998』

GROUP BY s.LastName,s.FirstName,o.OrderDate

C.SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount)

FROM salesperson AS s

INNER JOIN Orders AS o

ON o.SalePersonID=s.SalesPersonID

WHERE o.OrderDate=』09/15/1998』

GROUP BY s.LastName,s.FirstName,o.OrderDate,o.OrderID

D.SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount)

FROM salesperson AS s

INNER JOIN Orders AS o

ON o.SalePersonID=s.SalesPersonID

WHERE o.OrderDate=』09/15/1998』

And OrderAmount in (select MAX(OrderAmount) from Orders)

Answer: B

24)You increase the number of users of your customer

application from 20 to 120 .With the additional users, the

response time when retrieving and updating has slowed substantially

You examine all the queries and indexes and discover that they are all fully optimized. The application seems to run properly as long as Number of users is less than 50

What can you do to resolve the problem?

A. Ensure that table hints are used in key queries to force the use of the correct table index

B. Increase the LOCK_TIMEOUT setting to accommodate the long response

Times now being encountered.

C. Free up dirty pages in memory by configuring the SQL Server with a short recovery interval.

D. Ensure that application is using an optimistic locking strategy

Instead of a pessimistic locking strategy.

Answer: D

25).You have a database that keeps track of membership in an organization.Tables in this database includes the membership table,

the Committee table,the Address table,and the Phone table,When a person

resigns from the organization,you want to be able to delete membership

row and have all related rows be automatically removed.

What can you do to accomplish this task?

A. Create a DELETE trigger on the Membership table that deletes any

rows in Committee, Address ,Phone table that reference the primary

key in the Membership table

Do not place a FORDIGN KEY constraints on the Committee, Address,

Phone table.

B. Create a DELETE trigger on the Membership table that deletes any

rows in Committee, Address ,Phone table that reference the primary

key in the Membership table place a FOREIGN KEY constraints on

the Committee, Address ,Phone table.

C. Place a PRIMARY KEY constraints on the Membership table with FOREIGN

KEY constraints on the Committee, Address ,Phone table.

D. Place a PRIMARY KEY constraints on the Membership table Place

FOREIGN KEY constraints on the Committee, Address, Phone table

that reference the primary key in the Membership table. Create

DELETE trigger on the Committee, Address, Phone table will Fire

when their FOREIGN KEY constraints are violated.

Answer: A

26). Your database includes an Orders tables that are defined as follow:

create table Orders{

OrderID int identity(1,1) NOT NULL

PRIMARY KEY NONCLUSTERED,

SalePersonID int NOT NULL,

RegionID int NOT NULL,

Orderdate datatime NOT NULL,

OrderAmount money NOT NULL}

You have written a stored procedure named GetOrders.

You must change the stored procedure to produce a list of orders in order first by RegionID ,then by SalePersonID.Permissions have been granted on the stored procedure,and you do not want to have to

Grant them again.

How must you change the stored procedure?

A. DROP PROCEDURE GetOrders

GO

CREATE PROCEDURE GetOrders

AS

SELECT SalesPersionID,RegionID,OrderID,OrderDate

OrderAmount

From Orders

ORDER BY RegionID, SalePersonID

B. ALTER PROCEDURE GetOrders

AS

SELECT SalesPersionID,RegionID,OrderID,OrderDate

OrderAmount

From Orders

ORDER BY RegionID, SalePersonID

C. ALTER PROCEDURE GetOrders

AS

SELECT SalesPersionID,RegionID,OrderID,OrderDate

OrderAmount

From Orders

D.DROP PROCEDURE GetOrders

GO

CREATE PROCEDURE GetOrders

AS

SELECT SalesPersionID,RegionID,OrderID,OrderDate

OrderAmount

From Orders

Answer: B

27). Your database includes a table that is defined as follow:

create table SalesInformation{

SalesInformation ID int identity(1,1) NOT NULL

SalePersonID int NOT NULL,

RegionID int NOT NULL,

ReceiptID int NOT NULL,

SalesAmount money NOT NULL}

You want to populate the table with data from an existing application

That has a numeric primary key.In order to maintain the referential

Integrity of the database, You want to preserve the value of the original

primary key when you convert the data.

What can you do to populate the table?

A. Set the IDENTIEY_INSERT option to OFF, and then insert the data by using a SELECT statement that has a column list.

B. Set the IDENTIEY_INSERT option to ON, and then insert the data by using a SELECT statement that has a column list.

C. insert the data by using a SELECT statement that has a column list, and then alter the table to add the foreign key.

D. insert the data by using a SELECT statement that has a column list, and then alter the table to add the primay key.

Answer: B

28). You have a database to keep track of sales information. You are working with a nested procedure that will pass a parameter back to the calling procedure containing the total sales as follows:

CREATE PROCEDURE GetSalesPersonData

@SalesPersonID int,

@RegionID int,

@salesAmount money OUTPUT

AS

SELECT @salesAmount=SUM(salesAmount)

FROM SalesInformation

WHERE @SalesPersonID=SalesPersonID

Which statement will accurately execute the procedure and receive the value?

A. EXECUTE GetSalesPersonData 1,1 NULL

B. EXECUTE GetSalesPersonData @SalesPersonID=1, @RegionID=1,

@salesAmount=0

C. EXECUTE GetSalesPersonData 1,1, @salesAmount OUTPUT

D. EXECUTE GetSalesPersonData @SalesPersonID=1, @RegionID=1,

@salesAmount=NULL

Answer: C

29)You have an application that captures real-time stock market information and generates trending reports. In the past, the reports are generated after the stock markets closed. The reports now need to be generated on demand during trading hours.

What can you do so that reports can be generated without affecting the rest application? (choose two)

A. Program the application to issue the following command before generating a report.

Set transaction isolation level read uncommitted

B. Program the application to issue the following command before

generating a report.

Set transaction isolation level serializable

C Require the application to include the NOLOCK table hint when

generating a report.

D Require the application to include the TABLOCKX table hint when

generating a report.

E. On the stock transaction tables, create triggers that update summary tables instead of performing a data analysis each time a report is generated.

F. Declare global scrollable cursors on the stock transaction tables.

Answer: A,C

30).You server named Corporate has a Sales database that stores

sales data for a software distribution company, Two remote

servers named NewYork and Chicago each store sales data in a

salesorder table relative only to their respective sales territories. The salesorder table on the Corporate server is updated once a week with data from the remote servers.

The salesorder table on each server including Corporate is defined as follows:

CREATE TABLE salesorder{

Number char(10) NOT NULL,

CustomerName varchar(100) NOT NULL,

TerritoryName varchar(50) NOT NULL,

EntryDate datetime NOT NULL,

Amount money NOT NULL}

You need to create a view that shows a current list of all sales from the NewYork

And Chicago sales territories, and the list should have the following format

Territory Customer Date Amount

Which view can you create to show all sales from the NewYork and Chicago sales territories in the required format?

A. CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount)

AS

SELECT TerritoryName , CustomerName, EntryDate, Amount

FROM Corporate.Sales.dbo.salesorder

WHERE TerritoryName=』 NewYork』OR TerritoryName=』 Chicago』

B. CREATE VIEW SalesSummary_view

AS

SELECT NY.TerritoryName , NY.CustomerName, NY.EntryDate, NY.Amount, CHI.TerritoryName , CHI.CustomerName, CHI.EntryDate, CHI.Amount

FROM NewYork.Sales.dbo.salesorder NY,

Chicago.Sales.dbo.salesorder CHI

C. CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount)

AS

SELECT NY.TerritoryName , NY.CustomerName, NY.EntryDate, NY.Amount, CHI.TerritoryName , CHI.CustomerName, CHI.EntryDate, CHI.Amount

FROM NewYork.Sales.dbo.salesorder NY,

Chicago.Sales.dbo.salesorder CHI

WHERE NY.Number=CHI.Number

D.CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount)

AS

SELECT TerritoryName , CustomerName, EntryDate, Amount

FROM NewYork.Sales.dbo.salesorder

UNION ALL

SELECT TerritoryName , CustomerName, EntryDate, Amount

FROM Chicago.Sales.dbo.salesorder

Answer: D

31). Your database includes a table named product. The procduct

table currently has clustered index on the primary key of the

product_id column. There is also a non clustered index on the

description column.

You are experiencing very poor response times when querying the product table. Most of the queries against the table include search arguments

On the description and product_type columns. Because there are many

Values in the size column for any product, query result sets usually contain between 200 and 800 rows.

You want to improve the response times when querying the product table.

What should you do?

A. Use SQL Server Enterprise Manger to create nonclustered indexes on each column being referenced by each SELECT statement.

B. Use SQL Server Enterprise Manger to generate stored procedure for the product table.

C. Use the index Tuning Wizard to identify and build any missing

Indexes.

D. Use SQL Server Profiler to capture performance statistics of queries against the product table

Answer: C

32).You are building a decision support system(DSS)database for

your company. The new database is expected to include information

from existing data sources that are based on Microsoft Access, dBaseIII

, Microsoft Excel, and Oracle.

You want to use SQL Server Agent to run a scheduled job to extract

Information from the existing data source into a centralized database

On SQL Server 7.0.You do not want to perform any additional programming

Outside the SQL Server environment

How must you extract information from the existing data sources

A. Use the bulk copy program to import the information from all data

Source.

B. Use the xp_cmdshell extended stored procedure to execute the

Microsoft Windows copy command-line command/

C. Create Data Transaction Services package to import data from each

data source.

D. Create export files for each data source.

Answer: C

33)You automate the backup and recovery process for your database application. After the database is restored, you discover that queries that use the FREETEXT and CONTAINS keywords no longer return the expected rows.

What should you do?

A. Alter the queries to use the LIKE keyword instead of the FREETEXT and CONTAINS keywords.

B. Alter the queries to use the FREETEXTABLE and CONTAINSTABLE keywords instead of the FREETEXT and CONTAINS keyword.

C. Add the database』s full-text catalog to both the backup job and the recovery job.

D. Add a job to the restoration process to re-create and populate the full-text catalog.

ANSWER: D

34) you are working on a data conversion effort for a Sales database. You have successfully extracted all of the existing customer data into a tab_delimited flat file. The new customer table is defined as follows:

CREATE TABLE customer(

Id int IDENTITY NOT NULL,

LastName varchar(50) NOT NULL,

FirstName varchar(50) NOT NULL,

Phone varchar(15) NOT NULL,

Email varchar(255) NULL)

You need to populate this new table with the customer information exists in a tab_delinited flat file with the following format:

Name Phone E-mail

Adam Barr 555-555-1098 abarr@adatum.com

Karen Berge 555-555-7868 kberge@woodgrovebank.com

How can you transfer the data to accurately populate the customer table?

A. Import the data by using the bcp utility with the /E option to accommodate the column that has the IDENTITY property.

B. In the flat file, separate the Name column into FirstName and Lastname columns by using a bcp format file, and then import the data by using the bcp untility.

C. Import the data by using Data Transformation Services with the Transform information as it is copied to the destination option button selected.

D. Import the data by using Data Transformation Services with the Enable identity insert check box selected.

Answer: C

35) You are developing an application for a worldwide furniture wholesaler. You need to create an inventory table on each of the databases located in New York,Chicago,Paris,London,San Francisco, and Tokyo. In order to accommodate a distributed data environment, you must ensure that each row entered into the inventory table is unique across all locations.

How can you create the inventory table?

A. CREATE TABLE inventory(

Id int IDENTITY NOT NULL

CONSTRAINT pk_inventory_id PRIMARY KEY NONCLUSTERED,

ItemName varchar(100) NOT NULL,

ItemDescription varchar(255) NULL,

Quantity int NOT NULL,

EntryDate datetime NOT NULL)

B. CREATE TABLE inventory(

Id uniqueidentifier NOT NULL

DEFAULT NEWID(),

ItemName varchar(100) NOT NULL,

ItemDescription varchar(255) NULL,

Quantity int NOT NULL,

EntryDate datetime NOT NULL)

C. CREATE TABLE inventory(

Id int NOT NULL

CONSTRAINT u_inventory_id UNIQUE CLUSTERED(Id),

ItemName varchar(100) NOT NULL,

ItemDescription varchar(255) NULL,

Quantity int NOT NULL,

EntryDate datetime NOT NULL)

D. CREATE TABLE inventory(

Id int NOT NULL

CONSTRAINT pk_inventory_id PRIMARY KEY CLUSTERED,

ItemName varchar(100) NOT NULL,

ItemDescription varchar(255) NULL,

Quantity int NOT NULL,

EntryDate datetime NOT NULL)

Answer: B

36)You need to create a development database that will hold 20 MB of data and indexes and a 4-MB transaction log. There are no concerns regarding query performance or log placement with this database. The SQL Server was installed on drive E of the server computer, and there is plenty of disk space on drive E.

How should you create the database?

A. disk init name=』development1』,physname=

『 e:\mssql7\data\development1.dat』,vdevno=45,size=10240

disk init name=』developmentlog1』,physname=

『 e:\mssql7\data\developmentlog1.dat』,vdevno=46,size=2048

CREATE DATABASE development on developement1=20 log on

Developmentlog1=4

B. CREATE DATABASE development on default=20

C. CREATE DATABASE development on primary(name=development1,

Filename=』 e:\mssql7\data\development1.mdf』,size=20MB) log

On (name=developmentlog1,filename=

『 e:\mssql7\data\developmentlog1.ldf』,size=4MB)

D. CREATE DATABASE development on (name=developmnet1,filename

=』e:\mssql7\data\development1.mdf』,size=24MB)

Answer: C

37)You must reconcile the checking account for your company. You have a CheckRegister table, an InvalidCheck table, and a ClearedCheck table. The ClearedCheck table lists checks that have cleared the bank.

You must update the ClearedDate column of the CheckRegister table for

any checks that are on the ClearedCheck table. If the check is in the ClearedCheck table but not on the CheckRegister table, you must insert a row into the InvalidCheck table. If the amount shown for a check in the ClearedCheck table is different from the amount shown for the same check in the CheckRegister table, you must insert a row into the InvalidCheck table. Each row must be deleted from the ClearedCheck table after it has been evaluated for accuracy.

Which statement group should you use to accomplish this task in the shortest time?

A. ...

B. ...

C. UPDATE CheckRegister SET CheckRegister.ClearedDate = ClearedCheck.ClearedDate

FROM CheckRegister

JOIN ClearedCheck ON CheckRegister.CheckNumber = clearedCheck.CheckNumber

AND CheckRegister.CheckAmount = ClearedCheck.CheckAmount

DELETE ClearedCheck

FROM ClearedCheck

JOIN CheckRegister ON CheckRegister.CheckNumber = ClearedCheck.CheckNumber

AND CheckRegister.CheakAmount = ClearedCheck.CheckAmount

INSERT InvalidCheck (CheckNumber, CheckAmount, ClearedDate)

SELECT CheckNumber, CheckAmount, ClearedDate

FROM ClearedCheck

DELETE ClearedCheck

D. ...

Answer: C

38)Your company stores its sales information in a SQL Server 7.0 database

To identity the amount of sales per product in each shipped to customers

In Montreal in 1998.you create the following query:

SELECT p.ProductID.o.OrderID.p.ProductName.Total=p.UnitPrice*od.Quantity

FROM Products p JOIN [Order Details] od ON p.ProductID=od.ProductID

JOIN Order o ON od.OrderID=o. OrderID

WHERE O.ShipCity=』Montreal』AND DATEPART(yy.o.ShippedDate)=1998

ORDER BY p.ProductName

Which of the following execution plans is generated for this query?

A).

B).

C).

D).

ANSWER:A

此題的答案是A,希望能通過這題看懂意思。

在考試時的題目和此題有點不同,考試時你應選B。

39).Your database application includes a complex stored procedure that displays status information as it processes transactions. You obtain unexpected results in the status information when you run the stored procedure with certain input parameters. You want to use SQL server profiler to help find the problem in your stored procedure.

Which four event should you track?(choose four)

A. sqltransaction

B. sp:stmtstarting

C. sp:stmtcompleted

D. sql:stmtstarting

E. sql:stmtcompleted

F. scan:started

G. object:opened

ANSWER: B,C,D,E

40).You are the database developer for a leasing company

Your leasing database includes a lessee table that is defined as

Followings:

CREATE TABLE lessee{

ID int IDENTITY NOTNULL

CONSTRAINT Pk_lessee_id Primary key Non inclustered

Surname Vchar(50) NOTNULL

First Name Vchar(50) NOTNULL

SocialSecurity Char(9) NOTNULL

Credit Rating Char(10) NULL

Credit Limit money NULL}

each social security number must be unique. You want the data to be physically stored in order by social security number.

Which constraint should you add to the socialsecurityno column on the lessee table?

A. A unique clustered constraint

B. A unique nonclustered constraint

C. A Primary key Clustered constraint

D. A Primary key NonClustered constraint

ANSWER: A

41).You are implementing a logical data model for an online transaction processing(OLTP) application.One entry from the logical models in third normal form and currently has 10 attributes.

One attribute is the Primary Key.Six of the attributes are Foreign Key references into six other entries.

The last three attributes represent columns that hold numeric values.

How should this entry from the logical at model be implemented?

A. Create a table by denormalizing the entry. Add the information from the six Foreign Key references as additional columns of the table.

B. Create two tables by denormalizing the entry. Add the Primary Key

And the three numeric values as columns of one table.Add the Primary Key and information from the six Foreign Key references as additional columns of other tables.

C. Create the table as described in the logical data model.

D. create a view that joins the six foreign key references.

ANSWER:C

42).You are design a database that will be used to store information about tasks assigned to various employees.Each tasks is assigned to

only one employee. The database contains a table named Task that is

modeled as shown in the exhibit.

You want to use a Primary Key constraint to uniquely identity each row in the Task table.

On which columns or columns should you define the Primary Key constraint

(choose all the apply)

table:

Task

Taskno

Employeeno

Title

Description

Status

Datecompleted

A. Task No.

B. Emplyee No.

C. Status.

ANSWER: A,B,C

43).You are building a new database for the human resources department of a company.There are ten departments within the company,and each

department contains multiple employee in addtion.each employee might

work for several departments.

How should you logically model the relationship between the department

entry and the employee entry.

A.Create a mandatory one-to-many relationship between the department

entry and the employee entry.

B. Create an optional one-to-many relationship between the department entity and the employee entity.

C. create a new entity, create a one-to-many relationship from the employee entity to the new entity, and the create a one-to-many relationship from the department entity to the new entity.

D. Create a new entity, create a one-to-many relationship from the new entity to the employee entity, and then create a one-to-many relationship from the new entity to the department entity.

ANSWER:C

44). You are building a database for the human resources department of your company. You want to eliminate duplicate entry and minimize data

storage wherever possible. You want to track the following information

for each employee.

Which table or tables should you use?

A.

Employee

EmployeeID

ManageremplyID

FirstName

MiddleName

LastName

Address

Date of hire

Department

Salary

B.

Employee

EmployeeID

FirstName

MiddleName

LastName

Address

Date of hire

Department

Salary

Manager

ManagerID

FirstName

MiddleName

LastName

C.

Employee

EmployeeID

ManagerID

FirstName

MiddleName

LastName

Address

Date of hire

Department

Salary

Manager

ManagerID

FirstName

MiddleName

LastName

D.

Employee

EmployeeID

FirstName

MiddleName

LastName

Address

Date of hire

Department

Salary

Manager

ManagerID

FirstName

MiddleName

LastName

Employee Manager

EmployeeID

ManagerID

ANSWER:A

45). You are designing a data model to track purchases for a fish canning company. You take the following actions

1.Create the data model as shown in the exhibit.

...

Which results does these actions produced (choose all the apply)

A. all purchase amounts are rounded to the nearest whole dollar.

B. Record for tuna purchased must be in multiples of 100, and records for salmon purchased must be in multiples of 50.

C. The daily total quantity of salmon purchased cannot exceed 150.

D. The monthly government report can be produced reporting the kind, quantity, and supplier for each lot of tuna or salmon purchased.

ANSWER:A,C,D

Company

CompanyID(PK)

Purchase

PurchaseID(PK)

PurchaseID

PurchaseDate

Fish

FishName

PurchaseDetail

PurchaseID(PK)

LineNumber(PK)

FishName

FishQuantity

Purchaseamount

46). You are implementing a logical data model for a decision support system(DSS)database. Two of the tables in the model have a parent/child relationship. The parent table is expected to have more than 1 million rows. The child table is expected to have more than 100 million rows.

Most reports present aggragate child information grouped accounting to each parent row reports containing detailed child table information

are occasionly needed.

How should tables be implemented?

A. Create the parent table and the child table as described in the logical data model

B. Create the parent table that include aggregate child information, do not create the child table.

C. create the parent table that include aggregate child information,

Create the child table as it exists in the logical data model

D. in child table, create a nonclustered index that includes any columns that are aggregated and the foreign key reference to the parent.

ANSWER:C

47).Your are building an invocing system for your company.

there can be multiple parts on one invoice, cost and price information comes from a master list but the history store for each invoice should show the cost and price at time of sale you want to make sure your database is properly normalized.

You want to accomplish the following goals

1. Every table must have a Primary Key.

2. All non-key columns must depend on the whole Primary Key.

3. All columns must contain exactly one value

4. Each column in a table must be independent of any non-key column

in the same table

You create the logical model as show in the exhibit

Which result or results does this model produce(choose all the apply)

Part

PartNo(PK)

PartDescription

Unitweight

UnitCost

UnitPrice

Orders

OrdersNo(PK)

OrdersDate

Customer

CustomerNo(PK)

CustomerName

CustomerAddress1

CustomerAddress2

City

Territory

Postal Code

OrderDetails

OrdersNo(PK)

ItemNo

CustomerNo

PartNo

PartDescription

QuantitySold

Unitweight

UnitCost

UnitPrice

A. every table has a primary key

B. all non-key columns depend on the whole primary key

C. all columns contain exactly one value

D. each column in a table is independent of any non-key column in the same table.

ANSWER:A,C

48).Your database include a Salesperson table that tracks various data including the sales goals and actual sales for individual salespeople.

The sales manager wants a report containg a list of the five least

Productive salepeople,along with their goals and their actual sales

Production.You will use an ascending sort to order the information

In the report by actual sale production.

What should you do to produce report?

A. Issue a set rowcount 5 statement before issuing a select statement against the salesperson table

B. Include a Top 5 clause in the select list against the salesperson table

C. Issue a set query_governor_cost_limit 5 statement before issue select list against the salesperson table.

D. Count the row returned by using a having count(*)<=5 clause.

ANSWER:B

49).You development team has just developed tested and developed a new accounting application that including many integrated modules.Users frequently encounter deadlocks whenever someone performs a function that integrates data from multiple modules.The

development team never encounted deadlocks when unit testing the application.

What can you do to minmize deadlock?

A. Set the deadlock priority to low

B.issue the begin distributed transaction statement whenever data modifications involve data in different modules

C.ensure that all transactions modify tables in the same order.

D.Ensure that all tables have indexes on primary and foreign keys.

ANSWER: C

50).You issure an UPDATE statement and then run a select query to verify that the update were accurate .You find out the UPDATE statement

was excecuted properly.However,the next time you log on to the SQL Server computer.It appears that your UPDATE statement was not excecuted

What is the most likely cause the problem?

A.The FMTONLY option is set to on

B.the showplan_all option is set to on

C.the parseonly option is set to on

D.the implicit_transactions option is set to on

ANSWER: D

51)You are investigating reported problems regarding the performance of a query in your database .The WHERE clause of the query includes

search arguments on ColumnA, ColumnB, ColumnC

You analyze the data and discover that the content of ColumnA

is nearly identical in all rows.The content of ColumnB is the same in about 50 percent of the rows.

The content of ColumnC is the same in about 10 percent of the rows

How should you index the table to improve query performance?

A.create a composite clustered index on columnA,columnB,columnC

B.create a composite clustered index on columnC,columnB,columnA

C.create a composite nonclustered index on columnA, columnB, columnC

D.create a composite nonclustered index on columnC, columnB, columnA

E.create a clustered indes on columnA, create separate nonclustered indexes on columB and columnC

F.create a separate nonclustered indexes in each column.

ANSWER:B

52).Your shipping company has a database application that maintains

an invetory of items on each vessel.When each vessel is unloaded at

its destination.The invetory is counted and the arrived-quantity column is updated in the database.

There can be thousands of vessels en route at any one time.

Each shipments id identified by a shipment_id.Each vessel can carry

Thousands of items.Each item in a shipment is identified by an item_number.

You want to make sure the update of the arrived-quantity column is as fast as possible .

What should you do ?

A. create a nonclustered index on the shipment_id column, the item_number column, and the arrived_quantity column.

B. create a clustered index on the shipment_id column, the item_number column, and the arrived_quantity column.

C. Create a clustered index on the shipment_id column and item_number column.

D. Create a nonclustered index on the shipment_id column and item_number column.

ANSWER:C

53).You have a database that contains information abuot publications for sale.You want to write a full-text search query that will search

through all the columns in one table enabled for full-text querying.

The table includes a column named titles. a column named price

and a column named notes.The titles and notes column are full-text enabled.

you want to find all publications that deal with French gourmet cooking, which contains statement should you use?

A. where contains(*, 』」French gourmet」』)

B. where contains(notes, 』」French gourmet」』)

C. where contains(titles, 』」French gourmet」』)

D. where contains(price, 』」French gourmet」』)

ANSWER: A

54)You need to create two new tables for your Purchaseing database

The new tables will be named Purchaseorderheader and Purchaseorderline

The Purchaseorderheader table will have the PurchaseorderheaderID columns as the Primary Key.A Purchaseorderline row must not exist without a corresponding Purchaseorderheader row.

How can you create the tables?

A. Create both tables and then use the ALTER TABLE statement to create a CHECK constraint on Purchaseorderline Primary Key column.

B. Create both tables, and then use the the alter table statemetn to create a check constraint on the purchaseorderheader primary key column

C. Create the purchaseorderheader table and then create the purchaseorderline table that has a foreign key constraint referencing the purchaseorderheader table

D. Create the purchaseorderline table, and the create the purchaseorderheader table that has a foreign key constraint referencing the purchaseorderline table

ANSWER:C

55).You have a database that is used for storing the text of speeches given by certain government officials.

The text of each speech is stored in the speeches table that is defined

as follows:

CREATE TABLE Speeches{

SpeechID Char(32),

SpeechText Text,

AuthorID Char(32)}

GO

A full-text indexs exist for all columns in the speeches table .You want to search for a speech that includes the phrase

「 Four score and seven years ago」

which query should you use to perform this search?

A. where speechid like 『% Fore Score and only %』

B. where speechtext like 『% Fore Score and only %』

C. where freetext (speechid, 『 Fore Score and only 『』)

D. where freetext (speechtext, 『 Fore Score and only 『』)

ANSWER:D

56).You need to create a 6-GB online transaction processing(OLTP)database,Your SQL Server computer has 2 disk controllers and each controller has 4 6-GB hard disk drivers.Each hard

disk drive is configured as a separate NTFA partion.Microsoft WindowsNT

.The Microsoft WindowsNT swap file and SQL Server are all installed on drive C .The remaining drivers,which are labled as drive D through J are empty.

How should you create OLTP database?

A. Create the data portion of the database as a single file on D, create the log on E

B. Create the data portion of the database as six separate files on D through I, with one file on each drive, create log as a single file on J

C. Create the data portion of the database as 24 separate files on D through I, with four files on each drive, create the log as a single file on J

D. Create the data portion of database as four separate files on D through G, with one file one each drive, create the log as thress separate files on H through J

ANSWER:B

57).Department managers in your company want to use Microsoft Excel pivot tables analyze data from your SQL Server database.You need to

extract data from tables in the database to an Extent spreadsheet so

that managers can copy the spreadsheet and build pivot tables

The data in database changes frequently and you want to automate the

Process of updating the Excel spreadsheet.

you plan to use a sql server agent scheduled job to automation the extraction of the data to the spreadsheet.

What should the scheduled job execute?

A. a bulk copy program script to create a tab_delimited text file

B. a bulk copy program script to populate the spreadsheet

C. a data transformation services export package to create a tab_delimited text file

D. a data transformation services export package to populate the spreadsheet

ASNWER:D

58).Your users report that your database application takes an excessive amount of time to complete an operation.Overtime.with the addition

of new rows and changes to existing rows the situation has worsened.

You suspect that the tables are not optimally indexed

You plan to use the SQL Server Profiler Create Trace wizard to find out the cause of the problems

What should you use the create Trace Wizard to do (choose two)

A. Find the worst performing queries

B. identify scans of large tables

C. identify the cause of a deadlock

D. profile the performance of a store procedure

E. trace T-SQL activity by application

F. trace T-SQL activity by user

ANSWER: A,B

59) You run the DBCC SHOWCONTIG statement, the statement provides the following output.

---Page scanned ------------------------------158

---Extends scanned ---------------------------21

---Extend Switch------------------------------20

---Avg.Pages per Extent ----------------------7.5

---Scan Destiny(Best Count:Actual Count) -----95.24%(20:21)

---Extent scan Fragmentation------------------4.76%

--- Avg.Bytes Free per page ------------------408.4

--- Avg.Page Destiny(full) -------------------94.95%

What does this output tell you about how the data is stored ?

(choose all the apply)

A. The table is not externally fragmented

B. the table is not internally fragmented

C. the number of extent switches is excessives

D. the rowsize does not efficiently fit on a page

E. the IAM page does not reflect the actual extent usage

ANSWER:A,B

60).You add new fuctionally to an existing database application.

After the upgrade.Users of the application report slower performance .

The new fuctionally executes multiple store procedure and dynamic

SQL statement.You want to be able to identity specific queries

That are encountering excessively long execution times.

What should you do ?

A. Run the sql server undex tuning wizard to retune the indexes for the new functionality

B. create a sql server profiler trace that uses the minimum execution time and application filters

C. use the current activity dialog box of the sql server enterprise manager to list all current user tasks and object blocks.

D. Use the sp_monitor store procedure to monitor the cpu_busy and io_busy column before and after testing the new fuctionally.

ANSWER:B

61).You are troubleshooting a process that makes use of multiple complex stored procedure that operate on a table.

The process is producing an unexpected update to the table

You want to identity the specific stored procedure and statement that

are causing the problems.

What should you do ?

A. Use sql server profiler to create and reply a trace by using single stepping.

B. place a trigger on the table to send an e-mail message when a column is set to a specific value

C. execute the store procedure』s create procedure statement to verify that the syntax is still valid

D. Examine the transaction log locate the statement that make the unexpected update to the table

ANSWER:A

62). You run the DBCC SHOWCONTIG statement on the sales table and receive

The following output:

---Page scanned ------------------------------1657

---Extends scanned ---------------------------210

---Extend Switch------------------------------1528

---Avg.Pages per Extent ----------------------7.9

---Scan Destiny(Best Count:Actual Count) -----13.60%(208:1529)

---Extent scan Fragmentation------------------97.43%

--- Avg.Bytes Free per page ------------------2843。5

--- Avg.Page Destiny(full) -------------------64.87%

What should you do to improve the response times for querise?

A. Update the statistics on the Sales table

B. Create additional statistics on the Sales table.

C. Run the DBCC DBREINDEX statement on the Sales table.

D Run the DBCC CHECKTABLE statement on the Sales table.

ANSWER: C

63). You run the DBCC SHOWCONTIG statement on the table and receive

The following output:

---Page scanned ------------------------------354

---Extends scanned ---------------------------49

---Extend Switch------------------------------253

---Avg.Pages per Extent ----------------------7.2

---Scan Destiny(Best Count:Actual Count) -----17.79%(45:94)

---Extent scan Fragmentation------------------82.21%

--- Avg.Bytes Free per page ------------------485.2

--- Avg.Page Destiny (full) -------------------94.01%

what should you do to improve the query performance ?

A. Update the statistics on the clustered index.

B. Change the row size to fit efficiently on a page.

C. Rebulid the clustered index with a fill factor value set to 100.

D. Rebulid the clustered index with a fill factor value set to 25.

E. Rebulid the clustered index with a fill factor value set to 75.

ANSWER: E

64)You have an accounting application that allows users to enter

information into a table named staging

when data entry is complete a batch job users .

the rows in the staging table to update a table named prodution

Each users rows in the staging table are identified by the user』s

SQL Server Process ID number in a column named spid

the code for the batch job that updates the production table is:

declare @count int

begin tran

select @count=count(*) from production p join staging a on p.account=s.account where s.spid=@@spid

update p set amount=s.amount from production p join staging s on p.account=s.account where s.spid=@@spid

if @@rowcount<>@count

rollback tran

else

commit tran

You find out that there have been locking problems when two users run

the batch job at the same time.What should you do to solve the locking

problems?

A. Program the accounting application to set the transaction isolation

level to SERIALTABLE before running the batch job.

B. Program the accounting application to set the transaction isolation

level to READ UNCOMMITTED before running the batch job.

C. program the accounting application to set the deadlock priority to normal before running the batch job

D. program the accounting application to set the deadlock priority to low before running the batch job

E. inclued the table hit WITH ROWLOCK UPDLOCK when counting the rows

in the production table.

F. inclued the table hit WITH TABLOCKX when counting the rows

in the production table.

ANSWER: E

65).You are implementing a logical data model.All of the tables in your logical data model are normalized to at least third normal form

There are no surrogate Primary keys in any of the tables.Some table

relationships involve up to eight levels of parent,child grand child

and so forth, in the model ,the Primary key of each descendent table

inherts Primary key of all ancestor tables.

you want to accomplish the following goals:

.allow tables at any level in the hierarchy to be joined to any other table in the hierarchy.

.ensure that tables are joined on a single column.

.limit the index length of primary keys to 10 bytes or less.

.ensure that key columns are always compared on a bunary basis regardless of which options were selected during the installation of sql server.

You take the following actions.

1.Implement the data model as is.

2.Create indexs on all foreign keys.

Which result or results do these actions produce(choose all the apply)

A. tables in the data model hierarchy can be joined to any other table in the hierarchy

B. tables are joined on a single column

C. The Index length of all Primary keys is 10 bytes or less.

D. Key columns are always compared on a binary basis regardless of

Which options were selected during the installtion of SQL Server.

ANSWER:A,B,C,D

66).You are implementing a marketing application that was not origianlly designed replication.You need to use replication to distribute data among four separate SQL Server .You also need to allow

users to enter data into a table named sales from any location and have the data distributed to all locations.

When creating tables,you must follow your company programming standards which reqire the use of surrogate Primary key in all tables.

What should you do ?

A. Create a primary key column that uses the identity property with a step of 1 and a separate starting number for each server so that the first server starts at 1, the second server starts at 10000001, and so forth.

Replicate new rows by using store procedure that include the following command: set identity_insert sales on

B. Create a primary key column that uses the identity property with a step of 4 and a separate starting number for each server so that the first server starts at 1, the second server start at 2, and so forth

Replicate new rows by using store procedure that include the following command: set identity_insert sales on

C.Create a Primary key column that uses the timestamp data type.

D.Create a Primary key column that uses the uniqueidentifier data

Type and a default constraint of NEWID()

ANSWER: D

67).You have existing data that must be moved into a new database.The

data is stored in two tables named table A and table B.Each table

contains more than 100 million rows.You know that application are joining table A to table B by using a cluster index.

Fast response time is critical ,in the past separating the tables from

The rest of the database and placing them onto separate physical disks

has improved response time.

In the new database.how should you save the data in table A and table B in order to reduce response time when joining the two tables.

A. In the database, create two segments, named segmentA and segmentB, that point to separate physical disk drives. Remove the default and system segments from the devices where segmentA and segmentB reside.

Create tableA and any associated index on segmentA.

Create tableB and any associated index on segmentB.

B. create two filegroups, named filegroupA and filegroupB, as part of the database on separate physical disk drives.

Create tableA and any associated index on filegroupA.

Create tableB and any associated index on filegroupB.

C. create three filegroup named filegroupA and filegroupB and filegroupC, as part of the database on separate physical disk drives.

Create tableA and any associated index on filegroupA.

Create tableB and any associated index on filegroupB.

Create all clustered and nonclustered indexes for tableA and tableB on filegroupC.

D. create the data portion of the database on a RAID 5 device, create tableA and tableB and all indexes in the database.

E. Create the data partion of the database on a RAID1 +0 the device

Create table A and table B and all indexes in the database.

ANSWER:B

68).You are the database manager for a manufacturing company.You are

receiving the mixture of online transaction processing(OLTP) and

decision support system(DSS) activities that from the shop floor in real time.The application must report production results trends and

variances in a timely fashion.

What should you do to maxmize the overall performance of the production

Monitoring application?

A. Add additional indexes to support both OLTP and DSS requirements.

B. create an OLTP sql server and a DSS sql server replicate table from the OLTP sql server to a DSS sql server with the distribution database on the DSS server. Index the tables on each sql server to support their respective activities.

C. create an OLTP sql server and a DSS sql server. On the OLTP sql server, index the tables to support the OLTP requirements every 15 minute, backup the log from OLTP sql server and restore it on the DSS sql server. Reindex the table on the DSS sql server to support the DSS activities.

D.Creat an OLTP database and a DSS database on the Same SQL Server

In the OLTP database ,create triggers that automatically update tables

In the DSS database

ANSWER: B

69) You need to produce a sales report listing all salesperson numbers

sales amounts ,and order dates. You want the report stored from most

recent sales to oldest sales. For each day, you want the sales amounts

stored from highest to lowest.

You will be selecting this information from a table that is defined

As follow:

CREATE TABLE Saleinformation

(Sales informaitonID int IDENTITY(1,1) NOT NULL

PRIMARY KEY NONCLUSTERED.

SalePersonID int NOT NULL

RegionID int NOT NULL

ReceiptID int NOT NULL

SalesAmount money NOT NULL

OrderDate datetime NOT NULL}

Which query will accurately produce the report?

A) SELECT SalePersonID, SalesAmount ,OrderDate

FROM Saleinformation

ORDER BY OrderDate ,SalesAmount DESC

B). SELECT SalePersonID, SalesAmount ,OrderDate

FROM Saleinformation

ORDER BY SalesAmount, OrderDate DESC

C). SELECT SalePersonID, SalesAmount ,OrderDate

FROM Saleinformation

ORDER BY OrderDate DESC,SalesAmount DESC

D) . SELECT SalePersonID, SalesAmount ,OrderDate

FROM Saleinformation

ORDER BY SalesAmount DESC, OrderDate DESC

ANSWER:C

70). You have an accounting application that captures batches of transactions into a staging table before being proceed. Processing

can be performed on individual batches or on the whole staging table

Processing includes many validations before updating any of the

Production tables.

you take the following actions:

...

Which result or results do these actions produce? (choose all apply)

A. Deadlocks are avoided or handled appropriately.

B. Each batch of transactions is accepted or rejected.

C. Users to access the production tables while accounting

Transactions are being processed.

D. Resource locking is minimized

ANSWER B,C,D

71) You database stores telephone number. Each telephone number

is stored as an integer. You must format the telephone number to

print on a report in the following format:

(999)-999-999

You have selected the phone number into a local variable as follows:

DECLARE @ PhoneNumber int

Which statement will correctly format the number?

A. Select』PhoneNumber』=』(『+SUBSTRING(CONVERT(varchar(10),

@PhoneNumber),3,0)+』)』+SUBSTRING(CONVERT(varchar(10), honeNumber),3,3)+』-『+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),4,6)

B. Select』PhoneNumber』=』(『+SUBSTRING(CONVERT(varchar(10),

@PhoneNumber),3,1)+』)』+SUBSTRING(CONVERT(varchar(10), honeNumber),3,4)+』-『+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),4,7)

C. Select』PhoneNumber』=』(『+SUBSTRING(CONVERT(varchar(10),

@PhoneNumber),0,3)+』)』+SUBSTRING(CONVERT(varchar(10), honeNumber),3,3)+』-『+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),6,4)

D. Select』PhoneNumber』=』(『+SUBSTRING(CONVERT(varchar(10),

@PhoneNumber),1,3)+』)』+SUBSTRING(CONVERT(varchar(10), honeNumber),4,3)+』-『+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),7,4)

ANSWER:D

72).Your Orders table is defined as follows:

CREATE TABLE Orders{

OrderID int IDENTITY(1,1) NOTNULL

SalesPersonID int NOTNULL

RegionID int NOTNULL

OrderDate datetime NOTNULL

OrderAmount int NOTNULL}

The table is becoming too large to manage.You must delete all

Sales that are more than three years old.

Which query will accomplish the desired result?

A. Delete from Orders Where OrderDate < DATEADD(YY,-3,GETDATE())

B. Delete from Orders Where OrderDate < DATEADD(YY,3,GETDATE())

C. Delete from Orders Where OrderDate < GETDATE(), -3

D. Delete from Orders Where OrderDate < GETDATE(), +3

ANSWER:A

73). You are developing a sales database for a company that has a 100

person sales staff. The company』s policy requires that any sales orders

in excess of $100,000 be approved and entered into the database by

the sales manager

Your database includes a SalesOrder table that is defined as follows:

CREATE TABLE Saleorder {

Number char(10) NOTNULL

Saleperson varachar(50) NOTNULL

Amount money NOTNULL}

You need to create a view on the SalesOrder table that will prevent

The sales staff from entering a sales order in excess of $100,000.

Which view should you write?

A. CREATE VIEW SaleOrderLimit

AS SELECT Number,Saleperson,Amount

FROM SaleOrder

WHERE Amount<=100000

WITH CHECK OPTION

B. CREATE VIEW SaleOrderLimit

AS SELECT Number,Saleperson,Amount

FROM SaleOrder

WHERE Amount<=100000

C. CREATE VIEW SaleOrderLimit(Number,Saleperson,Amount)

AS SELECT Number,Saleperson,SUM(Amount)

FROM SaleOrder

GROUP BY Number. Saleperson

HAVING(SUM(Amount)<=100000)

D. CREATE VIEW SaleOrderLimit

AS SELECT TOP 100000

Number,Saleperson,Amount

FROM SaleOrder

WHERE Amount<=100000

ORDER BY Amount

ANSWER :A

74). Your database includes a table named SaleInformation that tracks sales by region. The tables is defined as follows:

CREATE TABLE Saleinformation

(Sales informaitonID int IDENTITY(1,1) NOT NULL

PRIMARY KEY NONCLUSTERED.

SalePersonID int NOT NULL

RegionID int NOT NULL

ReceiptID int NOT NULL

SalesAmount money NOT NULL}

Your database also includes a table named SalePerson that is defined

As follows:

CREATE TABLE SalePerson

(SalePersonID int IDENTITY(1,1) NOT NULL

PRIMARY KEY NONCLUSTERED.

RegionID int NOT NULL

LastName varchar(30) NOT NULL

FirstName varchar(30) NULL

MiddleName varchar(30) NULL

AddressID int NULL}

You want to ensure that each saleperson enters sales only in the

Saleperson』s own region. Which of the following actions can you perform to accomplish this task?

A. Place a FOREIGN KEY constraint on the SalesInformation table that relates to the SalePerson table.

B. Place a FOREIGN KEY constraint on the SalePerson table that relates to the SalesInformation table.

C. Create a trigger on the SalesInformation table that verities

That the region for the sale in the same as the region for the

Saleperson.

D. Create a trigger on the Saleperson table that verities

That the region for the sale in the same as the region for the

Saleperson.

ANSWER:C

75). Your database includes a table that is defined as follows:

CREATE TABLE Orders{

OrderID int IDENTITY(1,1) NOTNULL

SalesPersonID int NOTNULL

RegionID int NOTNULL

OrderDate datetime NOTNULL

OrderAmount int NOTNULL}

The sales manager wants to see a report that shows total sales by

region as well as a grand total of sale.

Which query can you use to create the report?

A. SELECT SalePersonID, RegionID OrderAmount

FROM Orders

ORDER BY RegionID

COMPUTE SUM(OrderAmount)

B. SELECT SalePersonID, RegionID OrderAmount

FROM Orders

ORDER BY RegionID

COMPUTE SUM(OrderAmount) BY RegionID

COMPUTE SUM(OrderAmount)

C. SELECT SalePersonID, RegionID SUM(OrderAmount)

FROM Orders

ORDER BY SalePersonID ,RegionID

D. SELECT SalePersonID, RegionID SUM(OrderAmount)

FROM Orders

ORDER BY SalePersonID ,RegionID

HAVING SUM(OrderAmount)=RegionID

ANSWER:B

76). Your company has a headquarters sales office and two remote sales

offices. Each sales office has a database containing an Orders table

that is defined as follows

CREATE TABLE Orders{

OrderID int IDENTITY(1,1) NOTNULL

SalesPersonID int NOTNULL

OrderDate datetime NOTNULL

OrderAmount int NOTNULL}

You want a report in the following format that combines data for

All orders from headquarters and the other two sales offices:

Office OrderID SalePersonID OrderDate OrderAmount

The server in the headquarters office is named HQ,and the servers in

The reomte sales offices are named RS1 and RS2.

Which query can you use to produce the report?

A. ...

B. ...

C. SELECT Office=』HQ』,OrderID,SalePersonID,OrderDate,OrderAmount

FROM HQ.Sales.dbo.Order

UNION ALL

Office=』RS2』,OrderID,SalePersonID,OrderDate,OrderAmount

FROM RS2.Sales.dbo.Order

UNION ALL

Office=』RS1』,OrderID,SalePersonID,OrderDate,OrderAmount

FROM RS1.Sales.dbo.Order

D. ...

ANSWER:C

微軟認證最新考題70-029 SQL7.0實現 微軟認證最新考題70-029 SQL7.0實現 70-029 SQL7.0實現 1). Users report slow response times when they are modify data in your application. Response times are excellent when users are merely retrieving data. The search criteria used for modifying data are the same as the search criteria for retrieving data. All transactions are short and follow standard guidelines for coding transactions. You monitor blocking locks, it』s ok. What is the most likely cause? A. The transaction log is placed on an otherwise busy disk drive. B. The transaction log is nearly full C. The checkpoint process is set too short D. The tempdb database is too small E. The tempdb database is on the same physical disk drive as the database. Answer: A 2). Database includes a table named experiments that is defined as follows: CREATE TABLE experiments { experimentid char(32), description text, status integer, results text} You write the following: SELECT* from experiments where contains(description, 'angina') you are certain that there are matching rows, but you receive an empty result set when you run the query. what should you do?(choose two) A. ensure that there is a nonunique index on the description column of the experiments table. B. ensure that there is a clustered index on the results column experiments table. C. create a full-text catalog that includes the experiments table. D. create a scheduled job to populate the full-text catalog. Answer: C,D 3). Database includes a job_cost table that typically holds 100000 rows but can grow or shrink by as much as 75000 rows at a time. the job_cost table is maintained by a batch job that runs at night. during the day, the job_cost table is frequently joined to other tables by many different queries. Users report that their initial queries are very slow, but then response time improves for subsequent queries. How should you improve the response time of the initial queries? A. run the sp_updatestats stored procedure as part of the nightly batch job. B. run the sp_createstats stored procedure as part of the nightly batch job. C. set the auto create statistics database option to true. D. set the auto update statistics database option to true. Answer: A 4).you are the DBA. You receive reports that your sales application has very poor response times. The database includes a table that is defined as follow: create table dbo.orders { OrderID int identity(1,1) NOT NULL, SalespersonID int NOT NULL, RegionID int NOT NULL, Orderdate datetime NOT NULL, Orderamount int NOT NULL, CustomerID int NULL} the orderid column is the primary key of the table. there are also indexes on the regionid and orderamount columns. you decide to run a showplan on all queries in the application. The following query, which accesses this table is used to list total average sales by region: select t1.regionid,avg(t1.salestotal) as regionaverage from (select regionid,salespersonid,sum(orderamount) as salestotal from orders group by regionid,salespersonid as t1 group by t1.regionid you set the SHOWPLAN_TEXT option to ON execute the query.the showplan output is as follows: ... you suspect that this query is part of the problem because the showplan indicates that the query is performing a table scan operation. what is the most likely reason that this query is performing a table scan? A. there is no composite index on OrderID,RegionID,and Orderamount. B. there is no where clause in the query. C. the query contains a subquery. D. the query is performing aggregate operations. ANSWER: B 5). Users can create and submit their own ad hoc queries against any of The tables in DSS database. You users report that the responses time for some queries are too long. Response times for other queries are acceptable. What should you use to identify long-running queries? A. SQL Server Enterprise Manager B. SQL Server Profiler C SQL Server Analyzer D. Microsoft Windows NT Performance Monitor. ANSWER: B 6). You have an application that makes four connections to the SQL Server at the same time, The connections are used to Execute SELECT, INSERT, UPDATE, and DELETE statements. The application occasionally stops responding when a user is trying to update or Delete rows, and then the user must close the application. The problem occurs When a user attempts to execute an UPDATE or DELETE statement after Submitting a SELECT statement that retrieves a result of more than 10,000 Rows What can you do? A. On the connection for the SELECT statement, set the deadlock priority to low. B. On the connections for the UPDATE and DELETE statement, set the deadlock priority to low. C. On the connection for the SELECT, set the transaction isolation level to READ UNCOMMITTED. D. Set the query wait configuration option of the SQL Server 5,000. Answer: C 7). The policy table will be accessed and updated by several additional applications, in the policy table, you need to ensure that the value entered into the beginning_effective_date column is less than or equal to the value entered into The ending_effective_date column. What should you do? A. Program each application to compare the values before updating the policy table. B. Create a CHECK constraint on the policy table that compares the values. C. Create a rule and bind the rule to the Beginning_effective_date column. D. Create INSERT and UPDATE triggers on the policy table that compare the values. ANSWER:B 8). The demographic table in the application contains more than 1,000 columns Most of the reports relate to long response times when users are updating or retrieving data from the demographic table. Nearly 90 percent of the users search or update 20 of the columns .The remaining columns are seldom used, but there are important. What should you do to? A. Create a clustered index on the demographic table over the most accessed columns. B. Create a view based on the demographic table, which selects the 20 most accessed columns. C. Divide the data in the demographic table into two new tables ,with one table Containing 20 most accessed columns and the other containing the remaining columns. D. Create a series of stored procedures that select or update the demographic table according to user needs. Answer: C 9). You are implementing a transaction-based application for a Credit card company. More than 10 million vendors accept the Company』s credit card, and more than 100 million people Regularly use the credit card. Vendors around the world must be able to authorize purchases in less than 30 Seconds, 24 hours a day, seven days a week, Additionally, the application must be able to accommodate more vendors in more locations in the future. What should you do? A. .. . B. .. . C. .. . D. Implement an n-tier architecture in which vendor make calls to the geographically dispersed Microsoft Transaction Servers(MTS),which would then obtain an authorization code from geographically dispersed SQL Servers. Answer: D 10). Two SQL Servers supporting two separate applications on your network. Each application uses stored procedures for all data manipulation. You need to integrate parts of the two applications. The changes are limited to a few stored procedures that need to make calls to remote stored procedures. What should you do to implement calls to remote stored procedures? A. Add the remote server as a linked server, Fully qualify the remote procedures names. B. Program each application to connect to both servers. Use the sp_bindsession stored procedures to bind both connections before running any stored procedures that start a transaction. C. Program each application to issue the BEGIN DISTRIBUTED TRANSACTION Statement before each transaction . D. Configure the SQL Server by setting the remote proc trans option to 1. Answer: A 11). You need to produce a list of the five Highest revenue transactions from the Orders table in the Sales database. The Orders table is defined as follows: CREATE TABLE Orders{ Order ID int IDENTITY(1,1) NOT NULL, SalePersonID int NOT NULL, RegionID int NOT NULL, Orderdate datetime NOT NULL, OrderAmount int NOT NULL } Which statement will produce the report? A. SELECT TOP 5 OrderAmount, SalePersonID FROM Orders ORDER BY OrderAmount DESC. B. SELECT TOP 5 OrderAmount, SalePersonID FROM Orders. C. SELECT TOP 5 with TIES OrderAmount, SalePersonID FROM Orders ORDER BY OrderAmount. D. SELECT TOP 5 with TIES OrderAmount, SalePersonID FROM Orders ORDER BY Order ID. Answer: A 12).Your Sales database is accessed by a Micorosoft Visual Basic Client/Server application. The application is not using the Microsoft Windows NT Authentication security model. You write the following stored procedure to be called by the Visual Basic application: CREATE PROCEDURE InsertReinstatedOrder ... ... A User named Andrew assigned to the Sales role. Andrew reports that he is receiving an error message indicating that he is having a permissions problem with the procedure. What must you do to solve the problem? A. Grant permission on the stored procedure specifically to Andrew. B. Add Andrew to the Windows NT Sales group. C. Add Andrew to the Windows NT Administrator group. D. Add Andrew to the db_owner role. Answer: D 13).You are designing a data model to track research projects. You want to accomplish the following goals: ... You design the logical model as shown in the exhibit(Click the exhibit Button) Institution InstitutionID(PK) InstitutionID1 Job JobName(PK) Project ProjectID(PK) Scientist ScientistID(PK) InstitutionID JobName ProjectDetail ProjectID(PK) InstitutionID(PK) Which result or results does this model produce?(choose all that apply) A. All the scientists conducting research for any specific project can be reported. B. A scientist』s job for a specific project in a specific institution can be reported. C. All the institution participating in any specific project can be reported. D. The institution at which a scientist is a staff member can be tracked. E. An institution can be identified as part of another institution. Answer: C,D,E 14)You work for a licensing agency that buys photographs and then sells them to other companies for commercial use... You want to accomplish the following goals: ... You take the following actions: ... Which result or results do these actions produce?(choose all that apply) A. An individual can be identified as a customer, a supplier or an employee, or any combination of the three. B. There is no data redundancy. c. No individual can have the same identification number as an organization. D. An individual can be associated with more than one organization E. An individual can be associated with more than one individual. Answer: B,D,E 15).You are designing an inventory database application for a national automobile sales registry. ... You want to be able to track information about each automobile. You want to normalize your database. ... Which tables should be included in the database application(choose all that apply) A. a table containing the list of all dealerships along with the address and identification number for each dealership. B. a table containing the contact information for each automobile manufacturer along with the name of each model manufactured by each manufacturer. C. a table containing the name and address of each dealership along with automobile information D. a table containing identification number for each automobile, the owning dealership』s identification number, and other information specific to each automobile Answer: A,D 16).You are designing the data model to maintain information about students living in a group home. You want to accomplish the following goals: ... You design the logical model as shown in the exhibit(click the exhibit) Student StudentID(PK) StudentAlias StudentID(PK) Alias(PK) StudentEvent StudentID(PK) EventName(PK) FamilyRelationship StudentID1(PK) StudentID2(PK) Address AddressID(PK) StudentID Which result or results do this model produce?(choose all that apply) A. Any kind of descriptor can be associated with a student. A. Multiple addresses can be associated with multiple students, and that address usage can be reported. C. Any family relationship with another student can be reported. D. All known aliases for a student can be reported. E. Significant events in a student』s life can be reported. Answer: C,D,E 17).You are creating a table named recruit to track the status of potential new employees. The SocialSecurityNo column must not allow null values. However, a value for a potential employee』s Social Security number is not always known at the time of initial Entry. You want the database to populate the SocialSecurityNo column with a value of UNKNOWN when a recruiter enters the name of a new potential Employee without a Social Security number. How can you accomplish this task? A. Create a rule on the SocialSecurityNo column. B. Create a default definition on the SocialSecurityNo column. C. Create a CHECK contraint on the SocialSecurityNo column. D. Create a user-defined data type. apply it to the SocialSecurityNo column, and bind a rule to the user-defined data type. Answer: B 18).You are developing a Personnel database for your company. This database includes an employee table that is defined as Follows: CREATE TABLE employee{ ID int IDENTITY NOT NULL, Surname varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, SocialSecurityNo char(10) NOT NULL, Extention char(4) NOT NULL, EmailAddress varchar(255) NOT NULL} Each employee must have a unique telephone extension number and a unique e-mail address. In addition, you must prevent duplicate Social Security numbers from being entered into the database How can you alter the table to meet all of the requirements? A. ALTER TABLE employee ADD CONSTRAINT u_nodups UNIQUE NONCLUSTERED (SocialSecurityNo, Extention, EmailAddress). B. ALTER TABLE employee ADD CONSTRAINT u_nodups UNIQUE CLUSTERED (SocialSecurityNo, Extention, EmailAddress). C. ALTER TABLE employee ADD CONSTRAINT u_nodupssn UNIQUE NONCLUSTERED (SocialSecurityNo ). ALTER TABLE employee ADD CONSTRAINT u_nodupext UNIQUE NONCLUSTERED (Extension) ALTER TABLE employee ADD CONSTRAINT u_nodupemail UNIQUE NONCLUSTERED (EmailAddress). D. Alter table employee Add constraint u_nodupssn check... Answer: C 19).Your database is used to store information about each employee department. An employee can work in only one department. The database contains two tables, which are named Department and Employee. The tables are modeled as shown in the exhibit(click the exhibit) Department DepartmentNo(PK) DepartmentName Employee EmployeeID(PK) FirstName LastName SocialsecurityNo DepartmentNo DepartmentName Phone You want to ensure that all data stored is dependent on the whole key Of the table in which data is stored. What should you do ? A. Add an EmployeeID column to the Department table. B. Remove the SocialSecurityNo column from the Employee table. C. Remove the Departmentname column from the Employee table. D. Remove the Departmentname column from the Departmenttable. Answer: C 20).You are designing a distributed data model for an international importing and exporting company. You take the following actions: ... Which result or results does these actions produce?(choose all apply) A. All sales record primary keys are unique throughout the distributed database. B. A sales record created in London or Nairobi includes a value-added tax,but that a sales record created in Cairo does not. C.Local sales amounts can be calculated in United States dollars at the time of the sale. D. Local sales amounts can be calculated in United States dollars at the end of the month. E. The difference between the values of a sale United States dollars at the time of the sale and the value at the end of the month can be calculated. Answer: A,B 21).You must write a stored procedure to perform cascading deletes on the HomeLoan database. The client application will pass a parameter containing the CustomerID of the customer to be deleted. Customer CustomerID(PK) LastName FirstName AddressID StatusID Country Loan LoanID(PK) LoanDate LoanStatusID AppraisalDate AppraisalAmount LoanAmount CustomerID Inspection InspectionID(PK) LoanID InspectionResultID InspectionDate InspectionTypeID Appraisal AppraisalID(PK) LoanID AppraisalDate AppraisalAmount AppralsorID Which stored procedure should you use? A. CREATE PROCEDURE LoanCascadeDelete @customerID int AS DELETE FROM Appraisal FROM Appraisal JOIN Loan ON Appraisal.LoanID=Loan.LoanID WHERE customerID=@customerID DELETE FROM Inspection FROM Inspection JOIN Loan ON Inspection.LoanID=Loan.LoanID WHERE customerID=@customerID DELETE FROM Loan WHERE customerID=@customerID DELETE FROM Customer WHERE customerID=@customerID B. ... C. ... D. ... Answer: A (A是級聯刪除的標准寫法) 22).You have a database that is accessed by many different applications complies with ANSI 92 written with many different development tools. Each application uses a different mechanism for accessing the database. Transaction processing in not uniform across all your applications. How Can you ensure that all applications handle transactions in the some Fashion? A. Inside the database,create an application role,Program all applications to activate the application role. B. Program all applications to issue the SET ANSI_DEFAULTS ON command Immediately after establishing a user connection. C. Configure the SQL Server by using the sp_configure 『user options』 Stored procedure to turn on the following options:implicit_transactions,cussor_close_on_commit, ansi_warnings, ansi_padding, ansi_nulls,quoted_identifier, ansi_null_dflt_on. D. Create a stored procedure that sets all the users seesion properties Run the sp_procoption stored procedure to mark your procedure for startup. Answer: B 23).Your database includes tables that are defined as follow: create table SalesPerson( SalesPersonID int identity(1,1) NOT NULL PRIMARY KEY NONCLUSTERED, RegionID int NOT NULL, Lastname varchar(30) NULL, Firstname varchar(30) NULL, Middlename varchar(30) NULL, AddressId int NULL) create table Orders( OrderID int identity(1,1) NOT NULL PRIMARY KEY NONCLUSTERED, SalePersonID int NOT NULL, RegionID int NOT NULL, Orderdate datetime NOT NULL, OrderAmount money NOT NULL) You need to produce a list of the highest sale for each salesperson on September 15,1998. The list id to be printed in the following: LastName FirstName OrderDate OrderAmount Which query will accurately produce the list? A. SELECT s.LastName,s.FirstName,o.OrderDate,OrderAmount FROM salesperson AS s LEFT OUTER JOIN Orders AS o ON o.SalePersonID=s. SalesPersonID WHERE o.OrderDate=』09/15/1998』 and OrderAmount in (select MAX(OrderAmount) from Orders) B. SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount) FROM salesperson AS s LEFT OUTER JOIN Orders AS o ON o.SalePersonID=s.SalesPersonID AND o.OrderDate=』09/15/1998』 GROUP BY s.LastName,s.FirstName,o.OrderDate C.SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount) FROM salesperson AS s INNER JOIN Orders AS o ON o.SalePersonID=s.SalesPersonID WHERE o.OrderDate=』09/15/1998』 GROUP BY s.LastName,s.FirstName,o.OrderDate,o.OrderID D.SELECT s.LastName,s.FirstName,o.OrderDate,MAX(OrderAmount) FROM salesperson AS s INNER JOIN Orders AS o ON o.SalePersonID=s.SalesPersonID WHERE o.OrderDate=』09/15/1998』 And OrderAmount in (select MAX(OrderAmount) from Orders) Answer: B 24)You increase the number of users of your customer application from 20 to 120 .With the additional users, the response time when retrieving and updating has slowed substantially You examine all the queries and indexes and discover that they are all fully optimized. The application seems to run properly as long as Number of users is less than 50 What can you do to resolve the problem? A. Ensure that table hints are used in key queries to force the use of the correct table index B. Increase the LOCK_TIMEOUT setting to accommodate the long response Times now being encountered. C. Free up dirty pages in memory by configuring the SQL Server with a short recovery interval. D. Ensure that application is using an optimistic locking strategy Instead of a pessimistic locking strategy. Answer: D 25).You have a database that keeps track of membership in an organization.Tables in this database includes the membership table, the Committee table,the Address table,and the Phone table,When a person resigns from the organization,you want to be able to delete membership row and have all related rows be automatically removed. What can you do to accomplish this task? A. Create a DELETE trigger on the Membership table that deletes any rows in Committee, Address ,Phone table that reference the primary key in the Membership table Do not place a FORDIGN KEY constraints on the Committee, Address, Phone table. B. Create a DELETE trigger on the Membership table that deletes any rows in Committee, Address ,Phone table that reference the primary key in the Membership table place a FOREIGN KEY constraints on the Committee, Address ,Phone table. C. Place a PRIMARY KEY constraints on the Membership table with FOREIGN KEY constraints on the Committee, Address ,Phone table. D. Place a PRIMARY KEY constraints on the Membership table Place FOREIGN KEY constraints on the Committee, Address, Phone table that reference the primary key in the Membership table. Create DELETE trigger on the Committee, Address, Phone table will Fire when their FOREIGN KEY constraints are violated. Answer: A 26). Your database includes an Orders tables that are defined as follow: create table Orders{ OrderID int identity(1,1) NOT NULL PRIMARY KEY NONCLUSTERED, SalePersonID int NOT NULL, RegionID int NOT NULL, Orderdate datatime NOT NULL, OrderAmount money NOT NULL} You have written a stored procedure named GetOrders. You must change the stored procedure to produce a list of orders in order first by RegionID ,then by SalePersonID.Permissions have been granted on the stored procedure,and you do not want to have to Grant them again. How must you change the stored procedure? A. DROP PROCEDURE GetOrders GO CREATE PROCEDURE GetOrders AS SELECT SalesPersionID,RegionID,OrderID,OrderDate OrderAmount From Orders ORDER BY RegionID, SalePersonID B. ALTER PROCEDURE GetOrders AS SELECT SalesPersionID,RegionID,OrderID,OrderDate OrderAmount From Orders ORDER BY RegionID, SalePersonID C. ALTER PROCEDURE GetOrders AS SELECT SalesPersionID,RegionID,OrderID,OrderDate OrderAmount From Orders D.DROP PROCEDURE GetOrders GO CREATE PROCEDURE GetOrders AS SELECT SalesPersionID,RegionID,OrderID,OrderDate OrderAmount From Orders Answer: B 27). Your database includes a table that is defined as follow: create table SalesInformation{ SalesInformation ID int identity(1,1) NOT NULL SalePersonID int NOT NULL, RegionID int NOT NULL, ReceiptID int NOT NULL, SalesAmount money NOT NULL} You want to populate the table with data from an existing application That has a numeric primary key.In order to maintain the referential Integrity of the database, You want to preserve the value of the original primary key when you convert the data. What can you do to populate the table? A. Set the IDENTIEY_INSERT option to OFF, and then insert the data by using a SELECT statement that has a column list. B. Set the IDENTIEY_INSERT option to ON, and then insert the data by using a SELECT statement that has a column list. C. insert the data by using a SELECT statement that has a column list, and then alter the table to add the foreign key. D. insert the data by using a SELECT statement that has a column list, and then alter the table to add the primay key. Answer: B 28). You have a database to keep track of sales information. You are working with a nested procedure that will pass a parameter back to the calling procedure containing the total sales as follows: CREATE PROCEDURE GetSalesPersonData @SalesPersonID int, @RegionID int, @salesAmount money OUTPUT AS SELECT @salesAmount=SUM(salesAmount) FROM SalesInformation WHERE @SalesPersonID=SalesPersonID Which statement will accurately execute the procedure and receive the value? A. EXECUTE GetSalesPersonData 1,1 NULL B. EXECUTE GetSalesPersonData @SalesPersonID=1, @RegionID=1, @salesAmount=0 C. EXECUTE GetSalesPersonData 1,1, @salesAmount OUTPUT D. EXECUTE GetSalesPersonData @SalesPersonID=1, @RegionID=1, @salesAmount=NULL Answer: C 29)You have an application that captures real-time stock market information and generates trending reports. In the past, the reports are generated after the stock markets closed. The reports now need to be generated on demand during trading hours. What can you do so that reports can be generated without affecting the rest application? (choose two) A. Program the application to issue the following command before generating a report. Set transaction isolation level read uncommitted B. Program the application to issue the following command before generating a report. Set transaction isolation level serializable C Require the application to include the NOLOCK table hint when generating a report. D Require the application to include the TABLOCKX table hint when generating a report. E. On the stock transaction tables, create triggers that update summary tables instead of performing a data analysis each time a report is generated. F. Declare global scrollable cursors on the stock transaction tables. Answer: A,C 30).You server named Corporate has a Sales database that stores sales data for a software distribution company, Two remote servers named NewYork and Chicago each store sales data in a salesorder table relative only to their respective sales territories. The salesorder table on the Corporate server is updated once a week with data from the remote servers. The salesorder table on each server including Corporate is defined as follows: CREATE TABLE salesorder{ Number char(10) NOT NULL, CustomerName varchar(100) NOT NULL, TerritoryName varchar(50) NOT NULL, EntryDate datetime NOT NULL, Amount money NOT NULL} You need to create a view that shows a current list of all sales from the NewYork And Chicago sales territories, and the list should have the following format Territory Customer Date Amount Which view can you create to show all sales from the NewYork and Chicago sales territories in the required format? A. CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount) AS SELECT TerritoryName , CustomerName, EntryDate, Amount FROM Corporate.Sales.dbo.salesorder WHERE TerritoryName=』 NewYork』OR TerritoryName=』 Chicago』 B. CREATE VIEW SalesSummary_view AS SELECT NY.TerritoryName , NY.CustomerName, NY.EntryDate, NY.Amount, CHI.TerritoryName , CHI.CustomerName, CHI.EntryDate, CHI.Amount FROM NewYork.Sales.dbo.salesorder NY, Chicago.Sales.dbo.salesorder CHI C. CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount) AS SELECT NY.TerritoryName , NY.CustomerName, NY.EntryDate, NY.Amount, CHI.TerritoryName , CHI.CustomerName, CHI.EntryDate, CHI.Amount FROM NewYork.Sales.dbo.salesorder NY, Chicago.Sales.dbo.salesorder CHI WHERE NY.Number=CHI.Number D.CREATE VIEW SalesSummary_view (Territory,Customer,Date,Amount) AS SELECT TerritoryName , CustomerName, EntryDate, Amount FROM NewYork.Sales.dbo.salesorder UNION ALL SELECT TerritoryName , CustomerName, EntryDate, Amount FROM Chicago.Sales.dbo.salesorder Answer: D 31). Your database includes a table named product. The procduct table currently has clustered index on the primary key of the product_id column. There is also a non clustered index on the description column. You are experiencing very poor response times when querying the product table. Most of the queries against the table include search arguments On the description and product_type columns. Because there are many Values in the size column for any product, query result sets usually contain between 200 and 800 rows. You want to improve the response times when querying the product table. What should you do? A. Use SQL Server Enterprise Manger to create nonclustered indexes on each column being referenced by each SELECT statement. B. Use SQL Server Enterprise Manger to generate stored procedure for the product table. C. Use the index Tuning Wizard to identify and build any missing Indexes. D. Use SQL Server Profiler to capture performance statistics of queries against the product table Answer: C 32).You are building a decision support system(DSS)database for your company. The new database is expected to include information from existing data sources that are based on Microsoft Access, dBaseIII , Microsoft Excel, and Oracle. You want to use SQL Server Agent to run a scheduled job to extract Information from the existing data source into a centralized database On SQL Server 7.0.You do not want to perform any additional programming Outside the SQL Server environment How must you extract information from the existing data sources A. Use the bulk copy program to import the information from all data Source. B. Use the xp_cmdshell extended stored procedure to execute the Microsoft Windows copy command-line command/ C. Create Data Transaction Services package to import data from each data source. D. Create export files for each data source. Answer: C 33)You automate the backup and recovery process for your database application. After the database is restored, you discover that queries that use the FREETEXT and CONTAINS keywords no longer return the expected rows. What should you do? A. Alter the queries to use the LIKE keyword instead of the FREETEXT and CONTAINS keywords. B. Alter the queries to use the FREETEXTABLE and CONTAINSTABLE keywords instead of the FREETEXT and CONTAINS keyword. C. Add the database』s full-text catalog to both the backup job and the recovery job. D. Add a job to the restoration process to re-create and populate the full-text catalog. ANSWER: D 34) you are working on a data conversion effort for a Sales database. You have successfully extracted all of the existing customer data into a tab_delimited flat file. The new customer table is defined as follows: CREATE TABLE customer( Id int IDENTITY NOT NULL, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, Phone varchar(15) NOT NULL, Email varchar(255) NULL) You need to populate this new table with the customer information exists in a tab_delinited flat file with the following format: Name Phone E-mail Adam Barr 555-555-1098 abarr@adatum.com Karen Berge 555-555-7868 kberge@woodgrovebank.com How can you transfer the data to accurately populate the customer table? A. Import the data by using the bcp utility with the /E option to accommodate the column that has the IDENTITY property. B. In the flat file, separate the Name column into FirstName and Lastname columns by using a bcp format file, and then import the data by using the bcp untility. C. Import the data by using Data Transformation Services with the Transform information as it is copied to the destination option button selected. D. Import the data by using Data Transformation Services with the Enable identity insert check box selected. Answer: C 35) You are developing an application for a worldwide furniture wholesaler. You need to create an inventory table on each of the databases located in New York,Chicago,Paris,London,San Francisco, and Tokyo. In order to accommodate a distributed data environment, you must ensure that each row entered into the inventory table is unique across all locations. How can you create the inventory table? A. CREATE TABLE inventory( Id int IDENTITY NOT NULL CONSTRAINT pk_inventory_id PRIMARY KEY NONCLUSTERED, ItemName varchar(100) NOT NULL, ItemDescription varchar(255) NULL, Quantity int NOT NULL, EntryDate datetime NOT NULL) B. CREATE TABLE inventory( Id uniqueidentifier NOT NULL DEFAULT NEWID(), ItemName varchar(100) NOT NULL, ItemDescription varchar(255) NULL, Quantity int NOT NULL, EntryDate datetime NOT NULL) C. CREATE TABLE inventory( Id int NOT NULL CONSTRAINT u_inventory_id UNIQUE CLUSTERED(Id), ItemName varchar(100) NOT NULL, ItemDescription varchar(255) NULL, Quantity int NOT NULL, EntryDate datetime NOT NULL) D. CREATE TABLE inventory( Id int NOT NULL CONSTRAINT pk_inventory_id PRIMARY KEY CLUSTERED, ItemName varchar(100) NOT NULL, ItemDescription varchar(255) NULL, Quantity int NOT NULL, EntryDate datetime NOT NULL) Answer: B 36)You need to create a development database that will hold 20 MB of data and indexes and a 4-MB transaction log. There are no concerns regarding query performance or log placement with this database. The SQL Server was installed on drive E of the server computer, and there is plenty of disk space on drive E. How should you create the database? A. disk init name=』development1』,physname= 『 e:\mssql7\data\development1.dat』,vdevno=45,size=10240 disk init name=』developmentlog1』,physname= 『 e:\mssql7\data\developmentlog1.dat』,vdevno=46,size=2048 CREATE DATABASE development on developement1=20 log on Developmentlog1=4 B. CREATE DATABASE development on default=20 C. CREATE DATABASE development on primary(name=development1, Filename=』 e:\mssql7\data\development1.mdf』,size=20MB) log On (name=developmentlog1,filename= 『 e:\mssql7\data\developmentlog1.ldf』,size=4MB) D. CREATE DATABASE development on (name=developmnet1,filename =』e:\mssql7\data\development1.mdf』,size=24MB) Answer: C 37)You must reconcile the checking account for your company. You have a CheckRegister table, an InvalidCheck table, and a ClearedCheck table. The ClearedCheck table lists checks that have cleared the bank. You must update the ClearedDate column of the CheckRegister table for any checks that are on the ClearedCheck table. If the check is in the ClearedCheck table but not on the CheckRegister table, you must insert a row into the InvalidCheck table. If the amount shown for a check in the ClearedCheck table is different from the amount shown for the same check in the CheckRegister table, you must insert a row into the InvalidCheck table. Each row must be deleted from the ClearedCheck table after it has been evaluated for accuracy. Which statement group should you use to accomplish this task in the shortest time? A. ... B. ... C. UPDATE CheckRegister SET CheckRegister.ClearedDate = ClearedCheck.ClearedDate FROM CheckRegister JOIN ClearedCheck ON CheckRegister.CheckNumber = clearedCheck.CheckNumber AND CheckRegister.CheckAmount = ClearedCheck.CheckAmount DELETE ClearedCheck FROM ClearedCheck JOIN CheckRegister ON CheckRegister.CheckNumber = ClearedCheck.CheckNumber AND CheckRegister.CheakAmount = ClearedCheck.CheckAmount INSERT InvalidCheck (CheckNumber, CheckAmount, ClearedDate) SELECT CheckNumber, CheckAmount, ClearedDate FROM ClearedCheck DELETE ClearedCheck D. ... Answer: C 38)Your company stores its sales information in a SQL Server 7.0 database To identity the amount of sales per product in each shipped to customers In Montreal in 1998.you create the following query: SELECT p.ProductID.o.OrderID.p.ProductName.Total=p.UnitPrice*od.Quantity FROM Products p JOIN [Order Details] od ON p.ProductID=od.ProductID JOIN Order o ON od.OrderID=o. OrderID WHERE O.ShipCity=』Montreal』AND DATEPART(yy.o.ShippedDate)=1998 ORDER BY p.ProductName Which of the following execution plans is generated for this query? A). B). C). D). ANSWER:A 此題的答案是A,希望能通過這題看懂意思。 在考試時的題目和此題有點不同,考試時你應選B。 39).Your database application includes a complex stored procedure that displays status information as it processes transactions. You obtain unexpected results in the status information when you run the stored procedure with certain input parameters. You want to use SQL server profiler to help find the problem in your stored procedure. Which four event should you track?(choose four) A. sqltransaction B. sp:stmtstarting C. sp:stmtcompleted D. sql:stmtstarting E. sql:stmtcompleted F. scan:started G. object:opened ANSWER: B,C,D,E 40).You are the database developer for a leasing company Your leasing database includes a lessee table that is defined as Followings: CREATE TABLE lessee{ ID int IDENTITY NOTNULL CONSTRAINT Pk_lessee_id Primary key Non inclustered Surname Vchar(50) NOTNULL First Name Vchar(50) NOTNULL SocialSecurity Char(9) NOTNULL Credit Rating Char(10) NULL Credit Limit money NULL} each social security number must be unique. You want the data to be physically stored in order by social security number. Which constraint should you add to the socialsecurityno column on the lessee table? A. A unique clustered constraint B. A unique nonclustered constraint C. A Primary key Clustered constraint D. A Primary key NonClustered constraint ANSWER: A 41).You are implementing a logical data model for an online transaction processing(OLTP) application.One entry from the logical models in third normal form and currently has 10 attributes. One attribute is the Primary Key.Six of the attributes are Foreign Key references into six other entries. The last three attributes represent columns that hold numeric values. How should this entry from the logical at model be implemented? A. Create a table by denormalizing the entry. Add the information from the six Foreign Key references as additional columns of the table. B. Create two tables by denormalizing the entry. Add the Primary Key And the three numeric values as columns of one table.Add the Primary Key and information from the six Foreign Key references as additional columns of other tables. C. Create the table as described in the logical data model. D. create a view that joins the six foreign key references. ANSWER:C 42).You are design a database that will be used to store information about tasks assigned to various employees.Each tasks is assigned to only one employee. The database contains a table named Task that is modeled as shown in the exhibit. You want to use a Primary Key constraint to uniquely identity each row in the Task table. On which columns or columns should you define the Primary Key constraint (choose all the apply) table: Task Taskno Employeeno Title Description Status Datecompleted A. Task No. B. Emplyee No. C. Status. ANSWER: A,B,C 43).You are building a new database for the human resources department of a company.There are ten departments within the company,and each department contains multiple employee in addtion.each employee might work for several departments. How should you logically model the relationship between the department entry and the employee entry. A.Create a mandatory one-to-many relationship between the department entry and the employee entry. B. Create an optional one-to-many relationship between the department entity and the employee entity. C. create a new entity, create a one-to-many relationship from the employee entity to the new entity, and the create a one-to-many relationship from the department entity to the new entity. D. Create a new entity, create a one-to-many relationship from the new entity to the employee entity, and then create a one-to-many relationship from the new entity to the department entity. ANSWER:C 44). You are building a database for the human resources department of your company. You want to eliminate duplicate entry and minimize data storage wherever possible. You want to track the following information for each employee. Which table or tables should you use? A. Employee EmployeeID ManageremplyID FirstName MiddleName LastName Address Date of hire Department Salary B. Employee EmployeeID FirstName MiddleName LastName Address Date of hire Department Salary Manager ManagerID FirstName MiddleName LastName C. Employee EmployeeID ManagerID FirstName MiddleName LastName Address Date of hire Department Salary Manager ManagerID FirstName MiddleName LastName D. Employee EmployeeID FirstName MiddleName LastName Address Date of hire Department Salary Manager ManagerID FirstName MiddleName LastName Employee Manager EmployeeID ManagerID ANSWER:A 45). You are designing a data model to track purchases for a fish canning company. You take the following actions 1.Create the data model as shown in the exhibit. ... Which results does these actions produced (choose all the apply) A. all purchase amounts are rounded to the nearest whole dollar. B. Record for tuna purchased must be in multiples of 100, and records for salmon purchased must be in multiples of 50. C. The daily total quantity of salmon purchased cannot exceed 150. D. The monthly government report can be produced reporting the kind, quantity, and supplier for each lot of tuna or salmon purchased. ANSWER:A,C,D Company CompanyID(PK) Purchase PurchaseID(PK) PurchaseID PurchaseDate Fish FishName PurchaseDetail PurchaseID(PK) LineNumber(PK) FishName FishQuantity Purchaseamount 46). You are implementing a logical data model for a decision support system(DSS)database. Two of the tables in the model have a parent/child relationship. The parent table is expected to have more than 1 million rows. The child table is expected to have more than 100 million rows. Most reports present aggragate child information grouped accounting to each parent row reports containing detailed child table information are occasionly needed. How should tables be implemented? A. Create the parent table and the child table as described in the logical data model B. Create the parent table that include aggregate child information, do not create the child table. C. create the parent table that include aggregate child information, Create the child table as it exists in the logical data model D. in child table, create a nonclustered index that includes any columns that are aggregated and the foreign key reference to the parent. ANSWER:C 47).Your are building an invocing system for your company. there can be multiple parts on one invoice, cost and price information comes from a master list but the history store for each invoice should show the cost and price at time of sale you want to make sure your database is properly normalized. You want to accomplish the following goals 1. Every table must have a Primary Key. 2. All non-key columns must depend on the whole Primary Key. 3. All columns must contain exactly one value 4. Each column in a table must be independent of any non-key column in the same table You create the logical model as show in the exhibit Which result or results does this model produce(choose all the apply) Part PartNo(PK) PartDescription Unitweight UnitCost UnitPrice Orders OrdersNo(PK) OrdersDate Customer CustomerNo(PK) CustomerName CustomerAddress1 CustomerAddress2 City Territory Postal Code OrderDetails OrdersNo(PK) ItemNo CustomerNo PartNo PartDescription QuantitySold Unitweight UnitCost UnitPrice A. every table has a primary key B. all non-key columns depend on the whole primary key C. all columns contain exactly one value D. each column in a table is independent of any non-key column in the same table. ANSWER:A,C 48).Your database include a Salesperson table that tracks various data including the sales goals and actual sales for individual salespeople. The sales manager wants a report containg a list of the five least Productive salepeople,along with their goals and their actual sales Production.You will use an ascending sort to order the information In the report by actual sale production. What should you do to produce report? A. Issue a set rowcount 5 statement before issuing a select statement against the salesperson table B. Include a Top 5 clause in the select list against the salesperson table C. Issue a set query_governor_cost_limit 5 statement before issue select list against the salesperson table. D. Count the row returned by using a having count(*)<=5 clause. ANSWER:B 49).You development team has just developed tested and developed a new accounting application that including many integrated modules.Users frequently encounter deadlocks whenever someone performs a function that integrates data from multiple modules.The development team never encounted deadlocks when unit testing the application. What can you do to minmize deadlock? A. Set the deadlock priority to low B.issue the begin distributed transaction statement whenever data modifications involve data in different modules C.ensure that all transactions modify tables in the same order. D.Ensure that all tables have indexes on primary and foreign keys. ANSWER: C 50).You issure an UPDATE statement and then run a select query to verify that the update were accurate .You find out the UPDATE statement was excecuted properly.However,the next time you log on to the SQL Server computer.It appears that your UPDATE statement was not excecuted What is the most likely cause the problem? A.The FMTONLY option is set to on B.the showplan_all option is set to on C.the parseonly option is set to on D.the implicit_transactions option is set to on ANSWER: D 51)You are investigating reported problems regarding the performance of a query in your database .The WHERE clause of the query includes search arguments on ColumnA, ColumnB, ColumnC You analyze the data and discover that the content of ColumnA is nearly identical in all rows.The content of ColumnB is the same in about 50 percent of the rows. The content of ColumnC is the same in about 10 percent of the rows How should you index the table to improve query performance? A.create a composite clustered index on columnA,columnB,columnC B.create a composite clustered index on columnC,columnB,columnA C.create a composite nonclustered index on columnA, columnB, columnC D.create a composite nonclustered index on columnC, columnB, columnA E.create a clustered indes on columnA, create separate nonclustered indexes on columB and columnC F.create a separate nonclustered indexes in each column. ANSWER:B 52).Your shipping company has a database application that maintains an invetory of items on each vessel.When each vessel is unloaded at its destination.The invetory is counted and the arrived-quantity column is updated in the database. There can be thousands of vessels en route at any one time. Each shipments id identified by a shipment_id.Each vessel can carry Thousands of items.Each item in a shipment is identified by an item_number. You want to make sure the update of the arrived-quantity column is as fast as possible . What should you do ? A. create a nonclustered index on the shipment_id column, the item_number column, and the arrived_quantity column. B. create a clustered index on the shipment_id column, the item_number column, and the arrived_quantity column. C. Create a clustered index on the shipment_id column and item_number column. D. Create a nonclustered index on the shipment_id column and item_number column. ANSWER:C 53).You have a database that contains information abuot publications for sale.You want to write a full-text search query that will search through all the columns in one table enabled for full-text querying. The table includes a column named titles. a column named price and a column named notes.The titles and notes column are full-text enabled. you want to find all publications that deal with French gourmet cooking, which contains statement should you use? A. where contains(*, 』」French gourmet」』) B. where contains(notes, 』」French gourmet」』) C. where contains(titles, 』」French gourmet」』) D. where contains(price, 』」French gourmet」』) ANSWER: A 54)You need to create two new tables for your Purchaseing database The new tables will be named Purchaseorderheader and Purchaseorderline The Purchaseorderheader table will have the PurchaseorderheaderID columns as the Primary Key.A Purchaseorderline row must not exist without a corresponding Purchaseorderheader row. How can you create the tables? A. Create both tables and then use the ALTER TABLE statement to create a CHECK constraint on Purchaseorderline Primary Key column. B. Create both tables, and then use the the alter table statemetn to create a check constraint on the purchaseorderheader primary key column C. Create the purchaseorderheader table and then create the purchaseorderline table that has a foreign key constraint referencing the purchaseorderheader table D. Create the purchaseorderline table, and the create the purchaseorderheader table that has a foreign key constraint referencing the purchaseorderline table ANSWER:C 55).You have a database that is used for storing the text of speeches given by certain government officials. The text of each speech is stored in the speeches table that is defined as follows: CREATE TABLE Speeches{ SpeechID Char(32), SpeechText Text, AuthorID Char(32)} GO A full-text indexs exist for all columns in the speeches table .You want to search for a speech that includes the phrase 「 Four score and seven years ago」 which query should you use to perform this search? A. where speechid like 『% Fore Score and only %』 B. where speechtext like 『% Fore Score and only %』 C. where freetext (speechid, 『 Fore Score and only 『』) D. where freetext (speechtext, 『 Fore Score and only 『』) ANSWER:D 56).You need to create a 6-GB online transaction processing(OLTP)database,Your SQL Server computer has 2 disk controllers and each controller has 4 6-GB hard disk drivers.Each hard disk drive is configured as a separate NTFA partion.Microsoft WindowsNT .The Microsoft WindowsNT swap file and SQL Server are all installed on drive C .The remaining drivers,which are labled as drive D through J are empty. How should you create OLTP database? A. Create the data portion of the database as a single file on D, create the log on E B. Create the data portion of the database as six separate files on D through I, with one file on each drive, create log as a single file on J C. Create the data portion of the database as 24 separate files on D through I, with four files on each drive, create the log as a single file on J D. Create the data portion of database as four separate files on D through G, with one file one each drive, create the log as thress separate files on H through J ANSWER:B 57).Department managers in your company want to use Microsoft Excel pivot tables analyze data from your SQL Server database.You need to extract data from tables in the database to an Extent spreadsheet so that managers can copy the spreadsheet and build pivot tables The data in database changes frequently and you want to automate the Process of updating the Excel spreadsheet. you plan to use a sql server agent scheduled job to automation the extraction of the data to the spreadsheet. What should the scheduled job execute? A. a bulk copy program script to create a tab_delimited text file B. a bulk copy program script to populate the spreadsheet C. a data transformation services export package to create a tab_delimited text file D. a data transformation services export package to populate the spreadsheet ASNWER:D 58).Your users report that your database application takes an excessive amount of time to complete an operation.Overtime.with the addition of new rows and changes to existing rows the situation has worsened. You suspect that the tables are not optimally indexed You plan to use the SQL Server Profiler Create Trace wizard to find out the cause of the problems What should you use the create Trace Wizard to do (choose two) A. Find the worst performing queries B. identify scans of large tables C. identify the cause of a deadlock D. profile the performance of a store procedure E. trace T-SQL activity by application F. trace T-SQL activity by user ANSWER: A,B 59) You run the DBCC SHOWCONTIG statement, the statement provides the following output. ---Page scanned ------------------------------158 ---Extends scanned ---------------------------21 ---Extend Switch------------------------------20 ---Avg.Pages per Extent ----------------------7.5 ---Scan Destiny(Best Count:Actual Count) -----95.24%(20:21) ---Extent scan Fragmentation------------------4.76% --- Avg.Bytes Free per page ------------------408.4 --- Avg.Page Destiny(full) -------------------94.95% What does this output tell you about how the data is stored ? (choose all the apply) A. The table is not externally fragmented B. the table is not internally fragmented C. the number of extent switches is excessives D. the rowsize does not efficiently fit on a page E. the IAM page does not reflect the actual extent usage ANSWER:A,B 60).You add new fuctionally to an existing database application. After the upgrade.Users of the application report slower performance . The new fuctionally executes multiple store procedure and dynamic SQL statement.You want to be able to identity specific queries That are encountering excessively long execution times. What should you do ? A. Run the sql server undex tuning wizard to retune the indexes for the new functionality B. create a sql server profiler trace that uses the minimum execution time and application filters C. use the current activity dialog box of the sql server enterprise manager to list all current user tasks and object blocks. D. Use the sp_monitor store procedure to monitor the cpu_busy and io_busy column before and after testing the new fuctionally. ANSWER:B 61).You are troubleshooting a process that makes use of multiple complex stored procedure that operate on a table. The process is producing an unexpected update to the table You want to identity the specific stored procedure and statement that are causing the problems. What should you do ? A. Use sql server profiler to create and reply a trace by using single stepping. B. place a trigger on the table to send an e-mail message when a column is set to a specific value C. execute the store procedure』s create procedure statement to verify that the syntax is still valid D. Examine the transaction log locate the statement that make the unexpected update to the table ANSWER:A 62). You run the DBCC SHOWCONTIG statement on the sales table and receive The following output: ---Page scanned ------------------------------1657 ---Extends scanned ---------------------------210 ---Extend Switch------------------------------1528 ---Avg.Pages per Extent ----------------------7.9 ---Scan Destiny(Best Count:Actual Count) -----13.60%(208:1529) ---Extent scan Fragmentation------------------97.43% --- Avg.Bytes Free per page ------------------2843。5 --- Avg.Page Destiny(full) -------------------64.87% What should you do to improve the response times for querise? A. Update the statistics on the Sales table B. Create additional statistics on the Sales table. C. Run the DBCC DBREINDEX statement on the Sales table. D Run the DBCC CHECKTABLE statement on the Sales table. ANSWER: C 63). You run the DBCC SHOWCONTIG statement on the table and receive The following output: ---Page scanned ------------------------------354 ---Extends scanned ---------------------------49 ---Extend Switch------------------------------253 ---Avg.Pages per Extent ----------------------7.2 ---Scan Destiny(Best Count:Actual Count) -----17.79%(45:94) ---Extent scan Fragmentation------------------82.21% --- Avg.Bytes Free per page ------------------485.2 --- Avg.Page Destiny (full) -------------------94.01% what should you do to improve the query performance ? A. Update the statistics on the clustered index. B. Change the row size to fit efficiently on a page. C. Rebulid the clustered index with a fill factor value set to 100. D. Rebulid the clustered index with a fill factor value set to 25. E. Rebulid the clustered index with a fill factor value set to 75. ANSWER: E 64)You have an accounting application that allows users to enter information into a table named staging when data entry is complete a batch job users . the rows in the staging table to update a table named prodution Each users rows in the staging table are identified by the user』s SQL Server Process ID number in a column named spid the code for the batch job that updates the production table is: declare @count int begin tran select @count=count(*) from production p join staging a on p.account=s.account where s.spid=@@spid update p set amount=s.amount from production p join staging s on p.account=s.account where s.spid=@@spid if @@rowcount<>@count rollback tran else commit tran You find out that there have been locking problems when two users run the batch job at the same time.What should you do to solve the locking problems? A. Program the accounting application to set the transaction isolation level to SERIALTABLE before running the batch job. B. Program the accounting application to set the transaction isolation level to READ UNCOMMITTED before running the batch job. C. program the accounting application to set the deadlock priority to normal before running the batch job D. program the accounting application to set the deadlock priority to low before running the batch job E. inclued the table hit WITH ROWLOCK UPDLOCK when counting the rows in the production table. F. inclued the table hit WITH TABLOCKX when counting the rows in the production table. ANSWER: E 65).You are implementing a logical data model.All of the tables in your logical data model are normalized to at least third normal form There are no surrogate Primary keys in any of the tables.Some table relationships involve up to eight levels of parent,child grand child and so forth, in the model ,the Primary key of each descendent table inherts Primary key of all ancestor tables. you want to accomplish the following goals: .allow tables at any level in the hierarchy to be joined to any other table in the hierarchy. .ensure that tables are joined on a single column. .limit the index length of primary keys to 10 bytes or less. .ensure that key columns are always compared on a bunary basis regardless of which options were selected during the installation of sql server. You take the following actions. 1.Implement the data model as is. 2.Create indexs on all foreign keys. Which result or results do these actions produce(choose all the apply) A. tables in the data model hierarchy can be joined to any other table in the hierarchy B. tables are joined on a single column C. The Index length of all Primary keys is 10 bytes or less. D. Key columns are always compared on a binary basis regardless of Which options were selected during the installtion of SQL Server. ANSWER:A,B,C,D 66).You are implementing a marketing application that was not origianlly designed replication.You need to use replication to distribute data among four separate SQL Server .You also need to allow users to enter data into a table named sales from any location and have the data distributed to all locations. When creating tables,you must follow your company programming standards which reqire the use of surrogate Primary key in all tables. What should you do ? A. Create a primary key column that uses the identity property with a step of 1 and a separate starting number for each server so that the first server starts at 1, the second server starts at 10000001, and so forth. Replicate new rows by using store procedure that include the following command: set identity_insert sales on B. Create a primary key column that uses the identity property with a step of 4 and a separate starting number for each server so that the first server starts at 1, the second server start at 2, and so forth Replicate new rows by using store procedure that include the following command: set identity_insert sales on C.Create a Primary key column that uses the timestamp data type. D.Create a Primary key column that uses the uniqueidentifier data Type and a default constraint of NEWID() ANSWER: D 67).You have existing data that must be moved into a new database.The data is stored in two tables named table A and table B.Each table contains more than 100 million rows.You know that application are joining table A to table B by using a cluster index. Fast response time is critical ,in the past separating the tables from The rest of the database and placing them onto separate physical disks has improved response time. In the new database.how should you save the data in table A and table B in order to reduce response time when joining the two tables. A. In the database, create two segments, named segmentA and segmentB, that point to separate physical disk drives. Remove the default and system segments from the devices where segmentA and segmentB reside. Create tableA and any associated index on segmentA. Create tableB and any associated index on segmentB. B. create two filegroups, named filegroupA and filegroupB, as part of the database on separate physical disk drives. Create tableA and any associated index on filegroupA. Create tableB and any associated index on filegroupB. C. create three filegroup named filegroupA and filegroupB and filegroupC, as part of the database on separate physical disk drives. Create tableA and any associated index on filegroupA. Create tableB and any associated index on filegroupB. Create all clustered and nonclustered indexes for tableA and tableB on filegroupC. D. create the data portion of the database on a RAID 5 device, create tableA and tableB and all indexes in the database. E. Create the data partion of the database on a RAID1 +0 the device Create table A and table B and all indexes in the database. ANSWER:B 68).You are the database manager for a manufacturing company.You are receiving the mixture of online transaction processing(OLTP) and decision support system(DSS) activities that from the shop floor in real time.The application must report production results trends and variances in a timely fashion. What should you do to maxmize the overall performance of the production Monitoring application? A. Add additional indexes to support both OLTP and DSS requirements. B. create an OLTP sql server and a DSS sql server replicate table from the OLTP sql server to a DSS sql server with the distribution database on the DSS server. Index the tables on each sql server to support their respective activities. C. create an OLTP sql server and a DSS sql server. On the OLTP sql server, index the tables to support the OLTP requirements every 15 minute, backup the log from OLTP sql server and restore it on the DSS sql server. Reindex the table on the DSS sql server to support the DSS activities. D.Creat an OLTP database and a DSS database on the Same SQL Server In the OLTP database ,create triggers that automatically update tables In the DSS database ANSWER: B 69) You need to produce a sales report listing all salesperson numbers sales amounts ,and order dates. You want the report stored from most recent sales to oldest sales. For each day, you want the sales amounts stored from highest to lowest. You will be selecting this information from a table that is defined As follow: CREATE TABLE Saleinformation (Sales informaitonID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED. SalePersonID int NOT NULL RegionID int NOT NULL ReceiptID int NOT NULL SalesAmount money NOT NULL OrderDate datetime NOT NULL} Which query will accurately produce the report? A) SELECT SalePersonID, SalesAmount ,OrderDate FROM Saleinformation ORDER BY OrderDate ,SalesAmount DESC B). SELECT SalePersonID, SalesAmount ,OrderDate FROM Saleinformation ORDER BY SalesAmount, OrderDate DESC C). SELECT SalePersonID, SalesAmount ,OrderDate FROM Saleinformation ORDER BY OrderDate DESC,SalesAmount DESC D) . SELECT SalePersonID, SalesAmount ,OrderDate FROM Saleinformation ORDER BY SalesAmount DESC, OrderDate DESC ANSWER:C 70). You have an accounting application that captures batches of transactions into a staging table before being proceed. Processing can be performed on individual batches or on the whole staging table Processing includes many validations before updating any of the Production tables. you take the following actions: ... Which result or results do these actions produce? (choose all apply) A. Deadlocks are avoided or handled appropriately. B. Each batch of transactions is accepted or rejected. C. Users to access the production tables while accounting Transactions are being processed. D. Resource locking is minimized ANSWER B,C,D 71) You database stores telephone number. Each telephone number is stored as an integer. You must format the telephone number to print on a report in the following format: (999)-999-999 You have selected the phone number into a local variable as follows: DECLARE @ PhoneNumber int Which statement will correctly format the number? A. Select』PhoneNumber』=』(『+SUBSTRING(CONVERT(varchar(10), @PhoneNumber),3,0)+』)』+SUBSTRING(CONVERT(varchar(10), honeNumber),3,3)+』-『+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),4,6) B. Select』PhoneNumber』=』(『+SUBSTRING(CONVERT(varchar(10), @PhoneNumber),3,1)+』)』+SUBSTRING(CONVERT(varchar(10), honeNumber),3,4)+』-『+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),4,7) C. Select』PhoneNumber』=』(『+SUBSTRING(CONVERT(varchar(10), @PhoneNumber),0,3)+』)』+SUBSTRING(CONVERT(varchar(10), honeNumber),3,3)+』-『+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),6,4) D. Select』PhoneNumber』=』(『+SUBSTRING(CONVERT(varchar(10), @PhoneNumber),1,3)+』)』+SUBSTRING(CONVERT(varchar(10), honeNumber),4,3)+』-『+ SUBSTRING(CONVERT(varchar(10), PhoneNumber),7,4) ANSWER:D 72).Your Orders table is defined as follows: CREATE TABLE Orders{ OrderID int IDENTITY(1,1) NOTNULL SalesPersonID int NOTNULL RegionID int NOTNULL OrderDate datetime NOTNULL OrderAmount int NOTNULL} The table is becoming too large to manage.You must delete all Sales that are more than three years old. Which query will accomplish the desired result? A. Delete from Orders Where OrderDate < DATEADD(YY,-3,GETDATE()) B. Delete from Orders Where OrderDate < DATEADD(YY,3,GETDATE()) C. Delete from Orders Where OrderDate < GETDATE(), -3 D. Delete from Orders Where OrderDate < GETDATE(), +3 ANSWER:A 73). You are developing a sales database for a company that has a 100 person sales staff. The company』s policy requires that any sales orders in excess of $100,000 be approved and entered into the database by the sales manager Your database includes a SalesOrder table that is defined as follows: CREATE TABLE Saleorder { Number char(10) NOTNULL Saleperson varachar(50) NOTNULL Amount money NOTNULL} You need to create a view on the SalesOrder table that will prevent The sales staff from entering a sales order in excess of $100,000. Which view should you write? A. CREATE VIEW SaleOrderLimit AS SELECT Number,Saleperson,Amount FROM SaleOrder WHERE Amount<=100000 WITH CHECK OPTION B. CREATE VIEW SaleOrderLimit AS SELECT Number,Saleperson,Amount FROM SaleOrder WHERE Amount<=100000 C. CREATE VIEW SaleOrderLimit(Number,Saleperson,Amount) AS SELECT Number,Saleperson,SUM(Amount) FROM SaleOrder GROUP BY Number. Saleperson HAVING(SUM(Amount)<=100000) D. CREATE VIEW SaleOrderLimit AS SELECT TOP 100000 Number,Saleperson,Amount FROM SaleOrder WHERE Amount<=100000 ORDER BY Amount ANSWER :A 74). Your database includes a table named SaleInformation that tracks sales by region. The tables is defined as follows: CREATE TABLE Saleinformation (Sales informaitonID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED. SalePersonID int NOT NULL RegionID int NOT NULL ReceiptID int NOT NULL SalesAmount money NOT NULL} Your database also includes a table named SalePerson that is defined As follows: CREATE TABLE SalePerson (SalePersonID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED. RegionID int NOT NULL LastName varchar(30) NOT NULL FirstName varchar(30) NULL MiddleName varchar(30) NULL AddressID int NULL} You want to ensure that each saleperson enters sales only in the Saleperson』s own region. Which of the following actions can you perform to accomplish this task? A. Place a FOREIGN KEY constraint on the SalesInformation table that relates to the SalePerson table. B. Place a FOREIGN KEY constraint on the SalePerson table that relates to the SalesInformation table. C. Create a trigger on the SalesInformation table that verities That the region for the sale in the same as the region for the Saleperson. D. Create a trigger on the Saleperson table that verities That the region for the sale in the same as the region for the Saleperson. ANSWER:C 75). Your database includes a table that is defined as follows: CREATE TABLE Orders{ OrderID int IDENTITY(1,1) NOTNULL SalesPersonID int NOTNULL RegionID int NOTNULL OrderDate datetime NOTNULL OrderAmount int NOTNULL} The sales manager wants to see a report that shows total sales by region as well as a grand total of sale. Which query can you use to create the report? A. SELECT SalePersonID, RegionID OrderAmount FROM Orders ORDER BY RegionID COMPUTE SUM(OrderAmount) B. SELECT SalePersonID, RegionID OrderAmount FROM Orders ORDER BY RegionID COMPUTE SUM(OrderAmount) BY RegionID COMPUTE SUM(OrderAmount) C. SELECT SalePersonID, RegionID SUM(OrderAmount) FROM Orders ORDER BY SalePersonID ,RegionID D. SELECT SalePersonID, RegionID SUM(OrderAmount) FROM Orders ORDER BY SalePersonID ,RegionID HAVING SUM(OrderAmount)=RegionID ANSWER:B 76). Your company has a headquarters sales office and two remote sales offices. Each sales office has a database containing an Orders table that is defined as follows CREATE TABLE Orders{ OrderID int IDENTITY(1,1) NOTNULL SalesPersonID int NOTNULL OrderDate datetime NOTNULL OrderAmount int NOTNULL} You want a report in the following format that combines data for All orders from headquarters and the other two sales offices: Office OrderID SalePersonID OrderDate OrderAmount The server in the headquarters office is named HQ,and the servers in The reomte sales offices are named RS1 and RS2. Which query can you use to produce the report? A. ... B. ... C. SELECT Office=』HQ』,OrderID,SalePersonID,OrderDate,OrderAmount FROM HQ.Sales.dbo.Order UNION ALL Office=』RS2』,OrderID,SalePersonID,OrderDate,OrderAmount FROM RS2.Sales.dbo.Order UNION ALL Office=』RS1』,OrderID,SalePersonID,OrderDate,OrderAmount FROM RS1.Sales.dbo.Order D. ... ANSWER:C
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有