Part B: for this part, you will use tables from IST302 database as source tables. 1...
Fantastic news! We've Found the answer you've been seeking!
Question:
![Part B: for this part, you will use tables from IST302 database as source tables. 1 Create a copy of SALES](https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/answers/2023/10/651f1c7f354cf_727651f1c7f30954.jpg)
![5 Save output as above, but the query JOINS PRODUCT_SUMMARY_M_AVG and PRODUCTS table. Report the query](https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/answers/2023/10/651f1c8025bc7_728651f1c8021793.jpg)
Transcribed Image Text:
Part B: for this part, you will use tables from IST302 database as source tables. 1 Create a copy of SALES table from IST302 database to your database. Name this table MY SALES. SELECT * INTO newtable FROM oldtable WHERE condition; 2 Create a view PRODUCT SUMMARY_V that has the Product ID, Year (i.e. year component of TIME_ID), Month (i.e. month component of TIME_ID), Total Amount Sold, Total Quantity Sold, and Average Sale Price (=Total Amount Sold/Total Quantity Sold) for each product in the MY_SALES table. Round the Average Sale Price to 2 decimal places. Note: you should include WITH SCHEMABINDING option when creating the view to prevent underlying tables being changed under the view. Create an indexed view PRODUCT_SUMMARY_M that correspond to your PRODUCT_SUMMARY_V. (HINT: you will get an error code 8668 if you simply create an indexed view using the same query as part B.2. The work around is to create an indexed view that do not include the Average Sale Price column; and then create a view PRODUCT_SUMMARY_M_AVG on top of the indexed view with the additional calculated column Average Sale Price.) Note: you must verify that the view definition is deterministic; and you must create the view by using the WITH SCHEMABINDING option. Also, if GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. 3 4 Run a query that JOINS PRODUCT_SUMMARY_V and PRODUCTS table, and outputs Product ID, Product Description, Year, Month, Total Amount Sold, Total Quantity Sold, and Average Sale Price where the product subcategory is 'Documentation' or 'Accessories'. Report the query execution time from Messages tab. Note: you should run SET STATISTICS TIME ON command before running the query; and run SET STATISTICS TIME OFF after. The query execution time includes SQL Server parse and compile time and execution time. Report both. 1 Read Create Index View document before you proceed. 5 Save output as above, but the query JOINS PRODUCT_SUMMARY_M_AVG and PRODUCTS table. Report the query execution time from Messages tab. The query execution time includes SQL Server parse and compile time and execution time. Report both. Compare the query execution time of part B.4 and part B.5 with your explanation. 6 7 Same as part B.4 but use an In-Line view that corresponds to PRODUCT_SUMMARY_V. APPENDIX A: Additional Tables for Computer Exercises In these exercises, you will continue using tables in the AdventureWorksDB: Human Resources Schema (see CE4) as the source data. You will also be using Person.Person and Sales. PersonCreditCard tables see below. You can click the data dictionary link to understand the meaning of the columns and relationships between tables. Table Person. Person: Column Name Business EntityID PersonType NameStyle Title FirstName MiddleName LastName Suffix EmailPromotion Additional Contact Info Demographics rowguid ModifiedDate NULL? NOT NULL int NOT NULL nchar(2) bit Credit CardID NOT NULL |nvarchar(8) nvarchar (50) NOT NULL |nvarchar (50) NOT NULL |nvarchar(50) Invarchar(10) NOT NULL |int xml xml Table Sales. Person Credit Card: NOT NULL uniqueidentifier | NOT NULL datetime Column Name NULL? Type Business EntityID NOT NULL int NOT NULL int L Part B: for this part, you will use tables from IST302 database as source tables. 1 Create a copy of SALES table from IST302 database to your database. Name this table MY SALES. SELECT * INTO newtable FROM oldtable WHERE condition; 2 Create a view PRODUCT SUMMARY_V that has the Product ID, Year (i.e. year component of TIME_ID), Month (i.e. month component of TIME_ID), Total Amount Sold, Total Quantity Sold, and Average Sale Price (=Total Amount Sold/Total Quantity Sold) for each product in the MY_SALES table. Round the Average Sale Price to 2 decimal places. Note: you should include WITH SCHEMABINDING option when creating the view to prevent underlying tables being changed under the view. Create an indexed view PRODUCT_SUMMARY_M that correspond to your PRODUCT_SUMMARY_V. (HINT: you will get an error code 8668 if you simply create an indexed view using the same query as part B.2. The work around is to create an indexed view that do not include the Average Sale Price column; and then create a view PRODUCT_SUMMARY_M_AVG on top of the indexed view with the additional calculated column Average Sale Price.) Note: you must verify that the view definition is deterministic; and you must create the view by using the WITH SCHEMABINDING option. Also, if GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. 3 4 Run a query that JOINS PRODUCT_SUMMARY_V and PRODUCTS table, and outputs Product ID, Product Description, Year, Month, Total Amount Sold, Total Quantity Sold, and Average Sale Price where the product subcategory is 'Documentation' or 'Accessories'. Report the query execution time from Messages tab. Note: you should run SET STATISTICS TIME ON command before running the query; and run SET STATISTICS TIME OFF after. The query execution time includes SQL Server parse and compile time and execution time. Report both. 1 Read Create Index View document before you proceed. 5 Save output as above, but the query JOINS PRODUCT_SUMMARY_M_AVG and PRODUCTS table. Report the query execution time from Messages tab. The query execution time includes SQL Server parse and compile time and execution time. Report both. Compare the query execution time of part B.4 and part B.5 with your explanation. 6 7 Same as part B.4 but use an In-Line view that corresponds to PRODUCT_SUMMARY_V. APPENDIX A: Additional Tables for Computer Exercises In these exercises, you will continue using tables in the AdventureWorksDB: Human Resources Schema (see CE4) as the source data. You will also be using Person.Person and Sales. PersonCreditCard tables see below. You can click the data dictionary link to understand the meaning of the columns and relationships between tables. Table Person. Person: Column Name Business EntityID PersonType NameStyle Title FirstName MiddleName LastName Suffix EmailPromotion Additional Contact Info Demographics rowguid ModifiedDate NULL? NOT NULL int NOT NULL nchar(2) bit Credit CardID NOT NULL |nvarchar(8) nvarchar (50) NOT NULL |nvarchar (50) NOT NULL |nvarchar(50) Invarchar(10) NOT NULL |int xml xml Table Sales. Person Credit Card: NOT NULL uniqueidentifier | NOT NULL datetime Column Name NULL? Type Business EntityID NOT NULL int NOT NULL int L
Expert Answer:
Answer rating: 100% (QA)
Answer Create an indexed view without Average Sale Price ... View the full answer
Related Book For
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George
Posted Date:
Students also viewed these programming questions
-
What are the current statutory requirements for tax compliance? Which is the most suitable commercially available financial-management accounting software for Career Training? What are the...
-
The payroll project that follows is the online version of the same project you completed manually in Chapter 7 of your text. For this project, you will use the Cengage Learning General Ledger to...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Differentiate implicitly to find 2x + 5xy + 5y +16y - 8 = 0; dy dx . Then find the slope of the curve at the given point. (-2,0)
-
Explain why Sec. 382 will not be an obstacle to the use of NOL carryovers following an acquistion if the value of the old loss corporation is large relative to its NOL carryovers.
-
It is necessary to design a digital circuit with four inputs C, S, Z, and V and 10 outputs, one for each of the branch conditions listed in Probs. 8-26 and 8-27. (The equal and unequal conditions are...
-
More randomization. Most sample surveys call residential telephone numbers at random. They do not, however, always ask their questions of the person who picks up the phone. Instead, they ask about...
-
Carry Books needs to decide how many copies of a new hardcover release to purchase for its shelves. The store has assumed that demand will be 50, 100, 150, or 200 copies next month, and it needs to...
-
.
-
As an official sponsor of the Olympics, what specific benefit did John Hancock use to help drive sales in their national offices?
-
Prove that if r and s are rational numbers, then r + s is a rational number. Start of Proof: Let r = p/q and s = u/v for some integers p, q, u, v where q and v arent equal to zero.
-
PART 4.1 Process Costing - Weighted Average MOLDING Physical Flow of Units Work-in-Process - Beginning Units Started this Period Units to Account for Total transferred out Work-in-Process - Ending...
-
3. A boy walks 10 m north then 3 m west. What is his total displacement? [3 marks] 4. A and B are perpendicular vectors. A = 2 and A + B a. Calculate b. Calculate A - B c. Explain your results. B bd....
-
Virginia has just been quoted what appears to be a very competitive loan for $2,000 to be paid back in 24 monthly payments of $96.66.What is Virginia's APR? What is the monthly payment for a $24,000,...
-
A four-lane urban freeway (two lanes in each direction) is located on rolling terrain and has 12-ft lanes, no lateral obstructions within 6 ft of the pavement edges, and an interchange every 2 miles....
-
In January, 1993, there were about 1,313,000 internet hosts. During the next five years, the number of hosts increased by about 100% per year. a. Write a model giving the number h (in millions) of...
-
Facts: The following facts relate to Go Purple, Inc.'s production of a product. For purposes of this question, "ROI" stands for "return on investment". Target Costing Estimated units sold Market...
-
Draw the appropriate control flow graph of the given pseudocode.Make sure to only use one number for blocks of code which are all sequential and when the first line is executed, all of those lines...
-
What are the common security threats to systems? How can they be addressed?
-
What is a use case diagram?
-
What is the conventional wisdom about implementation success?
-
Where is accumulated depreciation reported on the balance sheet? AppendixLO1
-
What was Tootsie Roll's largest current asset at December 31, 2001? The answer to this question is provided on page101. AppendixLO1
-
What is the primary determining factor to distinguish current assets from long-term assets? AppendixLO1
![Mobile App Logo](https://dsd5zvtm8ll6.cloudfront.net/includes/images/mobile/finalLogo.png)
Study smarter with the SolutionInn App