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.
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
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=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
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)
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)
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)
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)
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)
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)
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)
13. Create a view to retrieve the employee IDs along with the corresponding Group Name. (Use the AdventureWorks database)
14. Create a view to retrieve SalesOrderId and Price of the Product along with the corresponding Product Name. (Use the AdventureWorks database)
15. Create a view to display SalesOrderId, CustomerId, OrderQty, UnitPrice, SubTotal, and TotalDue of the orders placed. (Use the AdventureWorks database)
No comments:
Post a Comment