Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 271:

  You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named ProductsDB. The ProductsDB database is shown in the following database diagram:

  

  You need to write a Transact-SQL query that displays all the products received by a single supplier in the following XML format:

  Which of the following SELECT statement would you write?

  A. SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID,
  B. UnitPrice, p.UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID WHERE s.SupplierID = 22 FOR XML RAW
  C. SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID,
  D. UnitPrice, p.UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID WHERE s.SupplierID = 22 FOR XML
  E. SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company], Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.SupplierID = 22 FOR XML AUTO
  F. SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company], Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.SupplierID = 22 FOR XML AUTO, RAW
  E. SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company], Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.SupplierID = 22 FOR XML AUTO

• Question 272:

  You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

  A. SET XACT_ABORT ON
  B. SET ARITHABORT ON
  C. TRY
  D. BEGIN
  E. SET ARITHABORT OFF
  F. SET XACT_ABORT OFF
  A. SET XACT_ABORT ON

  ---

  http://msdn.microsoft.com/en-us/library/ms190306.aspx http://msdn.microsoft.com/en-us/library/ms188792.aspx

• Question 273:

  You develop a Microsoft SQL Server database. The database contains a table defined by the following Transact-SQL statement:

  

  All regions have more than one employee.

  You need to write a query to find the employee with the highest SalesYTD in each region with the following result set:

  First Name Last Name Region Sales YTD

  Which Transact-SQL query should you run?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A

  ---

  Use TOP 1 and RANK() OVER (PARTITION BY).

  References: https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-2017

• Question 274:

  You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB. The SalesDB is shown in the following database diagram:

  

  You need to write a Transact-SQL query that display a single row in the following XML format:

  Which of the following SELECT statement would you write?

  A. SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID WHERE cu.CustomerID = 1001 FOR XML RAW
  B. SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS [Name], CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = FOR XML
  C. SELECT Invoices.InvoiceID, Invoices.InvoiceDate AS [Date], Invoices.InvoiceValue AS [Value], Customers.CustomerName AS [Name], Customers.CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1001 FOR XML AUTO
  D. SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS [Name], CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = FOR XML AUTO, RAW
  C. SELECT Invoices.InvoiceID, Invoices.InvoiceDate AS [Date], Invoices.InvoiceValue AS [Value], Customers.CustomerName AS [Name], Customers.CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1001 FOR XML AUTO

• Question 275:

  HOTSPOT

  You have a database that contains the following related tables:

  

  You create a view named OrderSummary by using the following Transact-SQL statement:

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  Hot Area:

  

  

  ---

  The SalesOrderID column is used in a join statement and cannot be updated.

  The Status column is used can be updated.

  The SubTotal column is an aggregate column and cannot be updated.

• Question 276:

  You have an XML schema collection named Sales.InvoiceSchema.

  You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.

  Which code segment should you use?

  To answer, type the correct code in the answer area.

  DECLARE @XML1 XML(Sales.InvoiceSchema)

• Question 277:

  DRAG DROP

  You are a Microsoft SQL Server client tools to develop a Microsoft Azure SQL Database database that supports an e-learning application. The database consists of a Course table, a Subject table, and a CourseSubject table as shown in the exhibit. (Click the Exhibit button.)

  

  You need to write a trigger that meets the following requirements:

  Subjects are not physically deleted, but are marked as deleted.

  When a subject is deleted, the courses that offer that subject are marked as discontinued.

  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.

  Select and Place:

  

  

• Question 278:

  ABC.com has a SQL Server 2012 database instance that hosts a database named ComDB. The ComDB database has a table named Partners that was created using the following Transact-SQL code:

  CREATE TABLE [dbo].[Partners]

  (

  [CompanyID] [int] NOT NULL,

  [CompanyName] [nvarchar] (50) NOT NULL,

  [Location] [nvarchar] (50) NOT NULL,

  [ContactName] [nvarchar] (50) NOT NULL,

  [Email] [nvarchar] (50) NOT NULL,

  [Phone] [nvarchar] (10) NOT NULL,

  CONSTRAINT [PK_Partners] PRIMARY KEY CLUSTERED

  (

  [CompanyID] ASC

  )

  ON PRIMARY )

  You want to create a FOR UPDATE trigger that will track changes to the ContactName and Phone columns.

  Which of the following statements should you use in the trigger definition?

  A. IF COLUMNS_UPDATED (ContactName, Phone)
  B. IF COLUMNS_UPDATED (ContactName) OR COLUMNS_UPDATED (Phone)
  C. IF UPDATED (ContactName, Phone).
  D. IF UPDATED (ContactName) OR UPDATED (Phone)
  D. IF UPDATED (ContactName) OR UPDATED (Phone)

• Question 279:

  You create a table that has three columns named StudentCode, SubjectCode, and Marks. The Marks column records grades for students expressed as a percentage. The table has marks obtained by 50 students for various subjects.

  You need to retrieve the StudentCode and Marks for students who scored the highest percentage for each subject.

  

  Which Transact-SQL query should you use?

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  G. Option G
  H. Option H
  F. Option F

  ---

  We use PARTION BY SubjectCode to get the Subjects, DESC sorting and RANK =1 to get highest scores.

• Question 280:

  Which of the following conversions is NOT allowed either implicitly or explicitly using CONVERT OR CAST?

  A. varchar to decimal
  B. decimal to bit
  C. date to varchar
  D. datetime to text
  D. datetime to text

  ---

  There are very few conversions which are allowed to the Text data type. They include varchar, char, nchar, nvarchar and ntext. Converting datetime to text is not an allowed conversion using either CAST or CONVERT.