[Free] 2018(Aug) Dumps4cert Microsoft 70-461 Dumps with VCE and PDF Download 21-30

Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Aug Microsoft Official New Released 70-461
100% Free Download! 100% Pass Guaranteed!

Querying Microsoft SQL Server 2012

Question No: 21

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server.

The database contains two tables that have the following definitions:

image

Global customers place orders from several countries.

You need to view the country from which each customer has placed the most orders. Which Transact-SQL query do you use?

  1. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c

    INNER JOIN

    (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID

    ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders

    GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID

    WHERE o.Rnk = 1

  2. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM

    (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID

    ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer c

    INNER JOIN Orders o

    ON c.CustomerID = o.CustomerID

    GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1

  3. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c

    INNER JOIN

    (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID

    ORDER BY OrderAmount DESC) AS Rnk FROM Orders

    GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID

    WHERE o.Rnk = 1

  4. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c

INNER JOIN

(SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) DESC) AS OrderAmount FROM Orders

GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID

ORDER BY OrderAmount DESC

Answer: A

Question No: 22

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another.

Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.

You need to ensure that your query executes in the minimum possible time. Which query should you use?

  1. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (

    SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders

    UNION ALL

    SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders

    ) AS p

  2. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (

    SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders

    UNION

    SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders

    ) AS p

  3. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders

    UNION

    SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders

  4. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders

UNION ALL

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders

Answer: A

Reference: http://msdn.microsoft.com/en-us/library/ms180026.aspx

Reference: http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs- union-all-optimalperformance-comparison/

Question No: 23

You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

  1. Use the CAST function.

  2. Use the DATE data type.

  3. Use the FORMAT function.

  4. Use an appropriate collation.

  5. Use a user-defined table type.

  6. Use the VARBINARY data type.

  7. Use the DATETIME data type.

  8. Use the DATETIME2 data type.

  9. Use the DATETIMEOFFSET data type.

  10. Use the TODATETIMEOFFSET function.

Answer: E

Question No: 24 CORRECT TEXT

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.)

image

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:

-> Return rows ordered by descending values in the UnitPrice column.

-> Use the Rank function to calculate the results based on the UnitPrice column.

-> Return the ranking of rows in a column that uses the alias PriceRank.

-> Use two-part names to reference tables.

-> Display the columns in the order that they are defined in the table. The PriceRank column must appear last.

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

image

Answer: SELECT CatID, CatName, ProductID, ProdName, UnitPrice, RANK (ORDER BY UnitPrice DESC) OVER () AS PriceRank

FROM Sales.ProductCatalog ORDER BY PriceRank

Question No: 25

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.

The tables have the following definitions:

image

Users are able to use single INSERT statements or INSERT…SELECT statements into this view.

You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.

