[Apr 2021] sql exam 70-461

Real of 70-461 exam question materials and software for Microsoft certification for consumer, Real Success Guaranteed with Updated 70-461 pdf dumps vce Materials. 100% PASS Querying Microsoft SQL Server 2012 exam Today!

2021 Apr 70-461 Study Guide Questions:

Q16. You use Microsoft SQL Server 2012 database to develop a shopping cart application. 

You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output. 

Which Transact-SQL operator should you use? 

A. CROSS JOIN 

B. CROSS APPLY 

C. PIVOT 

D. UNPIVOT 

Answer: C 

Explanation: 

http://technet.microsoft.com/en-us/library/ms177634.aspx 


Q17. You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers. 

The view has the following definition: 



You need to ensure that users can update only the phone numbers and email addresses by using this view. 

What should you do? 

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement. 

B. Create an INSTEAD OF UPDATE trigger on the view. 

C. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view. 

D. Create an AFTER UPDATE trigger on the view. 

Answer: B 


Q18. You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns: 


Id is the Primary Key. 

You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. 

Which Transact-SQL statement should you use? 

A. UPDATE TOP(10) BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', NULL, 0) 

B. UPDATE BlogEntry 

SET Summary = CAST(N' This is in a draft stage' as nvarchar(max)) 

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) 

C. UPDATE BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM ( 

SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID 

D. UPDATE BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', 0, 0) 

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) 

Answer: C 


Q19. You develop a Microsoft SQL Server 2012 server database that supports an application. 

The application contains a table that has the following definition: 

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

The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use? 

A. ALTER TABLE Inventory 

ADD TotalItems AS ItemslnStore + ItemsInWarehouse 

B. ALTER TABLE Inventory 

ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED 

C. ALTER TABLE Inventory 

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED 

D. ALTER TABLE Inventory 

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) 

Answer: B 


Q20. You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.) 


The procedure can be called within other transactions. 

You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. 

Which code segment should you add to line 14? 

A. IF @@TRANCOUNT = 0 

B. IF (XACT_STATE ( ) ) = 0 

C. IF (XACT_STATE ( ) ) = 1 

D. IF @@TRANCOUNT = l 

Answer: C 


70-461 torrent

Most recent sql server 70-461 sample questions:

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

Answer: C 


Q22. You administer a Microsoft SQL Server database that supports a banking transaction management application. 

You need to retrieve a list of account holders who live in cities that do not have a branch location. 

Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.) 

A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster) 

B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster) 

C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster) 

D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster) 

Answer: A,B 

Explanation: 

Verified the answers as correct. 

Reference: http://msdn.microsoft.com/en-us/library/ms188047.aspx 

Reference: http://msdn.microsoft.com/en-us/library/ms177682.aspx 

Reference: http://msdn.microsoft.com/en-us/library/ms173545.aspx 


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

Answer: F 


Q24. CORRECT TEXT 

You have a database named Sales that contains the tables as shown in the exhibit. (Click 

the Exhibit button.) 


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. 


Answer: 


Q25. CORRECT TEXT 

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


You have an application named Appl. You have a parameter named @Count that uses the 

int data type. App1 is configured to pass @Count to a stored procedure. 

You need to create a stored procedure named usp_Customers for App1 that returns only 

the number of rows specified by the @Count parameter. 

The solution must NOT use BEGIN and END statements. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the 

complete code. 


Answer: 


70-461 rapidshare

Refined 70-461 querying microsoft sql server 2012:

Q26. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD parameter. 

You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence. 

Which approach should you use? 

A. A SELECT statement that includes CASE 

B. Cursor 

C. BULK INSERT 

D. View 

E. A user-defined function 

Answer: E 

Explanation: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. 


Q27. DRAG DROP 

You use Microsoft SQL Server 2012 to develop a database application. 

You create a table by using the following definition: 

CREATE TABLE Prices ( 

PriceId int IDENTITY(1,1) PRIMARY KEY, 

ActualPrice NUMERIC(16,9), 

PredictedPrice NUMERIC(16,9) 

You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. 

Which three 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.) 


Answer: 



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

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

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

Answer: B 


Q29. 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 Customerld 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 OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS 

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

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

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

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

G. 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') 

H. 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') 

Answer: G 


Q30. Your database contains a table named Customer that has columns named CustomerID 

and Name. 

You want to write a query that retrieves data from the Customer table sorted by Name 

listing 20 rows at a time. 

You need to view rows 41 through 60. 

Which Transact-SQL query should you create? 


A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: B