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:
You receive the output as shown in this 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:
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:
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:
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:
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:
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