Microsoft Certification: Question : 70-229

Showing posts with label Question : 70-229. Show all posts

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

91. You are a database developer for a consulting company. You are creating a database named Reporting.

Customer names and IDs from two other databases, named Training and consulting, must be loaded into the Reporting database. You create a table named Customers in the Reporting database.

The script that was used to create this table is shown in the Script for Customers Table exhibit here:

onClick="window.open('./70-229/imageT39.jpg','Exhibit','height=148,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Data must be transferred into the Customers table from the Students table in the training database and from the Clients table in the Consulting database.

The Students and Clients tables are shown in the Students and Clients tables exibit here:

onClick="window.open('./70-229/imageT40.jpg','Exhibit','height=155,width=453,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You must create a script to transfer the data into the Customers table.

The first part of the script is shown in the exhibit here:

onClick="window.open('./70-229/imageT41.jpg','Exhibit','height=370,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit SCRIPT

To complete the script look at the following exibit (Drag and Drop style)

onClick="window.open('./70-229/imageT42.jpg','Exhibit','height=471,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Place the following possible script lines in their appropriate order. (Use only the script lines that apply)

A. Insert INTO Customers

(CustomerKey, SourceID, CustomerName)

SELECT CustomerID, SourceID, CustomerName

FROM #tmpCustomers

B. IF @@ERROR = 0 RETURN

C. IF @@ERROR <> 0

BEGIN ROLLBACK TRAN

RETURN

END

D. SAVE TRAN Customers

E. COMMIT TRAN

F. SELECT CustomerID, SourceID, CustomerNAme

INTO Customers

FROM #tmpCustomers

G. BEGIN TRAN

Ans:G,A,C,E

onClick="window.open('./70-229/imageT43.jpg','Exhibit','height=444,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Explanation:

Line 1 BEGIN TRAN

Line 2 Insert INTO CustomerID
(CustomerKey, SourceID, CustomerName)
SELECT CustomerID, SourceID, CustomerName
FROM #tmpCustomers

Line 3 IF @@ERROR <> 0
BEGIN ROLLBACK TRAN
RETURN
END

Line 4 COMMIT TRAN

92. You are a database developer for an investment brokerage company. The company has a database named Stocks that contains tables named CurrentPrice and PastPrice.

The current prices of investment stocks are stored in the CurrentPrice table.

Previous stock prices are stored in the PastPrice table.

These tables are shown in the CurrentPrice and PastPrice Tables exhibit here:

onClick="window.open('./70-229/imageT44.jpg','Exhibit','height=150,width=448,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

A sample of the data contained in thee tables is shown in this Sample Data exhibit:

onClick="window.open('./70-229/imageT45.jpg','Exhibit','height=235,width=546,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

All of the rows in the CurrentPrice table are updated at the end of the business day, even if the price of the stock has not changed since the previous update.

If the stock price has changed since the previous update, then a row must also be inserted into the PastPrice table.

You need to design a way for the database to execute this action automatically.

What should you do?

A. Create an AFTER trigger on the CurrentPrice table that compares the values of the StockPrice column in the inserted and deleted tables. If the values are different, then the trigger will insert a row into the PastPrice table.

B. Create an AFTER trigger on the CurrentPrice table that compares the values of the StockPrice column in the inserted table with the StockPrice column in the CurrentPrice table. If the values are different, then the trigger will insert a row into the PastPrice table.

C. Create a cascading update constraint on the CurrentPrice table that updates a row in the PastPrice table.

D. Create a stored procedure that compares the new value of the StockPrice column in the CurrentPrice table with the old value. If the values are different, then the procedure will insert a row into the PastPrice table.

Ans:A

93. You are a database developer for Wingtip Toys. The company tracks its inventory in an SQL Server 2000 database. You have several queries and stored procedures that are executed on the database indexes to support the queries that have been created.

As the number of cataloged inventory items has increased, the execution time of some of the stored procedures has increased significantly.

Other queries and procedures that access the same information in the database have not experienced an increase in execution time.

You must restore the performance of the slow-running stored procedures to their original execution times.

What should you do?

A. Always use the WITH RECOMPILE option to execute the slow-running stored procedures.

B. Execute the UPDATE STATISTICS statement for each of the tables accessed by the slow-running stored procedures.

C. Execute the sp_recompile system stored procedure for each of the slow-running procedures.

D. Execute the DBCC REINDEX statement for each of the tables accessed by the slow-running stored procedures.

Ans:C

94. You are a database developer for a marketing firm. You have designed a quarterly sales view. This view joins several tables and calculates aggregate information.

You create a unique index on the view. You want to provide a parameterised query to access the data contained in your indexed view.

The output will be used in other SELECT lists.

How should you accomplish this goal?

A. Use an ALTER VIEW statement to add the parameter value to the view definition.

B. Create a stored procedure that accepts the parameter as input and returns a rowset with the result set.

C. Create a scalar user-defined function that accepts the parameter as input.

D. Create an inline user-defined function that accepts the parameter as input.

Answer:C

95. You a database developer for a large grocery store chain. The partial database schema is shown in the Partial Database Schema exhibit (hmmmm, I guess it is still under construction).

The script that was used to create the Customers table is shown in the Script for Customers Table exhibit (still under constuction as well I think).

The store managers want to track customer demographics so they can target advertisements and coupon promotions to customers.

These advertisements and promotions will be based on the past purchases of existing customers.

The advertisements and promotions will target buying patterns by one or more of these demographics: gender, age, postal code, and region.

Most of the promotions will be based on gender and age. Queries will be used to retrieve the customer demographics information.

You want the query response time to be as fast as possible.

What should you do?

A. Add indexes on the PostalCode, State, and DateOfBirth columns of the Customers table.

B. Denormalize the customers table.

C. Create a view on the Customers, SalesLineItem, State, and Product tables.

D. Create a function to return the required data from the Customers table.

Ans:B

96. You are a database developer for Lucerne Publishing. You are designing a HR database that contains tables named Employee and Salary.

You interview users and discover the following information:

The employee table will often be joined with the Salary table on the EmployeeID column.

Individual records in the Employee table will be selected by social security number (SSN)

A list of employees will be created. The list will be produced in alphabetical order by last name, and then followed by first name.

You need to design the indexes for the tables while optimizing the performance of the indexes.

Which three scripts should you use? (Each correct answer presents part of the solution. Choose three)

A. CREATE CLUSTERED INDEX [IX_EmployeeName] ON [dbo].[Employee]([LastName],[FirstName])

B. CREATE INDEX [IX_EmployeeFirstName] ON [dbo].[Employee] ([First Name])

CREATE INDEX [IX_EmployeeLastName] ON [dbo].[Employee] ([Last Name])

C. CREATE UNIQUE INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID])

D. CREATE UNIQUE INDEX [IX_EmployeeSSN] ON [dbo].[Employee] ([SSN])

E. CREATE CLUSTERED INDEX [IX_EmployeeEmployeeID] ON [dbo].[Employee] ([EmployeeID])

F. CREATE CLUSTERED INDEX [IX_EmployeeESSN] ON [dbo].[Employee] ([SSN])

Ans:A,C,D

97. You are a database developer for a large electric company. The company is divided into many departments, and each employee of the company is assigned to a department. You create a table named Employee that contains information about all employees, including the department to which they belong.

The script that was used to create the Employee table is shown in this exhibit:

onClick="window.open('./70-229/imageT46.jpg','Exhibit','height=207,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Each department manager should be able to view only the information in the Employee table that pertains to his or her department.

What should you do?

A. Use GRANT, REVOKE, and DENY statements to assign permissions to each department manager.

B. Add the database login of each department manager to the db_datareader fixed database role.

C. Build tables and views that enforce row-level security on the Employee table.

D. USE SQL Server Enterprise Manager to assign permissions on the Employee table.

Ans:C

98. You are a database developer for your company's Human Resources database. This database includes a table named Employee that contains confidential ID numbers and salaries. The table also includes non-confidential information, such as employee names and addresses.

You need to make all the non-confidential information in the Employee table available in XML format to an external application. The external application should be able to specify the exact format of the XML data.

You also need to hide the existence of the confidential information from the external application.

What should you do?

A. Create a stored procedure that returns the non-confidential information from the Employee table formatted as XML.

B. Create a user-defined function that returns the non-confidential information from the Employee table in a rowset that is formatted as XML.

C. Create a view that includes only the non-confidential information from the Employee table. Give the external application permission to submit queries against the view.

D. Set column-level permissions on the Employee table to prevent the external application from viewing the confidential columns. Give the external application permissions to submit queries against the table.

Ans:C

99. You are a database developer for Tailspin Toys. You have two SQL Server 2000 computers named CORP1 and CORP2. Both of these computers use SQL Server authentication.

CORP2 stores data that has been archived from CORP1.

At the end of each month, data is removed from CORP1 and transferred to CORP2.

You are designing quarterly reports that will include data from both CORP1 and CORP2. You want the distributed queries to execute as quickly as possible.

Which three actions should you take? (Each correct answer presents part of the solution. Choose Three)

A. Create a stored procedure that will use the OPENROWSET statement to retrieve the data.

B. Create a stored procedure that will use the fully qualified table name on CORP2 to retrieve the data.

C. Create a script that uses the OPENQUERY statement to retrieve the data.

D. On CORP1, execute the sp_addlinkedserver system stored procedure.

E. On CORP1, execute the sp_addlinkedsrvlogin system stored procedure.

F. On CORP2, execute the sp_serveroption system stored procedure and set the collation compatible to ON.

Ans:B,D,E

100. You are a database developer for an IT consulting company. You are designing a database to record information about potential consultants. You create a table named CandidateSkills for the database.

The table is shown in this exhibit:

onClick="window.open('./70-229/imageT47.jpg','Exhibit','height=160,width=239,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

How should you uniquely identify the skills for each consultant?

A. Create a PRIMARY KEY constraint on the CandidateID column.

B. Create a PRIMARY KEY constraint on the CandidateID and DateLastUsed columns.

C. Create a PRIMARY KEY constraint on the CandidateID and SkillID columns.

D. Create a PRIMARY KEY constraint on the CandidateID, SkillID, and DateLastUsed columns.

Ans: C

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

81. You are the developer of a database named Inventory. You have a list of reports that you must create. These reports will be run at the same time. You write queries to create each report. Based on the queries, you design and create the indexes for the database tables.

You want to ensure that you have created useful indexes.

What should you do?

A. Run sql trace, and use the Objects event classes.

B. Run the Index Tuning Wizard against a workload file that contains the queries used in the reports.

C. Run System Monitor, and use the SQLServer:Access Methods counter.

D. Execute the queries against the tables in SQL Query Analyzer, and use the SHOWPLAN_TEXT option.

Ans: B

82. You are a database developer for your company's SQL server 2000 database.

You use the following script to create a view named Employee in the database:

CREATE VIEW Employee AS

SELECT P.SSN, P.LastName, P.FirstName, P. Address, P.city, P.State, P.Birthdate, E.EmployeeID, E.Department, E.Salary

FROM Person AS P JOIN Employees AS E ON (P.SSN = E.SSN)

The view will be used by an application that inserts records in both the Person and Employees tables.

The script that was used to create these tables is shown in the exhibit:

onClick="window.open('./70-229/imageT33.jpg','Exhibit','height=361,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You want to enable the application to issue INSERT statements against the view.

What should you do?

A. Create an AFTER trigger on the view.

B. Create an INSTEAD OF trigger on the view.

C. Create an INSTEAD OF trigger on the Person and Employees tables.

D. Alter the view to include the WITH CHECK option.

E. Alter the view to include the WITH SCHEMA BINDING option.

Ans: B

83. You are a database developer for Wide World Importers. You are creating a table named Orders for the company's SQL Server 2000 database. Each order contains an order ID, an order date, a customer ID, a shipper ID, and a ship date.

Customer services representatives who take the orders must enter the order date, customer ID, and shipper ID when the order is taken. The order ID must be generated automatically by the database and must be unique.

Orders can be taken from existing customers only. Shippers can be selected only from an existing set of shippers. After the customer service representatives complete the order, the order is send to the shipping department for final processing.

The shipping department enters the ship date when the order is shipped.

Which script should you use to create the Orders table?

A. CREATE TABLE Orders

(

Order ID uniqueidenfitier PRIMARY KEY NOT NULL,

OrderDate, datetime NULL,

CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID),

ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers(shipperID),

ShipDate datetime Null

)

B. CREATE TABLE Orders

(

Order ID int identity (1, 1)PRIMARY KEY NOT NULL,

OrderDate, datetime NOT NULL,

CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID),

ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers(shipperID),

ShipDate datetime Null

)

C. CREATE TABLE Orders

(

Order ID int identity (1, 1)PRIMARY KEY NOT NULL,

OrderDate, datetime NULL,

CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID),

ShipperID int NULL

ShipDate datetime Null

)

D. CREATE TABLE Orders

(

Order ID uniqueidenfitier PRIMARY KEY NOT NULL,

OrderDate, datetime NOT NULL,

CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer ID),

ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers(shipperID),

ShipDate datetime Null

)

Ans:B

84. You are a database developer for Luce Publishing. The company stores its sales data in a SQL Server 2000 database. This database contains a table named Orders.

There is currently a clustered index on the table, which is generated by using a customer's name and the current date. The Orders table currently contains 750,000 rows, and the number of rows increased by 5 percent each week.

The company plans to launch a promotion next week that will increase the volume of inserts to the Orders table by 50 percent.

You want to optimize inserts to the Orders table during the promotion.

What should you do?

A. Create a job that rebuilds the clustered index each night by using the default FILLFACTOR.

B. Add additional indexes to the Orders table.

C. Partition the Orders table vertically.

D. Rebuild the clustered index with a FILLFACTOR of 50.

E. Execute the UPDATE STATISTICS statement on the Orders table.

Ans:D

85. You are designing your company's Sales database. The database will be used by three custom applications. Users who require access to the database are currently members of Microsoft Windows 2000 groups.

Users were placed in the Windows 2000 groups according to their database access requirements. The custom applications will connect to the sales database through application roles that exist for each application.

Each application role was assigned a password.

All users should have access to the Sales database only through the custom applications. No permissions have been granted in the database.

What should you do?

A. Assign appropriate permissions to each Windows 2000 group.

B. Assign appropriate permissions to each application role.

C. Assign the Windows 2000 groups to the appropriate application role.

D. Provide users with the password to the application role

Ans:B

86. You are a database developer for your company's database named Insurance.

You execute the following script in SQL Query Analyzer to retrieve agent and policy information:

SELECT A.LastName, A.FirstName, A.CompanyName, P.PolicyNumber

FROM Policy AS P JOIN AgentPolicy AS AP

ON (P.PolicyNumber = AP.PolicyNumber)

JOIN Agents AS A ON (A.AgentID= AP.AgentID)

The query execution plan that is generated is shown below:

onClick="window.open('./70-229/imageT34.jpg','Exhibit','height=285,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

The information received when you move the pointer over the Table Scan icon is shown below:

onClick="window.open('./70-229/imageT35.jpg','Exhibit','height=288,width=350,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You want to improve the performance of this query.

What should you do?

A. Change the order of the tables in the FROM clause to list the Agent table first

B. Use a hash join hint to join the Agent table in the query.

C. Create a clustered index on the AgentID column of the Agent table.

D. Create a nonclustered index on the AgentID column of the Agent table.

Ans: C

87. You are a database consultant. One of your customers reports slow query response times for a SQL Server 2000 database, particularly when table joins are required.

Which steps should you take to analyze this performance problem?

To answer, Simulate the Drag and Drop style and list in steps for the appropriate transaction order choices beside the appropriate transaction steps. (Use only order choices that apply)

onClick="window.open('./70-229/imageT36.jpg','Exhibit','height=380,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

A. Use SQL Profiler to create a trace based on the SQL Profiler Tuning template.

B. Specify that the trace data be saved to a file, and specify a maximum file size.

C. Specify a stop time for the trace.

D. Run the trace.

E. Use the trace file as an input to the Index Tuning Wizard.

F. Replay the trace and examine the output.

G. Use SQL Profiler to create a trace based on the SQL Profiler Standard Template.

Ans: A,B,C,D,E,F

Step 1 Use SQL Profiler to create a trace based on the SQL Profiler Tuning Template
Step 2 Specify that the trace data be saved to a file, and specify a maximum file size.
Step 3 Specify a stop time for the trace.
Step 4 Run the trace.
Step 5 Use the trace file as an input to the Index Tuning Wizard.
Step 6 Replay the trace, and examine the output.
Step 7 (none)

The answer (G) is Wrong and is Not used.

88. You are a database developer for Wingtip Toys. The company stores its sales information in a SQL Server 2000 database. This database contains a table named Orders. You want to move old data from the orders table to an archive table.

Before implementing this process, you want to identify how the query optimizer will process the INSERT statement.

You execute the following script in SQL Query Analyzer:

SET SHOWPLAN_TEXT ON

GO

CREATE TABLE Archived_Orders_1995_1999

(

OrderID int,

CustomerID char (5),

EmployeeID int,

OrderDate datetime,

ShippedDate datetime

)

INSERT INTO Archived_Orders_1995_1999

SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate

FROM SalesOrders

WHERE ShippedDate < DATEADD (year, -1, getdate())

You receive the following error message:

Invalid object name 'Archived_Orders_1995_1999.'

What should you do to resolve the problem?

A. Query the Archived_Orders_1995_1999 table name with the owner name.

B. Request CREATE TABLE permissions.

C. Create the Archived_Orders_1995_1999 table before you execute the SET SHOWPLAN_TEXT ON statement.

D. Change the table name to ArchivedOrders.

Ans:C

89. You are a database developer for WoodGrove Bank. The company has a database that contains human resources information. You are designing transactions to support data entry into this database.

The script for two of the transactions that you designed are shown in the exhibit:

onClick="window.open('./70-229/imageT37.jpg','Exhibit','height=376,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

While testing these scripts, you discover that the database server occasionally detects a deadlock condition.

What should you do?

A. In Transaction 2, move the UPDATE Customer statement before the UPDATE CustomerPhone statement

B. Add the SET DEADLOCK_PRIORITY LOW statement to both transactions

C. Add code that checks for server error 1205 to each script. If this error is encountered, restart the transaction in which it occurred.

D. Add the SET LOCK_TIMEOUT 0 statement to both transactions

Ans: A

90. You are a database developer for a company that compiles statistics for baseball teams. These statistics are stored in a database named Statistics.

The Players of each team are entered in a table named Rosters in the Statistics database.

The script that was used to create the Rosters table is shown in the exhibit:

onClick="window.open('./70-229/imageT38.jpg','Exhibit','height=234,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Each baseball team can have a maximum of 24 players on the roster at any one time.

