SQL LAB @ HOME 9 ~ NIIT POST

sql lab @ home 9

1.   The Store table is often queried. The queries are based on the CustomerID attribute and take a
long time to execute. Optimize the execution of the queries. In addition, ensure that the
CustomerID attribute does not contain duplicate values. (Use the AdventureWorks database)


ANS:
CREATE UNIQUE NONCLUSTERED INDEX Idx_CustomerID
 ON Sales.Store(CustomerID)


2.   The SalesOrderDetail and SalesOrderHeader tables store the details of the sales orders. You
have created a backup of the SalesOrderDetail and SalesOrderHeader tables in the
SalesOrderDetail_Backup and SalesOrderHeader_Backup tables. To generate a report displaying
the sales order ID and the total amount of all the products purchased against an order, you are
using the following query:
SELECT sd.SalesOrderID, SUM(LineTotal) AS [Total Amount]
FROM Sales.SalesOrderDetail_Backup sd JOIN Sales.SalesOrderHeader_Backup sh
ON sd.SalesOrderID = sh.SalesOrderID
GROUP BY sd.SalesOrderID
The table contains a large amount of data. Suggest a solution to optimize the execution of this
query. (Use the AdventureWorks database)


ANS:

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)




3.   The SalesOrderDetail table is often queried. The queries are based on the SalesOrderDetailID
and SalesOrderID attributes. The execution of the queries takes a long time. Optimize the
execution of the queries. In addition, check the performance of the query before and after
optimizing the execution of the queries. (Use the AdventureWorks database)


ANS:
CREATE NONCLUSTERED INDEX Idx_SalesOrderId_SalesOrderDetailId
ON Sales.SalesOrderDetail(SalesOrderId, SalesOrderDetailId)




4.  A view has been defined as shown in the following statement:
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 statement 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. (Use the AdventureWorks database)


ANS:
UPDATE vwSalesOrderDetail
SET OrderQty = 2
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659


UPDATE vwSalesOrderDetail
SET TerritoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659


5.  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 too much time to be retrieved. (Use the AdventureWorks database)


ANS:
CREATE FULLTEXT CATALOG CAT4 AS DEFAULT

CREATE FULLTEXT INDEX ON Sales.CreditCard(CardType)
KEY INDEX AK_CreditCard_CardNumber

SELECT * FROM Sales.CreditCard
WHERE CONTAINS (CardType, '"SuperiorCard"')


6.  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 quickly. (Use the AdventureWorks database)


ANS:
 CREATE FULLTEXT CATALOG CAT3 AS DEFAULT

CREATE FULLTEXT INDEX ON Sales.Store(Name)
KEY INDEX PK_Store_CustomerID

SELECT * FROM Sales.Store
WHERE FREETEXT (Name, 'Bike')


SELECT * FROM Sales.Store
WHERE CONTAINS (Name, '"Bike Store"')


7.   The manager of the production department wants to analyze the products that contain the exact
word 'road' in their description. Write a query so that the result set does not take a long time to
execute. (Use the AdventureWorks database)

ANS:
SELECT * FROM Production.ProductDescription
WHERE CONTAINS (Description, 'road')


8.  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 too much time to execute. (Use the AdventureWorks database)

ANS:
CREATE FULLTEXT INDEX ON Sales.Currency(Name)
KEY INDEX AK_Currency_Name


SELECT * FROM Sales.Currency
WHERE FreeText (Name, '"New" And "Dollar"')


9.  The Store table is often queried. The queries are based on the Demographics attribute and take a
long time to execute. Optimize the execution of these queries. (Use the AdventureWorks
database)

ANS:
CREATE XML INDEX Sdx_Sales_Demographics_PATH
ON Sales.Store (Demographics)
USING XML INDEX PXML_Store_Demographics
FOR PATH


10.  You need to create a report displaying the details of all the products that contain the word 'top'
near the word 'line' in their description. Write a query to retrieve the desired output. Write the query
such that it does not take a long time to execute. (Use the AdventureWorks database)

ANS:
SELECT Description, ProductDescriptionID
FROM Production.ProductDescription
WHERE CONTAINS (Description, ' "top" near "line"')


11.  Display the details of all the stores having the word 'bike' in their name. In addition, ensure that the
report contains the details of those stores that have the sales person ID as 277. You need to write
a query so that the result set does not take a long time to be retrieved. (Use the AdventureWorks
database)

ANS:
 SELECT * FROM Sales.Store WHERE
SalesPersonID = 227 AND CONTAINS (Name, 'Bike')


12.  The DepartmentHistory table of employees is often queried. The queries are based on the
EmployeeID attribute and take a long time to execute. Optimize the execution of these queries.
(Use the AdventureWorks database)

ANS:
 CREATE NONCLUSTERED INDEX Idx_EmployeeId
ON HumanResources.EmployeeDepartmentHistory(EmployeeID)



13.    Create a view to retrieve the employee IDs along with the corresponding Group Name. (Use the
AdventureWorks database)

ANS:
CREATE VIEW vsSales
AS
SELECT E.EmployeeId, D.GroupName
FROM HumanResources.Department D
INNER JOIN
HumanResources.EmployeeDepartmentHistory E
ON D.DepartmentID = E.DepartmentID


14.  Create a view to retrieve SalesOrderId and Price of the Product along with the corresponding
Product Name. (Use the AdventureWorks database)

ANS:
CREATE VIEW vsSales
AS
SELECT P.Name, S.SalesOrderID, S.UnitPrice
FROM Production.Product P INNER JOIN
Sales.SalesOrderDetail S
ON P.ProductID = S.ProductID

15.   Create a view to display SalesOrderId, CustomerId, OrderQty, UnitPrice, SubTotal, and TotalDue
of the orders placed. (Use the AdventureWorks database)

ANS:
CREATE VIEW vwSale AS
SELECT H.CustomerID, H.SubTotal, H.TotalDue,
FROM Sales.SalesOrderDetail AS H
JOIN Sales.SalesOrderDetail AS D ON
H.SalesOrderID = D.SalesOrderID