November 29, 2007 at 10:43 am
Here is the code where I created my tables -
I want to Create a stored procedure named “dropatable” that will drop a table name that is entered as a parameter if it exists.
create PROCEDURE [dbo].[createalltables]
AS
BEGIN
CREATE TABLE SALES_REP (
SREP_NUMCHAR(2)NOT NULLUNIQUE,
SREP_LNAMECHAR(20) NOT NULL,
SREP_FNAMECHAR(20) NOT NULL,
SREP_STREETCHAR(30) NOT NULL,
SREP_CITYCHAR(25) NOT NULL,
SREP_STATE CHAR(2) NOT NULL,
SREP_ZIPCHAR(5) NOT NULL,
SREP_COMM_TO_DATENUMERIC(7,2),
SREP_COMM_RATENUMERIC(3,2)
PRIMARY KEY(SREP_NUM));
CREATE TABLE CUSTOMER (
CUST_NUMCHAR(3)NOT NULLUNIQUE,
CUST_NAMECHAR(30) NOT NULL,
CUST_STREETCHAR(30) NOT NULL,
CUST_CITYCHAR(25) NOT NULL,
CUST_STATECHAR(2) NOT NULL,
CUST_ZIPCHAR(5) NOT NULL,
CUST_BALANCENUMERIC(7,2),
CUST_CREDIT_LIMITNUMERIC(7,2),
SREP_NUMCHAR(2) NOT NULL,
PRIMARY KEY(CUST_NUM),
FOREIGN KEY(SREP_NUM) REFERENCES SALES_REP);
INSERT INTO SALES_REP
VALUES(20,'Kaiser','Valerie','624 Randall','Grove','FL',33321,20542.50,0.05)
INSERT INTO SALES_REP
VALUES(35,'Hull','Richard','532 Jackson','Sheldon','FL',33553,39216.00,0.07)
INSERT INTO SALES_REP
VALUES(65,'Perez','Juan','1626 Taylor','Fillmore','FL',33336,23487.00,0.05);
INSERT INTO SALES_REP
VALUES(45,'Yerby','Johnathan','476 Lincoln','Grove','FL',33321,37892.50,0.06);
INSERT INTO CUSTOMER
VALUES(148,'Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',$6550.00,$7500.00,20)
INSERT INTO CUSTOMER
VALUES(282,'Brookings Direct','3827 Devon','Grove','FL','33321',$431.50,$10000.00,35)
INSERT INTO CUSTOMER
VALUES(356,'Ferguson''s','382 Wildwood','Northfield','FL','33146',$5785.00,$7500.00,65)
INSERT INTO CUSTOMER
VALUES(408,'The Everything Shop','1828 Raven','Crystal','FL','33503',$5285.25,$5000.00,35)
INSERT INTO CUSTOMER
VALUES(462,'Bargains Galore','3829 Central','Grove','FL','33321',$3412.00,$10000.00,65)
INSERT INTO CUSTOMER
VALUES(524,'Kline''s','838 Ridgeland','Fillmore','FL','33336',$12762.00,$15000.00,20)
INSERT INTO CUSTOMER
VALUES(608,'Johnson''s Department Store','372 Oxford','Sheldon','FL','33553',$2106.00,$10000.00,65)
INSERT INTO CUSTOMER
VALUES(687,'Lee''s Sport and Appliance','282 Evergreen','Altonville','FL','32543',$2851.00,$5000.00,35)
INSERT INTO CUSTOMER
VALUES(725,'Deerfield''s Four Seasons','282 Columbia','Sheldon','FL','33553',$248.00,$7500.00,35)
INSERT INTO CUSTOMER
VALUES(842,'All Season','28 Lakeview','Grove','FL','33321',$8221.00,$7500.00,20);
CREATE TABLE ORDERS
(
ORD_NUMCHAR(5) PRIMARY KEY,
ORD_DATEDATETIME NOT NULL,
CUST_NUMCHAR(3) FOREIGN KEY REFERENCES CUSTOMER on Update Cascade,
);
INSERT INTO ORDERS
VALUES(21608,'10/20/2007',148)
INSERT INTO ORDERS
VALUES(21610,'10/20/2007',356)
INSERT INTO ORDERS
VALUES(21613,'10/21/2007',408)
INSERT INTO ORDERS
VALUES(21614,'10/21/2007',282)
INSERT INTO ORDERS
VALUES(21617,'10/23/2007',608)
INSERT INTO ORDERS
VALUES(21619,'10/23/2007',148)
INSERT INTO ORDERS
VALUES(21623,'10/23/2007',608);
CREATE TABLE PART
(
PART_NUMCHAR(5) PRIMARY KEY,
PART_DESCVARCHAR(20),
PART_ON_HANDINT NOT NULL,
PART_CLASSCHAR(2),
PART_WAREHOUSECHAR(2),
PART_PRICENUMERIC(7,2),
);
CREATE TABLE ORDER_LINE
(
ORD_NUMCHAR(5)FOREIGN KEY REFERENCES ORDERS,
PART_NUMCHAR(5) FOREIGN KEY REFERENCES PART,
ORD_NUM_ORDEREDINT,
ORD_QUOTED_PRICENUMERIC(7,2),
);
INSERT INTO PART
VALUES( 'AT94','Iron',50.00,'HW','3',24.95)
INSERT INTO PART
VALUES( 'BV06','Home Gym',45.00,'SG','2',794.95)
INSERT INTO PART
VALUES( 'CD52','Microwave Oven',32.00,'AP','1',165.00)
INSERT INTO PART
VALUES( 'DL71','Cordless Drill',21.00,'HW','3',129.95)
INSERT INTO PART
VALUES( 'DR93','Gas Range',8.00,'AP','2',495.00)
INSERT INTO PART
VALUES( 'DW11','Washer',12.00,'AP','3',399.99)
INSERT INTO PART
VALUES( 'FD21','Stand Mixer',22.00,'HW','3',159.95)
INSERT INTO PART
VALUES( 'KL62','Dryer',12.00,'AP','1',349.95)
INSERT INTO PART
VALUES( 'KT03','Dishwasher',8.00,'AP','3',595.00)
INSERT INTO PART
VALUES( 'KV29','Treadmill',9.00,'SG','2',1390.00);
INSERT INTO ORDER_LINE
VALUES( 21608,'AT94',11.00,21.95)
INSERT INTO ORDER_LINE
VALUES( 21610,'DR93',1.00,495.00)
INSERT INTO ORDER_LINE
VALUES( 21610,'DW11',1.00,399.99)
INSERT INTO ORDER_LINE
VALUES( 21613,'KL62',4.00,329.95)
INSERT INTO ORDER_LINE
VALUES( 21614,'KT03',2.00,595.00)
INSERT INTO ORDER_LINE
VALUES( 21617,'BV06',2.00,794.95)
INSERT INTO ORDER_LINE
VALUES( 21617,'CD52',4.00,150.00)
INSERT INTO ORDER_LINE
VALUES( 21619,'DR93',1.00,495.00)
INSERT INTO ORDER_LINE
VALUES( 21623,'KV29',2.00,1290.00);
SELECT * FROM CUSTOMER
SELECT * FROM ORDER_LINE
SELECT * FROM ORDERS
SELECT * FROM PART
SELECT * FROM SALES_REP
END
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply