Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 161:

  You are developing an SQL Server database. The database contains two tables and a function that are defined by the following Transact-SQL statements.

  

  You need to create a query to determine the total number of products that are sold each day for the live top-selling products on that particular day.

  How should you complete the relevant Transact-SQL script? To answer, select the appropriate Transact-SQL statements from each list in the answer area.

  Hot Area:

  

  

  ---

  The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

  There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set,

  and rows that do not, with NULL values in the columns produced by the table-valued function.

• Question 162:

  What is the data type of the target values column in the result of an UNPIVOT operator?

  A. INT
  B. NVARCHAR(128)
  C. SQL_VARIANT
  D. The data type of the source columns that you unpivot
  D. The data type of the source columns that you unpivot

• Question 163:

  A stored procedure calls another stored procedure. The calling stored procedure has created temporary tables, declared variables, and passes parameters to the called stored procedure. What data can the called stored procedure see from the caller?

  A. The called procedure can see the variables, temporary tables, and passed parameters of the caller.
  B. The called procedure can see the temporary tables but not the variables and passed parameters of the caller.
  C. The called procedure can see the passed parameters and temporary tables but not the variables of the caller.
  D. The called procedure cannot see any objects created by the calling procedure.
  C. The called procedure can see the passed parameters and temporary tables but not the variables of the caller.

  ---

  Both passed parameters and temporary tables are visible to called stored procedures.

• Question 164:

  What kind of dependencies do synonyms have on the objects they refer to?

  A. Synonyms can be created WITH SCHEMABINDING to prevent the underlying objects from being altered.
  B. Synonyms can refer to other synonyms.
  C. Synonyms can be created to refer to database objects that do not yet exist.
  D. Synonyms can be created without an initial schema name, which can be added later.
  C. Synonyms can be created to refer to database objects that do not yet exist.

  ---

  Synonyms can be created to refer to database objects that do not yet exist. but they can't be referenced unless existing

• Question 165:

  You are developing a SQL Server database for an order management system. The database contains a table that is defined by the following Transact-SQL statement:

  

  Transactions must commit if there are no errors. Transactions must roll back if constraint violations occur.

  You need to create the Transact-SQL script to insert new orders.

  How should you complete the relevant Transact-SQL script? To answer, select the appropriate Transact-SQL statements from each list in the answer area.

  Hot Area:

  

  

  ---

  Box 1: SET XACT_ABORT ON;

  XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

  When SET XACT_ABORT is ON, if a Transact-SQL statement raises arun-time error, the entire transaction is terminated and rolled back.

  Box 2: IF (XACT_STATE()) =-1

  If XACT_STATE has the value of -1, then the current request has an active user transaction, but an error has occurred that has caused the transaction to beclassified as an uncommittable transaction. The request cannot commit the

  transaction or roll back to a savepoint; it can only request a full rollback of the transaction.

  Box 3: IF (XACT_STATE()) =1

  If XACT_STATE has the value of 1, then the current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

  References:

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

  https://msdn.microsoft.com/en-us/library/ms189797.aspx

• Question 166:

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

  Select and Place:

  

  

• Question 167:

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

  

  You create a view on the SalesDB database using the following Transact-SQL code: CREATE VIEW SalesV WITH SCHEMABINDINGS AS SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice * OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price FROM OrderDetail INNER JOIN Products ON OrderDetail.ProductID = Products.ProducID INNER JOIN Invoices ON OrderDetail.InvoiceID = Invoices.InvoiceID GROUP BY Products.ProductID, Invoices.InvoiceDate GO You want the SalesV view to persist data to disk in order to improve performance. How would you accomplish this task?

  A. You should add a clustered index to the SalesV view.
  B. You should create a columnstore index on all columns used in the SalesV view.
  C. You should drop and recreate the SalesV view as a system view.
  D. You should drop and recreate the SalesV view as a partitioned view.
  A. You should add a clustered index to the SalesV view.

  ---

  Ref: http://msdn.microsoft.com/en-us/library/ms190174

• Question 168:

  You're designing a new SQL Server 2012 query for the HR department. The query will find records from the persons table for people whose name starts with 'Ja'. Which WHERE statement would be the correct choice?

  A. where (name like 'Ja%')
  B. where (name = 'Ja%')
  C. where (name > 'Ja')
  D. where (name like '%Ja%')
  A. where (name like 'Ja%')

• Question 169:

  DRAG DROP

  Your database contains tables that were created by running the following Transact-SQL statements:

  

  Data from the Ticket and Action production tables needs to be archived to the TicketArchive and ActionArchive tables, respectively. When a ticket is archived, data is copied from the production tables and then deleted from the production tables.

  You need to create statements to archive all the tickets that:

  1. were opened one year ago or earlier,

  2. were closed six months ago or earlier, and

  3. have a TicketStatusID of 10.

  Which Transact-SQL segment or segments should you use to complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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.

  Select and Place:

  

  

  ---

  Box 1: DECLARE.. Declare a table variable

  Box 2: OUTPUT..

  Box 3: DELETE FROM dbo.Ticket Remove from the Ticket table.

  Box 4: DELETE FROM dbo.Action Remove from the Action table.

  References: https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

• Question 170:

  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 ensure that the top half of the students

  arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2.

  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 FRCM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM 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
  D. SELECT StudentCode as Code, NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode