70-461 Exam
Foolproof mcsa 70-461 tips
Proper study guides for Up to the minute Microsoft Querying Microsoft SQL Server 2012 certified begins with Microsoft 70-461 preparation products which designed to deliver the Pinpoint 70-461 questions by making you pass the 70-461 test at your first time. Try the free 70-461 demo right now.
2021 Sep 70-461 querying microsoft sql server 2012 dumps:
Q11. You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3.
You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?
A. USE Database2
B. EXECUTE AS OWNER
C. USE Database1
D. EXECUTE AS CALLER
Answer: B
Q12. 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
C. TRY
D. BEGIN
E. SET ARITHABORT OFF
F. SET XACT_ABORT OFF
Answer: A
Q13. You develop a Microsoft SQL Server 2012 database. The database is used by two web
applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
. Future modifications to the table definition will not affect the applications' ability to access data. . The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the applications.
What should you create for each application?
A. Synonyms
B. Common table expressions
C. Views
D. Temporary tables
Answer: C
Q14. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
. Future modifications to the table definition will not affect the applications' ability to access data. . The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing applications.
What should you create for each application?
A. table partitions
B. views
C. table-valued functions
D. stored procedures
Answer: B
Q15. CORRECT TEXT
You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)
You have the following query:
You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
....
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer:
Renewal sql server 2012 certification 70-461:
Q16. 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?
A. DateHired
B. Departments
C. EmployeeID
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: C
Explanation:
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.
Q17. You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that a large amount of memory is consumed by single-use dynamic queries.
You need to reduce procedure cache usage from these statements without creating any additional indexes.
What should you do?
A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
Answer: G
Q18. 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
Answer: D
Q19. You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. 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.
Answer: E
Q20. CORRECT TEXT
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named invoice. The solution must ensure
that the invoice is validated by using Sales.InvoiceSchema.
Provide the correct code in the answer area.
Answer:
DECLARE @invoice XML(Sales.InvoiceSchema)