70-461 Exam Dumps | Microsoft Certified Professional 70-461 Exam Questions PDF [2018]

1. Microsoft 70-461 Exam Querying Microsoft SQL Server 2012/2014 Questions & Answers (Demo Version) https://examslead.com/70-461-practice-exam-dumps/ Buy Full Product Here:

2. Version: 18.0 Question 1 You develop a Microsof SQL Server 2012 server database that supports an applicatonn The applicaton contains a table that has the following defnitono CREATE TABLE Inventory (ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL) You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each rown Which Transact-SQL statement should you use? An ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse Bn ALTER TABLE Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItemss Cn ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse Dn ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse); Aoswern A Explanatono Referenceo htpo::technetnmicrosofncom:en-us:library:ms190273naspx Question 2 You develop a Microsof SQL Server 2012 databasen You create a view from the Orders and OrderDetails tables by using the following defnitonn You need to improve the performance of the view by persistng data to diskn What should you do? An Create an INSTEAD OF trigger on the viewn

3. Bn Create an AFTER trigger on the viewn Cn Modify the view to use the WITH VIEW_METADATA clausen Dn Create a clustered index on the viewn Aoswern D Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:ms188783naspx Question 3 You develop a database for a travel applicatonn You need to design tables and other database objectsn You create the Airline_Schedules tablen You need to store the departure and arrival dates and tmes of fights along with tme zone informatonn What should you do? An Use the CAST functonn Bn Use the DATE data typen Cn Use the FORMAT functonn Dn Use an appropriate collatonn En Use a user-defned table typen Fn Use the VARBINARY data typen Gn Use the DATETIME data typen Hn Use the DATETIME2 data typen In Use the DATETIMEOFFSET data typen Jn Use the TODATETIMEOFFSET functonn Aoswern I Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:f888733naspx Referenceo htpo::msdnnmicrosofncom:en-us:library:bb630289naspx Question 4 You develop a database for a travel applicatonn You need to design tables and other database objectsn You create a stored proceduren You need to supply the stored procedure with multple event names and their dates as parametersn What should you do? An Use the CAST functonn Bn Use the DATE data typen Cn Use the FORMAT functonn Dn Use an appropriate collatonn En Use a user-defned table typen Fn Use the VARBINARY data typen Gn Use the DATETIME data typen

4. Hn Use the DATETIME2 data typen In Use the DATETIMEOFFSET data typen Jn Use the TODATETIMEOFFSET functonn Aoswern E Question 5 CORRECT TEXT You have a view that was created by using the following codeo You need to create an inline table-valued functon named Salesnfn_OrdersByTerritory, which must meet the following requirementso Accept the @T integer parametern Use one-part names to reference columnsn Filter the query results by SalesTerritoryIDn Return the columns in the same order as the order used in OrdersByTerritoryViewn Which code segment should you use? To answer, type the correct code in the answer arean Aoswern CORRECT TEXTPlease review the explanaton part for this answer CREATE FUNCTION Salesnfn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue FROM SalesnOrdersByTerritory WHERE SalesTerritoryID = @T ) Question 6 CORRECT TEXT You have a database that contains the tables shown in the exhibitn (Click the Exhibit butonn)

5. You deploy a new server that has SQL Server 2012 installedn You need to create a table named SalesnOrderDetails on the new servern SalesnOrderDetails must meet the following requirementso Write the results to a diskn Contain a new column named LineItemTotal that stores the product of ListPrice and Quantty for each rown The code must NOT use any object delimitersn The soluton must ensure that LineItemTotal is stored as the last column in the tablen Which code segment should you use? To answer, type the correct code in the answer arean Aoswern CORRECT TEXTPlease review the explanaton part for this answer CREATE TABLE SalesnOrderDetails ( ListPrice money not null, Quantty int not null, LineItemTotal as (ListPrice * Quantty) PERSISTED)

6. Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:ms178979naspx Referenceo htpo::technetnmicrosofncom:en-us:library:ms188300naspx Question 7 CORRECT TEXT You have a database that contains the tables shown in the exhibitn (Click the Exhibit butonn) You need to create a view named uv_CustomerFullName to meet the following requirementso The code must NOT include object delimitersn The view must be created in the Sales scheman Columns must only be referenced by using one-part namesn The view must return the frst name and the last name of all customersn The view must prevent the underlying structure of the customer table from being changedn The view must be able to resolve all referenced objects, regardless of the user's default scheman

7. Which code segment should you use? To answer, type the correct code in the answer arean Aoswern CORRECT TEXTPlease review the explanaton part for this answer CREATE VIEW Salesnuv_CustomerFullName WITH SCHEMABINDING AS SELECT FirstName, LastName FROM SalesnCustomers Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:ms187956naspx Question 8 CORRECT TEXT You have a database that contains the tables shown in the exhibitn (Click the Exhibit butonn) You need to create a query that calculates the total sales of each OrderId from the SalesnDetails tablen The soluton must meet the following requirementso Use one-part names to reference columnsn Sort the order of the results from OrderIdn