You need to ensure that the number of players on the team never exceeds the maximum.

What should you do?

A. Create a trigger on the Rosters table that validates the data

B. Create a rule that validates the data

C. Create an update view that includes the WITH CHECK OPTION clause in its definition

D. Add a CHECK constraint on the Rosters table to validate the data

Ans:A

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

71. You are a database developer for Trey Research. You are designing a SQL Server 2000 database that will be distributed with an application to numerous companies.

You create several stored procedures in the database that contain confidential information.

You want to prevent the companies from viewing this confidential information.

What should you do?

A. Remove the text of the stored procedures from the syscomments system table.

B. Encrypt the text of the stored procedures.

C. Deny SELECT permissions on the syscomments system table to the public role.

D. Deny SELECT permissions on the sysobjects system table to the public role.

Ans: B

72. You are a database developer for Southridge Video. The company stores its sales information in a SQL Server 2000 database.

You are asked to delete order records from this database that are more than five years old.

To delete the records, you execute the following statement in SQL Query Analyzer:

DELETE FROM Orders WHERE OrderDate < (dateadd(year, -5, getdate()))

You close SQL Query Analyzer after you receive the following message:

(29979 row(s) affected)

You examine the table and find that the old records are still in the table.

The Current Connection properties are shown in the exhibit:

onClick="window.open('./70-229/imageT27.jpg','Exhibit','height=475,width=568,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

What should you do?

A. Delete records in the tables that reference the Orders table.

B. Disable triggers on the Orders table.

C. Execute a SET IMPLICIT_TRANSACTIONS OFF statement.

D. Execute a SET CURSOR_CLOSE_ON_COMMIT ON statement.

E. Change the logic in the DELETE statement.

Ans:C

73. You are a database developer for a telemarketing company. You are designing a database named CustomerContacts. This database will be updated frequently. The database will be about 1 GB in size.

You want to achieve the best possible performance for the database. You have 5 GB of free space on drive C.

Which script should you use to create the database?

A. CREATE DATABASE CustomerContacts

ON

(NAME = Contacts_database,

FILENAME = 'c:\data\contacts.mdf',

SIZE = 10,

MAXSIZE = 1GB

FILEGROWTH= 5)

B. CREATE DATABASE CustomerContacts

ON

(NAME = Contacts_dat,

FILENAME = 'c:\data\contacts.mdf',

SIZE = 10,

MAXSIZE = 1GB

FILEGROWTH= 10%)

C. CREATE DATABASE CustomerContacts

ON

(NAME = Contacts_dat,

FILENAME = 'c:\data\contacts.mdf',

SIZE = 100,

MAXSIZE = UNLIMITED)

D. CREATE DATABASE CustomerContacts

ON

(NAME = Contacts_dat,

FILENAME = 'c:\data\contacts.mdf',

SIZE = 1GB)

Ans:D

74. You are a database developer for WoodGrove Bank. The company stores its sales data in a SQL Server 2000 database.

You want to create an indexed view in this database.

To accomplish this, you execute the script shown in the exhibit:

onClick="window.open('./70-229/imageT28.jpg','Exhibit','height=275,width=628,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

The index creation fails, and you receive an error message.

You want to eliminate the error message and create the index.

What should you do?

A. Add an ORDER BY clause to the view.

B. Add a HAVING clause to the view.

C. Change the NUMERIC_ROUNDABORT option to ON.

D. Change the index to a unique, nonclustered index.

E. Add the WITH SCHEMABINDING option to the view.

Ans:E

75. You are a database developer for your company's SQL Server 2000 database. Another database developer named Andrea needs to be able to alter several existing views in the database. However, you want to prevent her from viewing or changing any of the data in the tables.

Currently, Andrea belongs only to the Public database role.

What should you do?

A. Add Andrea to the db_owner database role

B. Add Andrea to the db_ddladmin database role

C. Grant Andrea CREATE VIEW permissions.

D. Grant Andrea ALTER VIEW permissions

E. Grant Andrea REFERENCES permissions on the tables.

Ans: B

76. You are a database developer for a rapidly growing company. The company is expanding into new sales regions each month.

As each new sales region is added, one or more sales associates are assigned to the new region. Sales data is inserted into a table named RegionSales, which is located in the Corporate database.

The RegionSales table is shown in the exhibit:

onClick="window.open('./70-229/imageT29.jpg','Exhibit','height=175,width=235,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Each sales associate should be able to view and modify only the information in the RegionSales table that pertains to his or her regions.

It must be as easy as possible to extend the solution as new regions and sales associates are added.

What should you do?

A. Use GRANT, REVOKE and DENY statements to assign permission to the sales associates.

B. Use SQL Server Enterprise Manager to assign permission on the RegionSales table.

C. Create one view on the RegionSales table for each sales region. Grant the sales associates permission to access the views that correspond to the sales region to which they have been assigned.

D. Create a new table named Security to hold combinations of sales associates and sales regions. Create stored procedures that allow or disallow modifications of the data in the RegionSales table by validating the user of the procedures against the security table. Grant EXECUTE permissions on the stored procedures to all sales associates.

E. Create a new table named Security to hold combinations of sales associates and sales regions. Create user-defined functions that allow or disallow modifications of the data in the RegionSales table by validating the user of the function against the security table. Grant EXECUTE permissions on the functions to all sales associates.

Ans:D

77. You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role.

The ToySales table stored the sales information for all departments in the company.

This table is shown in the exhibit:

onClick="window.open('./70-229/imageT30.jpg','Exhibit','height=175,width=230,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls.

Employees in the dolls department should be given full access to the data. You have also created a view named vwActionFigureSales to display only the information that pertains to sales of action figures.

Employees in the action figures department should be given full access each other's data. The two departments currently have no access to the data.

Employees in the doll department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role.

You must ensure that the two departments can view only their own data.

Which three actions should you take? (Each correct answer presents part of the solution. Choose three)

A. Transfer the ownership of the table and the views to the database owner.

B. Grant SELECT permissions on the ToySales table to your login.

C. Grant SELECT permissions on the vwDollSales view to the Doll database role.

D. Grant SELECT permission on the vwActionFigureSales view to the ActionFigure database role.

E. Deny SELECT permission on the ToySales table for the Doll database role.

F. Deny SELECT permissions on the ToySales table for the ActionFigure database role.

Ans:A,C,D

78. You are a database developer for your company's SQL Server 2000 database. The database is installed on a Microsoft Windows 2000 server computer. The database is in the default configuration.

All tables in the database have at least one index. SQL Server is the only application running on the server.

Database activity peaks during the day, when sales representatives enter and update sales transactions. Batch reporting is performed after business hours.

The sales representatives report slow updates and inserts.

What should you do?

A. Run System Monitor on the SQL Server:Access Methods counter during the day. Use the output from System Monitor to identify which tables need indexes.

B. Use the sp_configure system stored procedure to increase the number of locks that can be used by SQL Server.

C. Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL Profiler as input to the index Tuning Wizard.

D. Increase the value of the min server memory option.

E. Rebuild indexes, and use a FILLFACTOR of 100.

Ans:C

79. You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails.

The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drives. The Order and OrderDetail tables are often joined in queries.

You need to tune the performance of the database.

What should you do? (Each correct answer presents part of the solution. Choose two.)

A. Create a new filegroup on each of the four disk drives.

B. Create the clustered index for the Order table on a separate filegroup from the non-clustered indexes

C. Store the data and the clustered index for the OrderDetail table on one filegroup, and create the non-clustered indexes on another filegroup

D. Create the order table and its indexes on one filegroup, and create the OrderDetail table and its indexes on another filegroup

E. Create two filegroups that each consist of two disk drives connected to the same controller.

Ans: D,E

80. You are the database developer for a brokerage firm. The database contains a tablenamed Trades.

The script that was used to create this table is shown in the Script for Trades Table exhibit:

onClick="window.open('./70-229/imageT31.jpg','Exhibit','height=260,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

The Trades table has frequent inserts and updates during the day. Reports are run against the table each night.

You execute the following statement in the SQL Query Analyzer:

DBCC SHOWCONTIG (Trades)

onClick="window.open('./70-229/imageT32.jpg','Exhibit','height=291,width=645,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You want to ensure optional performance for the insert and select operations on the Trades table.

What should you do?

A. Execute the DBCC DBREINDEX statement on the table.

B. Execute the UPDATE STATISTICS statement on the table.

C. Execute the DROP STATISTICS statement on the clustered index.

D. Execute the DBCC INDEXDEFRAG statement on the primary key index.

E. Execute the DROP INDEX and CREATE INDEX statements on the primary key index.

Ans: A

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

61. You are a database developer for a bookstore. You are designing a stored procedure to process XML documents.

You use the following script to create the stored procedure:

CREATE PROCEDURE spParseXML (@xmlDocument varchar(1000)) AS

DECLARE @dochandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

SELECT *

FROM OPENXML (@docHandle, '/ROOT/Category/Product',2)

WITH (ProductID int,

CategoryID int,

CategoryName varchar (50),

[Description] varchar (50))

EXEC sp_xml_removedocument @docHandle

You execute this stored procedure and use an XML documents as the input document.

The XML document is shown in the XML Document exhibit:

onClick="window.open('./70-229/imageT20.jpg','Exhibit','height=420,width=700,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You receive the output as shown in this exhibit :

onClick="window.open('./70-229/imageT21.jpg','Exhibit','height=190,width=641,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You need to replace the body of the stored procedure.

