← Back to exam page

70-461 – Querying Microsoft SQL Server 2012

Loading demo links...

Showing 13–15 of 15 questions

Question 13

You support a database structure shown in the exhibit.

You need to write a query that displays the following details:

Total sales made by sales people, year, city, and country

Sub totals only at the city level and country level A grand total of the sales amount

Which Transact-SQL query should you use?

Select an option, then click Submit answer.

  • ○

    SELECT SalesPerson.Name, Country, City,
    DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
    FROM Sale INNER JOIN SalesPerson
    ON Sale.SalesPersonId = SalesPerson.SalesPersonId
    GROUP BY SalesPerson.Name, Country, City,
    DatePart(yyyy, SaleDate)
  • ○

    SELECT SalesPerson.Name, Country, City,
    DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
    FROM Sale INNER JOIN SalesPerson
    ON Sale.SalesPersonId = SalesPerson.SalesPersonId
    GROUP BY ROLLUP(SalesPerson.Name, Country, City,
    DatePart(yyyy, SaleDate))
  • ○

    SELECT SalesPerson.Name, Country, City,
    DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
    FROM Sale INNER JOIN SalesPerson
    ON Sale.SalesPersonId = SalesPerson.SalesPersonId
    GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
  • ○

    SELECT SalesPerson.Name, Country, City,
    DatePart(yyyy, SaleDate), Sum(Amount)
    FROM Sale
    INNER JOIN SalesPerson
    ON Sale.SalesPersonId = SalesPerson.SalesPersonId
    GROUP BY SalesPerson.Name, Country, City,
    DatePart(yyyy, SaleDate),
    GROUPING SETS((Country, City, DatePart(yyyy, SaleDate)),
    ( Country, City) (Country),())
Question 14

You administer a Microsoft SQL Server database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.

You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

Select an option, then click Submit answer.

  • ○

    CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
  • ○

    ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
  • ○

    ALTER INDEX ALL ON OrderDetail REBUILD
  • ○

    ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Question 15

You are designing a Microsoft SQL Server database named Orders.

You create a table by running the following Transact-SQL statement:

You create a stored procedure to be used by an ASP.NET application that runs the following statement:

You need to ensure that the query runs as efficiently as possible.

Which Transact-SQL statement should you run?

Select an option, then click Submit answer.

  • ○

    Option A
  • ○

    Option B
  • ○

    Option C
  • ○

    Option D