Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 331:

  A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.

  The Production.Product table contains the following two columns: ProductID Name

  The Sales.SalesOrderDetail table contains the following three columns: SalesOrderID ProductID OrderQty

  You need to create a query listing all of the products that were never ordered. Which statements should you execute?

  

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

  ---

  EXCEPT and INTERSECT returns distinct rows by comparing the results of two queries. EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

  Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.

  Example: The following query returns any distinct values from the query to the left of the EXCEPT operator that are not also found on the right query. -- Uses AdventureWorks

  SELECT CustomerKey FROM FactInternetSales EXCEPT SELECT CustomerKey FROM DimCustomer WHERE DimCustomer.Gender = 'F' ORDER BY CustomerKey; --Result: 9351 Rows (Sales to customers that are not female.)

  Incorrect Answers:

  B: Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query. C, D: INTERSECT returns distinct rows that are output by both the left and right input queries operator.

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

• Question 332:

  How can you create synonyms for the words searched?

  A. You can edit the thesaurus file.
  B. You can create a thesaurus table.
  C. You can use the stopwords for synonyms as well.
  D. Full-text search does not support synonyms.
  A. You can edit the thesaurus file.

  ---

  Full-text search uses thesaurus files and not tables for synonyms.

• Question 333:

  You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named HRDB with tables named Staff and SalaryHistory. The Staff and SalaryHistory tables were created using the following Transact-

  SQL code:

  CREATE TABLE HRDB.Staff

  (

  StaffID int NOT NULL PRIMARY KEY,

  FirstName nvarchar(50) NOT NULL,

  LastName nvarchar(50) NOT NULL,

  JobTitle nvarchar(50) NOT NULL,

  StaffAddress nvarchar (200) NULL, StaffCity nvarchar(50) NOT NULL, StaffState nvarchar(50) NOT NULL,

  StaffPhone varchar (10) NOT NULL,

  Salary smallmoney NOT NULL

  )

  GO

  CREATE TABLE HRDB.SalaryHistory

  (

  StaffID int NOT NULL,

  IncreaseDate date DEFAULT GETDATE(),

  PrevSalary smallmoney NOT NULL,

  NewSalary smallmoney NOT NULL

  )

  GO

  You must write a Transact-SQL query to affect a salary increase of 6.5% for all staff members with a JobTitle of Support Technician. Your query must also update the data in the SalaryHistory table.

  What Transact SQL statement would accomplish this task?

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

  A. Check the answer below
  A. Check the answer below

  ---

  UPDATE Staff SET Salary = Salary * 1.065 WHERE JobTitle = 'Support Technician' OUTPUT inserted.StaffID, deleted.Salary, inserted.Salary INTO SalaryHistory(StaffID, PrevSalary, NewSalary)

• Question 334:

  DRAG DROP

  Your Microsoft SQL Server database contains tables as shown below.

  You have tables that were created by running the following Transact-SQL statements:

  

  The Product table contains 10,000 records. The maximum ProductID is 11,000.

  There are 12 rows in the Category table. The maximum CategoryID is 12.

  The Product table contains at least one product in every category.

  Data in the tables was accidently modified. To correct this, you need to make some updates directly to the tables. You issue several statements.

  Which result or results will you obtain for each Transact-SQL statement? To answer, drag the appropriate results to the correct Transact-SQL statements. Each result 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:

  The SET IDENTITY_INSERT command allows explicit values to be inserted into the identity column of a table.

  Box 2:

  The Product table contains at least one product in every category.

  Box 3:

  Box 4:

  Bit is a data type that can take a value of 1, 0, or NULL.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017

  https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017

• Question 335:

  What is the result of the WITH CHECK OPTION in a view that has a WHERE clause in its SELECT statement?

  A. Data can no longer be updated through the view.
  B. Data can be updated through the view, but primary key values cannot be changed.
  C. Data can be updated through the view, but values cannot be changed that would cause rows to fall outside the filter of the WHERE clause.
  D. Data can be updated through the view, but only columns with check constraints can be changed.
  C. Data can be updated through the view, but values cannot be changed that would cause rows to fall outside the filter of the WHERE clause.

• Question 336:

  SIMULATION

  You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements:

  1. Contain a new column named LineltemTotal that stores the product of ListPrice and Quantity for each row.

  2. The calculation for a line item total must not be run every time the table is queried.

  3. The code must NOT use any object delimiters.

  The solution must ensure that LineItemTotal is stored as the last column in the table.

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

  

  Check the answer below

  LineItemTotal AS (ListPrice * Quantity) PERSISTED

  Add the following line: LineItemTotal AS (ListPrice * Quantity) PERSISTED

  To get the code:

  CREATE TABLE OrderDetails ( ListPrice money NOT NULL, Quantity int NOT NULL, LineItemTotal AS (ListPrice * Quantity) PERSISTED )

• Question 337:

  You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. What should you do?

  A. Add a HASH hint to the query.
  B. Add a LOOP hint to the query.
  C. Add a FORCESEEK hint to the query.
  D. Add an INCLUDE clause to the index.
  E. Add a FORCESCAN hint to the Attach query.
  F. Add a columnstore index to cover the query.
  G. Enable the optimize for ad hoc workloads option.
  H. Cover the unique clustered index with a columnstore index.
  I. Include a SET FORCEPLAN ON statement before you run the query.
  J. Include a SET STATISTICS PROFILE ON statement before you run the query.
  M

• Question 338:

  You have a view that was created by using the following code:

  

  You need to create an inline table-valued function named Sales.fn_OrdersByTerritory,

  which must meet the following requirements:

  Accept the @T integer parameter.

  Use one-part names to reference columns.

  Filter the query results by SalesTerritoryID.

  Return the columns in the same order as the order used in OrdersByTerritoryView.

  Which code segment should you use?

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

  Check the answer below

  CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T )

• Question 339:

  You administer a Microsoft SQL Server database named Orders. Orders is highly active OLTP system used for e-commerce.

  Performance on the database has degraded over the past few months as the volume of data has increased, and now users report the application is unusable.

  

  You need to identify the cause of the performance problem. Which Transact-SQL statement should you run?

  A. SET STATISTICS TIME ON
  B. SET FORCEPLAN ON
  C. SET STATISTICS IO ON
  D. DBCC CHECKCONTRAINTS
  B. SET FORCEPLAN ON

  ---

  When FORCEPLAN is set to ON, the SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.

  Incorrect Answers:

  A: When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When OFF, the time statistics are not displayed.

  C: Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

  D: DBCC CHECKCONSTRAINTS constructs and executes a query for all FOREIGN KEY constraints and CHECK constraints on a table. References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkconstraints-transact-sql?view=sql-server-2017

• Question 340:

  What is the relationship between SQL and T-SQL?

  A. T-SQL is the standard language and SQL is the dialect in Microsoft SQL Server.
  B. SQL is the standard language and T-SQL is the dialect in Microsoft SQL Server.
  C. Both SQL and T-SQL are standard languages.
  D. Both SQL and T-SQL are dialects in Microsoft SQL Server.
  B. SQL is the standard language and T-SQL is the dialect in Microsoft SQL Server.