70-461 無料問題集「Microsoft Querying Microsoft SQL Server 2012/2014」
You develop a Microsoft SQL Server database for an order processing system that contains a table named OrderCountSummary, as shown in the first exhibit. (Click the Exhibit tab.)
The table stores the names of vendors and the number of orders submitted by each vendor for each food category: Dairy, Meat, Poultry, Seafood, and Vegetarian.
You need to generate a report that displays the total number of orders placed for each food category, as shown in the following table.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
The table stores the names of vendors and the number of orders submitted by each vendor for each food category: Dairy, Meat, Poultry, Seafood, and Vegetarian.
You need to generate a report that displays the total number of orders placed for each food category, as shown in the following table.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
正解：
Explanation:
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot
You need to create a cursor that meets the following requirements:
Executes as quickly as possible.
Reflects all data changes made to the table while scrolling.
Which five Transact-SQL statements should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer are and arrange them in the correct order.
Executes as quickly as possible.
Reflects all data changes made to the table while scrolling.
Which five Transact-SQL statements should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer are and arrange them in the correct order.
正解：
Explanation:
Step 1: Declare variables.
Step 2: Open the cursor
Step 3: Fetch the first instance.
Step 4: Loop
Step 5: Close and deallocate the cursor
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017
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.
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.
正解：A、B、E
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders.
The tables are related by a column named CustomerID.
You need to create a query that meets the following requirements:
* Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
* Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?
The tables are related by a column named CustomerID.
You need to create a query that meets the following requirements:
* Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
* Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?
正解：A
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You are designing an order entry system that uses an SQL Server database. The following tables exist in the Purchasing database:
You create the following trigger. Line numbers are included for reference only.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
You create the following trigger. Line numbers are included for reference only.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
正解：
You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.)
You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.
You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN, END, or DECLARE statements.
Part of the correct Transact-SQL statement has been provided in the answer area. Complete the Transact-SQL statement
You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.
You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN, END, or DECLARE statements.
Part of the correct Transact-SQL statement has been provided in the answer area. Complete the Transact-SQL statement
正解：A
解答を投票する
You administer a SQL Server database that tracks sales that are made by sales persons. The database contains a table that is defined by the following Transact-SQL statements:
You have the following requirements:
* accept a datetime value for the query month
* return a list of salespeople IDs who have sales in the query month or before the query month
* compare sales with sales quota for salespeople who have a sales quota
* display year-to-date sales for salespeople that do not have a sales quota How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segments to the correct locations. 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.
You have the following requirements:
* accept a datetime value for the query month
* return a list of salespeople IDs who have sales in the query month or before the query month
* compare sales with sales quota for salespeople who have a sales quota
* display year-to-date sales for salespeople that do not have a sales quota How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segments to the correct locations. 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: IIF ..
The IIF logical function returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.
Syntax: IIF ( boolean_expression, true_value, false_value )
Box 2: [LastSzale] < EOMONTH(@queryMonth)
The EOMONTH function returns the last day of the month containing a specified date, with an optional offset.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017
A database contains tables as shown in the exhibit. (Click the Exhibit button.) Products that are discontinued are moved from the Products table to the DiscontinuedProducts table. Any orders for discontinued products are removed from the Orders table.
You write the following SELECT statement to return all the discontinued products:
SELECT ProductId FROM DiscontinuedProducts
You need to extend the SELECT statement to include products who do not have any orders.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Exhibit
You write the following SELECT statement to return all the discontinued products:
SELECT ProductId FROM DiscontinuedProducts
You need to extend the SELECT statement to include products who do not have any orders.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Exhibit
正解：
You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
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.)
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
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.)
正解：
You administer a Microsoft SQL Server database named Orders.
The database contains a table named Customer that has the following definition:
You add a new data source to the application that inserts records into the Customer table. An example of a query that the application executes is shown below.
Users report that these new records are not displayed correctly. You review data in the table and observe that records from the new data source are displayed as shown below.
You need to change the database schema to support the characters coming in from this new data source. You need to achieve this goal without losing existing customer records.
Which Transact-SQL statement should you run?
The database contains a table named Customer that has the following definition:
You add a new data source to the application that inserts records into the Customer table. An example of a query that the application executes is shown below.
Users report that these new records are not displayed correctly. You review data in the table and observe that records from the new data source are displayed as shown below.
You need to change the database schema to support the characters coming in from this new data source. You need to achieve this goal without losing existing customer records.
Which Transact-SQL statement should you run?
正解：A
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
A local bank uses a SQL Server database to manage accounts. You are developing a stored procedure that contains multiple Transact-SQL INSERT statements.
The stored procedure must use transaction management to handle errors.
You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs.
Which Transact-SQL statement should you add to the stored procedure?
The stored procedure must use transaction management to handle errors.
You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs.
Which Transact-SQL statement should you add to the stored procedure?
正解：D
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another.
Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.
You need to ensure that your query executes in the minimum possible time.
Which query should you use?
Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.
You need to ensure that your query executes in the minimum possible time.
Which query should you use?
正解：A
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You are a database developer of a Microsoft SQL Server 2012 database.
You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID.
A sample of this data is as shown in the following table.
You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?
You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID.
A sample of this data is as shown in the following table.
You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?
正解：C
解答を投票する
You develop a Microsoft SQL Server database that contains a two tables named OrdersHistorical and CompletedOrders.
Both tables have the same schema that has the following definition:
You write the following Transact-SQL statement:
You need to ensure that data that falls into the date range of the SELECT statement cannot be inserted into CompletedOrders while the INSERT statement is running. You also need to ensure that other operations are unaffected.
Which table hint should you use?
Both tables have the same schema that has the following definition:
You write the following Transact-SQL statement:
You need to ensure that data that falls into the date range of the SELECT statement cannot be inserted into CompletedOrders while the INSERT statement is running. You also need to ensure that other operations are unaffected.
Which table hint should you use?
正解：D
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)