Increased Query Functionality-Start the VM for assignment five. Inside the VM, click Start, type SSMS (to open SQL Server Management Studio), Select Database Engine and the server type, and then click connect. Select Databases, then right click the most current version of AdventureWorks database to create a new query for this assignment.
In this assignment, students will use the most current version of AdventureWorks database and practice writing CASE, SET, and DECLARE statements to build a stored procedure that can parse data quickly.
The marketing department wants to promote sales of the Road-650 bicycle. It has developed a request list related to the promotion, and you have been tasked with determining questions related to the Road-650 bicycle inventory.
Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to a CSV file for a visual check of accuracy.
Create a Microsoft Word document that includes the SQL query code used to explore the database tables and complete the following criteria. Make sure to provide a screenshot for each.
- The marketing department wants to know if the Road-650 bicycle has more than 10 styles currently available. Using the Product table, create a statement using IF THEN logic to trigger on whether there are more or less than 10 of Road-650 bicycle units available. Submit the statement and the result of the query.
- The marketing department has decided that it will only feature black colored Road-650 bicycles in the sales promotion. However, the marketing department may want to modify the sale at a later date. Use a DECLARE and SET statement to pre-set a variable color to “Black.” Using the variable, run a query to show all the type of bicycles including all fields.
- Building on the question two activity, add the “Quantity” and “ListPrice.” Filter the results to only items in “Finished Goods Storage.”
- Building on the question three activity, the marketing department has decided that any products that currently have more than 100 units in stock will be discounted by 10%. All other products will have a 5% discount as part of the promotion. Create a CASE statement that modifies the “ListPrice” into a new field called “SalesPrice.”
- Your manager has asked you to create a stored procedure for an application the business is developing. For the application, management has requested that when a “City” is selected, it returns with the corresponding region. By integrating code, the business will be able to return results quickly for users. Use the Address, StateProvince, and SalesTerritory tables and name the procedure “GetRegion.” TIP: Use GO. Test that your procedure works by running an EXECUTE statement.
Submit the Word document (with screenshots).
APA style is not required, but solid academic writing is expected.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are not required to submit this assignment to LopesWrite.
CAHIIM
This assignment aligns with the following AHIMA 2018 Entry Level Competencies for Health Information Management Curricula at the bachelor’s degree level:
Domain III. Informatics, Analytics, and Data Use
III.6. Manage data within a database management system.
Answer Preview-Increased Query Functionality-SYM-400
