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?
A.
B.
C.
D.
E.
F.
G.
H.
How can this answer relates to The report requires that the data be denormalized ?
7
1
G
0
4
D
6
0
ANSWER SHOULD BE D
20
2
Why do you think D is denormalized? which normal form is it breaking?
I initially thinked on 1NF by having the same attribute(year) spread in multiple columns. but there is only on spreading column in this query (2014) so there is no spreading of the same attribute for multiple columns.
if we had 2014 and 2015. I would say right away that would be D). but as it is I’m doubtfully and would like to know what you thinked.
I see denormalization in no query, unfortunately
3
0
I’ve updated recreated the exercise and eligible answers down below.
the tabel is called BM_Customers instead of Customers, and I go for 2018 instead of 2014.
As I said before, I see no denormalized data anywhere.
you can check
–CREATE TABLE
CREATE TABLE BM_Customers (
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
TaxIdNumber varchar(20) NOT NULL,
Address nvarchar(1024) NOT NULL,
AnnualRevenue decimal(19,2) NOT NULL,
DateCreated datetime2(2) NOT NULL,
ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo datetime2(2) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.BM_CustomersHistory))
–insert data to table
insert into BM_Customers(CustomerID, FirstName, LastName, TaxIdNumber, Address, AnnualRevenue, DateCreated)
VALUES (1, ‘Cristiano’, ‘Ronaldo’, 1, ‘address’, 10, GETUTCDATE()),
(2, ‘Alistair’, ‘Overeem’, 1, ‘address’, 20, GETUTCDATE()),
(3, ‘Brolly’, ‘Legend’, 1, ‘address’, 30, GETUTCDATE())
–PERFORM RANDOM UPDATES
update BM_Customers
set Address = ‘Mars’
update BM_Customers
set FirstName = ‘Broly’
WHERE FirstName = ‘Brolly’
–CHECK DATA
SELECT * FROM BM_Customers
–ELIGIBLE ANSWERS SEEM TO GONE WRONG
–E.
SELECT CustomerID, AVG(AnnualRevenue) AS AverageAnnualRevenue, FirstName, LastName, Address, DateCreated
FROM BM_Customers
WHERE YEAR(DateCreated) >= 2014
GROUP BY CustomerID, FirstName, LastName, Address, DateCreated
–D.
SELECT * FROM (SELECT CustomerID, FirstName, LastName, Address, AnnualRevenue, DateCreated
FROM BM_Customers) AS BM_Customers PIVOT(AVG(AnnualRevenue)
FOR DateCreated IN([2018])) AS PivotCustomers
ORDER BY LastName, FirstName
–G.
SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo
FROM BM_Customers
FOR SYSTEM_TIME BETWEEN ‘2018-01-01 00:00:00.000000’ AND ‘2019-01-01 00:00:00.000000’
–H.
SELECT CustomerID, FirstName, LastName, TaxIdNumber, Address, ValidFrom, ValidTo
FROM BM_Customers
WHERE DateCreated BETWEEN ‘20180101’ AND ‘20181231’
–DROP TABLES
ALTER TABLE BM_Customers
SET (SYSTEM_VERSIONING = OFF)
ALTER TABLE BM_Customers
DROP PERIOD FOR SYSTEM_TIME
DROP TABLE BM_Customers
DROP TABLE dbo.BM_CustomersHistory
1
0
G
Example:
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN ‘2014-01-01 00:00:00.0000000’ AND ‘2015-01-01 00:00:00.0000000’
WHERE EmployeeID = 1000 ORDER BY ValidFrom;
1
4
Data is not aggregated in G and H
E is showing all data > 2014
So the only answer can be D
7
0
D is not a correct pivot query. the right one should be
select CustomerId,[2014]
from
(select CustomerId, datename(year,DateCreated) DateCreated , AnnualRevenue from BM_Customers) AS BM_Cust
PIVOT (
SUM(AnnualRevenue) FOR DateCreated in ([2014]) — sum or avg
) as pivottable;
i do not find any right answer. it could be E if >=2014 was = 2014
4
1