PrepAway - Latest Free Exam Questions & Answers

You need to write a Transact-SQL query to meet the foll…

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.

PrepAway - Latest Free Exam Questions & Answers

Answer: See the explanation

Explanation:
SELECT CityID, QuestionID, RawCount
FROM Cities AS t1
(SELECT Tokyo, Boston, London, “New York” FROM Rawsurvey) p
UNPIVOT
(Rawcount FOR CityName IN (‘Tokyo’,’Boston’,’London’,’New York’)
AS t2
JOIN t2
ON t1.CityName = t2.cityName

UNPIVOT must be used to rotate columns of the Rawsurvey table into column values.
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

10 Comments on “You need to write a Transact-SQL query to meet the foll…”

  2. Peter says:

    As stated by scotrid the answer is wrong. It even thows syntax errors.

    In my opinion the answer should be like the example given below:

    IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Cities’)
    BEGIN
    DROP TABLE dbo.Cities;
    END;
    IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘RawSurvey’)
    BEGIN
    DROP TABLE dbo.RawSurvey;
    END;

    CREATE TABLE Cities (
    CityId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CityName NVARCHAR(MAX) NOT NULL
    );

    INSERT INTO Cities VALUES
    (‘Tokyo’), (‘Boston’), (‘London’), (‘NewYork’);

    CREATE TABLE RawSurvey (
    QuestionId NCHAR(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),
    (‘Q5’, 63, 31, 41, 21),
    (‘Q6’, 32, 1, 16, 34);

    SELECT CityId, QuestionId, RawCount
    /*new*/ FROM (SELECT QuestionId, Tokyo, Boston, London, NewYork FROM RawSurvey)
    AS t1
    /*new*/ UNPIVOT (RawCount FOR CityName IN (Tokyo, Boston, London, NewYork))
    AS t2
    JOIN
    /*new*/ Cities ON t2.CityName = Cities.CityName;




    18



    0
    3. EY Auditor says:

      SELECT CityId, QuestionId, RawCount
      FROM (SELECT QuestionId, Tokyo, Boston, London, NewYork FROM RawSurvey) t2
      UNPIVOT
      (
      RawCount
      FOR CityName IN (Tokyo, Boston, London, NewYork)
      )
      AS t2
      JOIN Cities t1 ON t2.CityName = t1.CityName;




      10



      5
  3. scotrid says:

    Happy new year , here is the answer

    select cityid, questionID, Rawcount
    from
    (select cityID, cityname from cities ) as t1
    join
    (select questionID, tokyo, boston, London, Newyork from Rawsurvey ) p
    unpivot
    (Rawcount FOR cityname IN (tokyo, boston, London, Newyork )) as t2

    ON t1.cityname = t2.cityname
    go




    1



    0
  4. eder says:

    SELECT CityID, QuestionID, RawCount FROM (SELECT * FROM rawsurvey)
    as t1 UNPIVOT( RawCount FOR CityNAME IN (Tokyo,Boston,London,NewYork))
    as t2
    JOIN cities AS c
    on t2.CITYNAME=C.CITYNAME




    1



    1

Leave a Reply