Which Transact-SQL statement should you use?

  1. CREATE TRIGGER TrgVwEmployee ON VwEmployee

    FOR INSERT AS

    BEGIN

    INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted

    END

  2. CREATE TRIGGER TrgVwEmployee ON VwEmployee

    INSTEAD OF INSERT AS

    BEGIN

    INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted

    END

  3. CREATE TRIGGER TrgVwEmployee ON VwEmployee

    INSTEAD OF INSERT AS

    BEGIN

    DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25),

    @PersonID

    INT, @EmployeeNumber NVARCHAR(15)

    SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName,

    @EmployeeNumber

    = EmployeeNumber FROM inserted

    INSERT INTO Person(Id, FirstName, LastName) VALUES(@ID, @FirstName, @LastName)

    INSERT INTO Employee(PersonID, EmployeeNumber) VALUES(@PersonID, @EmployeeNumber

    End

  4. CREATE TRIGGER TrgVwEmployee ON VwEmployee

INSTEAD OF INSERT AS

BEGIN

INSERT INTO Person(Id, FirstName, LastName)

SELECT Id, FirstName, LastName FROM VwEmployee INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM VwEmployee End

Answer: B

Question No: 26

You create a stored procedure that will update multiple tables within a transaction.

You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.

Which Transact-SQL statement should you include at the beginning of the stored procedure?

  1. SET XACT_ABORT ON

  2. SET ARITHABORT ON

  3. TRY

  4. BEGIN

  5. SET ARITHABORT OFF

  6. SET XACT_ABORT OFF

Answer: A

Reference: http://msdn.microsoft.com/en-us/library/ms190306.aspx Reference: http://msdn.microsoft.com/en-us/library/ms188792.aspx

Question No: 27

You have several SQL Server queries.

You plan to optimize the queries to improve performance. You run the queries in SQL Server Management Studio.

You need to compare query runs to expose the indexing issues of the queries.

Which three actions should you perform from SQL Server Management Studio? Each correct answer presents part of the solution.

  1. Enable the Debug option.

  2. Add the STATISTICS TIME execution setting to the query.

  3. Add the STATISTICS IO execution setting to the query.

  4. Add the STATISTICS PROFILE execution setting to the query.

  5. Enable the Include Actual Execution Plan option.

Answer: B,C,E Explanation:

E: An execution plan is theresult of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. To generate the first execution plan, you can enable the Include Actual Execution Plan option.

B: SET STATISTICS TIME displays the number of milliseconds required to parse, compile, and execute each statement.

C: STATISTICS IO causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements. This is useful information for optimizing queries.

The information include Scan count:

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.

Scan count is 0 if the index used is a unique index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = lt;valuegt;.

Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on anon-primary key column. This is done to check for duplicate values for the keyvalue that you are searching for. For example WHERE Clustered_Index_Key_Column = lt;valuegt;.

Scan count is N when N is the number of different seek/scan started towards the leftor right side at the leaf level after locating a key value using the index key.

Question No: 28

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.

Which type of user should you create?

  1. SQL user without login

  2. SQL user with a custom SID

  3. SQL user with login

  4. Domain user

Answer: A

Question No: 29

You have a Microsoft SQL Server database that includes two tables named EmployeeBonus and BonusParameters. The tables are defined by using the following Transact-SQL statements:

image

The tables are used to compute a bonus for each employee. The EmployeeBonus table has a non-null value in either the Quarterly, HalfYearly or Yearly column. This value indicates which type of bonus an employee receives. The BonusParameters table contains one row for each calendar year that stores the amount of bonus money available and a company performance indicator for that year.

You need to calculate a bonus for each employee at the end of a calendar year. Which Transact-SQL statement should you use?

  1. SELECT

    CAST(CHOOSE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20,

    (Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS ‘Bonus’ FROM

    EmployeeBonus, BonusParameters

  2. SELECT 鈥淏onus鈥?= CASE EmployeeBonus

    WHEN Quarterly=1 THEN (Quarterly * AvailableBonus * CompanyPerformance)/40 WHEN HalfYearly=1 THEN (HalfYearly * AvailableBonus * CompanyPerformance)/20 WHEN Yearly=1 THEN (Yearly * AvailableBonus * CompanyPerformance)/10

    END

    FROM EmployeeBonus,BonusParameters

  3. SELECT

    CAST(COALESCE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20,

    (Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS ‘Bonus’ FROM

    EmployeeBonus, BonusParameters

  4. SELECT

NULLIF(NULLIF((Quarterly * AvailableBonus * CompanyPerformance)/40,(HalfYearly * AvailableBonus * CompanyPerformance)/20),

(Yearly * AvailableBonus * CompanyPerformance)/10) AS ‘Bonus’ FROM

EmployeeBonus, BonusParameters

Answer: B

Question No: 30 CORRECT TEXT

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.)

image

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:

-> UnitPrice must be returned in descending order.

-> The query must use two-part names to reference the table.

-> The query must use the RANK function to calculate the results.

-> The query must return the ranking of rows in a column named PriceRank.

-> The list must display the columns in the order that they are defined in the table.

PriceRank must appear last.

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

image

Answer: Please review the explanation part for this answer

Explanation:

SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,

ProductCatalog.ProdName, ProductCatalog.UnitPrice,

RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog

ORDER BY ProductCatalog.UnitPrice DESC

100% Dumps4cert Free Download!
70-461 PDF
100% Dumps4cert Pass Guaranteed!
70-461 Dumps

Dumps4cert ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.