8. NOT depend on the default schema of a usern Use an alias of TotalSales for the calculated ExtendedAmountn Display only the OrderId column and the calculated TotalSales columnn Which code segment should you use? To answer, type the correct code in the answer arean Aoswern CORRECT TEXTPlease review the explanaton part for this answer SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM SalesnDetails GROUP BY OrderID ORDER BY OrderID Question 9 You have a Microsof SQL Server 2012 database that contains tables named Customers and Ordersn The tables are related by a column named CustomerIDn You need to create a query that meets the following requirementso Returns the CustomerName for all customers and the OrderDate for any orders that they have placedn Results must include customers who have not placed any ordersn Which Transact-SQL query should you use? An SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON CustomersnCustomerID = OrdersnCustomerID Bn SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON CustomersnCustomerID = OrdersnCustomerID Cn SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON CustomersnCustomerID = OrdersnCustomerID Dn SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON CustomersnCustomerID = OrdersnCustomerID Aoswern D Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:ms177638naspx Question 10 You create a stored procedure that will update multple tables within a transactonn

9. You need to ensure that if the stored procedure raises a run-tme error, the entre transacton is terminated and rolled backn Which Transact-SQL statement should you include at the beginning of the stored procedure? An SET XACT_ABORT ON Bn SET ARITHABORT ON Cn TRY Dn BEGIN En SET ARITHABORT OFF Fn SET XACT_ABORT OFF Aoswern A Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:ms190306naspx Referenceo htpo::msdnnmicrosofncom:en-us:library:ms188792naspx Question 11 Your database contains two tables named DomestcSalesOrders and InternatonalSalesOrdersn Both tables contain more than 100 million rowsn Each table has a Primary Key column named SalesOrderIdn The data in the two tables is distnct from one anothern Business users want a report that includes aggregate informaton about the total number of global sales and total sales amountsn You need to ensure that your query executes in the minimum possible tmen Which query should you use? An SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomestcSalesOrders UNION ALL SELECT SalesOrderId, SalesAmount FROM InternatonalSalesOrders ) AS p Bn SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomestcSalesOrders UNION SELECT SalesOrderId, SalesAmount FROM InternatonalSalesOrders ) AS p Cn SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomestcSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

10. FROM InternatonalSalesOrders Dn SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomestcSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternatonalSalesOrders Aoswern A Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:ms180026naspx Referenceo htpo::blognsqlauthorityncom:2009:03:11:sql-server-diference-between-union-vs-union-all- optmalperformance-comparison: Question 12 You are a database developer at an independent sofware vendorn You create stored procedures that contain proprietary coden You need to protect the code from being viewed by your customersn Which stored procedure opton should you use? An ENCRYPTBYKEY Bn ENCRYPTION Cn ENCRYPTBYPASSPHRASE Dn ENCRYPTBYCERT Aoswern B Explanatono Referenceo htpo::technetnmicrosofncom:en-us:library:bb510663naspx Referenceo htpo::technetnmicrosofncom:en-us:library:ms178361naspx Referenceo htpo::msdnnmicrosofncom:en-us:library:ms187926naspx Referenceo htpo::technetnmicrosofncom:en-us:library:ms190357naspx Referenceo htpo::technetnmicrosofncom:en-us:library:ms188061naspx Question 13 You use a Microsof SQL Server 2012 databasen You want to create a table to store Microsof Word documentsn You need to ensure that the documents must only be accessible via Transact-SQL queriesn Which Transact-SQL statement should you use? An CREATE TABLE DocumentStore ( [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL

11. ) GO Bn CREATE TABLE DocumentStore ( [Id] hierarchyid, [Document] NVARCHAR NOT NULL ) GO Cn CREATE TABLE DocumentStore AS FileTable Dn CREATE TABLE DocumentStore ( [Id] [uniqueidentfer] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL ) GO Aoswern A Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:gg871897naspx Referenceo htpo::msdnnmicrosofncom:en-us:library:f929188naspx Question 14 You administer a Microsof SQL Server 2012 database that contains a table named OrderDetailn You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmentedn You need to reduce fragmentatonn You need to achieve this goal without taking the index ofinen Which Transact-SQL batch should you use? An CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetailnCustomerID WITH DROP EXISTING Bn ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetailnCustomerID REORGANIZE Cn ALTER INDEX ALL ON OrderDetail REBUILD Dn ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetailnCustomerID REBUILD Aoswern B Explanatono Referenceo htpo::msdnnmicrosofncom:en-us:library:ms188388naspx Question 15 DRAG DROP You have a SQL Server database named CUSTOMERSn You need to sign a stored procedure named SelectCustomers in the CUSTOMERS databasen Which four statements should you execute in sequence? To answer, move the appropriate statements

12. from the list of statements to the answer area and arrange them in the correct ordern Aoswern There are four steps involved in signing a moduleo Referencesohtpso::msdnnmicrosofncom:en-us:library:bb669102(v=vsn110)naspx

13. Buy Full Product Here: