(Ex:350-001):

1.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 permission to submit queries against the table.
Answer: C

3. You are a database developer for Coho Vineyard. You are designing a stored procedure that will be used to add customers to a database. The database schema is shown in the Database Schema exhibit. (Click the <> button.)
You create a stored procedure named spAddCustomer. This stored procedure is shown in the SpAddCustomer exhibit. (Click the <> button.)
You use the following script to test the stored procedure:
SELECT * FROM Customers WHERE CustomerID = 'COHO'
EXEC spAddCustomer 'COHO','Coho Vineyard'
SELECT * FROM Customers WHERE CustomerID = 'COHO'
The output from this script is shown in the Output exhibit. (Click the <> button.)
You need to replace the stored procedure with one that correctly processes the test script. Which stored procedure should you use?
A. CREATE PROCEDURE spAddCustomer @CustomerID varchar(5), @CoName varchar(40) AS SET IMPLICIT_TRANSACTIONS ON BEGIN TRANSACTION INSERT INTO Customer values ( @CustomerID, @CoName, NULL, NULL, NULL ) INSERT INTO CustomerCustomerDemo values
( @CustomerID, NULL ) COMMIT TRANSACTION
B. CREATE PROCEDURE spAddCustomer @CustomerID varchar(5), @CoName varchar(40) AS BEGIN TRANSACTION INSERT INTO Customer values ( @CustomerID, @CoName, NULL, NULL, NULL ) IF @@ERROR <> 0 RAISERROR ('Inserting into Customer', 16, 1) INSERT INTO
CustomerCustomerDemo values ( @CustomerID, NULL ) IF @@ERROR <> 0 RAISERROR ('Inserting into CustomerCustomerDemo', 16, 1) COMMIT TRANSACTION
C. CREATE PROCEDURE spAddCustomer @CustomerID varchar(5), @CoName varchar(40) AS BEGIN TRANSACTION INSERT INTO Customer values ( @CustomerID, @CoName, NULL, NULL, NULL ) IF @@ERROR <> 0 GOTO on_error INSERT INTO CustomerCustomerDemo values ( @CustomerID, NULL ) IF @@ERROR <> 0 GOTO on_error COMMIT TRANSACTION on_error: ROLLBACK TRANSACTION
D. CREATE PROCEDURE spAddCustomer @CustomerID varchar(5), @CoName varchar(40) AS BEGIN TRANSACTION INSERT INTO Customer values ( @CustomerID, @CoName, NULL, NULL, NULL ) IF @@ERROR <> 0 RETURN (-1) INSERT INTO CustomerCustomerDemo values ( @CustomerID, NULL ) IF @@ERROR <> 0 RETURN (-1) COMMIT TRANSACTION
Answer: C

4. You are the developer of a reporting and analysis database for a small insurance company. You create a table named DailyPremiums by using the script shown in the Script for DailyPremiums Table exhibit. (Click the <> button.)
This table contains the daily totals of life insurance and auto insurance premium payments.
Data is entered into this database from two applications. One application enters the daily total of life insurance premiums and the other enters the daily total of auto insurance premiums. Some days, no premiums are collected for life insurance policies or auto insurance policies. The application that enters the life insurance premiums represents no premiums with a value of zero. The application that enters the auto insurance premiums represents no premiums with a null value. An example of a week's data is shown in the Sample Data exhibit. (Click the <> button.)
You need to create a report that displays the average premium for life insurance policies and auto insurance policies.
Which script should you use?
A. SELECT AVG(LifePolicy), AVG(AutoPolicy) FROM DailyPremiums
B. SELECT AVG(NULLIF(LifePolicy, 0)), AVG(AutoPolicy) FROM DailyPremiums
C. SELECT AVG (LifePolicy), AVG(ISNULL(AutoPolicy, 0)) FROM DailyPremiums
D. SELECT AVG(LifePolicy), SUM(AutoPolicy)/SUM(CASE WHEN AutoPolicy IS NULL THEN 0 ELSE 1 END) FROM DailyPremiums
Answer: C

