SQL LAB at Home 9

SQL LAB at Home 9

01. 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.

++++=======================++++

++++=======================++++

02. 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.

select * from Sales.Store
create Unique nonclustered index
IDx_CustomerID on sales.Stare(CustomerID)

========================================

========================================

03. 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.

update vwSalesOrderDetail
set OrderQty = 2
from vwSalesOrderDetail
Where SalesOrderID=43659

Update vwSalesOrderDetail
SET TerritoryID=4
from vwSalesOrderDetail
Where SalesOrderID=43659

==========================================

==========================================

04. 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.

create nonclustered Index
 Idx_salesOrderID_salesOrderDetailID
 on sales.salesorderdetail(salesorderid,salesorderdetailid)

============================================

============================================

05. 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.

select * from Sales.Store
where contains (Name,'"Bike store"')

===========================================

===========================================

06. 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.

select * from sales.CreditCard
Where Contains(CardType,'SuperiorCard')

===========================================

===========================================

07. 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.

select * from Sales.Currency
Where FreeText (Name,'"New" and "Dollar"')

===============================================

===============================================

08. 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.

select * from Production.ProductDescription
Where Contains(Description,'Road')

==============================================

==============================================

09. 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.

select Description, ProductDescriptionID
from Production.ProductDescription
Where Contains (Description,'"Top" near "Line"')

==============================================

==============================================

10. 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.

create XML Index Sdx_Sales_Demographics_path
on Sales.Store(Demographics)
Using XML Index PXML_Store_Demographics
for path

===============================================

===============================================

11. 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.

create Nonclustered index Idx_EmployeeID
on HumanResources.EmployeeDepartmentHistory(EmployeeID)

================================================

================================================

12. 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.

select * from Sales.Store
Where SalesPersonID=277
and Contains(Name,'Bike')

==============================================

Http://Sonugiri.blogspot.in

==============================================

13. Create a view to retrieve SalesOrderId and Price of the Product along with the corresponding Product Name.

create View vwsales
as
select P.Name, S.SalesOrderID,S.UnitPrice
from Production.Product P inner join
Sales.SalesOrderDetail S
on P.ProductID = ProductID
go

================================================

================================================

14. Create a view to retrieve the employee IDs along with the corresponding Group Name.

create View Disp_Detail
as
select E.EmployeeID,D.GroupName
from HumanResources.Department d
Inner join
HumanResources.EmployeePayHistory E
on D.DepartmentID=E.EmployeeID

==================================================

==================================================

15. Create a view to display SalesOrderId, CustomerId, OrderQty, UnitPrice, SubTotal, and TotalDue of the orders placed.

create view vwsale as
select H.CustomerID, H.SubTotal, H.TotalDue,
D.SalesOrderID,D.OrderQty,D.UnitPrice
from Sales.SalesOrderHeader as H
join sales.salesOrderDetail as D on
H.salesOrderID=d.salesOrderID
select * from Sales.SalesOrderDetail