Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 131:

  HOTSPOT

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

  You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

  

  The customer table includes a column that stores the data for the last order that the customer placed.

  You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.

  You need to create triggers that meet the following requirements:

  Optimize the performance and data integrity of the tables.

  Provide a custom error if a user attempts to create an order for a customer that does not exist.

  In the Customers table, update the value for the last order placed.

  Complete all actions as part of the original transaction.

  In the table below, identify the trigger types that meet the requirements.

  NOTE: Make only selection in each column. Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT statement.

  AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully.

  References:https://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

• Question 132:

  You are evaluating the performance of a database environment.

  You must avoid unnecessary locks and ensure that lost updates do not occur.

  You need to choose the transaction isolation level for each data scenario.

  Which isolation level should you use for each scenario? To answer, drag the appropriate isolation levels to the correct scenarios. Each isolation 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: Readcommitted

  Read Committed: A transaction T1 executing under this isolation level can only access committed data.

  Pros: Good compromise between concurrency and consistency.

  Cons: Locking and blocking. The data can change when accessed multiple times within the same transaction.

  Box 2: Read Uncommitted

  Read Uncommitted (aka dirty read): A transaction T1 executing under this isolation level can access data changed by concurrent transaction(s).

  Pros: No read locks needed to readdata (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified.

  Cons: Data is not guaranteed to be transactionally consistent.

  Box 3: Serializable

  Serializable: A transaction T1 executing under this isolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if

  range lock can't be acquired (i.e. no index on the predicate column) for the duration of the transaction.

  Pros: Full data consistency including phantom protection.

  Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.

  References:

  https://blogs.msdn.microsoft.com/sqlcat/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels/

• Question 133:

  Background

  You have a database named HR1 that includes a table named Employee.

  You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run.

  You must monitor the database to identify issues that prevent the reports from running.

  You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.

  Employee Table

  You use the following Transact-SQL statements to create, configure, and populate the Employee table:

  

  Application

  You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:

  UspA: This stored procedure updates only the EmployeeStatus column.

  UspB: This stored procedure updates only the EmployeePayRate column.

  The application uses views to control access to data. Views must meet the following requirements:

  Allow user access to all columns in the tables that the view accesses.

  Restrict updates to only the rows that the view returns.

  Exhibit

  

  You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.

  You need to generate the reports more quickly. The database must not use additional resources.

  What should you do?

  A. Update the transaction level of the report query session to READPAST.
  B. Modify the report queries to use the UNION statement to combine the results of two or more queries.
  C. Set the READ_COMMITTED_SNAPSHOT database option to ON.
  D. Update the transaction level of the report query session to READ UNCOMMITTED.
  D. Update the transaction level of the report query session to READ UNCOMMITTED.

  ---

  Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. This is the least restrictive of the isolation levels.

  References: https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx

• Question 134:

  You are creating a stored procedure which will insert data into the table shown in the Database schema exhibit. (Click the exhibit button.)

  

  You need to insert a new customer record into the tables as a single unit of work.

  Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange the, in the correct order.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  

  ---

  The entities on the many side, of the 1-many relations, must be added beforewe add the entities on the 1-side.

  We must insert new rows into BusinessEntityContact and BusinessEntityAddress tables, before we insert the corresponding rows into the BusinessEntity and AddressType tables.

• Question 135:

  You run the following Transact-SQL statements:

  

  Records must only be added to the Orders table by using the view. If a customer name does not exist, then a new customer name must be created. You need to ensure that you can insert rows into the Orders table by using the view.

  A. Add the CustomerID column from the Orders table and the WITH CHECK OPTION statement to the view.
  B. Create an INSTEAD OF trigger on the view.
  C. Add the WITH SCHEMABINDING statement to the view statement and create a clustered index on the view.
  D. Remove the subquery from the view, add the WITH SCHEMABINDING statement, and add a trigger to the Orders table to perform the required logic.
  A. Add the CustomerID column from the Orders table and the WITH CHECK OPTION statement to the view.

  ---

  The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view.

  References: http://www.informit.com/articles/article.aspx?p=130855andseqNum=4

• Question 136:

  HOTSPOT

  You have a database named Database1.

  Users report that they experience deadlock issues- You run the sp_readerlog stored procedure. You view the output from the Process List section as shown in the Process List exhibit. (Click the Exhibit button.)

  You view the contents of the Resource List section as shown in the Resource List exhibit, (Click the Exhibit button.)

  You view deadlock information as shown in the Deadlock List exhibit. (Click the Exhibit button.)

  Hot Area:

  

  

• Question 137:

  HOTSPOT

  This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.

  To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.

  At the end of this case study, a review scree will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.

  To start the case study

  To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements. Existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.

  Background

  You have a database named Sales.

  The Customer table includes a column that stores the date for the last order that the customer placed.

  You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.

  Tables

  

  You need to create triggers on the Orders table that meet the following requirements:

  Optimize the performance and data integrity of the tables.

  Display a custom error if a user attempts to create an order for a customer that does not exist.

  In the Customers table, update the value for the LastOrderPlaced field.

  Complete all actions as part of the original transaction.

  In the table below, identify the trigger types that meet the requirements.

  NOTE: Make only selection in each column. Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Display Custom Error: INSTEAD OF INSERT trigger

  Before we insert, we check if the customer already exist or not.

  Update Customer table: AFTER UPDATE trigger

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql

• Question 138:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

  You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

  

  The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

  

  You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers. Website.Customer must meet the following requirements:

  1.

  Allow users access to the CustomerName and CustomerNumber columns for active customers.

  2.

  Allow changes to the columns that the view references. Modified data must be visible through the view.

  3.

  Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:

  1.

  Allow users access to the CustomerName, Address, City, State and PostalCode columns.

  2.

  Prevent changes to the columns that the view references.

  3.

  Only allow updates through the views that adhere to the view filter.

  You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

  

  You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

  You must update the design of the Customer table to meet the following requirements.

  1.

  You must be able to store up to 50 accounts for each customer.

  2.

  Users must be able to retrieve customer information by supplying an account number.

  3.

  Users must be able to retrieve an account number by supplying customer information.

  You need to implement the design changes while minimizing data redundancy.

  What should you do?

  A. Split the table into three separate tables. Include the AccountNumber and CustomerID columns in the first table. Include the CustomerName and Gender columns in the second table. Include the AccountStatus column in the third table.
  B. Split the table into two separate tables. Include AccountNumber, CustomerID, CustomerName and Gender columns in the first table. Include the AccountNumber and AccountStatus columns in the second table.
  C. Split the table into two separate tables, Include the CustomerID and AccountNumber columns in the first table. Include the AccountNumber, AccountStatus, CustomerName and Gender columns in the second table.
  D. Split the table into two separate tables, Include the CustomerID, CustomerName and Gender columns in the first table. Include AccountNumber, AccountStatus and CustomerID columns in the second table.
  D. Split the table into two separate tables, Include the CustomerID, CustomerName and Gender columns in the first table. Include AccountNumber, AccountStatus and CustomerID columns in the second table.

  ---

  Two tables is enough.CustomerID must be in both tables.

• Question 139:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is

  exactly the same in each question in this series.

  You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You

  create the Customer table by running the following Transact-SQL statement:

  

  The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

  

  You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers. Website.Customer must meet the following requirements:

  1. Allow users access to the CustomerName and CustomerNumber columns for active customers.

  2. Allow changes to the columns that the view references. Modified data must be visible through the view.

  3. Prevent the view from being published as part of Microsoft SQL Server replication.

  Sales.Female.Customers must meet the following requirements:

  1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.

  2. Prevent changes to the columns that the view references.

  3. Only allow updates through the views that adhere to the view filter.

  You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

  

  You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

  You need to create Website Customer.

  How should you complete the view definition? 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.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  Box 1: WITH ENCRYPTION

  Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

  Box 2: WITH CHECK OPTION

  CHECK OPTION forces all data modification statements executed against theview to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

  Note: Website.Customer must meet the followingrequirements:

  1. Allow users access to the CustomerName and CustomerNumber columns for active customers.

  2. Allow changes to the columns that the view references. Modified data must be visible through the view.

  3. Prevent the view from being published aspart of Microsoft SQL Server replication.

  Incorrect Asnwers:

  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.

  VIEW_METADATA specifies that theinstance of SQL Server will return to the DB-Library,ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view.

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

• Question 140:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

  You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

  

  The customer table includes a column that stores the data for the last order that the customer placed.

  You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.

  You need to modify the database design to meet the following requirements:

  In the table below, identify the constraint that must be configured for each table.

  NOTE: Make only one selection in each column.

  Hot Area:

  

  

  ---

  A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Here the foreign key constraint is put on the ProductIDin the Orders, and points to the ProductID of the Products table.

  With a check constraint on the ProductID we can ensure that the Products table contains only unique rows.

  References:http://www.w3schools.com/sql/sql_foreignkey.asp