Latest Real 70-461 Tests Dumps and VCE Exam Questions 71-80

Ensurepass

QUESTION 71

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. You need to retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?

 

A.  SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value

    FROM StudentMarks

    GROUP BY StudentCode

B.  SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank

    FROM StudentMarks

C.  SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value

    FROM StudentMarks

    GROUP BY StudentCode

D.  SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value

    FROM StudentMarks

    GROUP BY StudentCode

E.  SELECT StudentCode AS Code, Marks AS Value FROM (

      SELECT StudentCode, Marks AS Marks,

        RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank

      FROM StudentMarks) tmp

    WHERE Rank = 1

F.  SELECT StudentCode AS Code, Marks AS Value FROM (

      SELECT StudentCode, Marks AS Marks,

        RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank

      FROM StudentMarks) tmp

    WHERE Rank = 1

G.  SELECT StudentCode AS Code, Marks AS Value FROM (

      SELECT StudentCode, Marks AS Marks,

        RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank

      FROM StudentMarks) tmp

    WHERE Rank = 1

H.  SELECT StudentCode AS Code, Marks AS Value FROM (

      SELECT StudentCode, Marks AS Marks,

        RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank

      FROM StudentMarks) tmp

    WHERE Rank = 1

 

Correct Answer: F

 

 

QUESTION 72

You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

 

A.      Use the CAST function.

B.      Use the DATE data type.

C.      Use the FORMAT function.

D.      Use an appropriate collation.

E.       Use a user-defined table type.

F.       Use the VARBINARY data type.

G.      Use the DATETIME data type.

H.      Use the DATETIME2 data type.

I.        Use the DATETIME2 data type.

J.        Use the TODATETIMEOFFSET function.

 

Correct Answer: I

 

QUESTION 73

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?

 

A.      Use the CAST function.

B.      Use the DATE data type.

C.      Use the FORMAT function.

D.      Use an appropriate collation.

E.       Use a user-defined table type.

F.       Use the VARBINARY data type.

G.      Use the DATETIME data type.

H.      Use the DATETIME2 data type.

I.        Use the DATETIMEOFFSET data type.

J.        Use the TODATETIMEOFFSET function.

 

Correct Answer: E

 

 

QUESTION 74

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:

 

Ÿ   Future modifications to the table definition will not affect the applications’ ability to access data.

Ÿ   The new object can accommodate data retrieval and data modification.

Ÿ   You need to achieve this goal by using the minimum amount of changes to the applications.

 

What should you create for each application?

 

A.      Synonyms

B.      Common table expressions

C.      Views

D.      Temporary tables

 

Correct Answer: C

 

 

 

 

 

 

QUESTION 75

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:

 

Ÿ   Takes an input variable

Ÿ   Returns a table of values

Ÿ   Cannot be referenced within a view

 

Which object should you use?

 

A.      Scalar-valued function

B.      Inline function

C.      User-defined data type

D.      Stored procedure

 

Correct Answer: D

 

 

QUESTION 76

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:

 

clip_image002

 

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?

 

A.  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

 

B.  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

C.  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

D.  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

 

Correct Answer: A

 

 

QUESTION 77

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data. BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

 

Build List and Reorder:

clip_image004

 

Correct Answer:

clip_image005

 

 

QUESTION 78

You create a view based on the following statement:

 

clip_image006

 

You grant the Select permission to User1 for this view. You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1. Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

 

Build List and Reorder:

clip_image008

 

Correct Answer:

clip_image009

 

 

QUESTION 79

You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer. Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

 

Build List and Reorder:

clip_image011

 

Correct Answer:

clip_image012

 

 

QUESTION 80

You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition:

 

clip_image013

 

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

 

Build List and Reorder:

clip_image015

 

Correct Answer:

clip_image016