Create a stored procedure called Insert_ShippingMethod_Data and write the necess
ID: 3913412 • Letter: C
Question
Create a stored procedure called Insert_ShippingMethod_Data and write the necessary code to insert the shipping method data into your tables. Once you create the stored procedure, you must execute the stored procedure in order to populate the shippingMethods table. The data to be inserted into your table is on this chart.
shippingMethods
shipID | company | method | fRate | vRate | baseWeight
101 | Fedex | Standard Ground | 10 | 0.5 | 20
102 | Fedex | Standard Air | 20 | 0.5 | 10
103 | Fedex | Express Air | 30 | 1 | 10
104 | Fedex | Next Day Air | 40 | 1 | 10
201 | UPS | Standard Ground | 15 | 0.5 | 15
202 | UPS | Standard Air | 25 | 0.5 | 15
203 | UPS | Express Air | 35 | 0.5 | 15
204 | UPS | Next Day Air | 45 | 0.5 | 15
301 | USPS | Standard Ground 5 | 0.5 | 20
302 | USPS | Standard Air | 15 | 0.5 | 10
303 | USPS | Express Air | 30 | 1 | 10
304 | USPS | Next Day Air | 40 1 | 10
Explanation / Answer
Please find the procedure below:
CREATE PROCEDURE Insert_ShippingMethod_Data
@shipId INT(3),
@company VARCHAR(45),
@method VARCHAR(45),
@fRate INT(2),
@vRate DECIMAL(2, 1),
@baseWeight INT(2)
AS
BEGIN
INSERT INTO shippingMethods (shipID, company, method, fRate, vRate, baseWeight)
VALUES (@shipId, @company, @method, @fRate, @vRate, @baseWeight)
END
To insert data, call the procedure as shown below.
exec Insert_ShippingMethod_Data
@shipId = 101,
@company = 'Fedex',
@method = 'Standard Ground',
@fRate = 10,
@vRate = 0.5,
@baseWeight = 20;
exec Insert_ShippingMethod_Data
@shipId = 102,
@company = 'Fedex',
@method = 'Standard Air',
@fRate = 20,
@vRate = 0.5,
@baseWeight = 10;
exec Insert_ShippingMethod_Data
@shipId = 103,
@company = 'Fedex',
@method = 'Express Air',
@fRate = 30,
@vRate = 1,
@baseWeight = 10;
exec Insert_ShippingMethod_Data
@shipId = 104,
@company = 'Fedex',
@method = 'Next Day Air',
@fRate = 40,
@vRate = 1,
@baseWeight = 10;
exec Insert_ShippingMethod_Data
@shipId = 201,
@company = 'UPS',
@method = 'Standard Ground',
@fRate = 15,
@vRate = 0.5,
@baseWeight = 15;
exec Insert_ShippingMethod_Data
@shipId = 202,
@company = 'UPS',
@method = 'Standard Air',
@fRate = 25,
@vRate = 0.5,
@baseWeight = 15;
exec Insert_ShippingMethod_Data
@shipId = 203,
@company = 'UPS',
@method = 'Express Air',
@fRate = 35,
@vRate = 0.5,
@baseWeight = 15;
exec Insert_ShippingMethod_Data
@shipId = 204,
@company = 'UPS',
@method = 'Next Day Air',
@fRate = 45,
@vRate = 0.5,
@baseWeight = 15;
exec Insert_ShippingMethod_Data
@shipId = 301,
@company = 'USPS',
@method = 'Standard Ground',
@fRate = 5,
@vRate = 0.5,
@baseWeight = 20;
exec Insert_ShippingMethod_Data
@shipId = 302,
@company = 'USPS',
@method = 'Standard Air',
@fRate = 15,
@vRate = 0.5,
@baseWeight = 10;
exec Insert_ShippingMethod_Data
@shipId = 303,
@company = 'USPS',
@method = 'Express Air',
@fRate = 30,
@vRate = 1,
@baseWeight = 10;
exec Insert_ShippingMethod_Data
@shipId = 304,
@company = 'USPS',
@method = 'Next Day Air',
@fRate = 40,
@vRate = 1,
@baseWeight = 10;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.