Using SQL code Create a stored procedure “changeCreditLimit” to change the credit limit for a given customer. If the customer’s total payment amount (note: payment amount is in the table “payments”) is not smaller than a given amount, then add 2000 to the customer’s credit limit (note: credit limit is in the table “customers”). Below is a sample statement to test your stored procedure. set @customer = 114; set @totalpayment = 15000; call changeCreditLimit(@customer,@totalpayment); Having this database CREATE TABLE `payments` ( `customerNumber` int(11) NOT NULL, `checkNumber` varchar(50) NOT NULL, `paymentDate` date NOT NULL, `amount` double NOT NULL, PRIMARY KEY (`customerNumber`,`checkNumber`), CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `customers` ( `customerNumber` int(11) NOT NULL, `customerName` varchar(50) NOT NULL, `contactLastName` varchar(50) NOT NULL, `contactFirstName` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `city` varchar(50) NOT NULL, `state` varchar(50) DEFAULT NULL, `postalCode` varchar(15) DEFAULT NULL, `country` varchar(50) NOT NULL, `salesRepEmployeeNumber` int(11) DEFAULT NULL, `creditLimit` double DEFAULT NULL, PRIMARY KEY (`customerNumber`), KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`), CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Using SQL code
Create a stored procedure “changeCreditLimit” to change the credit limit for a given
customer. If the customer’s total payment amount (note: payment amount is in the
table “payments”) is not smaller than a given amount, then add 2000 to the customer’s
credit limit (note: credit limit is in the table “customers”).
Below is a sample statement to test your stored procedure.
set @customer = 114; set @totalpayment = 15000;
call changeCreditLimit(@customer,@totalpayment);
Having this
CREATE TABLE `payments` (
`customerNumber` int(11) NOT NULL,
`checkNumber` varchar(50) NOT NULL,
`paymentDate` date NOT NULL,
`amount` double NOT NULL,
PRIMARY KEY (`customerNumber`,`checkNumber`),
CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `customers` (
`customerNumber` int(11) NOT NULL,
`customerName` varchar(50) NOT NULL,
`contactLastName` varchar(50) NOT NULL,
`contactFirstName` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`city` varchar(50) NOT NULL,
`state` varchar(50) DEFAULT NULL,
`postalCode` varchar(15) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`salesRepEmployeeNumber` int(11) DEFAULT NULL,
`creditLimit` double DEFAULT NULL,
PRIMARY KEY (`customerNumber`),
KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Trending now
This is a popular solution!
Step by step
Solved in 4 steps