Which script should you use?

A. SELECT *

FROM OPENXML (@docHandle, '/ROOT/category/Product', 1)

WITH (ProductID int,

CategoryID int,

CategoryName varchar(50),

[Description] varchar (50))

B. SELECT *

FROM OPENXML (@docHandle, '/ROOT/category/Product', 8)

WITH (ProductID int,

CategoryID int,

CategoryName varchar(50),

[Description] varchar (50))

C. SELECT *

FROM OPENXML (@docHandle, '/ROOT/category/Product', 1)

WITH (ProductID int,

CategoryID int,

CategoryName varchar(50), '@CategoryName',

[Description] varchar (50))

D. SELECT *

FROM OPENXML (@docHandle, '/ROOT/category/Product', 1)

WITH (ProductID int,

CategoryID int '../@CategoryID',

CategoryName varchar(50), '../@CategoryName',

[Description] varchar (50))

Ans:D

62. You are a database developer for Adventure Works. You are designing a script for the human resources department that will report yearly wage information.

There are three types of employee. Some employees earn an hourly wage, some are salaried, and some are paid commission on each sale that they make.

This data is recorded in a table named Wages, which was created by using the following script:

CREATE TABLE Wages

(

emp_id tinyint identity,

hourly_wage decimal NULL,

salary decimal NULL,

commission decimal NULL,

num_sales tinyint NULL

)

An employee can have only one type of wage information.

You must correctly report each employee's yearly wage information.

Which script should you use?

A. SELECT CAST (hourly_wage +40 * 52 +

salary +

commission * num_sales AS MONEY)as YearlyWages

FROM Wages

B. SELECT CAST (COALESCE(hourly_wage +40 * 52,

salary,

commission * num_sales)AS MONEY)as YearlyWages

FROM Wages

C. SELECT CAST (CASE

WHEN((hourly_wage,) IS NOTNULL) THEN hourly_wage * 40 * 52

WHEN(NULLIF(salary,NULL)IS NULL)THEN salary

ELSE commission * num_sales

END

AS MONEY)

As_yearlyWages

FROM Wages

D. SELECT CAST(CASE

WHEN (hourly_wage IS NULL)THEN salary

WHEN (salary IS NULL)THEN commission*num_sales

ELSE commission * num_sales

END

AS MONEY)

As YearlyWages

FROM Wages

Ans: B

63. You are a database developer for an insurance company. The company's regional offices transmit their sales information to the company's main office in an XML document. The XML documents are then stored in a table named SalesXML, which is located in a SQL Server 2000 database.

The data contained in the XML documents includes the names of insurance agents, the names of insurance policy owners, information about insurance policy beneficiaries, and other detailed information about the insurance policies.

You have created tables to store information extracted from the XML documents.

You need to extract this information from the XML documents and store it in the tables.

What should you do?

A. Use SELECT statements that include the FOR XML AUTO clause to copy the data from the XML documents into the appropriate tables.

B. Use SELECT statements that include the FOR XML EXPLICIT clause to copy the data from the XML documents into the appropriate tables.

C. Use the OPENXML function to access the data and to insert it into the appropriate tables.

D. Build a view on the SalesXML table that displays the contents of the XML documents. Use SELECT INTO statements to extract the data from this view into the appropriate tables.

Ans: C

64. You are a database developer for an insurance company. You create a table named Insured, which will contain information about persons covered by insurance policies.

You use the script shown in the exhibit:

onClick="window.open('./70-229/imageT22.jpg','Exhibit','height=340,width=654,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

A person covered by an insurance policy is uniquely identified by his or her name and birth date. An insurance policy can cover more than one person. A person cannot be covered more than once by the same insurance policy.

You must ensure that the database correctly enforces the relationship between insurance policies and the persons covered by insurance policies.

What should you do?

A. Add the PolicyID, InsuredName, and InsuredBirthDate columns to the primary key.

B. Add a UNIQUE constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns.

C. Add a CHECK constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns.

D. Create a clustered index on the PolicyID, InsuredName, and InsuredBirthDate columns.

Ans: B

65. You are designing a database for Tailspin Toys.

You review the database design, shown in the exhibit:

onClick="window.open('./70-229/imageT23.jpg','Exhibit','height=285,width=577,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You want to promote quick response times for queries and minimize redundant data.

What should you do?

A. Create a new table named CustomerContact. Add CustomerID, ContactName, and Phone columns to this table.

B. Create a new composite PRIMARY KEY constraint on the OrderDetails table. Include the OrderID, ProductID, and CustomerID columns in the constraint.

C. Remove the PRIMARY KEY constraint from the OrderDetails table. Use an IDENTITY column to create a surrogate key for the OrderDetails table.

D. Remove the CustomerID column from the OrderDetails table.

E. Remove the Quantity column from the OrderDetails table. Add a Quantity column to the Orders table.

Ans: D

66. You are a database developer for an automobile dealership. The company stores its automobile inventory data in a SQL Server 2000 database.

Many of the critical queries in the database join three tables named Make, Model, and Manufacturer. These tables are updated infrequently.

You want to improve the response time of the critical queries.

What should you do?

A. Create an indexed view on the tables.

B. Create a stored procedures that returns data from the tables.

C. Create a scalar user-defined function that returns data from the tables.

D. Create a table-valued user-defined function that returns data from the tables.

Ans:A

67. You are a database developer for an insurance company. The company has one main office and 18 regional offices.

Each office has one SQL Server 2000 database. The regional offices are connected to the main office by a high-speed network.

The main office database is used to consolidate information from the regional office databases. The table in the main office database are partitioned horizontally.

The regional office location is used as part of the primary key for the main office database.

You are designing the physical replication model.

What should you do?

A. Configure the main office as a publishing Subscriber.

B. Configure the main office as a publisher with a remote distributor.

C. Configure the main office as a central publisher and the regional offices as Subscribers.

D. Configure the regional offices as Publishers and the man office as a central Subscriber.

Ans:D

68. You are a database developer for a clothing retailer. The company has a database named Sales. This database contains a table named Inventory.

The Inventory table contains the list of items for sale and the quantity available for each of those items.

When sales information is inserted into the database, this table is updated.

The stored procedure that updates the inventory table is shown in the exhibit:

onClick="window.open('./70-229/imageT24.jpg','Exhibit','height=385,width=632,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

When this procedure executes, the database server occasionally returns the following error message:

Transaction (Process ID 53) was deadlock on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

You need to prevent the error message from occurring while maintaining data integrity.

What should you do?

A. Remove the table hint.

B. Change the table hint to UPDLOCK.

C. Change the table hint to REPEATABLEREAD.

D. Set the transaction isolation level to SERIALIZABLE.

E. Set the transaction isolation level to REPEATABLE READ.

Ans:B

69. You are a database developer for wide world importers. The company tracks its order information in a SQL Server 2000 database. The database includes two tables that contain order details. The tables are named Order and LineItem.

This is the script used to create the two tables:

Create table Order

(

order_id int not null,

customer_id int not null,

order_date datetime not null,

contsraint DF_datetime default (getdate()) for order_date,

constraint PK_order primary key clustered (order_id)

)

go

Create table LineItem

(

item_id int primary key,

order_id int not null references Order(order_id),

product_id int not null,

price money not null

)

go

The company's auditors have discovered that every item that was ordered on June 1, 2000 was entered with a price that was $10 more than its actual price.

You need to correct the data in the database as quickly as possible.

Which script should you use?

A. UPDATE 1

SET Price = Price - 10

FROM LineItem AS 1 INNER JOIN [Order] AS o

ON 1.OrderID = o.OrderID

WHERE o.OrderDate >= '6/1/2000'

AND o.OrderDate < '6/2/2000'

B. UPDATE 1

SET Price = Price - 10

FROM LineItem AS 1 INER JOIN [Order] AS o

ON 1.OrderID = o.OrderID

WHERE o.OrderDate = '6/1/2000'

C. DECLARE @ItemID int

DECLARE items_ursor CUSOR FOR

SELECT 1_ItemID

FROM LineItem AS 1 INNER JOIN [Order] AS o

ON l.OrderID = o.OrderID

WHERE o.OrderDate >= '6/1/2000'

AND o.OrderDate < '6/2/2000'

FOR UPDATE

OPEN items_cursor

FETCH NEXT FROM Items_cursor INTO @ItemID

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE LineItem SET Price = Price - 10

WHERE CURRENT OF items_cursor

FETCH NEXT FROM items_cursor INTO @ItemID

END

CLOSE items_cursor

DEALLOCATE items_cursor

D. DECLARE @OrderID int

DECLARE order_cursor CURSOR FOR

SELECT ordered FROM [Order]

WHERE OrderDate = '6/1/2000'

OPEN order_cursor

FETCH NEXT FROM order_cursor INTO @OrdeID

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE LineItem SET Price = Price - 10

WHERE OrderID= @OrderID

FETCH NEXT FROM order_cursor INTO @OrderID

END

CLOSE order_cursor

DEALLOCATE order_cursor

Ans:A

70. You are a database developer for a bookstore. Each month, you receive new supply information from your vendors in the form of an XML document.

The XML document is shown in the XML Document exhibit:

onClick="window.open('./70-229/imageT25.jpg','Exhibit','height=290,width=675,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You are designing a stored procedure to read the XML document and to insert the data into a table named Products.

The Products table is shown in the Product Table exhibit:

onClick="window.open('./70-229/imageT26.jpg','Exhibit','height=175,width=295,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Which script should you use to create this stored procedure?

