RDBMS Lab@Home 9

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

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

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

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

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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8NENxa3dsYnhXcTg

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

Ans. https://drive.google.com/open?id=0B08RKBSts8D8dEVlQmpwS0NtN1E

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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8T3RIbzUxVWF3QWs

8. 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. https://drive.google.com/open?id=0B08RKBSts8D8T2REdkxDNHpkcHc

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. https://drive.google.com/open?id=0B08RKBSts8D8YmlzVlh6UHRNSmc

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. https://drive.google.com/open?id=0B08RKBSts8D8UzV2cVM2NUZrWG8

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

Ans. https://drive.google.com/open?id=0B08RKBSts8D8ZWxFSXhUTUxwQjA

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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8YTBwd2ZNLWJmVnM

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

Ans. https://drive.google.com/open?id=0B08RKBSts8D8YTUxY3k5MGJ3RTg

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

Ans. https://drive.google.com/open?id=0B08RKBSts8D8SEtPTXZoanB0TDg

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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8RFJ6UXV2ZmdSSmM