20 07 2018

[2018/July New]Free Offer of Braindump2go 70-461 PDF for Instant Downloading[200-210]

itexams | 70-461 Dumps, 70-461 Exam Questions, 70-461 PDF, 70-461 VCE, Microsoft

2018/July New Microsoft 70-461 Exam Dumps with PDF and VCE Free Updated Today! Following are some new 70-461 Real Exam Questions:

1.|2018 Latest 70-461 Exam Dumps (PDF & VCE) 236Q&As Download:

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

2.|2018 Latest 70-461 Exam Questions & Answers Download:

https://drive.google.com/drive/folders/0B75b5xYLjSSNTUlVSmx1LXg1TlU?usp=sharing

QUESTION 200
You use a Microsoft SQL Server database that contains a table. The table has records of customer orders.
Your company has three divisions that have the following names:
East
Central
West
You need to create a query that displays the following information:
The number of sales for each product (ProductName) grouped by the
division (Division) that sold the product
A column for each division
Which Transact-SQL query should you use?

Answer: D
Explanation:
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

QUESTION 201
SIMULATION
You need to execute an UPDATE statement that modifies a subset of rows in a table. The rows affected by the UPDATE statement must only be committed if the row count criteria is met.
Part of the correct Transact-SQL has been provided in the answer are 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.

Answer:
1. DECLARE @ExprectedRowCount int = 0
2.
3. BEGIN TRAN OrderUpdate
4.
5. UPDATE dbo.OrderHeader
6. SET PromisedDate = DATEADD(dd, 5, PromisedDate)
7. WHERE CustomerID = 0432
8. AND Shipped =0
9.
10. If @@RowCount = @ExpectedRowCount
11.
12. COMMIT OrderUpdate
13.
14. else
15.
16. ROLLBACK OrderUpdate
Explanation:
On line 3 add: BEGIN TRAN
On line 12 add: COMMIT
On line 16 add: ROLLBACK
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql

QUESTION 202
SIMULATION
You need to create a table named OrderDetails that contains the following columns: LineItemTotal, ListPrice, and Quantity. LineItemTotal stores the product of ListPrice and Quantity for each row.
Construct the Transact-SQL for creating the table by using the following guidelines:
Ensure that the calculation for a line item total is not run every time the table is queried.
Do not use any object delimiters.
Store LineItemTotal as the last column in the table.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment 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.
Answer:
1. CREATE TABLE OrderDetails
2. (
3. ListPrice money NOT NULL,
4. Quantity int NOT NULL,
5. LineItemTotal AS (ListPrice * Quantity) PERSISTED
6. )
To line 5 add: LineItemTotal AS (ListPrice * Quantity) PERSISTED

QUESTION 203
SIMULATION
You have a business reporting database.
Several power users generate reports from the database.
You need to create an object in the database to meet the following requirements:
– Provide the power users with the ability to use simple queries.
– Prevent schema changes on objects related to the query.
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 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.
Answer:
1 Create View Customer_Orders WITH SCHEMABINDING AS SELECT CustomerName,
2 OrderDate FROM Customers INNER JOIN Orders on
3 Customers.CustomerID=Orders.CustomerID
To Line 1, after WITH, add SCHEMABINDING
SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

QUESTION 204
SIMULATION
You have a view named Person.vAdditionalContactInfo that uses data from a table named Customers. The Customers table was created by running the following Transact-SQL statement:

You need to create a query that returns the first and last names of a customer from the view. If the CustomerID column does not have a value defined, the following message must be added to the query result: “Customer ID Does Not Exist.”
Construct the query using the following quidelines:
Do not use aliases for column or table names.
Display the columns in the same order as they are defined in the
Customers table.
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 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.
Answer:
1 SELECT FirstName, LastName, CustomerID =
2 CASE
3 WHEN CustomerID IS NULL THEN “Customer ID Does Not Exist”
4 ELSE CustomerID
5 END
6 FROM Person.vAdditionalContactInfo;
To line 1 add: CustomerID =
To line 2 add: CASE
To line 3 add: IS NULL
To line 5 add: END
References:
https://technet.microsoft.com/en-us/library/ms181765(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/t-sql/queries/is-null-transact-sql

QUESTION 205
SIMULATION
You have a data warehouse that contains the data for all the customers of your company.
You need to create a stored procedure that generates a list of customer addresses. The list must meet the following requirements:
The procedure must accept a string value.
The procedure should return all customers who reside in a city whose
name begins with the provided value.
If a user provides a blank value, the procedure must execute, and then
return a blank result set.
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 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.
Answer:
1 CREATE PROCEDURE uspGetAddress @City nvarchar(50) = “”
2 AS
3 SELECT *
4 FROM Person.Address
5 WHERE City LIKE @City *
6 GO
On line 1 add “” (set the default value)
On line 5 add LIKE (use string comparison)

QUESTION 206
SIMULATION
You have a database named Sales that contains the tables sworn in the exhibit. (Click the Exhibit button.)

You need to create a query for a report. The query must meet the following requirements:
Return the last name of the customer who placed the order.
Return the most recent order date for each customer.
Group the results by CustomerID.
Display the most recent OrderDate first.
The solution must support the ANSI SQL-99 standard and must not use table or column aliases.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment 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.
Answer:
1. SELECT LastName,
2 MAX(OrderDate) AS MostRecentOrderDate
3 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
4 GROUP BY CustomerID
5 ORDER BY OrderDate DESC
On line 3 add Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID On line 4 add CustomerID
On line 5 add OrderDate DESC
References: https://technet.microsoft.com/en-us/library/ms190014(v=sql.105).aspx

QUESTION 207
SIMULATION
You need to create a query that meets the following requirements:
The query must return a list of salespeople ranked by amount of sales
and organized by postal code.
The salesperson who has the highest amount of sales must be ranked
first.
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 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.
Answer:
1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS “Ranking”,
2 p.LastName, s.SalesYTD, a.PostalCode
3 FROM Sales.SalesPerson AS a
etc
On line 1 add: RowNumber
One line 1 add: PARTITION BY
ROW_NUMBER() numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SYNTAX for OVER:
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
Example: Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName, LastName,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Here is a partial result set.
RowNumber FirstName LastName SalesQuota
——— ——— —————— ————-
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

QUESTION 208
Drag and Drop Question
You use Microsoft SQL Server to develop a database.
You need to create a query that displays a list of all customers and related orders. The query must display customers that have no related orders. If the last order for a customer was within the last 30 days, the query must return the most recent OrderDate for the customer.
Which four Transact-SQL statements should you use in sequence? To answer, move the appropriate Transact-SQL statements from the list of statements to the answer area and arrange them in the correct order.

Answer:

QUESTION 209
You have a Microsoft SQL Server database. The database contains a table that is defined by the following Transact-SQL statement:

Employee names must always start with a capital letter.
You need to define a constraint to enforce the employee name requirement.
Which Transact-SQL statement should you use?

A.
B.
C.
D.

Answer: B

QUESTION 210
You develop a Microsoft SQL Server database that contains a table named Products. The Products table has columns named ProductId, CategoryId, Name, and Price.
Product prices are often updated as follows:
For a single product by a specific value by using the ProductId column
For an entire category by a percentage by using the CategoryId column
You need to log the change price to a new table named PriceChange by using the ProductId’ ChangedValue, and ChangedDate columns.
You also need to ensure that increases in price are recorded as positive values and decreases in price as negative values.
Which Transact-SQL query should you use?

A.
B.
C.
D.