Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 99 discussion

Actual exam question from Microsoft's 70-761

Question #: 99
Topic #: 1

SIMULATION -
You have a table named Cities that has the following two columns: CityID and CityName. The CityID column uses the int data type, and CityName uses nvarchar
(max).
You have a table named RawSurvey. Each row includes an identifier for a question and the number of persons that responded to that question from each of four cities. The table contains the following representative data:

A reporting table named SurveyReport has the following columns: CityID, QuestionID, and RawCount, where RawCount is the value from the RawSurvey table.
You need to write a Transact-SQL query to meet the following requirements:
✑ Retrieve data from the RawSurvey table in the format of the SurveyReport table.
✑ The CityID must contain the CityID of the city that was surveyed.
✑ The order of cities in all SELECT queries must match the order in the RawSurvey table.
✑ The order of cities in all IN statements must match the order in the RawSurvey table.
Construct the query using the following guidelines:
✑ Use one-part names to reference tables and columns, except where not possible.
ALL SELECT statements must specify columns.

✑ Do not use column or table aliases, except those provided.
Do not surround object names with square brackets.

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.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

Show Suggested Answer

Suggested Answer: Please see explanation
1 SELECT Rawcount
2 from (select cityid,questioned,rawcount) AS t1
3 unpivot
4 (rawcount for questioned in (QuestionID)) AS t2
5 JOIN t2
6. ON t1.CityName = t2.cityName
UNPIVOT must be used to rotate columns of the Rawsurvey table into column values.
References:
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

by raf77 at Sept. 7, 2019, 1:30 p.m.

Comments

Submit Cancel

mlourinho

Highly Voted 5 years, 7 months ago

SELECT Cities.CityID, T1.QuestionID, T2.RawCount FROM ( SELECT QuestionID, Tokyo, Boston, London, [New York] from RawSurvey ) t1 unpivot( RawCount FOR CityName (Tokyo, Boston, London, [New York]) ) t2 INNER JOIN Cities ON T2.CityName = Cities.CityName

upvoted 13 times

Andy7622

4 years, 6 months ago

two mistakes in this code : 1. missed IN before cities' list in UNPIVOT 2 there should '.t2.QuestionID ' in SELECT instead 't1.QuestionId'. Verified in SSMS

upvoted 2 times

...

M4x

Highly Voted 5 years, 9 months ago

declare @RawSurvey as table ( QuestionID char(2) not null primary key clustered, Tokyo int not null, Boston int not null, London int not null, NewYork int not null ) insert into @RawSurvey values ('Q1', 1, 42, 48, 51), ('Q2', 22, 39, 58, 42), ('Q3', 29, 41, 61, 33), ('Q4', 62, 70, 60, 50) select QuestionId, City, Answer from ( select QuestionId, Tokyo, Boston, London, NewYork from @RawSurvey ) as t1 unpivot ( Answer for City in ([Tokyo], [Boston], [London], [NewYork]) ) as t2

upvoted 5 times

Anette

5 years, 1 month ago

yes, its right instead of "Answer" must be "Rawcount"

upvoted 1 times

...

Aghie

4 years, 10 months ago

the syntax is corrext and agreed to the answer. but the instructions state that do not use bracket.

upvoted 1 times

...

Skc20

Most Recent 4 years, 5 months ago

;with cte as ( select a.City, a.QuestonID,a.RawCount from RawSurvey s unpivot (RawCount for City in (Tokyo,Boston,London,NewYork))a) select a.CityID, b.QuestonID,b.RawCount from Cities a join cte b on a.CityName= b.City

upvoted 1 times

...

kiri2020

4 years, 8 months ago

create table #RawSurvey ( QuestionID char(2) not null primary key clustered, Tokyo int not null, Boston int not null, London int not null, NewYork int not null ) insert into #RawSurvey values ('Q1', 1, 42, 48, 51), ('Q2', 22, 39, 58, 42), ('Q3', 29, 41, 61, 33), ('Q4', 62, 70, 60, 50) create table #Cities (CityID int, CityName varchar(20)) insert Cities values (1, 'Tokyo'), (2, 'Boston'),(3, 'London'),(4, 'NewYork') select CityID, QuestionId, Rawcount from ( select QuestionId, Tokyo, Boston, London, [NewYork] from #RawSurvey ) as t1 unpivot (Rawcount for CityName in ([Tokyo], [Boston], [London], [NewYork]) ) as t2 join #Cities on t2.CityName = #Cities.CityName

upvoted 4 times

...

Bartek

5 years, 9 months ago

SELECT CITYID, QUESTIONID, RAWCOUNT FROM CITIES AS T1 JOIN ( SELECT QUESTIONID, RAWCOUNT , CITYNAME FROM RAVSURVEY UNPIVOT (RAWCOUNT FOR CITYNAME IN ('TOKYO','BOSTON')) AS PVT ) AS T2 ON T1.CITYNAME = T2.CITYNAME

upvoted 2 times

...

M4x

5 years, 9 months ago

The purple text is the question, and the black text is the answer

upvoted 2 times

...

raf77

5 years, 9 months ago

Something is wrong with this question. I see only one solution: SELECT CityID, QuestionID, RawCount from RawSurvey UNPIVOT (CityName FOR ('Tokio', 'Boston', 'London', 'New York')) AS t1 PIVOT (sum(RawCount) for questionid in (QestionID)) as t2 INNER JOIN Cities on t1.CityName = Cities.CityName

upvoted 1 times

M4x

5 years, 9 months ago

You must enclose the cityname between [ and ]

upvoted 2 times

...