Instructor Inputs - Session 12

Instructor Inputs Solutions to Exercises This session includes exercises of Chapter 6, Chapter 7, and additional exercises. Chapter 6 Exercise 4 A view has been defined as follows: CREATE VIEW vwSalesOrderDetail AS SELECT oh.SalesOrderID, TerritoryID, TotalDue, OrderQty, ProductID FROM Sales.SalesOrderHeader oh JOIN Sales.SalesOrderDetail od ON oh.SalesOrderID = od.SalesOrderID The following update command gives an error when you update the OrderQty and TerritoryID attributes: UPDATE vwSalesOrderDetail SET OrderQty = 2, TerritoryID = 4 FROM vwSalesOrderDetail WHERE SalesOrderID = 43659 Identify the problem and provide the solution. Solution Since view contains the data from two tables, it is not possible to modify the data of two tables through a view in a single Update statement. To solve the problem, you need to execute two Update statements as follows: UPDATE vwSalesOrderDetail SET OrderQty = 2 FROM vwSalesOrderDetail WHERE SalesOrderID = 43659 UPDATE vwSalesOrderDetail SET TerritoryID = 4 FROM vwSalesOrderDetail WHERE SalesOrderID = 43659 ¤NIIT Instructor Inputs 12.3 Exercise 5 The Store table contains the details of all the stores. The HR Manager of AdventureWorks, Inc. frequently queries the Store table based on the names of the stores. He wants to create the following reports: ④ A report containing the details of all the stores that contain the word `bike` in their names. ④ A report displaying the names of all the stores containing the phrase `Bike Store`. Write the query so that the result set is retrieved very promptly. Solution To make searching fast on the Store table, you need to create a full-text index on the table. To solve the problem, you need to execute the following statements: SP_FULLTEXT_DATABASE ENABLE CREATE FULLTEXT CATALOG CAT3 AS DEFAULT CREATE FULLTEXT INDEX ON Sales.Store(Name) KEY INDEX PK_Store_CustomerID ④ To retrieve the details of all the stores containing the word `bike` in their names, execute the following statement: Select * from Sales.Store Where FreeText (Name, `Bike`) ④ To display the details of all the stores containing the phrase `Bike Store` in their names, execute the following statement: Select * from Sales.Store Where Contains (Name, `"Bike Store"`) Exercise 6 Display the details of all the credit cards that are of type `SuperiorCard`. The CreditCard table contains a large amount of data. Therefore, the query takes a long time to retrieve the details of the credit card. You need to optimize the execution of the query so that the result set does not take time to be retrieved. 12.4 Instructor Inputs ¤NIIT Solution To retrieve the details of all the credit cards that are of type `SuperiorCard`, you need to create a full-text index on the CreditCard table. This will make the searching very fast. To solve the problem, you need to execute the following statements: SP_FULLTEXT_DATABASE ENABLE CREATE FULLTEXT CATALOG CAT2 AS DEFAULT CREATE FULLTEXT INDEX ON Sales.CreditCard(CardType) KEY INDEX AK_CreditCard_CardNumber Select * from Sales.CreditCard Where Contains (CardType, `"SuperiorCard"`) Exercise 7 Display the details of all the currencies that contain the words `New` and `Dollar` in their names. These words can be included in any order. In addition, you need to make sure that the query does not take time to execute. Solution You need to create a full-text index on the Currency table to display the currencies containing the words `New` and `Dollar` in their names. Full-text index will help in make searching very fast. To solve the problem, you need to execute the following statements: CREATE FULLTEXT INDEX ON Sales.Currency(Name) KEY INDEX AK_Currency_Name Select * from Sales.Currency Where FreeText (Name, `"New" And "Dollar"`) Chapter 7 Exercise 1 Create a batch that finds the average pay rate of the employees and then lists the details of employees who have a pay rate less than the average pay rate. ¤NIIT Instructor Inputs 12.5 ... - tailieumienphi.vn