hw14

sql

School

Tulsa Community College *

*We aren’t endorsed by this school

Course

2133

Subject

Information Systems

Date

Oct 30, 2023

Type

sql

Pages

Uploaded by CorporalChimpanzee344

use F22_thazelr go CREATE PROCEDURE [dbo].[Create_eCommerce_Tables] AS IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='customer') CREATE TABLE Customer ( CustID INT NOT NULL, fname VARCHAR(20) NOT NULL, lname VARCHAR(20) NOT NULL, phone NUMERIC(15) NOT NULL, email VARCHAR(20) NOT NULL, DOB DATE NOT NULL, Gender CHAR(10) NOT NULL, PRIMARY KEY (CustID) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='orderitems') CREATE TABLE orderitems ( Oid INT NOT NULL, prodid INT NOT NULL, qty INT NOT NULL, price NUMERIC(15) NOT NULL, PRIMARY KEY (Oid, prodid) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='upsale') CREATE TABLE upsale ( prodid INT NOT NULL, percentage NUMERIC(5) NOT NULL, custid INT NOT NULL, PRIMARY KEY (prodid, custid) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='shiippingAddress') CREATE TABLE shiippingAddress ( sid INT NOT NULL, address1 VARCHAR(30) NOT NULL, address2 VARCHAR(30) NOT NULL, city VARCHAR(20) NOT NULL, state VARCHAR(20) NOT NULL, primary_YN CHAR(2) NOT NULL, CustID INT NOT NULL, PRIMARY KEY (sid), FOREIGN KEY (CustID) REFERENCES Customer(CustID) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='shippmentMethos') CREATE TABLE shippmentMethos ( shipid INT NOT NULL, company VARCHAR(20) NOT NULL, method VARCHAR(20) NOT NULL, frate NUMERIC(10) NOT NULL, vrate NUMERIC(10) NOT NULL, baseWeight NUMERIC(10) NOT NULL, PRIMARY KEY (shipid) );

IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='crossSell') CREATE TABLE crossSell ( prodid1 INT NOT NULL, prodid2 INT NOT NULL, percentage NUMERIC(10) NOT NULL, PRIMARY KEY (prodid1, prodid2) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='department') CREATE TABLE department ( deptid INT NOT NULL, name VARCHAR(20) NOT NULL, description VARCHAR(35) NOT NULL, PRIMARY KEY (deptid) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='paymentinfo') CREATE TABLE paymentinfo ( custid INT NOT NULL, pname VARCHAR(20) NOT NULL, cctype VARCHAR(20) NOT NULL, ccnumber INT NOT NULL, ccexpire DATE NOT NULL, billAddress VARCHAR(30) NOT NULL, city VARCHAR(20) NOT NULL, state CHAR(20) NOT NULL, zip VARCHAR(20) NOT NULL, PRIMARY KEY (custid, pname) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='Orders') CREATE TABLE Orders ( Oid INT NOT NULL, Orderdt DATE NOT NULL, ShipId INT NOT NULL, ShipCost NUMERIC(15) NOT NULL, CustID INT NOT NULL, shipid INT NOT NULL, FOREIGN KEY (CustID) REFERENCES Customer(CustID), FOREIGN KEY (shipid) REFERENCES shippmentMethos(shipid) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='category') CREATE TABLE category ( cid INT NOT NULL, name VARCHAR(20) NOT NULL, description VARCHAR(35) NOT NULL, deptid INT NOT NULL, PRIMARY KEY (cid), FOREIGN KEY (deptid) REFERENCES department(deptid) ); IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='product') CREATE TABLE product ( prodid INT NOT NULL, productname VARCHAR(20) NOT NULL, description VARCHAR(35) NOT NULL, rPrice NUMERIC(10) NOT NULL,