@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-536858881 -1073732485 9 0 511 0;}p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}a:link, span.MsoHyperlink {mso-style-noshow:yes; mso-style-priority:99; color:#0563C1; mso-themecolor:hyperlink; text-decoration:underline; text-underline:single;}a:visited, span.MsoHyperlinkFollowed {mso-style-noshow:yes; mso-style-priority:99; color:#954F72; mso-themecolor:followedhyperlink; text-decoration:underline; text-underline:single;}p {mso-style-noshow:yes; mso-style-priority:99; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-fareast-font-family:"Times New Roman";}span.instructurefileholder {mso-style-name:instructure_file_holder; mso-style-unhide:no;}span.screenreader-only {mso-style-name:screenreader-only; mso-style-unhide:no;}.MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}div.WordSection1 {page:WordSection1;}
Company X’s Business Intelligence team is developing a web application. One of the features of this application is to categorize the company’s customers based on the sales volume (qty sold). The application developers reached out to you to develop a stored procedure, which the application can call upon request from the user.
Based on your discussion with the developer, the logic for categorizing customer is as follows:
IF Total Quantity Sold to a Customer
IF Total Quantity Sold to a Customer
ELSE set the Customer Tier to “High-tier”
Deliverable 1 (90 pts):
Please develop a stored procedure with the name “custtier”, which generates the following information to the application:
Customername, TotalQtySold, CustTier
Please use the prj6_custanalytics_db.sql to create the mini database and insert the sample data. You will use this database to create your stored procedure.
prj6_custanalytics_db.sql
download
Deliverable 2 (10 pts):
Please create a call statement to show that your procedure works.