Foolproof 70 461 training kit pdf tips
Act now and download your Microsoft 70 461 practice test test today! Do not waste time for the worthless Microsoft exam 70 461 dumps tutorials. Download Rebirth Microsoft Querying Microsoft SQL Server 2012 exam with real questions and answers and begin to learn Microsoft 70 461 pdf with a classic professional.
Q41. You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire
transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?
A. SET XACT_ABORT ON
B. SET ARITHABORT ON
E. SET ARITHABORT OFF
F. SET XACT_ABORT OFF
Q42. DRAG DROP
You create a view based on the following statement:
You grant the Select permission to User1 for this view.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Q43. CORRECT TEXT
You have a database that contains the tables as shown below:
You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements:
The code must NOT use aliases.
The code must NOT use object delimiters.
The objects called in Procedure1 must be able to be resolved by all users.
OrderDate must be compared to Parameter1 after the data type of Parameter1 is
changed to datetime.
Which SELECT statement should you use?
To answer, type the correct code in the answer area.
Q44. You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal.
You write the following Transact-SQL query:
. INSERT INTO OrdersHistorical
. SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?
Q45. You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?
Decimal is the only one in the list that can give a fixed precision and scale. Reference: http://msdn.microsoft.com/en-us/library/ms187746.aspx
Q46. You administer a Microsoft SQL Server 2012 database.
The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database?
Use the EmployeeID, which would be used as a primary key in the Employee table, when defining a foreign key constraint from another table in the database.
Q47. You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition:
You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated.
Which Transact-SQL query should you use?
A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice) - - Create Audit Records
B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR EXISTS (SELECT WholeSalePnce FROM inserted) - - Create Audit Records
C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED(RetailPrice, WholesalePrice) - - Create Audit Records
D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) - - Create Audit Records
Q48. You develop a database for a travel application. You need to design tables and other database objects.
You create the Airline_Schedules table.
You need to store the departure and arrival dates and times of flights along with time zone
What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.
Q49. You develop a database application. You create four tables. Each table stores different categories of products.
You create a Primary Key field on each table.
You need to ensure that the following requirements are met:
The fields must use the minimum amount of space.
The fields must be an incrementing series of values.
The values must be unique among the four tables.
What should you do?
A. Create a ROWVERSION column.
B. Create a SEQUENCE object that uses the INTEGER data type.
C. Use the INTEGER data type along with IDENTITY
D. Use the UNIQUHDENTTFIER data type along with NEWSEQUENTIALID()
E. Create a TIMESTAMP column.
Q50. CORRECT TEXT
You have a database named Sales that contains the tables as shown in the exhibit. (Click
the Exhibit button.)
You need to create a query that meets the following requirements:
References columns by using one-part names only.
Groups aggregates by SalesTerritorylD, and then by ProductlD.
Orders the results in descending order by SalesTerritorylD and then by ProductlD.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.