Need help creating this table in SQL Create a stored procedure called Insert_Shi

Question

Need help creating this table in SQL

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

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.0,

@baseWeight = 10;

NOTE: I have written code to call the procedure to insert the first three rows. Similarly, you can call the procedure to insert the remaining rows.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.