Latest Microsoft 70-461 Actual Free Exam Questions & Community Discussion, Page 4

Question #34

Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice.
The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?

A. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 1.05
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

B. UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
SELECT ProductCode, Price, Price * 1.05 FROM Products

C. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

D. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

Discussion 0

Correct Answer: D Vote an answer

Question #35

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

You have the following query:

You need to recreate the query to meet the following requirements:
* Reference columns by using one-part names only.
* Sort aggregates by SalesTerritoryID, and then by ProductID.
* Order the results in descending order from SalesTerritoryID to ProductID.
* The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.

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

B. SELECT SalesTerritoryID,
ProductID,
AVG(UnitPrice),
MAX(OrderQty),
GROUP BY SalesTerritoryID , ProductID
ORDER BY SalesTerritoryID DESC, ProductID DESC

Discussion 0

Correct Answer: A Vote an answer

Question #36

The Agent table of a Microsoft SQL Server database contains several million rows. The database uses the SQL_Latin1_General_Cp1_CS_AS collation.
You need to ensure that the following requirements are met:
* The values of the StateRefID column conform to the pattern of 3 uppercase letters followed by 5 numeric digits, such as "ABC12345".
* The StateRefID values are unique within the Agent table.
* The values of all records that will be inserted or updated in the Agent table are correctly formatted.
* Exisitng rows are ignored
Which Transact-SQL statement should you run?

A. Option B

B. Option D

C. Option C

D. Option A

Discussion 0

Correct Answer: D Vote an answer

Question #37

You are the administrator for a heavily-used OLTP Microsoft SQL Server database.
You are troubleshooting performance issues seen when using stored procedures in the database. The database stores millions of orders across thousands of customers. Some of the customers have large numbers of orders, while others have only one order. You update the statistics and perform defragmentation of all tables and indexes, but two stored procedures still have issues when accessing data.
p_GetCustomer accepts @companyID as a parameter. From the results of profiling, you know that 90 percent of the calls use the @companyid value of 5, while the other 10 percent of calls are evenly distributed across another 10000 values. While viewing the execution plan, you discover that a non-clustered index seek is used.
p_GetShipDate accepts @orderID as a parameter and returns the ship date for that order. You discover that the execution plan is performing a scan on a non-clustered index that has orderID as the index key.
You need to add appropriate query hints to each stored procedure to improve the performance.
What should you do? To answer, drag the appropriate procedures to the correct hints. Each procedure may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Discussion 0

Correct Answer:

Explanation:
Box 1: Optimize FOR..
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] ) Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.
Box 2: FORCESEEK
FORCESEEK [ (index_value(index_column_name [ ,... n ] )) ]
Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view.
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017

Question #38

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW

B. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

C. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

D. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

E. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

F. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS

G. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

H. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

Discussion 0

Correct Answer: D Vote an answer

Question #39

You develop a Microsoft SQL Server Database. The database contains a table named Status that is defined by the following Transact-SQL statement:

There are thousands of rows in the Status table, with significant duplication of data in the Color column. Ninety percent of the rows in the table have Color="Red", and the remaining 10 percent have Color="Green".
You want to normalize the Color information in this table. You create a table named Colors that is defined by the following DDL:

You populate the new Colors table by using the following Transact-SQL statement:
INSERT Colors (ColorName) SELECT DISTINCT Color FROM Status
You need to ensure that the following requirements are met:
* The Status table uses only colors that exist in the Colors table.
* Data redundancy in the Status table is reduced.
* Data integrity is enforced during the normalization process.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segment from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Discussion 0

Correct Answer:

Explanation:
First update the new column ColorID, and drop the old Column Color.
Add a check constraint on the new ColorID column, and finally add a foreign key constraint.

Question #40

Question #41

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.
The tables have the following definitions:

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?

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

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

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

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

Discussion 0

Correct Answer: A Vote an answer

Question #42

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?

A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p

B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p

C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders

D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for EduDump members. You can sign-up / login (it's free).

Question #43

You administer a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Suppliers and an indexed view named VWLocalSuppliers, both of which were created by using the following Transact-SQL statement:

You need to change the data type of the Code column in the Suppliers table to nvarchar(50).
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Discussion 0

Correct Answer:

Question #44

You develop a Microsoft SQL Server database that supports an e-commerce website. You create a table defined by the following Transact-SQL statement:

You need to create a reusable function that will remove all carriage return characters from all the text values.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Discussion 0

Correct Answer:

Explanation:
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017

Microsoft 70-461 Actual Free Exam Questions & Community Discussion

Download Free Microsoft 70-461 Demo