70-229 Exam
Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition
- Exam Number/Code : 70-229
- Exam Name : Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition
- Questions and Answers : 193 Q&As
- Update Time: 2011-09-21
- Price:
$ 119.00$ 69.00
Free 70-229 Demo Download
Test4pass offers free demo for MCSE 2003 70-229 exam (Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition). You can check out the interface, question quality and usability of our practice exams before you decide to buy it. We are the only one site can offer demo for almost all products.
Exam Description
It is well known that 70-229 exam test is the hot exam of Microsoft certification. Test4pass offer you all the Q&A of the 70-229 real test . It is the examination of the perfect combination and it will help you pass 70-229 exam at the first time!
Why choose Test4pass 70-229 braindumps
Quality and Value for the 70-229 Exam
100% Guarantee to Pass Your 70-229 Exam
Downloadable, Interactive 70-229 Testing engines
Verified Answers Researched by Industry Experts
Drag and Drop questions as experienced in the Actual Exams
Practice Test Questions accompanied by exhibits
Our Practice Test Questions are backed by our 100% MONEY BACK GUARANTEE.
Microsoft MCSE 2003 70-229 exam braindumps questions and answers
¡¡
¡¡
Exam : Microsoft 70-229
Title : Microsoft Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition
1. 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
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
3. You are a database developer for your company's SQL Server 2000 database named Sales. The database contains tables named Orders, OrderLineItems, and Customers. The Orders and OrderLineItems tables have frequent insert activity during the day. You have created identical views on the Orders, OrderLineItems, and Customers tables for the years 1998 and 199 The script that was used to create the Orders1999 view is shown in the exhibit. (Click the <> button.)
Queries such as the following are frequently executed on the views:
SELECT OrderDate, Total, Discount, Company, EmployeeID
FROM Orders1999
WHERE OrderDate BETWEEN '10/1/1999' AND '12/31/1999' AND CompanyName = 'Tailspin Toys'
You want to improve the response time of this query. What should you do?
A. Alter the view to use the WITH CHECK OPTION clause.
B. Create a computed column on the OrderLineItems table by using the formula UnitPrice * Quantity.
C. Create a composite nonclustered index on the OrderID, EmployeeID, and CustomerID columns of the Orders table.
D. Create a nonclustered index on the OrderDate column of the views. E. Create a unique clustered index on the OrderID column of the views.
Answer: E
4. 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
5. 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
6. You are a database developer for a SQL Server 2000 database. The database is in the default configuration. The number of users accessing the database has increased from 100 to 1,000 in the last month. Users inform you that they are receiving error messages frequently. The following is an example of an error message that was received: Transaction (Process ID 56) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. What should you do?
A. Use a higher transaction isolation level for transactions used in the database.
B. Use SQL Profiler to capture deadlock events.
C. Use System Monitor to monitor lock requests and deadlocks.
D. Execute the sp_configure system stored procedure to increase the number of simultaneous user connections allowed to SQL Server.
E. Execute the sp_configure system stored procedure to increase the number of locks that can be used by SQL Server.
Answer: B
7. You are a database developer for a community college. You are designing a database that will be used for class registrations. You review the database design, which is shown in the exhibit. (Click the <> button.)
You want to normalize data, promote quick response times for queries, and minimize redundant data. What should you do? (Each correct answer presents part of the solution. Choose four.)
A. Create a UNIQUE constraint on the Class1, Class2, and Class3 columns.
B. Create a composite PRIMARY KEY constraint on the StudentID and Faculty columns of the Students table.
C. Create a new table named Faculty. Add FacultyID, Name, and Room columns to this table.
D. Create a new table named FacultyOffice. Add FacultyID and Room columns to this table.
E. Create a new table named CourseFaculty. Add FacultyID and ClassID columns to this table.
F. Create a new table named StudentFaculty. Add StudentID and FacultyID columns to this table.
G. Create a new table named StudentRegistration. Add StudentID and ClassID columns to this table.
Answer: CEFG
8. 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
9. 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
10. You are designing a database for Tailspin Toys. You review the database design, which is shown in the exhibit. (Click the <> button.)
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.
Answer: D
11. You are a database developer for a technical training center. Currently, administrative employees keep records of students, instructors, courses, and classroom assignments only on paper. The training center wants to eliminate the use of paper to keep records by developing a database to record this information. You design the tables for this database. Your design is shown in the exhibit. (Click the <> button.)
You want to promote quick response times for queries and minimize redundant data. What should you do?
A. Create a new table named Instructors. Include an InstructorID column, an InstructorName column, and an OfficePhone column. Add an InstructorID column to the Courses table.
B. Move all the columns from the Classroom table to the Courses table, and drop the Classroom table.
C. Remove the PRIMARY KEY constraint from the Courses table, and replace the PRIMARY KEY constraint with a composite PRIMARY KEY constraint based on the CourseID and CourseTitle columns.
D. Remove the ClassroomID column, and base the PRIMARY KEY constraint on the ClassroomNumber and ClassTime columns.
Answer: A
12. 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
13. 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
14. You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is shown in the exhibit. (Click the <> button.)
You want to promote quick response times for queries and minimize redundant data. What should you do? (Each correct answer presents part of the solution. Choose two.)
A. Create a new table named OrdersDetail. Add OrderID, ProductID, and Quantity columns to this table.
B. Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table.
C. Remove the ProductID and Quantity columns from the Orders table.
D. Create a UNIQUE constraint on the OrderID column of the Orders table.
E. Move the UnitPrice column from the Products table to the Orders table.
Answer: AC
Click Online chat to talk with us , get more informations about Microsoft MCSE 2003 70-229 practice exam study guides questions and answers
¡¡
Exam : Microsoft 70-229
Title : Microsoft Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition
1. 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
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
3. You are a database developer for your company's SQL Server 2000 database named Sales. The database contains tables named Orders, OrderLineItems, and Customers. The Orders and OrderLineItems tables have frequent insert activity during the day. You have created identical views on the Orders, OrderLineItems, and Customers tables for the years 1998 and 199 The script that was used to create the Orders1999 view is shown in the exhibit. (Click the <> button.)
Queries such as the following are frequently executed on the views:
SELECT OrderDate, Total, Discount, Company, EmployeeID
FROM Orders1999
WHERE OrderDate BETWEEN '10/1/1999' AND '12/31/1999' AND CompanyName = 'Tailspin Toys'
You want to improve the response time of this query. What should you do?
A. Alter the view to use the WITH CHECK OPTION clause.
B. Create a computed column on the OrderLineItems table by using the formula UnitPrice * Quantity.
C. Create a composite nonclustered index on the OrderID, EmployeeID, and CustomerID columns of the Orders table.
D. Create a nonclustered index on the OrderDate column of the views. E. Create a unique clustered index on the OrderID column of the views.
Answer: E
4. 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
5. 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
6. You are a database developer for a SQL Server 2000 database. The database is in the default configuration. The number of users accessing the database has increased from 100 to 1,000 in the last month. Users inform you that they are receiving error messages frequently. The following is an example of an error message that was received: Transaction (Process ID 56) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. What should you do?
A. Use a higher transaction isolation level for transactions used in the database.
B. Use SQL Profiler to capture deadlock events.
C. Use System Monitor to monitor lock requests and deadlocks.
D. Execute the sp_configure system stored procedure to increase the number of simultaneous user connections allowed to SQL Server.
E. Execute the sp_configure system stored procedure to increase the number of locks that can be used by SQL Server.
Answer: B
7. You are a database developer for a community college. You are designing a database that will be used for class registrations. You review the database design, which is shown in the exhibit. (Click the <> button.)
You want to normalize data, promote quick response times for queries, and minimize redundant data. What should you do? (Each correct answer presents part of the solution. Choose four.)
A. Create a UNIQUE constraint on the Class1, Class2, and Class3 columns.
B. Create a composite PRIMARY KEY constraint on the StudentID and Faculty columns of the Students table.
C. Create a new table named Faculty. Add FacultyID, Name, and Room columns to this table.
D. Create a new table named FacultyOffice. Add FacultyID and Room columns to this table.
E. Create a new table named CourseFaculty. Add FacultyID and ClassID columns to this table.
F. Create a new table named StudentFaculty. Add StudentID and FacultyID columns to this table.
G. Create a new table named StudentRegistration. Add StudentID and ClassID columns to this table.
Answer: CEFG
8. 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
9. 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
10. You are designing a database for Tailspin Toys. You review the database design, which is shown in the exhibit. (Click the <> button.)
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.
Answer: D
11. You are a database developer for a technical training center. Currently, administrative employees keep records of students, instructors, courses, and classroom assignments only on paper. The training center wants to eliminate the use of paper to keep records by developing a database to record this information. You design the tables for this database. Your design is shown in the exhibit. (Click the <> button.)
You want to promote quick response times for queries and minimize redundant data. What should you do?
A. Create a new table named Instructors. Include an InstructorID column, an InstructorName column, and an OfficePhone column. Add an InstructorID column to the Courses table.
B. Move all the columns from the Classroom table to the Courses table, and drop the Classroom table.
C. Remove the PRIMARY KEY constraint from the Courses table, and replace the PRIMARY KEY constraint with a composite PRIMARY KEY constraint based on the CourseID and CourseTitle columns.
D. Remove the ClassroomID column, and base the PRIMARY KEY constraint on the ClassroomNumber and ClassTime columns.
Answer: A
12. 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
13. 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
14. You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is shown in the exhibit. (Click the <> button.)
You want to promote quick response times for queries and minimize redundant data. What should you do? (Each correct answer presents part of the solution. Choose two.)
A. Create a new table named OrdersDetail. Add OrderID, ProductID, and Quantity columns to this table.
B. Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table.
C. Remove the ProductID and Quantity columns from the Orders table.
D. Create a UNIQUE constraint on the OrderID column of the Orders table.
E. Move the UnitPrice column from the Products table to the Orders table.
Answer: AC
Click Online chat to talk with us , get more informations about Microsoft MCSE 2003 70-229 practice exam study guides questions and answers
Test4pass 70-229 Exam Features
Quality and Value for the 70-229 Exam
Test4pass Practice Exams for Microsoft 70-229 are written to the highest standards of technical accuracy, using only certified subject matter experts and published authors for development.
100% Guarantee to Pass Your 70-229 Exam
If you prepare for the exam using our Test4pass testing engine, we guarantee your success in the first attempt. If you do not pass the MCSE 2003 70-229 exam (ProCurve Secure WAN) on your first attempt we will give you a FULL REFUND of your purchasing fee AND send you another same value product for free.
Microsoft 70-229 Downloadable, Printable Exams (in PDF format)
Our Exam 70-229 Preparation Material provides you everything you will need to take your 70-229 Exam. The 70-229 Exam details are researched and produced by Professional Certification Experts who are constantly using industry experience to produce precise, and logical. You may get questions from different web sites or books, but logic is the key. Our Product will help you not only pass in the first try, but also save your valuable time.
70-229 Downloadable, Interactive Testing engines
We are all well aware that a major problem in the IT industry is that there is a lack of quality study materials. Our Exam Preparation Material provides you everything you will need to take a certification examination. Like actual certification exams, our Practice Tests are in multiple-choice (MCQs) Our Microsoft 70-229 Exam will provide you with free 70-229 dumps questions with verified answers that reflect the actual exam. These questions and answers provide you with the experience of taking the actual test. High quality and Value for the 70-229 Exam:100% Guarantee to Pass Your MCSE 2003 exam and get your MCSE 2003 Certification.
Test4pass 70-229 examTest4pass 70-229 pdf exam
Test4pass 70-229 braindumps
Test4pass 70-229 study guides
Test4pass 70-229 trainning materials
Test4pass 70-229 simulations
Test4pass 70-229 testing engine
Test4pass 70-229 vce
Test4pass 70-229 torrent
Test4pass 70-229 dumps
free download 70-229
Test4pass 70-229 practice exam
Test4pass 70-229 preparation files
Test4pass 70-229 questions
Test4pass 70-229 answers
http://www.test4pass.com/70-229-exam.html The safer.easier way to get MCSE 2003 Certification
MCSE Certification
MCSD.NET Certification
MCDBA Certification
.
My Shopping Cart
