Latest Braindump2go Microsoft 70-461 Certification PDF With 100% Guarantee Pass (106-122)

New Braindump2go 70-461 Exam Questions Updated Today! Want to know New Questions in 2015 70-461 Exam? Download Free Braindump2go 70-461 Exam Preparation Materials Now!

Vendor: Microsoft
Exam Code: 70-461
Exam Name: Querying Microsoft SQL Server 2012

1102

QUESTION 106
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named invoice. The solution must ensure that the invoice is validated by using Sales.InvoiceSchema.
Provide the correct code in the answer area.
Answer:
DECLARE @invoice XML(Sales.InvoiceSchema)

QUESTION 107
You have a view that was created by using the following code:

wps6FF0.tmp_thumb[2]

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory. Sales.fn_OrdersByTerritory must meet the following requirements:
– Use one-part names to reference columns.
– Return the columns in the same order as the order used in OrdersByTerritoryView.
– Part of the correct T-SQL statement has been provided in the answer area.
Provide the complete code.

wpsB6FF.tmp_thumb[3]

Answer:
CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int)
RETURNS TABLE
AS
RETURN
(
SELECT
OrderID,
OrderDate,
SalesTerritoryID,
TotalDue
FROM Sales.OrdersByTerritory
WHERE SalesTerritoryID=@T
)

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

wpsEEF1.tmp_thumb

You need to create a query that meets the following requirements:
– References columns by using one-part names only.
– Groups aggregates by SalesTerritorylD, and then by ProductlD.
– Orders the results in descending order by SalesTerritorylD and then by ProductlD.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

wps13E0.tmp_thumb[2]

Answer:
SELECT SalesTerritoryID,
ProductID,
AVG(UnitPrice),
MAX(OrderQty)
MAX(DiscountAmount)
FROM Sales.Details
GROUP BY SalesTerritoryID, ProductID
ORDER BY SalesTerritoryID DESC, ProductID DESC

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

wps48A6.tmp_thumb

You need to create a query for a report. The query must meet the following requirements:
– NOT use object delimiters.
– Use the first initial of the table as an alias.
– Return the most recent order date for each customer.
– Retrieve the last name of the person who placed the order.
The solution must support the ANSI SQL-99 standard.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

wps7958.tmp_thumb[2]

Answer:
SELECT C.LastName,
MAX(O.OrderDate) AS MostRecentOrderDate
FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID=O.CustomerID
GROUP BY C.Lastname
ORDER BY MAX (O.OrderDate) DESC

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

wpsAD73.tmp_thumb

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.

wpsCDDF.tmp_thumb

Answer:
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

QUESTION 111
Why is it important to use standard SQL code when possible and know what is standard
and what isn’t? (Choose all that apply.)

A.    It is not important to code using standard SQL.
B.    Standard SQL code is more portable between platforms.
C.    Standard SQL code is more efficient.
D.    Knowing what standard SQL code is makes your knowledge more portable.

Answer: BD

QUESTION 112
Which of the following is not a violation of the relational model?

A.    Using ordinal positions for columns
B.    Returning duplicate rows
C.    Not defining a key in a table
D.    Ensuring that all attributes in the result of a query have names

Answer: D

QUESTION 113
What is the relationship between SQL and T-SQL?

A.    T-SQL is the standard language and SQL is the dialect in Microsoft SQL Server.
B.    SQL is the standard language and T-SQL is the dialect in Microsoft SQL Server.
C.    Both SQL and T-SQL are standard languages.
D.    Both SQL and T-SQL are dialects in Microsoft SQL Server.

Answer: B

QUESTION 114
Which of the following correctly represents the logical query processing order of the various query clauses?

A.    SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
B.    FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
C.    FROM > WHERE > GROUP BY > HAVING > ORDER BY > SELECT
D.    SELECT > ORDER BY > FROM > WHERE > GROUP BY > HAVING

Answer: B

QUESTION 115
Which of the following is invalid? (Choose all that apply.)

A.    Referring to an attribute that you group by in the WHERE clause
B.    Referring to an expression in the GROUP BY clause; for example, GROUP BY YEAR(orderdate)
C.    In a grouped query, referring in the SELECT list to an attribute that is not part of
D.    the GROUP BY list and not within an aggregate function Referring to an alias defined in the SELECT clause in the HAVING clause

Answer: CD

QUESTION 116
What is true about the result of a query without an ORDER BY clause?

A.    It is relational as long as other relational requirements are met.
B.    It cannot have duplicates.
C.    The order of the rows in the output is guaranteed to be the same as the insertion order.
D.    The order of the rows in the output is guaranteed to be the same as that of the clustered index.

Answer: A

QUESTION 117
What is the importance of the ability to assign attribute aliases in T-SQL? (Choose all
that apply.)

A.    The ability to assign attribute aliases is just an aesthetic feature.
B.    An expression that is based on a computation results in no attribute name unless you assign one with an alias, and this is not relational.
C.    T-SQL requires all result attributes of a query to have names.
D.    Using attribute aliases, you can assign your own name to a result attribute if you need it to be different than the source attribute name.

Answer: BD

QUESTION 118
What are the mandatory clauses in a SELECT query, according to T-SQL?

A.    The FROM and SELECT clauses
B.    The SELECT and WHERE clauses
C.    The SELECT clause
D.    The FROM and WHERE clauses

Answer: C

QUESTION 119
Which of the following practices are considered bad practices? (Choose all that apply.)

A.    Aliasing columns by using the AS clause
B.    Aliasing tables by using the AS clause
C.    Not assigning column aliases when the column is a result of a computation
D.    Using * in the SELECT list

Answer: CD

QUESTION 120
Why is it important to use the appropriate type for attributes?

A.    Because the type of your attribute enables you to control the formatting of the values
B.    Because the type constrains the values to a certain domain of supported values
C.    Because the type prevents duplicates
D.    Because the type prevents NULLs

Answer: B

QUESTION 121
Which of the following functions would you consider using to generate surrogate keys?
(Choose all that apply.)

A.    NEWID
B.    NEWSEQUENTIALID
C.    GETDATE
D.    CURRENT_TIMESTAMP

Answer: AB

QUESTION 122
You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

wpsB0F.tmp_thumb[3]

Answer:
CREATE VIEW sales.uv_CustomerFullNames
WITH SCHEMABINDING
AS SELECT
FirstName,
LastName
FROM Sales.Customers


Instant Download Braindump2go New Released Microsoft 70-461 Exam Dumps PDF & VCE! Enjoy 1 year Free Updation! 100% Exam Pass Guaranteed Or Full Money Back!

152

http://www.braindump2go.com/70-461.html