A. CREATE PROCEDURE spAddCatalogItems (

@xmlDocument varchar (8000))

AS

BEGIN

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

INSERT INTO Products

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

INSERT INTO Products

SELECT * FROM

OPENXML (@docHandle, '/ROOT/Category/Product', 1)

WITH Products

EXEC sp_xml_removedocument @docHandle

END

B. CREATE PROCEDURE spAddCatalogItems (

@xmlDocument varchar (8000))

AS

BEGIN

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

INSERT INTO Products

SELECT * FROM OPENXML (@docHandle, '/ROOT/Category/Product', 1)

WITH (ProductID int './@ProductID',

CategoryID int '../@CategoryID',

[Description] varchar (100) './@Description')

EXEC sp_xml_removedocument @docHandle

END

C. CREATE PROCEDURE spAddCatalogItems (

@xmlDocument varchar (8000))

AS

BEGIN

INSERT IN|TO Products

SELECT * FROM OPENXML (

@docHandle, '/ROOT/Category/Product', 1)

WITH (ProductID int, Description varchar (50))

END

D. CREATE PROCEDURE spAddCatalogItems (

@xmlDocument varchar (8000))

AS

BEGIN

INSERT INTO Products

SELECT* FROM

OPENXML (@xmlDocument, '/ROOT/Category/Product',1)

WITH Products

END

Ans:B

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

51. You are a database developer for Wingtip Toys.

You have created an order entry database that includes two tables, as shown in the exhibit:

onClick="window.open('./70-229/imageT17.jpg','Exhibit','height=150,width=469,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Users enter orders into the entry application. When a new order is entered, the data is saved to the Order and LineItem tables in the order entry database.

You must ensure that the entire order is saved successfully.

Which script should you use?

A. BEGIN TRANSACTION Order

INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)

INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)

SAVE TRANSACTION Order

B. INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)

INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)

IF (@@Error = 0)

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

C. BEGIN TRANSACTION

INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)

IF (@@Error = 0)

BEGIN

INSERT INTO LineItem

VALUES (@ItemID, @ID, @ProductID, @Price)

IF (@@Error = 0)

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

END

ELSE

ROLLBACK TRANSACTION

END

D. BEGIN TRANSACTION

INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate)

IF (@@Error = 0)

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

BEGIN TRANSACTION

INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price)

IF (@@Error = 0)

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

Ans:C

52. You are a database developer for a vacuum sales company. The company has a database named Sales that contains tables named VacuumSales and Employee.

Sales information is stored in the VacuumSales table. Employee information is stored in the Employee table. The Employee table has a bit column named IsActive. This column indicates whether an employee is currently employed.

The Employee table also has a column named EmployeeID that uniquely identifies each employee. All sales entered into the VacuumSales table must contain an employee ID of a currently employed employee.

How should you enforce this requirement?

A. Use the Distributed Transaction Coordinator to enlist the employee table in a distributed transaction that will roll back the entire transaction if the employee ID is not active.

B. Add a CHECK constraint on the EmployeeID column of the VacuumSales table.

C. Add a Foreign KEY constraint on the EmployeeID column of the VacuumSales table that references the EmployeeID column in the Employee table.

D. Add a FOR INSERT trigger on the Vacuumsales table. In the trigger, join the Employee table with the inserted table based on the EmployeeID column, and test the IsActive column.

Ans: D

53. You are a database developer for an online brokerage firm. The prices of the stocks owned by customers are maintained in a SQL Server 2000 database.

To allow tracking of the stock price history, all updates of stock prices must be logged. To help correct problems regarding price updates, any error that occurs during an update must also be logged.

When errors are logged, a message that identifies the stock producing the error must be returned to the client application.

You must ensure that the appropriate conditions are logged and that the appropriate messages are generated.

Which procedure should you use?

A. CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal

AS BEGIN

DECLARE @Msg varchar(50)

UPDATE Stocks SET CurrentPrice = @Price

WHERE STockID = @ StockID

AND CurrentPrice <> @ Price

IF @@ERROR <> 0

RAISERROR ('Error %d occurred updating Stock %d.', 10, 1, @@ERROR, @StockID) WITH LOG

IF @@ROWCOUNT > 0

BEGIN

SELECT @Msg = 'Stock' + STR (@StockID) + 'updated to' + STR (@Price) + '.'

EXEC master. . xp_LOGEVENT 50001, @Msg

END

END

B. CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal

AS BEGIN

UPDATE Stocks SET CurrentPrice = @Price

WHERE STockID = @ StockID

AND CurrentPrice <> @ Price

IF @@ERROR <> 0

PRINT 'ERROR' + STR(@@ERROR) + 'occurred updating Stock' +STR (@StockID)+ '.'

IF @@ROWCOUNT > 0

PRINT 'Stock' + STR (@StockID) + 'updated to' + STR (@Price) + '.'

END

C. CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal

AS BEGIN

DECLARE @Err int, @RCount int, @Msg varchar(50)

UPDATE Stocks SET CurrentPrice = @Price

WHERE STockID = @ StockID

AND CurrentPrice <> @ Price

SELECT @Err = @@ERROR, @RCount = @@ROWCOUNT

IF @Err <> 0

BEGIN

SELECT @Mag = 'Error' + STR(@Err) + 'occurred updating Stock' + STR (@StockID) + '.'

EXEC master..xp_logevent 50001, @Msg

END

IF @RCOUNT > 0

BEGIN

SELECT @Msg = 'Stock' + STR (@StockID) + 'updated to' + STR (@Price) + '.'

EXEC master. . xp_LOGEVENT 50001, @Msg

END

END

D. CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN

DECLARE @Err int, @RCount int, @Msg varchar (50)

UPDATE Stocks SET CurrentPrice = @Price

WHERE STockID = @StockID

AND CurrentPrice <> @Price

SELECT @Err = @@ERROR, @RCount = @@ROWCOUNT

If @Err <> 0

RAISEERROR ('Error %d occurred updating Stock %d.', 10, 1, @Err, @StockID) WITH LOG

If @RCount > 0

BEGIN

SELECT @Msg = 'Stock' + STR (@StockID) + 'update to' + STR (@Price) + '.'

EXEC master. . xp_logevent 50001, @Msg

END

END

Ans: D

54. You are a database developer for a loan servicing company. You are designing database transactions to support a new data entry application.

Users of the new database entry application will retrieve loan information from a database. Users will make any necessary changes to the information and save the updated information to the database.

How should you design these transactions? (see Exhibit, Drag and Drop style)

onClick="window.open('./70-229/imageT1.jpg','Exhibit','height=355,width=625,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

A. Retrive loan information from the database.

B. The user reviews and modifies one piece of loan information.

C. Begin a transaction.

D. Save the update information in the database.

E. Commit the transaction.

F. Repeat the modify and update process for each piece of loan information.

G. The user reviews and modifies all of the loan information.

H. Roll back the transaction.

Ans: A,B,C,D,E,F

onClick="window.open('./70-229/imageT2.jpg','Exhibit','height=325,width=625,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

55. You are a developer for a company that leases trucks. The company has created a Web site that customer can be used to reserve trucks. You are designing the SQL server 2000 database to support the Web site.

New truck reservations are inserted into a table named Reservations. Customers who have reserved a truck can return to the Web site and update their reservation. When a reservation is updated, the entire existing reservation must be copied to a table named History.

Occasionally, customers will save an existing reservation without actually changing any of the information about the reservation. In this case, the existing reservation should not be copied to the History table.

You need to develop a way to create the appropriate entries in the History table.

What should you do?

A. Create a trigger on the reservations table to create the History table entries.

B. Create a cascading referential integrity constraint on the reservations table to create the History table entries.

C. Create a view on the reservations table. Include the WITH SCHEMA BINDING option in the view definition.

D. Create a view on the Reservations table. Include the WITH CHECK OPTION clause in the view definition.

Ans:A

56. You are a database developer for Proseware,Inc. The company has a database that contains information about companies located within specific postal codes. This information is contained in the Company table within this database.

Currently, the database contains company data for five different postal codes. The number of companies in a specific postal code currently ranges from 10 to 5,000. More companies and postal codes will be added to the database over time.

You are creating a query to retrieve information from the database. You need to accommodate new data by making only minimal changes to the database. The performance of your query must not be affected by the number of companies returned.

You want to create a query that performs consistently and minimizes future maintenance.

What should you do?

A. Create a stored procedure that requires a postal code as a parameter. Include the WITH RECOMPILE option when the procedure is created.

B. Create one stored procedure for each postal code.

C. Create one view for each postal code.

D. Split the company table into multiple tables so that each table contains one postal code. Build a partitioned view on the tables so that the data can still be viewed as a single table.

Ans: A

57. You are a database developer for Woodgrove Bank. You are implementing a process that loads data into a SQL Server 2000 database. As a part of this process, data is temporarily loaded into a table named Staging.

When the data load process is complete, the data is deleted from this table. You will never need to recover this deleted data.

You need to ensure that the data from the Staging table is deleted as quickly as possible.

What should you do?

A. Use a DELETE statement to remove the data from the table.

B. Use a TRUNCATE TABLE statement to remove the data from the table.

C. Use a DROP TABLE statements to remove the data from the table.

D. Use an updatable cursor to access and remove each row of data from the table.

Ans:B

58. You are a database developer for an automobile dealership. You are designing a database to support a Web site that will be used for purchasing automobiles. A person purchasing an automobile from the Web site will be able to customize his or her order by selecting the model and color.

