Home » Microsoft » 70-461 v.2 » Which three Transact-SQL segments should you use to develop the solution?
DRAG DROP
You develop a Microsoft SQL Server database for a sales ordering application.
You want to create a report that displays the increase of order quantities over the previous year for each product.
You need to write a query that displays:
Product name,
Year of sales order,
Sales order quantity, and Increase of order quantity over the previous year.
Which three 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.
Select and Place:
Correct Answer:
Explanation/Reference:
Explanation: Box 1: FROM ..
Box 2: LAG (not LEAD)
Lag accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
Not lead: Lead accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row.
Box 3: GROY BY PRO.NAME, YEAR (OrderDate)
References: https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017
What the … ?
Surely one starts with the SELECT clause first and then the FROM clause.
Again, not very helpful.