|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, November 30, 2007 8:49 AM
Points: 1,
Visits: 2
|
|
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_NUM CHAR(2) NOT NULL UNIQUE,
SREP_LNAME CHAR(20) NOT NULL,
SREP_FNAME CHAR(20) NOT NULL,
SREP_STREET CHAR(30) NOT NULL,
SREP_CITY CHAR(25) NOT NULL,
SREP_STATE CHAR(2) NOT NULL,
SREP_ZIP CHAR(5) NOT NULL,
SREP_COMM_TO_DATE NUMERIC(7,2),
SREP_COMM_RATE NUMERIC(3,2)
PRIMARY KEY(SREP_NUM));
CREATE TABLE CUSTOMER (
CUST_NUM CHAR(3) NOT NULL UNIQUE,
CUST_NAME CHAR(30) NOT NULL,
CUST_STREET CHAR(30) NOT NULL,
CUST_CITY CHAR(25) NOT NULL,
CUST_STATE CHAR(2) NOT NULL,
CUST_ZIP CHAR(5) NOT NULL,
CUST_BALANCE NUMERIC(7,2),
CUST_CREDIT_LIMIT NUMERIC(7,2),
SREP_NUM CHAR(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_NUM CHAR(5) PRIMARY KEY,
ORD_DATE DATETIME NOT NULL,
CUST_NUM CHAR(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_NUM CHAR(5) PRIMARY KEY,
PART_DESC VARCHAR(20),
PART_ON_HAND INT NOT NULL,
PART_CLASS CHAR(2),
PART_WAREHOUSE CHAR(2),
PART_PRICE NUMERIC(7,2),
);
CREATE TABLE ORDER_LINE
(
ORD_NUM CHAR(5) FOREIGN KEY REFERENCES ORDERS,
PART_NUM CHAR(5) FOREIGN KEY REFERENCES PART,
ORD_NUM_ORDERED INT,
ORD_QUOTED_PRICE NUMERIC(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
|
|
|
|
