070-461 無料問題集「Microsoft Querying Microsoft SQL Server 2012/2014」
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to ensure that the following requirements are met:
* Students must be ranked based on their average marks.
* If one or more students have the same average, incremental ranks must be given based on the order they are created.
* Ranks must be sequential without gaps in between.
Which Transact-SQL query should you use?
You need to ensure that the following requirements are met:
* Students must be ranked based on their average marks.
* If one or more students have the same average, incremental ranks must be given based on the order they are created.
* Ranks must be sequential without gaps in between.
Which Transact-SQL query should you use?
正解：F
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?
Which type of user should you create?
正解：D
解答を投票する
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named invoice. The solution must ensure that the invoice is validated by using Sales.InvoiceSchema.
The solution must ensure that the invoice variable is validated by using Sales.InvoiceSchema schema.
Provide the correct code in the answer area.
You need to declare a variable of the XML type named invoice. The solution must ensure that the invoice is validated by using Sales.InvoiceSchema.
The solution must ensure that the invoice variable is validated by using Sales.InvoiceSchema schema.
Provide the correct code in the answer area.
正解：
DECLARE @invoice XML(Sales.InvoiceSchema)
The Agent table of a Microsoft SQL Server database contains several million rows. The database uses the SQL_Latin1_General_Cp1_CS_AS collation.
You need to ensure that the following requirements are met:
* The values of the StateRefID column conform to the pattern of 3 uppercase letters followed by 5 numeric digits, such as "ABC12345".
* The StateRefID values are unique within the Agent table.
* The values of all records that will be inserted or updated in the Agent table are correctly formatted.
* Exisitng rows are ignored
Which Transact-SQL statement should you run?
You need to ensure that the following requirements are met:
* The values of the StateRefID column conform to the pattern of 3 uppercase letters followed by 5 numeric digits, such as "ABC12345".
* The StateRefID values are unique within the Agent table.
* The values of all records that will be inserted or updated in the Agent table are correctly formatted.
* Exisitng rows are ignored
Which Transact-SQL statement should you run?
正解：D
解答を投票する
You develop a database for a travel application. You need to design tables and other database objects.
You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?
You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?
正解：C
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You develop a database for a travel application. You need to design tables and other database objects.
You create the Historical_Events table.
You need to store dates by using the smallest possible storage size.
What should you do?
You develop a database for a travel application. You need to design tables and other database objects.
You create the Historical_Events table.
You need to store dates by using the smallest possible storage size.
What should you do?
正解：E
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You are creating queries for a shopping cart application.
You have the following requirements:
* Query1 must list products where less than 100 units have been sold in total.
* Query2 must list products where more than 10 units have been sold in a single order.
You need to identify the correct sub-queries to complete the outer queries that you have written.
Which Transact-SQL statement or statements should you use? To answer, drag the appropriate SQL statements to the correct locations. Each SQL statement 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:
* Query1 must list products where less than 100 units have been sold in total.
* Query2 must list products where more than 10 units have been sold in a single order.
You need to identify the correct sub-queries to complete the outer queries that you have written.
Which Transact-SQL statement or statements should you use? To answer, drag the appropriate SQL statements to the correct locations. Each SQL statement 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 develop a Microsoft SQL Server database. You design a table to store product information. The table is defined as follows:
You need to meet the following requirements:
* If a product has a product category value "REGULAR", the product should have a sub-category with a length of two characters.
* If a product has the product category other than "REGULAR", the product may or may not have a sub-category.
Which Transact-SQL statement should you use?
You need to meet the following requirements:
* If a product has a product category value "REGULAR", the product should have a sub-category with a length of two characters.
* If a product has the product category other than "REGULAR", the product may or may not have a sub-category.
Which Transact-SQL statement should you use?
正解：D
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You want to add a new GUID column named ProductGUID to a table named dbo.Product that already contains data.
ProductGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Product.
You need to ensure that the new column is assigned a GUID for existing rows.
Which four Transact-SQL statements should you use to develop the solution? (To answer, move the appropriate Transact-SQL segments from the list of SQL-segments to the answer area and arrange them in the correct order.)
ProductGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Product.
You need to ensure that the new column is assigned a GUID for existing rows.
Which four Transact-SQL statements should you use to develop the solution? (To answer, move the appropriate Transact-SQL segments from the list of SQL-segments to the answer area and arrange them in the correct order.)
正解：
Explanation:
Box 1:
Existing BookGuid can be null.
Box 2:
Box 3: new()
Generate a new global value
Box 4:
Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.
Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter.
You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.
Which approach should you use?
You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.
Which approach should you use?
正解：E
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
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.
正解：
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.