Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 251:

  Which conditional expression is supported in XQuery?

  A. IIF
  B. if..then..else
  C. CASE
  D. switch
  B. if..then..else

• Question 252:

  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 following requirements are met:

  -Students must be ranked based on their average marks.

  -

  If one or more students have the same average, the same rank must be given to these students.

  -

  Consecutive ranks must be skipped when the same rank is assigned. 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
  B. Option B

  ---

  The DENSE_RANK function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row. References: https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-2017

• Question 253:

  You are a database developer of a Microsoft SQL Server database.

  The database contains a table named Instructor that has the following definition:

  

  You are designing a new table named Course that has the following definition:

  

  You need to ensure that the InstructorId column in the Course table contains only values that exist in the InstructorID column of the Instructor table. Which Transact-SQL statement should you use?

  

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

  ---

  References: http://msdn.microsoft.com/en-us/library/ms189049.aspx

• Question 254:

  How do you delete rows from a table for which a ROW_NUMBER computation is equal to 1?

  A. You refer to the ROW_NUMBER function in the DELETE statement's WHERE clause.
  B. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered DELETE statement against the table expression.
  C. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered TRUNCATE statement against the table expression.
  D. The task cannot be achieved.
  B. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered DELETE statement against the table expression.

• Question 255:

  You create the following stored procedure. (Line numbers are included for reference only.)

  

  You need to ensure that the stored procedure performs the following tasks:

  1. If a record exists, update the record.

  2. If no record exists, insert a new record.

  Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 256:

  Why is it important to use the appropriate type for attributes?

  A. Because the type of your attribute enables you to control the formatting of the values
  B. Because the type constrains the values to a certain domain of supported values
  C. Because the type prevents duplicates
  D. Because the type prevents NULLs
  B. Because the type constrains the values to a certain domain of supported values

• Question 257:

  You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:

  -Status information must be logged to a status table.

  -

  If the status table does not exist at the beginning of the batch, it must be created. Which object should you use?

  A. Scalar user-defined function
  B. Inline user-defined function
  C. Table-valued user-defined function
  D. Stored procedure
  D. Stored procedure

  ---

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

• Question 258:

  You use Microsoft SQL Server to develop a database application.

  Your application sends data to a VARCHAR(50) variable named @var.

  You need to write a Transact-SQL statement that will return information on a successful or unsuccessful cast to an integer in a table.

  Which Transact-SQL statement should you run?

  

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

  ---

  TRY_PARSE returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.

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

• Question 259:

  You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1MB in size. The media files will require fast access and will be retrieved frequently. What should you do?

  A. Use the CAST function.
  B. Use the DATE data type.
  C. Use the FORMAT function.
  D. Use an appropriate collation.
  E. Use a user-defined table type.
  F. Use the VARBINARY data type.
  G. Use the DATETIME data type.
  H. Use the DATETIME2 data type.
  I. Use the DATETIMEOFFSET data type.
  J. Use the TODATETIMEOFFSET function.
  F. Use the VARBINARY data type.

  ---

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

• Question 260:

  How do you read a graphical execution plan?

  A. From top to bottom, from left to right
  B. From top to bottom, from right to left
  C. From left to right, from top to bottom
  D. From right to left, from top to bottom
  D. From right to left, from top to bottom