@font-face {font-family:"Cambria Math"; panose-1: XXXXXXXXXX; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature: XXXXXXXXXX;}@font-face...

1 answer below »
@font-face {font-family:"Cambria Math"; panose-1: XXXXXXXXXX; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature: XXXXXXXXXX;}@font-face {font-family:Calibri; panose-1: XXXXXXXXXX; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature: XXXXXXXXXX XXXXXXXXXX;}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.

Answered 1 days AfterMay 06, 2021

Solution

Aniket Gupta answered on May 07 2021
19 Votes

CREATE DATABASE CustAnalytics;
USE CustAnalytics
CREATE TABLE CUSTOMER
(
customerID char(5),
customername varchar(15) not null,
customerzip char(5) not null,
primary key...

Submit New Assignment

Copy and Paste Your Assignment Here