70-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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that includes the bookmark lookup columns.
Does this 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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that includes the bookmark lookup columns.
Does this meet the goal?
正解：B
解答を投票する
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 create a table by running the following Transact-SQL statement:
You need to audit all customer data.
Which Transact-SQL statement should you run?
You create a table by running the following Transact-SQL statement:
You need to audit all customer data.
Which Transact-SQL statement should you run?
正解：E
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You have two tables named UserLogin and Employee respectively.
You need to create a Transact-SQL script that meets the following requirements:
* The script must update the value of the IsDeleted column for the UserLogin table to 1 if the value of the Id column for the UserLogin table is equal to 1.
* The script must update the value of the IsDeleted column of the Employee table to 1 if the value of the Id column is equal to 1 for the Employee table when an update to the UserLogin table throws an error.
* The error message "No tables updated!" must be produced when an update to the Employee table throws an error.
Which five 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.
You need to create a Transact-SQL script that meets the following requirements:
* The script must update the value of the IsDeleted column for the UserLogin table to 1 if the value of the Id column for the UserLogin table is equal to 1.
* The script must update the value of the IsDeleted column of the Employee table to 1 if the value of the Id column is equal to 1 for the Employee table when an update to the UserLogin table throws an error.
* The error message "No tables updated!" must be produced when an update to the Employee table throws an error.
Which five 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
A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
References: https://msdn.microsoft.com/en-us/library/ms175976.aspx
You need to develop a Transact-SQL statement that meets the following requirements:
- The statement must return a custom error when there are problems updating a table.
- The error number must be value 50555.
- The error severity level must be 14.
- A Microsoft SQL Server alert must be triggered when the error condition occurs.
Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate Transact-SQL segments in the answer area.
- The statement must return a custom error when there are problems updating a table.
- The error number must be value 50555.
- The error severity level must be 14.
- A Microsoft SQL Server alert must be triggered when the error condition occurs.
Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate Transact-SQL segments in the answer area.
正解：
Explanation
Box 1: TRY...CATCH
The TRY...CATCH Transact-SQL construct implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.
Box 2: RAISERROR(50555, 14, 1 'The update failed.") WITH LOG
We must use RAISERROR to be able to specify the required severity level of 14, and we should also use the LOG option, which Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine, as this enable a MS MS SQL SERVER alert to be triggered.
Note: RAISERROR generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct.
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 create a table named Customer by running the following Transact-SQL statement:
You must insert the following data into the Customer table:
You need to ensure that both records are inserted or neither record is inserted.
Solution: You run the following Transact-SQL statement:
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 create a table named Customer by running the following Transact-SQL statement:
You must insert the following data into the Customer table:
You need to ensure that both records are inserted or neither record is inserted.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
正解：A
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You have a database that includes the following tables. All of the tables are in the Production schema.
You need to create a query that returns a list of product names for all products in the Beverages category.
Construct the query using the following guidelines:
* Use the first letter of the table name as the table alias.
* Use two-part column names.
* Do not surround object names with square brackets.
* Do not use implicit joins.
* Do not use variables.
* Use single quotes to surround literal 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 may check syntax as many times as needed.
You need to create a query that returns a list of product names for all products in the Beverages category.
Construct the query using the following guidelines:
* Use the first letter of the table name as the table alias.
* Use two-part column names.
* Do not surround object names with square brackets.
* Do not use implicit joins.
* Do not use variables.
* Use single quotes to surround literal 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 may check syntax as many times as needed.
正解：
Please see explanation
Explanation
1 SELECT p.productname
2 FROM Production.categories AS c
3 inner join production.products as p on c.categoryid=p.categoryid
4 WHERE c.categoryname = 'Beverages'
Note: On line 3 change * to =
Explanation
1 SELECT p.productname
2 FROM Production.categories AS c
3 inner join production.products as p on c.categoryid=p.categoryid
4 WHERE c.categoryname = 'Beverages'
Note: On line 3 change * to =
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
You create a table named Sales.Orders by running the following Transact-SQL statement:
You need to write a query that removes orders from the table that have a Status of Canceled.
Construct the query using the following guidelines:
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 write a query that removes orders from the table that have a Status of Canceled.
Construct the query using the following guidelines:
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.
正解：
Please see explanation
Explanation
1. DELETE from sales.orders where status='Canceled'
Note: On line 1 change calceled to Canceled
Example: Using the WHERE clause to delete a set of rows
The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in which the value in the StandardCost column is more than 1000.00.
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql
Explanation
1. DELETE from sales.orders where status='Canceled'
Note: On line 1 change calceled to Canceled
Example: Using the WHERE clause to delete a set of rows
The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in which the value in the StandardCost column is more than 1000.00.
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql
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 create a table by running the following Transact-SQL statement:
You are developing a report that aggregates customer data only for the year 2014. The report requires that the data be denormalized.
You need to return the data for the report.
Which Transact-SQL statement should you run?
You create a table by running the following Transact-SQL statement:
You are developing a report that aggregates customer data only for the year 2014. The report requires that the data be denormalized.
You need to return the data for the report.
Which Transact-SQL statement should you run?
正解：A
解答を投票する
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 stored procedure that inserts data into the Customers table. The stored procedure must meet the following requirements:
- Data changes occur as a single unit of work.
- Data modifications that are successful are committed and a value of 0 is returned.
- Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is returned.
- The stored procedure uses a built-it scalar function to evaluate the current condition of data modifications.
- The entire unit of work is terminated and rolled back if a run-time error occurs during execution of the stored procedure.
How should complete the stored procedure definition? To answer, drag the appropriate Transact-SQL segments to the correct targets. 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.
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 stored procedure that inserts data into the Customers table. The stored procedure must meet the following requirements:
- Data changes occur as a single unit of work.
- Data modifications that are successful are committed and a value of 0 is returned.
- Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is returned.
- The stored procedure uses a built-it scalar function to evaluate the current condition of data modifications.
- The entire unit of work is terminated and rolled back if a run-time error occurs during execution of the stored procedure.
How should complete the stored procedure definition? To answer, drag the appropriate Transact-SQL segments to the correct targets. 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
Explanation
Box 1: XACT_ABORT
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 a run-time error, the entire transaction is terminated and rolled back.
Box 2: COMMIT
Commit the transaction.
Box 3: XACT_STATE
Box 4: ROLLBACK
Rollback the transaction
Box 5: THROW
THROW raises an exception and the severity is set to 16.
Requirement: Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is returned.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ee677615.aspx
You are a developing a solution to manage employee training records. You have the following Transact-SQL statement:
You need to create a stored procedure that returns the total number of participants for a specific course.
How should you complete the 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.
NOTE: Each correct selection is worth one point.
You need to create a stored procedure that returns the total number of participants for a specific course.
How should you complete the 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.
NOTE: Each correct selection is worth one point.
正解：
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 on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:
Details for the Application.Cities table are shown in the following table:
Details for the Sales.CustomerCategories table are shown in the following table:
The marketing department is performing an analysis of how discount affect credit limits. They need to know the average credit limit per standard discount percentage for customers whose standard discount percentage is between zero and four.
You need to create a query that returns the data for the analysis.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. 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.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:
Details for the Application.Cities table are shown in the following table:
Details for the Sales.CustomerCategories table are shown in the following table:
The marketing department is performing an analysis of how discount affect credit limits. They need to know the average credit limit per standard discount percentage for customers whose standard discount percentage is between zero and four.
You need to create a query that returns the data for the analysis.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. 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
Box 1: 0, 1, 2, 3, 4
Pivot example:
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Box 2: [CreditLimit]
Box 3: PIVOT
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.
Box 4: 0, 1, 2, 3, 4
The IN clause determines whether a specified value matches any value in a subquery or a list.
Syntax: test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )
Where expression[ ,... n ]
is a list of expressions to test for a match. All expressions must be of the same type as test_expression.
References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
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 on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:
Details for the Application.Cities table are shown in the following table:
Details for the Sales.CustomerCategories table are shown in the following table:
You are preparing a promotional mailing. The mailing must only be sent to customers in good standing that live in medium and large cities.
You need to write a query that returns all customers that are not on credit hold who live in cities with a population greater than 10,000.
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.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:
Details for the Application.Cities table are shown in the following table:
Details for the Sales.CustomerCategories table are shown in the following table:
You are preparing a promotional mailing. The mailing must only be sent to customers in good standing that live in medium and large cities.
You need to write a query that returns all customers that are not on credit hold who live in cities with a population greater than 10,000.
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.
正解：
Explanation
Box 1: IN (
The IN clause determines whether a specified value matches any value in a subquery or a list.
Syntax: test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )
Where subquery
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
Box 2: WHERE
Box 3: AND [IsOnCreditHold] = 0
Box 4: )
References: https://msdn.microsoft.com/en-us/library/ms177682.aspx
You have a database that contains the following tables.
You need to create a query that lists the highest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements:
Construct the query using the following guidelines:
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.
1 SELECT top 3 lastname,salesYTD
2 FROM Person AS p INNER JOIN SalesPerson AS s
3 ON p.PersonID = s.SalesPersonID
4 WHERE territoryid is null
5 order by salesytd dsec
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 lists the highest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements:
Construct the query using the following guidelines:
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.
1 SELECT top 3 lastname,salesYTD
2 FROM Person AS p INNER JOIN SalesPerson AS s
3 ON p.PersonID = s.SalesPersonID
4 WHERE territoryid is null
5 order by salesytd dsec
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
正解：
Please see explanation
Explanation
1 SELECT top 3 lastname,salesYTD
2 FROM Person AS p INNER JOIN SalesPerson AS s
3 ON p.PersonID = s.SalesPersonID
4 WHERE territoryid is not null
5 order by salesytd desc
Note:
On line 4 add a not before null.
On line 5 change dsec to desc.
Explanation
1 SELECT top 3 lastname,salesYTD
2 FROM Person AS p INNER JOIN SalesPerson AS s
3 ON p.PersonID = s.SalesPersonID
4 WHERE territoryid is not null
5 order by salesytd desc
Note:
On line 4 add a not before null.
On line 5 change dsec to desc.