Questão 1292542 de Tecnologia da Informação - PUC PR 2017

Questão

ID 1292542

Disciplina: Tecnologia da Informação

Assunto: Select

Ano: 2017 Banca: PUC PR - Pontifícia Universidade Católica do Paraná Órgão: Tribunal de Justiça de Mato Grosso do Sul

Dada uma estrutura de variáveis do tipo tabela, conforme abaixo, no SQL Server 2012, com os dados de amostra fornecidos:

DECLARE @StockTransactions TABLE (

[StockSymbol] -----VARCHAR(10),

[TransactionDate] --DATE,

[StockPrice] -----MONEY

)

INSERT INTO @StockTransactions ( [StockSymbol], [TransactionDate], [StockPrice] )

VALUES ( 'ESQLH', '2017/06/02', 150.00 ),

( 'ESQLH', '2017/06/03', 155.00 ),

( 'SSQLS', '2017/06/02', 107.50 ),

( 'SSQLS', '2017/06/03', 112.20 )

Das instruções SELECT, assinale a alternativa que indica qual delas gerará o resultado abaixo:

StockSymbol -TransactionDate- StockPrice -PreviousPrice

------------ ---------------- ----------- -------

ESQLH -----2017-06-02 -----150.00 -----NULL

ESQLH -----2017-06-03 -----155.00 -----150.00

SSQLS -----2017-06-02 -----107.50 -----NULL

SSQLS -----2017-06-03 -----112.20 ------107.50

Alternativa A:
SELECT [StockSymbol], [TransactionDate], [StockPrice], LAG([StockPrice]) OVER (PARTITION BY [StockSymbol]
ORDER BY [TransactionDate] ASC) AS [PreviousPrice]
FROM @StockTransactions
ORDER BY [StockSymbol], [TransactionDate]
Alternativa B:
SELECT [StockSymbol], [TransactionDate], [StockPrice], LAG([StockPrice], 0, NULL) OVER (GROUP BY
[StockSymbol] ORDER BY [TransactionDate] ASC) AS [PreviousPrice]
FROM @StockTransactions
ORDER BY [StockSymbol], [TransactionDate]
Alternativa C:
SELECT [StockSymbol], [TransactionDate], [StockPrice], LAG([StockPrice]) OVER (ORDER BY [TransactionDate]
ASC) AS [PreviousPrice]
FROM @StockTransactions
GROUP BY [StockSymbol]
ORDER BY [StockSymbol], [TransactionDate]
Alternativa D:
SELECT [StockSymbol], [TransactionDate], [StockPrice], LAG([StockPrice], 0, NULL) OVER (PARTITION BY
[StockSymbol] ORDER BY [TransactionDate] ASC) AS [PreviousPrice]
FROM @StockTransactions
ORDER BY [StockSymbol], [TransactionDate]
Alternativa E:
SELECT [StockSymbol], [TransactionDate], [StockPrice], LAG([StockPrice]) OVER (GROUP BY [StockSymbol]
ORDER BY [TransactionDate] ASC) AS [PreviousPrice]
FROM @StockTransactions
ORDER BY [StockSymbol], [TransactionDate]