070-761試験無料問題集「Microsoft Querying Data with Transact-SQL 認定」
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that contains a single table named tblVehicleRegistration. The table is defined as follows:
You run the following query:
The query output window displays the following error message: "Conversion failed when converting the varchar value 'AB012' to data type int." You need to resolve the error.
Solution: You modify the Transact-SQL statement as follows:
Does the solution meet the goal?
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that contains a single table named tblVehicleRegistration. The table is defined as follows:
You run the following query:
The query output window displays the following error message: "Conversion failed when converting the varchar value 'AB012' to data type int." You need to resolve the error.
Solution: You modify the Transact-SQL statement as follows:
Does the solution meet the goal?
正解：A
解答を投票する
解説: (GoShiken メンバーにのみ表示されます)
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You are developing a database to track customer orders. The database contains the following tables:
Sales.Customers, Sales.Orders, and Sales.OrderLines.
The following table describes the columns in Sales.Customers.
The following table describes the columns in Sales.Orders.
The following table describes the columns in Sales.OrderLines.
You need to create a database object that calculates the total price of an order including the sales tax. The database object must meet the following requirements:
- Reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated execution.
- Return a value.
- Be callable from a SELECT statement.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
You are developing a database to track customer orders. The database contains the following tables:
Sales.Customers, Sales.Orders, and Sales.OrderLines.
The following table describes the columns in Sales.Customers.
The following table describes the columns in Sales.Orders.
The following table describes the columns in Sales.OrderLines.
You need to create a database object that calculates the total price of an order including the sales tax. The database object must meet the following requirements:
- Reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated execution.
- Return a value.
- Be callable from a SELECT statement.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
正解：
Explanation
Box 1: FUNCTION
To be able to return a value we should use a scalar function.
CREATE FUNCTION creates a user-defined function in SQL Server and Azure SQL Database. The return value can either be a scalar (single) value or a table.
Box 2: RETURNS decimal(18,2)
Use the same data format as used in the UnitPrice column.
Box 3: BEGIN
Transact-SQL Scalar Function Syntax include the BEGIN ..END construct.
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Box 4: @OrderPrice * @CalculatedTaxRate
Calculate the price including tax.
Box 5: END
Transact-SQL Scalar Function Syntax include the BEGIN ..END construct.
References: https://msdn.microsoft.com/en-us/library/ms186755.aspx
You have a database containing the following tables:
Servers
Errors
You have a user-defined, scalar function named IPLookup that takes a DNS name as a parameter and returns the IP address of the server. You have an additional user-defined, scalar function named DNSLookup, that takes an IP address as a parameter and returns a DNS name.
You create a view named vwErrors by running the following Transact-SQL statement:
You need to insert data by using the view.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location. Each Transact-SQL segments 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.
Servers
Errors
You have a user-defined, scalar function named IPLookup that takes a DNS name as a parameter and returns the IP address of the server. You have an additional user-defined, scalar function named DNSLookup, that takes an IP address as a parameter and returns a DNS name.
You create a view named vwErrors by running the following Transact-SQL statement:
You need to insert data by using the view.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location. Each Transact-SQL segments 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://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql
You have a database that contains the following tables:
A delivery person enters an incorrect value for the CustomerID column in the Invoices table and enters the following text in the ConfirmedReceivedBy column: "Package signed for by the owner Tim." You need to find the records in the Invoices table that contain the word Tim in the CustomerName field.
How should you complete the Transact-SQL statement? 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.
NOTE: Each correct selection is worth one point.
A delivery person enters an incorrect value for the CustomerID column in the Invoices table and enters the following text in the ConfirmedReceivedBy column: "Package signed for by the owner Tim." You need to find the records in the Invoices table that contain the word Tim in the CustomerName field.
How should you complete the Transact-SQL statement? 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.
NOTE: Each correct selection is worth one point.
正解：
Explanation
Box 1: SELECT CustomerID FROM Sales.Invoices
Box 2: INNER JOIN Sales.Customers.CustomerID = Sales.Invoices.CustomerID Box 3: WHERE CustomerName LIKE '%tim%' Box 4: WHERE ConfirmedReceiveBy IN (SELECT CustomerName FROM Sales.Customers)
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:
The tables include the following records:
Customer_CRMSystem
Customer_HRSystem
Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
You need to display a list of customers that do not appear in the Customer_HRSystem table.
Which Transact-SQL statement should you run?
You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:
The tables include the following records:
Customer_CRMSystem
Customer_HRSystem
Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
You need to display a list of customers that do not appear in the Customer_HRSystem table.
Which Transact-SQL statement should you run?
正解：C
解答を投票する
解説: (GoShiken メンバーにのみ表示されます)
You are developing a mobile app to manage meetups. The app allows for users to view the 25 closest people with similar interests. You have a table that contains records for approximately two million people. You create the table by running the following Transact-SQL statement:
You create the following table valued function to generate lists of people:
You need to build a report that shows meetings with at least two people only.
What should you use?
You create the following table valued function to generate lists of people:
You need to build a report that shows meetings with at least two people only.
What should you use?
正解：A
解答を投票する
解説: (GoShiken メンバーにのみ表示されます)
You have a database named DB1 that contains two tables named Sales.Customers and Sales.CustomerTransaction. Sales.CustomerTransactions has a foreign key relationship to column named CustomerID in Sales.Customers.
You need to recommend a query that returns the number of customers who never completed a transaction.
Which query should you recommend?
You need to recommend a query that returns the number of customers who never completed a transaction.
Which query should you recommend?
正解：C
解答を投票する
You have a project management application. The application uses a Microsoft SQL Server database to store data. You are developing a software bug tracking add-on for the application.
The add-on must meet the following requirements:
* Allow case sensitive searches for product.
* Filter search results based on exact text in the description.
* Support multibyte Unicode characters.
You run the following Transact-SQL statement:
You need to display a comma separated list of all product bugs filed by a user named User1.
How should you complete the Transact-SQL statement? 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.
NOTE: Each correct selection is worth one point.
The add-on must meet the following requirements:
* Allow case sensitive searches for product.
* Filter search results based on exact text in the description.
* Support multibyte Unicode characters.
You run the following Transact-SQL statement:
You need to display a comma separated list of all product bugs filed by a user named User1.
How should you complete the Transact-SQL statement? 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.
NOTE: Each correct selection is worth one point.
正解：
Explanation
References: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017
You create a table named sales.orders by running the following Transact-SQLstatement:
DELETE from sales.orders
where orderdate <'2012-01-01' and shippeddate is not null
DELETE from sales.orders
where orderdate <'2012-01-01' and shippeddate is not null
正解：
See the explanation
Explanation
DELETE FROM Sales.OrdersWHERE OrderDate '2012-01-01' AND ShippedDate NOT NULL
https://msdn.microsoft.com/en-us/library/ms189835.aspx
https://msdn.microsoft.com/en-us/library/bb630352.aspx
Explanation
DELETE FROM Sales.OrdersWHERE OrderDate '2012-01-01' AND ShippedDate NOT NULL
https://msdn.microsoft.com/en-us/library/ms189835.aspx
https://msdn.microsoft.com/en-us/library/bb630352.aspx
