Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 171:

  You develop a Microsoft SQL Server database that contains a table named Employee, defined as follows:

  

  You need to create a view that contains two computed columns representing the month and the year of the [HireDate] of each Employee. Which function should you use?

  A. DATENAME( )
  B. CONVERT( )
  C. TRYDATEDIFF( )
  D. MONTH( ) and YEAR( )
  D. MONTH( ) and YEAR( )

  ---

  The Month function returns an integer that represents the month of the specified date. The Year function an integer that represents the year of the specified date. References:

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

• Question 172:

  You work as a database administrator at ABC.com. You are developing a database that will be used by a web site analysis application name ABCWeb1.

  The ABCWeb1 application must display the date and time each visitor visits a page on a website as well as the date and time they leave that web page.

  This data needs to be displayed in different date and time formats.

  How would you accomplish this?

  A. You should make use of a scalar user-defined function.
  B. You should make use of the SET CONTEXT_INFO statement in the stored procedure.
  C. You should make use of the DATETIMEOFFSET data type.
  D. You should make use of the FORMAT function.
  E. You should make use of the SET FORCEPLAN ON statement in the stored procedure.
  F. You should make use of a cursor.
  D. You should make use of the FORMAT function.

• Question 173:

  You are tasked with implementing a trigger. As part of the trigger's code in specific conditions, you need to roll back the transaction.

  However, you need to copy the data from the inserted and deleted tables in the trigger into audit tables to keep track of what was supposed to be changed. How can you achieve this?

  A. Roll back the transaction, and then copy the data from the inserted and deleted tables into the audit tables.
  B. Copy the data from the inserted and deleted tables into the audit tables and then roll back the transaction.
  C. Copy the rows from the inserted and deleted tables into temporary tables, roll back the transaction, and then copy the data from the temporary tables into the audit tables.
  D. Copy the rows from the inserted and deleted tables into table variables, roll back the transaction, and then copy the data from the table variables into the audit tables.
  D. Copy the rows from the inserted and deleted tables into table variables, roll back the transaction, and then copy the data from the table variables into the audit tables.

• Question 174:

  You administer a Microsoft SQL Server database named Orders.

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

  

  You add a new data source to the application that inserts records into the Customer table. An example of a query that the application executes is shown below.

  

  Users report that these new records are not displayed correctly. You review data in the table and observe that records from the new data source are displayed as shown below.

  

  You need to change the database schema to support the characters coming in from this new data source. You need to achieve this goal without losing existing customer records. Which Transact-SQL statement should you run?

  

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

  ---

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/windows-collation-name-transact-sql?view=sql-server-ver15

• Question 175:

  What metadata tables give you a list of constraints in a database? (Choose all that apply.)

  A. sys.key_constraints
  B. sys.indexes
  C. sys.default_constraints
  D. sys.foreign_keys
  A. sys.key_constraints
  C. sys.default_constraints
  D. sys.foreign_keys

  ---

  A. Correct: sys.key_constraints lists all primary key and unique constraints in a database.

  B. Incorrect: sys.indexes does not list constraints.

  C. Correct: sys.default_constraints lists the default constraints in a database. D. Correct: sys.foreign_keys lists all the foreign keys in a database.

• Question 176:

  You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named SalesDB.

  You are developing a stored procedure that takes a parameter named @date that uses the varchar datatype. The @date parameter must be compared to the value in a datetime column named OrderDate.

  Which of the following WHERE clauses would be the most efficient WHERE clause to use?

  A. WHERE OrderDate = CAST(datetime,@date)
  B. WHERE OrderDate = CONVERT(datetime,@date)
  C. WHERE OrderDate =@date
  D. WHERE OrderDate = CAST(@date AS datetime)
  E. WHERE OrderDate = PARSE(@date AS Date)
  C. WHERE OrderDate =@date

• Question 177:

  You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB with a table named Invoices.

  Application developers are developing several in-house applications that will access the Invoices table. You need to develop a solution that will allow the applications to access the table indirectly while still allowing them to update the Invoice

  table.

  How would you accomplish this task?

  A. You should create a view on the Invoices table.
  B. You should create a columnstore index on all columns used by the applications.
  C. You should allow the applications access to the Invoices table via stored procedures.
  D. You should drop and recreate the Invoices table as a partitioned table.
  A. You should create a view on the Invoices table.

• Question 178:

  You use a Microsoft Azure SQL DataBase instance. The instance contains a table named Customers that has columns named Id, Name, and IsPriority.

  You need to create a view named VwPriorityCustomers that:

  returns rows from Customer that have a value of True in the IsPriority column, and does not allow columns to be altered or dropped in the underlying table.

  Which Transact-SQL statement shoul you run?

  A. CREATE VIEW VwPriorityCustomers AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1 WITH CHECK OPTION
  B. CREATE VIEW VwPriorityCustomers WITH VIEW_METADATA AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1
  C. CREATE VIEW VwPriorityCustomers WITH ENCRYPTION AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1
  D. CREATE VIEW VwPriorityCustomers WITH SCHEMABINDING AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1
  D. CREATE VIEW VwPriorityCustomers WITH SCHEMABINDING AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1

  ---

  SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017

• Question 179:

  What is the result of WITH SCHEMABINDING in a view?

  A. The view cannot be altered without altering the table.
  B. The tables referred to in the view cannot be altered unless the view is first altered.
  C. The tables referred to in the view cannot be altered unless the view is first dropped.
  D. The view cannot be altered unless the tables it refers to are first dropped.
  C. The tables referred to in the view cannot be altered unless the view is first dropped.

• Question 180:

  You want to enforce that a valid supplierid be entered for each productid in the Production.Products table. What is the appropriate constraint to use?

  A. A unique constraint
  B. A default constraint
  C. A foreign key constraint
  D. A primary key constraint
  C. A foreign key constraint