The manufacturer makes four different models of automobiles. The models can be ordered in any one of five colors. A default color is assigned to each model.

The models are stored in a table named Models, and the colors are stored in a table named Colors.

These tables are shown in the exhibit:

onClick="window.open('./70-229/imageT18.jpg','Exhibit','height=135,width=488,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You need to create a list of all possible model and color combinations.

Which script should you use?

A. SELECT m.ModelName, c.ColorName

FROM Colors AS c FULL OUTER JOIN Models AS m

ON c.ColorID = m.ColorID

ORDER BY m.ModelName, c.ColorName

B. SELECT m.ModelName, c.ColorName

FROM Colors AS c CROSS JOIN Models AS m

ORDER BY m.ModelName, c.ColorName

C. SELECT m.ModelName, c.ColorName

FROM Colors AS m INNER JOIN Colors AS c

ON m.ColorID = c.ColorID

ORDER BY m.ModelName, c.ColorName

D. SELECT m.ModelName, c.ColorName

FROM Colors AS c LEFT OUTER JOIN Models AS m

ON c.ColorID = m.ColorID

UNION

SELECT m.ModelName, c.ColorName

FROM Colors AS c RIGHT OUTER JOIN Models AS m

ON c.ColorID = m.ColorID

ORDER BY m.ModelName, c.ColorName

E. SELECT m.ModelName

FROM Models AS m

UNION

SELECT c.ColorName

FROM Colors AS c

ORDER BY m.ModelName

Ans:B

59. You are a database developer for Adventure Works. A large amount of data has been exported from a human resources application to a text file.

The format file that was used to export the human resources data is shown below:

1 SQLINT 0 4 "," 1 EmployeeID ""

2 SQLCHAR 0 50 "," 2 FirstName SQL.Latin1_Gen...

3 SQLCHAR 0 50 "," 3 LastName SQL.Latin1_Gen...

4 SQLCHAR 0 10 "," 4 SSN SQL.Latin1_Gen...

5 SQLDATETIME 0 8 "," 5 Hire_Date ""

You need to import that data programmatically into a table named Employee.

The Employee table is shown in the exhibit table:

onClick="window.open('./70-229/imageT19.jpg','Exhibit','height=170,width=210,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You need to run this import as quickly as possible.

What should you do?

A. Use SQL-DMO and Microsoft Visual Basic Scripting Edition to create a table object. Use the ImportData method of the table object to load the table.

B. Use SQL-DMO and Microsoft Visual Basic Scripting Edition to create a database object. Use the CopyData property of the database object to load the table.

C. Use data transformation services and Microsoft Visual Basic Scripting edition to create a Package object. Create a connection object for the text file. Add a BulkInsertTask object to the Package object. Use the Execute method of the package object to load the data.

D. Use data transformation services and Microsoft Visual Basic Scripting edition to create a Package object. Create a connection object for the text file. Add a BulkInsertTask2 object to the Package object. Use the Execute method of the ExecuteSQLTask2 object to load the data.

Ans: C

60. You are a database developer for an insurance company. The company has a database named Policies. You have designed stored procedures for this database that will use cursors to process large result sets.

Analysts who use the stored procedures report that there is a long initial delay before data is displayed to them. After the delay, performance is adequate. Only data analysts, who perform data analysis, use the Policies database.

You want to improve the performance of the stored procedures.

Which script should you use?

A. EXEC sp_configure 'cursor threshold', 0

B. EXEC sp_dboption 'Policies' SET CURSOR_CLOSE_ON_COMMIT ON

C. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

D. ALTER DATABASE Policies SET CURSOR_DEFAULT LOCAL

Ans:A

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

41. You are a database developer for Litware,Inc. You are restructuring the company's sales database. The database contains customer information in a table named customers.

This table includes a character field named country that contains the name of the country in which the customer is located. You have created a new table named country.

The scripts that were used to create the customers and country tables are shown below:

CREATE TABLE dbo.Country

(

CountryID int IDENTITY(1,1) NOT NULL,

CountryName char(20) NOT NULL,

CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID)

)

CREATE TABLE dbo.Customers

(

CustomerID int NOT NULL,

CustomerName char(30) NOT NULL,

Country char(20) NULL,

CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomersID)

)

You must move the country information from the customers table into the new country tables as quickly as possible.

Which script should you use?

A. INSERT INTO Country (CountryName)

SELECT DISTINCT Country

FROM Customers

B. SELECT (*) AS ColID, cl.Country

INTO Country

FROM(SELECT DISTINCT Country FROM Customers)AS c1,

(SELECT DISTINCT Country FROM Customers) AS c2,

WHERE c1.Country >=c2.Country

GROUP BY c1.Country ORDER BY 1

C. DECLARE @Country char (20)

DECLARE cursor_country CURSOR

FOR SELECT Country FROM Customers

OPEN cursor_country

FETCH NEXT FROM cursor_country INTO @Country

WHILE (@@FETCH_STATUS <> -1)

BEGIN

If NOT EXISTS ( SELECT countryID

FROM Country

WHERE CountryName = @Country)

INSERT INTO Country (CountryName) VALUES (@Country)

FETCH NEXT FROM cursor_country INTO @Country

END

CLOSE cursor_country

DEALLOCATE cursor_country

D. DECLARE @SQL varchar (225)

SELECT @SQL = 'bcp "SELECT ColID = COUNT(*), c1. Country' +

'FROM (SELECT DISTINCT Country FROM Sales..Customers) AS

cl,'+

WHERE c1.Country >= c2.Country' +

'GROUP BY c1.Country ORDER BY 1" ' +

'query out c:\country.txt -c'

EXEC master..xp_cmdshell @SQL, no_output

EXEC master..xp_cmdshell 'bcp Sales..country in c:\country. Txt-c', no_output

Ans: C

WARNING!

The answer B fourth row had a typo with c1 incorrectly, should be c2 (corrected).

42. You are a database developer for Contoso, Ltd. The company has a database named Human Resources that contains information about all employees and office locations.

The database also contains information about potential employees and office locations.

The tables that contain this information are shown in the exhibit:

onClick="window.open('./70-229/imageT14.jpg','Exhibit','height=150,width=499,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Current employees are assigned to a location, and current locations have one or more employees assigned to them. Potential employees are not assigned to a location, and potential office locations do not have any employees assigned to them.

You need to create a report to display all current and potential employees and office locations. The report should list each current and potential location, followed by any employees who have been assigned to that location. Potential employees should be listed together.

Which script should you use?

A. SELECT l.LocationName, e.FirstName, e.LastName

FROM Employee AS e LEFT OUTER JOIN Location AS l

ON e.LocationID= l.LocationID

ORDER BY l.LocationName, e.LastName, e.FirstName

B. SELECT l.LocationName, e.FirstName, e.LastName

FROM Location AS l LEFT OUTER JOIN EMPLOYEE AS l

ON e.LocationID= l.LocationID

ORDER BY l.LocationName, e.LastName, e.FirstName

C. SELECT l.LocationName, e.FirstName, e.LastName

FROM Employee AS e FULL OUTER JOIN Location AS l

ON e.LocationID= l.LocationID

ORDER BY 1.LocationName, e.LastName, e.FirstName

D. SELECT l.LocationName, e.FirstName, e.LastName

FROM Employee AS e CROSS JOIN Location AS l

ORDER BY l.LocationName, e.LastName, e.FirstName

E. SELECT l.LocationName, e.FirstName, e.LastName

FROM Employee AS e, Location AS l

ORDER BY l.LocationName, e.LastName, e.FirstName

Ans: C

WARNING: in the orig razor the answer "E" was missing.

43. You are designing a database for a Web-based ticket reservation application. There might be 500 or more tickets available for any single event. Most users of the application will view fewer than 50 of the available tickets before purchasing tickets. However, it must be possible for a user to view the entire list of available tickets.

As the user scrolls through the list, the list should be updated to reflect tickets that have been sold to other users. The user should be able to select tickets from the list and purchase the tickets.

You need to design a way for the user to view and purchase available tickets.

What should you do?

A. Use a scrollable static cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases.

B. Use a scrollable dynamic cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases.

C. Use stored procedures to retrieve the list of tickets. Use a second stored procedure to make purchase.

D. Use a user-defined function to retrieve the list of tickets. Use a second stored procedure to make purchase.

Ans: B

44. You are a database consultant. You have been hired by a local dog breeder to develop a database. This database will be used to store information about the breeder's dogs.

You create a table named Dogs by using the following script:

CREATE TABLE[dbo].[Dogs]

(

[DogID] [int] NOT NULL,

[BreedID] [int] NOT NULL,

[Date of Birth] [datetime] NOT NULL,

[WeightAtBirth] [decimal] (5, 2) NOT NULL,

[NumberOfSiblings] [int] NULL,

[MotherID] [int] NOT NULL,

[FatherID] [int] NOT NULL

)_on [PRIMARY]

GO

ALTER TABLE [dbo].[Dogs] WITH NOCHECK ADD

CONSTRAINT [PK_Dogs]PRIMARY KEY CLUSTERED

(

[DogID]

) ON [PRIMARY]

GO

You must ensure that each dog has a valid value for the MotherID and FatherID columns. You want to enforce this rule while minimizing disk I/O.

What should you do?

A. Create an ALTER INSERT trigger on the dogs table that rolls back the transaction if the MotherID or FatherID column is not valid.

B. Create a table-level CHECK constraint on the MotherID and FatherID columns.

