IT認証試験問題集
毎月、ITshikenは1500人以上の受験者が試験準備を助けて、試験に合格するために受験者にご協力します
 ホームページ / 70-461 問題集  / 70-461 問題練習

Microsoft 70-461 問題練習

Querying Microsoft SQL Server 2012 試験

最新更新時間: 2019/07/10,合計527問。

平成から令和へ:70-461 最新真題を買う時、日本語版と英語版両方を同時に獲得できます。

実際の問題集を練習し、試験のポイントを了解し、テストに申し込むするかどうかを決めることができます。

さらに試験準備時間の35%を節約するには、70-461 問題集を使用してください。

 / 36

Question No : 1
You need to execute an UPDATE statement that modifies a subset of rows in a table. The rows affected by the UPDATE statement must only be committed if the row count criteria is met.
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






正解: Pending

Question No : 2
You have a view named Person.vAdditionalContactlnfo that uses data from a table named Customers.
The Customers table was created by running the following Transact-SQL statement:



You need to create a query that returns the first and last names of a customer from the view. If the CustomerlD column does not have a value defined, the following message must be added to the query result: "Customer ID Does Not Exist"
Construct the query using the following guidelines:
• Do not use aliases for column or table names.
• Display the columns in the same order as they are defined in the Customers table.
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.



正解: Pending

Question No : 3
The following 5 questions without answers, only supplies as reference.

You need to create a query that meets the following requirements:
• The query must return a list of salespeople ranked by amount of sales and organized by postal code.
• The salesperson who has the highest amount of sales must be ranked first.
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.



正解: Pending

Question No : 4
HOTSPOT
You are developing an employee management database in Microsoft SQL Server.
The database contains two tables that are defined with the following Transact-SQL statements:



If the employee qualify for a bonus level they must receive a percentage of any sales.
Select the appropriate from below:



正解:

Question No : 5
You have a SQL Server database that contains all of the sales data for your company.
You need to create a query to update the rows in a table.
The solution must write original and updated values to a separate table.
Which clause should you add to the UPDATE statement in the query?

正解:

Question No : 6
DRAG DROP
You have a SQL Server database named CUSTOMERS.
You need to sign a stored procedure named SelectCustomers in the CUSTOMERS database.
Which four statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.



正解:


Explanation:
There are four steps involved in signing a module:
References: https://msdn.microsoft.com/en-us/library/bb669102(v=vs.110).aspx

Question No : 7
DRAG DROP
You develop a SQL Server database for a large multinational company.
You must develop a stored procedure to loop through employee information and return data for one employee at a time.
How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segment to the correct locations in the answer area. Each Transact-SQL segment 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.



正解:


Explanation:
Box 1: CURSOR VARYING OUTPUT
Transact-SQL stored procedures can use the cursor data type only forOUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.
Box2: FORWARD_ONLY STATIC
Box 3: CLOSE
Box 4: DEALLOCATE
References: https://technet.microsoft.com/en-us/library/ms175498(v=sql.105).aspx

Question No : 8
You use a Microsoft SQL Server database that contains a table named BlogEntry that has the following columns:



Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime.
Which Transact-SQL statement should you use?



正解:
Explanation:
The UPDATE SET colum_name.WRITE (expression,@Offset,@Length) command specifies that a section of the value of column_name is to be modified. expression replaces @Length units starting from @Offset of column_name.
If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored.

Question No : 9
You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime.
You need to modify the Products table to meet the following requirements:



Which Transact-SQL query should you use?



正解:

Question No : 10
You need to build a table structure for a stored procedure to support data entry from a website form.
The website form must meet the following requirements:



Which two actions should you perform? Each correct answer presents part of the solution.

正解:
Explanation:
SYSDATETIMEOFFSET returns a datetimeoffset (7) value that contains the date and time of the computer on which the instance of SQL Server is running.
ISDATE returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
References: https://msdn.microsoft.com/en-us/library/bb677334.aspx

Question No : 11
You generate a daily report according to the following query:



You need to improve the performance of the query.
What should you do?






正解:

Question No : 12
HOTSPOT
You are developing a SQL Server database for an order management system.
The database contains a table that is defined by the following Transact-SQL statement:



Transactions must commit if there are no errors. Transactions must roll back if constraint violations occur.
You need to create the Transact-SQL script to insert new orders.
How should you complete the relevant Transact-SQL script? To answer, select the appropriate Transact-SQL statements from each list in the answer area.



正解:


Explanation:
Box 1: SET XACT_ABORT ON;
XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises arun-time error, the entire transaction is terminated and rolled back.
Box 2: IF (XACT_STATE()) =-1
If XACT_STATE has the value of -1, then the current request has an active user transaction, but an error has occurred that has caused the transaction to beclassified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction.
Box 3: IF (XACT_STATE()) =1
If XACT_STATE has the value of 1, then the current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ms189797.aspx

Question No : 13
You have a vendor application that uses a scalar function.
You discover that the queries for the application run slower than expected.
You need to gather the runtime information of the scalar function.
What are two possible ways to achieve this goal? Each correct answer presents a complete solution.

正解:
Explanation:
A: An execution plan is the result of the query optimizer’s attempt to calculate the mostefficient way to implement the request represented by the T-SQL query you submitted. To generate the first execution plan, you can enable the Include Actual Execution Plan option.
D: SQL Server Extended Events can be used to capture User Defined Function(UDF) counts
References: https://www.mssqltips.com/sqlservertip/4100/how-to-find-udfs-causing-sql-server-performance-issues/

Question No : 14
You have several SQL Server queries.
You plan to optimize the queries to improve performance.
You run the queries in SQL Server Management Studio.
You need to compare query runs to expose the indexing issues of the queries.
Which three actions should you perform from SQL Server Management Studio? Each correct answer presents part of the solution.

正解:
Explanation:
E: An execution plan is theresult of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. To generate the first execution plan, you can enable the Include Actual Execution Plan option.
B: SET STATISTICS TIME displays the number of milliseconds required to parse, compile, and execute each statement.
C: STATISTICS IO causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements. This is useful information for optimizing queries.
The information include Scan count:
Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.
Scan count is 0 if the index used is a unique index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = <value>.
Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on anon-primary key column. This is done to check for duplicate values for the keyvalue that you are searching for. For example WHERE Clustered_Index_Key_Column = <value>.
Scan count is N when N is the number of different seek/scan started towards the leftor right side at the leaf level after locating a key value using the index key.

Question No : 15
DRAG DROP
You need to create a stored procedure that enters values into multiple tables. The solution must ensure that if a single insert fails, none of the values are inserted into the tables.
How should you complete the stored procedure? To answer, drag the appropriate values to the correct locations. Each value 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.



正解:


Explanation:
References: https://msdn.microsoft.com/en-us/library/ms188378.aspx

 / 36