70-461 | Up To The Minute Querying Microsoft SQL Server 2012 70-461 Exam

Act now and download your Microsoft 70-461 test today! Do not waste time for the worthless Microsoft 70-461 tutorials. Download Latest Microsoft Querying Microsoft SQL Server 2012 exam with real questions and answers and begin to learn Microsoft 70-461 with a classic professional.

Check 70-461 free dumps before getting the full version:

NEW QUESTION 1

You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

  • A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
  • B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
  • C. ALTER INDEX ALL ON OrderDetail REBUILD
  • D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Answer: B

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188388.aspx

NEW QUESTION 2

You administer a Microsoft SQL Server 2012 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:
70-461 dumps exhibit Remove all duplicates of the Products table based on the ProductName column.
70-461 dumps exhibit Retain only the newest Products row.
Which Transact-SQL query should you use?

  • A. WITH CTEDupRecords AS(SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName HAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON
  • B. ProductName = cte.ProductNameAND p.CreatedDateTime > cte.CreatedDateTime
  • C. WITH CTEDupRecords AS(SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName HAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON cte.ProductName = p.ProductNameAND cte.CreatedDateTime > p.CreatedDateTime
  • D. WITH CTEDupRecords AS(SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName)DELETE pFROM Products pJOIN CTEDupRecords cte ON
  • E. ProductName = cte.ProductName
  • F. WITH CTEDupRecords AS(SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName HAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON
  • G. ProductName = cte.ProductName

Answer: B

NEW QUESTION 3

You have a database that contains the tables as shown below:
70-461 dumps exhibit
You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements:
70-461 dumps exhibit The code must NOT use aliases.
70-461 dumps exhibit The code must NOT use object delimiters.
70-461 dumps exhibit The objects called in Procedure1 must be able to be resolved by all users.
70-461 dumps exhibit OrderDate must be compared to Parameter1 after the data type of Parameter1 is changed to datetime. Which SELECT statement should you use?
To answer, type the correct code in the answer area.

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
SELECT Orders.OrderID FROM Orders
WHERE Orders.OrderDate>CONVERT(datetime,@Parameter1)

NEW QUESTION 4

You are developing an SQL Server database for an automobile manufacturer. The manufacturer maintains the list of vehicles sold and vehicles that have been recalled. The tables are shown below:
70-461 dumps exhibit
You have the following Transact-SQL code. Line numbers are included for reference only.
70-461 dumps exhibit
You must update the VehiclesToRecall table with the list of vehicles that were recalled in 2014. You must maximize the performance of the operation.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
70-461 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
Reference: MERGE (Transact-SQL) https://msdn.microsoft.com/en-us/library/bb510625.aspx

NEW QUESTION 5

You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition:
70-461 dumps exhibit
You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?

  • A. CREATE TRIGGER TrgPhoneNumberChangeON Customers FOR UPDATE ASIF COLUMNS_UPDATED (HomeNumber, MobileNumber)- - Create Audit Records
  • B. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATEASIF EXISTS( SELECT HomeNumber from inserted) OR EXISTS (SELECT MobileNumber FROM inserted)- - Create Audit Records
  • C. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATEASIF COLUMNS_CHANGED (HomeNumber, MobileNumber)- - Create Audit Records
  • D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATEASIF UPDATE (HomeNumber) OR UPDATE (MobileNumber)- - Create Audit Records

Answer: D

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/bb510663.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms186329.aspx

NEW QUESTION 6

You are developing an SQL Server database. The database contains two tables and a function that are defined by the following Transact-SQL statements.
70-461 dumps exhibit
70-461 dumps exhibit
You need to create a query to determine the total number of products that are sold each day for the live top-selling products on that particular day.
How should you complete the relevant Transact-SQL script? To answer, select the appropriate Transact-SQL statements from each list in the answer area.
70-461 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. T
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
Reference: Using APPLY
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

NEW QUESTION 7

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit
You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
70-461 dumps exhibit Write the results to a disk.
70-461 dumps exhibit Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
70-461 dumps exhibit The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use?
To answer, type the correct code in the answer area.

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
CREATE TABLE Sales.OrderDetails ( ListPrice money not null,
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)
Reference: http://msdn.microsoft.com/en-us/library/ms174979.aspx
Reference: http://technet.microsoft.com/en-us/library/ms188300.aspx

NEW QUESTION 8

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit
You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:
70-461 dumps exhibit UnitPrice must be returned in descending order.
70-461 dumps exhibit The query must use two-part names to reference the table.
70-461 dumps exhibit The query must use the RANK function to calculate the results.
70-461 dumps exhibit The query must return the ranking of rows in a column named PriceRank.
70-461 dumps exhibit The list must display the columns in the order that they are defined in the table. PriceRank must appear last.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
70-461 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC

NEW QUESTION 9

You are the administrator for a heavily-used OLTP Microsoft SQL Server database.
You are troubleshooting performance issues seen when using stored procedures in the database. The database stores millions of orders across thousands of customers. Some of the customers have large numbers of orders, while others have only one order. You update the statistics and perform defragmentation of all tables and indexes, but two stored procedures still have issues when accessing data.
p_GetCustomer accepts @companyID as a parameter. From the results of profiling, you know that 90 percent of the calls use the @companyid value of 5, while the other 10 percent of calls are evenly distributed across another 10000 values. While viewing the execution plan, you discover that a non-clustered index seek is used.
p_GetShipDate accepts @orderID as a parameter and returns the ship date for that order. You discover that the execution plan is performing a scan on a non-clustered index that has orderID as the index key.
You need to add appropriate query hints to each stored procedure to improve the performance.
What should you do? To answer, drag the appropriate procedures to the correct hints. Each procedure 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.
70-461 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
Box 1: Optimize FOR..
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.
Box 2: FORCESEEK
FORCESEEK [ (index_value(index_column_name [ ,... n ] )) ]
Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view.
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017

NEW QUESTION 10

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.
70-461 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

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

NEW QUESTION 11

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?

  • A. Query the sys.dm_db_index_usage_stats DMV.
  • B. Query the sys.dm_db_missing_index_details DMV.
  • C. Use the Database Engine Tuning Advisor.
  • D. Query the sys.dm_db_missing_index_columns DMV.

Answer: C

NEW QUESTION 12

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit
70-461 dumps exhibit
Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000
and an increment of 1?

  • A. DateHired
  • B. DepartmentID
  • C. EmployeeID
  • D. EmployeeNum
  • E. FirstName
  • F. JobTitle
  • G. LastName
  • H. MiddleName
  • I. ReportsToID

Answer: C

NEW QUESTION 13

You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?

  • A. Smallmoney
  • B. Numeric
  • C. Money
  • D. Varchar

Answer: B

Explanation:
Numeric data types that have fixed precision and scale. decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
where
* p (precision)
The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
* (scale)
The number of decimal digits that will be stored to the right of the decimal point. Reference: decimal and numeric (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms187746.aspx

NEW QUESTION 14

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.

  • A. Enable the Include Actual Execution Plan option.
  • B. Enable the Display Estimated Execution Planoption.
  • C. Create and then enable a profiler trace.
  • D. Create and then enable an extended events trace.
  • E. Run the Database Engine Tuning Advisor.

Answer: AD

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-issu

NEW QUESTION 15

You are writing a set of queries against a FILESTREAM-enabled database.
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

  • A. SET IMPLICIT_TRANSACTIONS ON
  • B. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  • C. SET IMPLICIT_TRANSACTIONS OFF
  • D. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  • E. SET XACT_ABORT OFF
  • F. SET XACT_ABORT ON

Answer: F

Explanation:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
Reference: SET XACT_ABORT (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms188792.aspx

NEW QUESTION 16

You administer a Microsoft SQL Server database that supports a banking transaction management application. You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

  • A. SELECT AccountHolderID FROM AccountHolderWHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
  • B. SELECT AccountHolderID FROM AccountHolderWHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
  • C. SELECT AccountHolderID FROM AccountHolderWHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
  • D. SELECT AccountHolderID FROM AccountHolderWHERE CityID <> ANY (SELECT CityID FROM BranchMaster)

Answer: AB

Explanation:
Verified the answers as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms188047.aspx Reference: http://msdn.microsoft.com/en-us/library/ms177682.aspx Reference: http://msdn.microsoft.com/en-us/library/ms173545.aspx

NEW QUESTION 17

You use Microsoft SQL Server to develop a database application.
Your application sends data to a VARCHAR(50) variable named @var.
You need to write a Transact-SQL statement that will return information on a successful or unsuccessful cast to an integer in a table.
Which Transact-SQL statement should you run?
70-461 dumps exhibit
70-461 dumps exhibit

  • A. Option A
  • B. Option B
  • C. Option C
  • D. Option D

Answer: B

Explanation:
TRY_PARSE returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.
References: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-2017

NEW QUESTION 18

You need to create a query that meets the following requirements:
70-461 dumps exhibitThe 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 code that has been provided as well as below it.
70-461 dumps exhibit
70-461 dumps exhibit
70-461 dumps exhibit
Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS "Ranking", 2 p.LastName, s.SalesYTD, a.PostalCode
3 FROM Sales.SalesPerson AS a etc
On line 1 add: RowNumber
One line 1 add: PARTITION BY
ROW_NUMBER() numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SYNTAX for OVER: OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
Example: Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber, FirstName, LastName,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName; Here is a partial result set.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

NEW QUESTION 19

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit
70-461 dumps exhibit
Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database?

  • A. DateHired
  • B. DepartmentID
  • C. EmployeeID
  • D. EmployeeNum
  • E. FirstName
  • F. JobTitle
  • G. LastName
  • H. MiddleName
  • I. ReportsToID

Answer: C

Explanation:
Use the EmployeeID, which would be used as a primary key in the Employee table, when defining a foreign key constraint from another table in the database.

NEW QUESTION 20

Your Microsoft SQL Server database contains tables as shown below.
You have tables that were created by running the following Transact-SQL statements:
70-461 dumps exhibit
The Product table contains 10,000 records. The maximum ProductID is 11,000. There are 12 rows in the Category table. The maximum CategoryID is 12.
The Product table contains at least one product in every category.
Data in the tables was accidently modified. To correct this, you need to make some updates directly to the tables. You issue several statements.
Which result or results will you obtain for each Transact-SQL statement? To answer, drag the appropriate results to the correct Transact-SQL statements. Each result 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.
70-461 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
70-461 dumps exhibit
Box 1:
The SET IDENTITY_INSERT command allows explicit values to be inserted into the identity column of a table.
Box 2:
The Product table contains at least one product in every category. Box 3:
Box 4:
Bit is a data type that can take a value of 1, 0, or NULL. References:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017

NEW QUESTION 21

You have a SQL database that contains a table named Products.
You are implementing a stored procedure that retrieves the list of products, performs custom business logic and then retrieve the list of products again.
The custom business logic in the stored procedure does not modify data from the Products table. The stored procedure contains the following:
70-461 dumps exhibit
You need to complete line 01 of the stored procedure to ensure that when the transaction occurs, the data read from the SELECT * FROM Products statement on line 05 is identical to the data read from the SELECT * FROM Products statement on line 10. The solution must maximize concurrency.
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.
70-461 dumps exhibit
70-461 dumps exhibit
70-461 dumps exhibit
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
REPEATABLE READ

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
Add REPEATABLE READ to line 1 to get:
SET TRANSACTIONISOLATION LEVEL 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.

NEW QUESTION 22

You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9), PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index.
Which three 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.)
70-461 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
70-461 dumps exhibit

NEW QUESTION 23

You have a business reporting database.
Several power users generate reports from the database.
You need to create an object in the database to meet the following requirements:
70-461 dumps exhibit Provide the power users with the ability to use simple queries.
70-461 dumps exhibit Prevent schema changes on objects related to the query.
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 code that has been provided as well as below it.
70-461 dumps exhibit
70-461 dumps exhibit
70-461 dumps exhibit
Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
1 Create View Customer_Orders WITH SCHEMABINDING AS SELECT CustomerName, 2 OrderDate FROM Customers INNER JOIN Orders on
3 Customers.CustomerID=Orders.CustomerID To Line 1, after WITH, add SCHEMABINDING
SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

NEW QUESTION 24

You develop a Microsoft SQL Server database that contains a table named Products. The Products table has columns named ProductId, CategoryId, Name, and Price.
Product prices are often updated as follows:
70-461 dumps exhibit For a single product by a specific value by using the ProductId column
70-461 dumps exhibit For an entire category by a percentage by using the CategoryId column
You need to log the change price to a new table named PriceChange by using the ProductId’ ChangedValue, and ChangedDate columns. You also need to ensure that increases in price are recorded as positive values and decreases in price as negative values.
Which Transact-SQL query should you use?
70-461 dumps exhibit
70-461 dumps exhibit
70-461 dumps exhibit
70-461 dumps exhibit

  • A. Option A
  • B. Option B
  • C. Option C
  • D. Option D

Answer: D

NEW QUESTION 25

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:
70-461 dumps exhibit Students must be ranked based on their average marks.
70-461 dumps exhibit If one or more students have the same average, the same rank must be given to these students.
70-461 dumps exhibit Consecutive ranks must be skipped when the same rank is assigned.
Which Transact-SQL query should you use?

  • A. SELECT StudentCode as Code,RANK() OVER(ORDER BY AVG (Marks) DESC) AS ValueFROM StudentMarks GROUP BY StudentCode
  • B. SELECT Id, Name, Marks,DENSE_RANK() OVER(ORDER BY Marks DESC) AS RankFROM StudentMarks
  • C. SELECT StudentCode as Code,DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS ValueFROM StudentMarks GROUP BY StudentCode
  • D. SELECT StudentCode as Code,NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS ValueFROM StudentMarks GROUP BY StudentCode
  • E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS RankFROM StudentMarks) tmpWHERE Rank = 1
  • F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS RankFROM StudentMarks) tmp WHERE Rank = 1
  • G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS RankFROM StudentMarks) tmp WHERE Rank = 1
  • H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS RankFROM StudentMarks) tmp WHERE Rank = 1

Answer: A

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms189798.aspx

NEW QUESTION 26
......

P.S. Thedumpscentre.com now are offering 100% pass ensure 70-461 dumps! All 70-461 exam questions have been updated with correct answers: https://www.thedumpscentre.com/70-461-dumps/ (232 New Questions)