C. Create two FOREIGN KEY constraints, one constraint on the MotherID column and one constraint on the FatherID column. Specify that each constraint reference the DogID column.

D. Create a rule and bind it to the MotherID. Bind the same rule to the FatherID column.

Ans:C

45. You are the database developer for a company that provides consulting services. The company maintains data about its employees in a table named Employee.

The script that was used to create the employee table is shown below:

CREATE TABLE Employee

(

EmployeeID int NOT NULL,

EmpType char (1) NOT NULL,

EmployeeName char (50) NOT NULL,

Address char (50) NULL,

Phone char (20) NULL,

CONSTRAINT PK_Employee PRMARY KEY (Employee ID)

)

The Emp type column in this table is used to identify employees as Executive, Administrative, or Consultants. You need to ensure that the administrative employees can Add, Update, or Delete data for non-executive employees only.

What should you do?

A. Create a view, and include the WITH ENCRYPTION clause.

B. Create a view, and include the WITH CHECK OPTION clause.

C. Create a view, and include the SCHEMABINDING clause.

D. Create a view, and build a covering index on the view.

E. Create a user-defined function that returns a table containing the non-executive employees.

Ans:B

46. You are a database developer for an electric utility company. When customers fail to pay the balance on a billing statement before the statement due date, the balance of the billing statement needs to be increased by 1 percent each day until the balance is paid.

The company needs to track the number of overdue billing statements. You create a stored procedure to update the balances and to report the number of billing statements that are overdue.

The stored procedure is shown in the exhibit:

onClick="window.open('./70-229/imageT15.jpg','Exhibit','height=475,width=554,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

Each time the stored procedure executes without error, it reports that zero billing statements are overdue. However, you observe that balances are being updated by the procedure.

What should you do to correct the problem?

A. Replace the lines 12-17 of the stored procedure with the following: Return @@ROWCOUNT

B. Replace line 5-6 of the stored procedure with the following:

DECLARE @count int

Replace lines 12-17 with the following:

SET@Count = @ROWCOUNT

If @@ERROR = 0

Return @Count

Else

Return -1

C. Replace line 5 of the stored procedure with the following:

DECLARE @Err int, @Count int

Replace lines 12-17 with the following:

SELECT @Err = @@ERROR, @Count = @@ROWCOUNT

IF @Err = 0

Return @Count

Else

Return @Err

D. Replace line 5 of the stored procedure with the following: Return @@Error

E. Replace line 5 of the stored procedure with the following

DECLARE @Err int, @Count int

Replace line 9 with the following:

SET Balance = Balance 1.01, @Count = Count (*)

Replace line 15 with the following:

Return @Count

Ans:C

47. You are a database developer for an online electronics company. The company's product catalog is contained in a table named Products.

The Products table is frequently accessed during normal business hours. Modifications to the Products table are written to a table named PendingProductUpdate.

The tables are shown in the exhibit:

onClick="window.open('./70-229/imageT16.jpg','Exhibit','height=150,width=501,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

The PendingProductUpdate table will be used to update the Products table after business hours. The database server runs SQL Server 2000 and is set to 8.0 compatibility mode.

You need to create a script that will be used to update the products table.

Which script should you use?

A. UPDATE Products

SET p1.[Description]=p2.[Description], p1.UnitPrice =

P2.UnitPrice

FROM Product p1, PendingProductUpdate p2

WHERE p1.ProductID= p2.ProductID

GO

TRUNCATE TABLE PendingProductUpdate

GO

B. UPDATE Products p1
SET [Description]=p2.[Description], UnitPrice=P2.UnitPrice
FROM Product, PendingProductUpdate p2
WHERE p1.ProductID= p2.ProductID
GO
TRUNCATE TABLE PendingProductUpdate
GO

C. UPDATE Products p1

SET p1.[Description]=p2.[Description], p1.UnitPrice =

P2.UnitPrice

FROM (SELECT [Description], UnitPrice

FROM PendingProductUpdate p2

WHERE p1.ProductID= p2.ProductID

GO

TRUNCATE TABLE PendingProductUpdate

GO

D. UPDATE p1

SET p1.[Description]=p2.[Description], p1.UnitPrice = p2.UnitPrice

FROM Products p1, PendingProductUpdate p2

WHERE p1.ProductID= p2.ProductID

GO

TRUNCATE TABLE PendingProductUpdate

Ans: D

48. You are the database developer for a sporting goods company that exports products to customers worldwide. The company stores its sales information in a database named sales. Customer names are stored in a table named Customer in this database.

The script that was used to create this table is shown below:

CREATE TABLE customers (

CustmerID int NOT NULL,

CustomerName varchar(30) NOT NULL,

ContactName varchar(30) NULL,

Phone varchar(20) NULL,

Country varchar(30) NOT NULL,

CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)

)

There are usually only one or two customers per country. However, some countries have as many as 20 customers. Your company's marketing department wants to target its advertising to countries that have more than 10 customers.

You need to create a list of these countries for the marketing department.

Which script(s) should you use?

A. SELECT Country FROM Customers

GROUP BY Country HAVING COUNT (Country)>10

B. SELECT TOP 10 Country FROM Customers

C. SELECT TOP 10 Country FROM Customers

FROM (SELECT DISTINCT Country FROM Customers) AS X

GROUP BY Country HAVING COUNT(*)> 10

D. SELECT Country, COUNT (*) as "NumCountries"

FROM Customers

GROUP BY Country ORDER BY NumCountries Desc

Ans: A

WARNING! Answer D had here a "SET ROWCOUNT 10" as the first line, in the real exam it is NOT there!

49. You are a database developer for a sales organization. Your database has a table named Sales that contains summary information regarding the sales orders from salespeople.

The sales manager asks you to create a report of the salespeople who had the 20 highest total sales.

Which query should you use to accomplish this?

A. SELECT TOP 20 PERCENT LastName, FirstName, SUM (OrderAmount) AS ytd

FROM sales

GROUP BY LastName, FirstName

ORDER BY 3 DESC

B. SELECT LastName, FirstName, COUNT(*) AS sales

FROM sales

GROUP BY LastName, FirstName

HAVING COUNT (*) > 20

ORDER BY 3 DESC

C. SELECT TOP 20 LastName, FirstName, MAX(OrderAmount) AS ytd

FROM sales

GROUP BY LastName, FirstName

ORDER BY 3 DESC

D. SELECT TOP 20 LastName, FirstName, SUM (OrderAmount) AS ytd

FROM sales

GROUP BY LastName, FirstName

ORDER BY 3 DESC

E. SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd

FROM sales

GROUP BY LastName, FirstName

ORDER BY 3 DESC

Ans: E

50. You are a database developer for a travel agency. A table named FlightTimes in the Airlines database contains flight information for all airlines.

The travel agency uses an intranet-based application to manage travel reservations. This application retrieves flight information for each airline from the FlightTimes table. Your company primarily works with one particular airline. In the Airlines database, the unique identifier for this airline is 101.

The application must be able to request flight times without having to specify a value for the airline. The application should be required to specify a value for the airline only if a different airline's flight times are needed.

What should you do?

A. Create two stored procedures, and specify that one of the stored procedures should accept a parameter and that the other should not.

B. Create a user-defined function that accepts a parameter with a default value of 101.

C. Create a stored procedure that accepts a parameter with a default value of 101.

D. Create a view that filters the FlightTimes table on a value of 101.

E. Create a default of 101 on the FlightTimes table.

Ans: C

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

31. You are a database developer for a toy manufacturer. Each employee of the company is assigned to either an executive, administrative, or labor position. The home page of the company intranet displays company news that is customized for each position type.

When an employee logs on to the company intranet, the home page identifies the employee's position type and displays the appropriate company news. Company news is stored in a table named News, which is located in the corporate database.

The script that was used to create the News table is shown:

CREATE TABLE News

(

NewsID int NOT NULL

NewsText varchar (8000) NOT NULL

EmployeePositionType char (15) NOT NULL

DisplayUntil datetime NOT NULL

DateAdded datetime NOT NULL DEFAULT (getdate( ))

CONSTRAINT PK_News PRIMARY KEY (NewsID)

)

Users of the intranet need to view data in the news table, but do not need to insert, update, or delete data in the table.

You need to deliver only the appropriate data to the intranet, based on the employee's position type.

What should you do?

A. Create a view that is defined to return the rows that apply to a specified position type.

B. Create a stored procedure that returns the rows that apply to a specified position type.

C. Grant SELECT permissions on the EmployeePositionType column for each position type.

D. Grant permission on the News table for each position type.

Ans:B

32. You are a database developer for a Company that produces an online telephone directory.

A table named PhoneNumbers is shown in the exhibit:

onClick="window.open('./70-229/imageT11.jpg','Exhibit','height=280,width=235,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

After loading 100,000 names into the table, you create indexes by using the following script:

ALTER TABLE [dbo]. [phonenumbers] WITH NOCHECK ADD

CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED (

[FirstName],

[LastName],

) ON [PRIMARY]

GO

CREATE UNIQUE INDEX

[IX_PhoneNumbers] ON [dbo].[phonenumbers](

[PhoneNumberID]

) ON [PRIMARY]

GO

You are testing the performance of the database. You notice that queries such as the following take a long time to execute:

Return all names and phone numbers for persons who live in a certain city and whose last name begin with 'W'

How should you improve the processing performance of these types of queries? (Each correct answer presents part of the solution. Choose two)

