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:
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:
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:
To complete the script look at the following exibit (Drag and Drop style)
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
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:
A sample of the data contained in thee tables is shown in this Sample Data 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:
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:
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