5. You are a database developer for a publishing company. You are designing a series of databases that will store excerpts of news articles. The database will be used for this purpose only. The data that will be stored includes text and images. The text columns will be bulk loaded from files. Each database will store data for one month of articles. Your database maintenance plan includes weekly re-indexing of the tables within each database.
You must minimize the impact of the database maintenance on users of the database while retaining the ability to recover from media failure. You want to accomplish this by using the least amount of administrative effort.
You are creating the first database, which is named Jan00. Which script should you use to create this database?
A. CREATE DATABASE Jan00 ON ( NAME = Jan00_dat, FILENAME = 'c:dataJan00.mdf')
GO ALTER DATABASE Jan00 SET RECOVERY BULK_LOGGED GO
B. ALTER DATABASE model SET RECOVERY BULK_LOGGED GO CREATE DATABASE Jan00 ON ( NAME = Jan00_dat, FILENAME = 'c:dataJan00.mdf') GO
C. CREATE DATABASE Jan00 ON ( NAME = Jan00_dat, FILENAME = 'c:dataJan00.mdf') GO ALTER DATABASE Jan00 SET RECOVERY FULL GO
D. CREATE DATABASE Jan00 ON ( NAME = Jan00_dat, FILENAME = 'c:dataJan00.mdf') GO ALTER DATABASE Jan00 SET RECOVERY SIMPLE
GO
Answer: B

6. 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. he main office database is used to consolidate information from the regional office databases. The tables 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 main office as a central Subscriber.
Answer: D

7. 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 is generated is shown in the Estimated Execution Plan exhibit. (Click the <> button.)
You then choose Show Execution Plan from the Query menu and execute the query. The query execution plan that is generated is shown in the Query Execution Plan exhibit. (Click the <> button.)
What should you do?
A. Rewrite the query to eliminate the 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.
Answer: D

8. You are a database developer for a sales company. The company's production database is hosted on a computer running SQL Server 2000. You are developing a view on the company's developer database. On the developer database, you design a view and an index on the view. The script that was used to create this view is shown in the exhibit. (Click the <> button.)
After you complete your testing on the developer database, you successfully create this view on the production database. You attempt to create the index on this view, but you are unable to do so. What should you do? (Each correct answer presents part of the solution. Choose two.)
A. Replace COUNT_BIG(*) with COUNT(*) in the select list.
B. Change the table names from two-part names to one-part names.
C. Re-create the view by using the WITH SCHEMABINDING option.
D. Remove AVG(SaleAmount) from the select list. Include AVG(SaleAmount) in the query that accesses the view.
E. Before creating the index, execute the SET NUMERIC_ROUNDABORT ON statement.
Answer: CD

2. You are a database developer for Lucerne Publishing. The company's existing Human Resources database is a dBASE 5.0 database. You are migrating this database to a SQL Server 2000 database. You are creating a Data Transformation Services (DTS) package to perform this migration. To prepare for import into the SQL Server database, you export the dBASE 5.0 table named Employee into comma-delimited text files. One of the text files contains the following columns: EmpID, Last, First, DeptID, MgrID, and deleted. This text file will be used to populate the Employee table in SQL Server. You will import only those records that have not been marked for deletion. ou want to load the data into the table as quickly as possible while minimizing space requirements. How should you create the DTS package?
A. Create a Data Driven Query task to import the data into the Employee table, and specify that the deleted column contains a zero value.
B. Use the bcp utility to create a format file, and then edit the file so that it contains the columns you want to import.
Use a Bulk Insert task to import the data into the Employee table. Specify the BatchSize property equal to 100. Specify the CHECK_CONSTRAINTS property, and allow only those rows that contain a zero value for the deleted column.
C. Create a Transform Data task to import the data into the Employee table. Use an ActiveX script to process the rows individually, and accept only those rows that contain a zero value for the deleted column.
D. Create a temporary table. Use the Bulk Insert task to load the data from the text file into the temporary table.
Use an Execute SQL task to insert the data from the temporary table into the Employee table for those rows that contain a zero value for the deleted column.
Answer: C