A. Change the PRIMARY KEY constraint to use the LastName column followed by the FirstName column.

B. Add a nonclustered index on the City column.

C. Add a nonclustered index on the AreaCode, Exchange, and Number columns.

D. Remove the unique index from the PhoneNumberID column.

E. Change the PRIMARY KEY constraints to a nonclustered index.

F. Execute an UPDATE STATISTICS FULLSCAN ALL statement in SQL Query Analyzer.

ANS: A,B

33. You are a database developer for an insurance company. You are tuning the performance of queries in SQL Query Analyzer.

In the query pane, you create the following query:

SELECT P.PolicyNumber, P.IssueState, AP.Agent

FROM Policy AS P JOIN AgentPolicy AS AP

ON (P.PolicyNUmber = AP.PolicyNumber)

WHERE IssueState = 'IL'

AND PolicyDate BETWEEN '1/1/2000' AND '3/1/2000'

AND FaceAmount > 1000000

You choose display estimated execution plan from the query menu and execute the query.

The query execution plan that generated is shown below:

onClick="window.open('./70-229/imageT12.jpg','Exhibit','height=275,width=610,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

What should you do?

A. Rewrite the query to eliminate BETWEEN keyword

B. Add a join hint that includes the HASH option to the query

C. Add the WITH (INDEX(0)) table hint to the policy table

D. Update statistics on the Policy table

E. Execute the DBCC DBREINDEX statement on the policy table.

Ans:D

34. You are a database developer for an SQL Server 2000 database. You are planning to add new indexes, drop some indexes, and change other indexes to composite and covering indexes.

For documentation purposes, you must create a report that shows the indexes used by queries before and after you make changes.

What should you do?

A. Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report.

B. Execute each query in SQL Query Analyzer, and use the Show Execution Plan option. Use the output for the report

C. Run the Index Tuning Wizard against a Workload file. Use the output for the report

D. Execute the DBCC SHOW_STATISTICS statement. Use the output for the report

Ans:A

35. You are a database developer for a hospital. You are designing a SQL Server 2000 database that will contain physician and patient information. This database will contain a table named Physicians and a table named Patients.

Physicians treat multiple patients. Patients have a primary physician and usually have a secondary physician. The primary physician must be identified as the primary physician. The Patients table will contain no more than 2 million rows.

You want to increase I/O performance when data is selected from the tables. The database should be normalized to the third normal form.

Which script should you use to create the tables?

A. CREATE TABLE Physicians

(

Physicians ID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED

LastName_varchar(25) NOT NULL

)

GO

CRETAE TABLE Patient

(

PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED,

LastName varchar (25) NOT NULL,

FirstName varchar (25) NOT NULL,

PrimaryPhysician int NOT NULL,

SecondaryPhysician int NOT NULL,

CONSTRAINT PK_Patients_Physicians1 FOREIGN KEY (PrimaryPhysician) REFERENCES Physicians (PhysicianID),

CONSTRAINT PK_Patients_Physicians2 FOREIGN KEY (SecondaryPhysician) REFERENCES Physicians (PhysicianID)

)

B. CREATE TABLE Patient

(

Patient ID smallint NOT NULL CONSTRAINT PK_Patient PRIMARY KEY CLUSTERED,

LastName_varchar(25) NOT NULL,

FirstName varchar (25) NOT NULL,

PrimaryPhysician int NOT NULL,

SecondaryPhysician int NOT NULL,

)

GO

CRETAE TABLE Physicians

(

PhysicianID smallint NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED,

LastName varchar (25) NOT NULL,

FirstName varchar (25) NOT NULL,

CONSTRAINT PK_Physicians_Patients FOREIGN KEY (PhysicianID) REFERENCES Patients (PatientID)

)

C. CREATE TABLE Patients

(

PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED,

LastName varchar (25) NOT NULL,

FirstName varchar (25) NOT NULL,

)

GO

CREATE TABLE Physicians

(

PhysicianID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED,

LastName varchar (25) NOT NULL,

FirstName varchar (25) NOT NULL,

)

GO

CREATE TABLE PatientPhysician

(

PatientPhysicianID bigint NOT NULL CONSTRAINT PK_PatientsPhysician PRIMARY KEY CLUSTERED,

PhysicianID int NOT NULL,

PatientID int NOT NULL,

PrimaryPhysician bit NOT NULL,

FOREIGN KEY (PhysicianID) REFERENCES Physicians (PhysicianID),

FOREIGN KEY (PatientID) REFERENCES Patients (PatientID)

)

D. CREATE TABLE Patients

(

PatientID int NOT NULL PRIMARY KEY,

LastName varchar (25) NOT NULL,

FirstName varchar (25) NOT NULL,

)

GO

CREATE TABLE Physicians

(

PhysicianID int NOT NULL PRIMARY KEY,

LastName varchar (25) NOT NULL,

FirstName varchar (25) NOT NULL,

)

GO

CREATE TABLE PatientPhysician

(

PhysicianID int NOT NULL REFERENCES Physicians (PhysicianID),

PatientID int NOT NULL REFERENCES Patients (PatientID), PrimaryPhysician bit NOT NULL,

CONSTRAINT PK_PatientsPhysician PRIMARY KEY (PhysicianID, PatientID)

)

Ans:D

36. You are the database developer for your company's SQL Server 2000 database. This database contains a table named Invoices. You are a member of the db_owner role.

Eric, a member of the HR database role, created the Trey_Research_Updateinvoices trigger on the Invoices table. Eric is out of the office, and the trigger is no longer needed.

You execute the following statement in the sales database to drop the trigger:

DROP TRIGGER Trey Research_UpdateInvoices

You receive the following error message:

Cannot drop the trigger 'Trey Research_UpdateInvoices', because it does not exist in the system catalog.

What should you do before you can drop the trigger?

A. Add your login name to the HR database role.

B. Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.

C. Disable the trigger before executing the DROP TRIGGER statement.

D. Define the trigger number in the DROP TRIGGER statement.

E. Remove the text of the trigger from the sysobjects and syscomments system tables.

Ans:B

37. You have designed the database for a Web site that is used to purchase concert tickets. During a ticket purchase, a buyer views a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor.

For popular concerts, thousands of buyers might attempt to purchase tickets at the same time.

Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data.

How should you design the cursor?

A. Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ.

B. Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.

C. Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an implicit transaction.

D. Create a cursor that uses positioned updates. Include the SCROLL_LOCKS argument in the cursor definition to enforce pessimistic concurrency. In the cursor, place the positioned UPDATE statements within an implicit transaction.

Ans: B

38. You are a database developer for a company that conducts telephone surveys of consumer music preferences. As the survey responses are received from the survey participants, they are inserted into a table named SurveyData.

After all of the responses to a survey are received, summaries of the results are produced.

You have been asked to create a summary by sampling every fifth row of responses for a survey. You need to produce the summary as quickly as possible.

What should you do?

A. Use a cursor to retrieve all of the data for the survey. Use the FETCH RELATIVE 5 statement to select the summary data from the cursor.

B. Use a SELECT INTO statement to retrieve the data for the survey into a temporary table. Use a SELECT TOP 1 statement to retrieve the first row from the temporary table.

C. Set the query rowcount to five. Use a SELECT statement to retrieve and summarize the survey data.

D. Use a SELECT TOP 5 statement to retrieve and summarize the survey data.

Ans: A

39. You are a database developer for a lumber company. You are performing a one-time migration from a flat-file database to SQL Server 2000. You export the flat-file database to a text file in comma-delimited format.

The text file is shown in the Import file below:

1111, '*4 Interior', 4, 'Interior Lumber', 1.12

1112, '2*4 Exterior', 5, 'Exterior Lumber', 1.87

2001, '16d galvanized',2, 'Bulk Nails', 2.02

2221, '8d Finishing brads',3, 'Nails', 0.01

You need to import this file into SQL Server tables named Product and Category.

The product and category tables are shown in the product and Category Tables exhibit:

onClick="window.open('./70-229/imageT13.jpg','Exhibit','height=150,width=505,status=no,toolbar=no,menubar=no,location=no,titlebar=no,scrollbars=no,alwaysRaised=1,resizable=yes,alwaysontop=yes')">Exhibit

You want to import the data using the least amount of administrative effort.

What should you do?

A. Use the bcp utility, and specify the -t option.

B. Use the BULK INSERT statement, and specify the FIRE_TRIGGERS argument.

C. Use the SQL-DMO BulkCopy2 object and set the TableLock property to TRUE.

D. Use data transformation services to create two Transform Data tasks. For each task, map the text file columns to the database columns.

Ans:D

40. You are a database developer for a database named Accounts at Woodgrove Bank. A developer is creating a multi-tier application for the bank. Bank employees will use the application to manage customer accounts.

The developer needs to retrieve customer names from the accounts database to populate a drop-down list box in the application. A user of the application will use the list box to locate a customer account.

The database contains more than 50,000 customer accounts. Therefore, the developer wants to retrieve only 25 rows as the user scrolls through the list box. The most current list of customers must be available to the application at all times.

You need to recommend a strategy for the developer to use when implementing the drop-down list box.

What should you recommend?

A. Create a stored procedure to retrieve all of the data that is loaded into the list box.

B. Use an API server-side cursor to retrieve the data that is loaded into list box.

C. Retrieve all of the data at once by using a SELECT statement, and then load the data into the list box.

D. Use a Transact-SQL server-side cursor to retrieve the data is loaded into the list box.

Ans: B

Microsoft Certification

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Related Links

Microsoft Certifications