Microsoft 70-461 Actual Free Exam Questions & Community Discussion
You administer a Microsoft Azure SQL Database instance.
You are troubleshooting a number of stored procedures that use transactions.
p_ModifyCustomer modifies customer records in the database. Processes that uses these records must receive a copy of the record as it exists at the beginning of the transaction, and the procedure must not block these processes.
p_GetOrders is used to retrieve orders for a customer. While the transaction is running, no other process should be able to read the same data, and no other transaction should be able to modify the data until the transaction completes.
p_ShipOrders is run once per day to batch orders into shipping criteria. While this transaction is running, no other transaction should be allowed to insert data into the range of orders being modified.
You need to choose the appropriate transaction isolation level for each stored procedure. The transaction must meet the need while providing the highest level of concurrency and performance.
Which isolation levels should you use? To answer, drag the appropriate isolation levels to correct stored procedures. Each isolation level may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
You are troubleshooting a number of stored procedures that use transactions.
p_ModifyCustomer modifies customer records in the database. Processes that uses these records must receive a copy of the record as it exists at the beginning of the transaction, and the procedure must not block these processes.
p_GetOrders is used to retrieve orders for a customer. While the transaction is running, no other process should be able to read the same data, and no other transaction should be able to modify the data until the transaction completes.
p_ShipOrders is run once per day to batch orders into shipping criteria. While this transaction is running, no other transaction should be allowed to insert data into the range of orders being modified.
You need to choose the appropriate transaction isolation level for each stored procedure. The transaction must meet the need while providing the highest level of concurrency and performance.
Which isolation levels should you use? To answer, drag the appropriate isolation levels to correct stored procedures. Each isolation level may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Correct Answer:
Explanation:
Box 1: READ_COMMITTED
READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option Box 2: REPEATABLE_READ:
REPEATABLE_READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction.
Box 3: SERIALIZABLE
SERIALIZABLE Specifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
Incorrect Answers:
Not READ:UNCOMMITTED:
Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads.
Your database contains a table named Products that has columns named ProductID and Name.
You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time.
You need to view rows 31 through 45.
Which Transact-SQL query should you create?
You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time.
You need to view rows 31 through 45.
Which Transact-SQL query should you create?
Correct Answer: C
Vote an answer
Explanation: Only visible for EduDump members. You can sign-up / login (it's free).
You have a SQL Server database that contains all of the sales data for your company.
You need to create a query that returns the customers who represent the top five percent of the total actual sales.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
You need to create a query that returns the customers who represent the top five percent of the total actual sales.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
Correct Answer: B
Vote an answer
Explanation: Only visible for EduDump members. You can sign-up / login (it's free).
You have a database named SALES that stored the sales data and the salespeople for your company.
You need to create a function that is passed a ProductID and then returns a list of SalesOrderID values for orders that must be associated to a salesperson in the future. The function must be able to return multiple SalesOrderID values.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
You need to create a function that is passed a ProductID and then returns a list of SalesOrderID values for orders that must be associated to a salesperson in the future. The function must be able to return multiple SalesOrderID values.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
Correct Answer: B
Vote an answer
Explanation: Only visible for EduDump members. You can sign-up / login (it's free).
You have a Microsoft SQL Server database. The database contains a table that is defined by the following Transact-SQL statement:
Employee names must always start with a capital letter.
You need to define a constraint to enforce the employee name requirement.
Which Transact-SQL statement should you use?
Employee names must always start with a capital letter.
You need to define a constraint to enforce the employee name requirement.
Which Transact-SQL statement should you use?
Correct Answer: A
Vote an answer
You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.
You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?
You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?
Correct Answer: C
Vote an answer
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.
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.
Correct Answer: B
Vote an answer
Explanation: Only visible for EduDump members. You can sign-up / login (it's free).
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.
Which Transact-SQL query should you use?
You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.
Which Transact-SQL query should you use?
Correct Answer: C
Vote an answer
You develop a Microsoft SQL Server database. The database contains a table defined by the following Transact-SQL statement:
All regions have more than one employee.
You need to write a query to find the employee with the highest SalesYTD in each region with the following result set:
* First Name
* Last Name
* Region
* Sales YTD
Which Transact-SQL query should you run?
All regions have more than one employee.
You need to write a query to find the employee with the highest SalesYTD in each region with the following result set:
* First Name
* Last Name
* Region
* Sales YTD
Which Transact-SQL query should you run?
Correct Answer: D
Vote an answer
Explanation: Only visible for EduDump members. You can sign-up / login (it's free).
You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.
You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.
You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.
Which Transact-SQL statement should you use?
You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.
You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.
Which Transact-SQL statement should you use?
Correct Answer: D
Vote an answer
A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
The Production.Product table contains the following two columns:
The Sales.SalesOrderDetail table contains the following three columns:
You need to create a query listing all of the products that were never ordered.
Which statements should you execute?
The Production.Product table contains the following two columns:
The Sales.SalesOrderDetail table contains the following three columns:
You need to create a query listing all of the products that were never ordered.
Which statements should you execute?
Correct Answer: D
Vote an answer
Explanation: Only visible for EduDump members. You can sign-up / login (it's free).
0
0
0
10