Question
Need help with adding sample data in these tables in SQL. I need the whole thing to run all at once THIS IS THE SQL
Need help with adding sample data in these tables in SQL. I need the whole thing to run all at once
THIS IS THE SQL CREATION SCRIPT THAT I MADE
-- Create MANUFACT Table
CREATE TABLE MANUFACT (
ManuCode INT PRIMARY KEY,
ManuName VARCHAR(50),
LeadTime INT
);
-- Create PRODUCT Table
CREATE TABLE PRODUCT (
ProdID INT PRIMARY KEY,
ProdName VARCHAR(50),
ProdDescr VARCHAR(50),
UnitPrice DECIMAL(10, 2),
QuantityOnHand INT
);
-- Create SUPPLIER Table
CREATE TABLE SUPPLIER (
SupplierID INT PRIMARY KEY,
SupplierName VARCHAR(50),
ContactPerson VARCHAR(50),
ContactNum VARCHAR(20),
Email VARCHAR(50),
ProductID INT,
ManuCode INT
);
-- Create CUSTOMER Table
CREATE TABLE CUSTOMER (
CusID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Address VARCHAR(50),
Email VARCHAR(50),
ContactNumber VARCHAR(20),
OrdNum INT,
OrderedProdID INT
);
-- Create STOCK Table
CREATE TABLE STOCK (
StockNum INT PRIMARY KEY,
Description VARCHAR(50),
UnitPrice DECIMAL(10, 2),
UnitDescr VARCHAR(255),
ManuCode INT
);
-- Create ORDERS Table
CREATE TABLE ORDERS (
OrdNum INT PRIMARY KEY,
OrdDate DATE,
Backlog INT,
PONum VARCHAR(50),
PaidDate DATE,
LeadTime INT,
TotalAmount DECIMAL(10, 2),
CusID INT
);
-- Create LOCATION Table
CREATE TABLE LOCATION (
WarehouseLocation INT PRIMARY KEY,
AisleNum INT,
ShelfNum INT,
BinNum INT,
Quantity INT,
ShipID INT
);
-- Create SHIPPINGINFORMATION Table
CREATE TABLE SHIPPINGINFORMATION (
ShipID INT PRIMARY KEY,
ShipDate DATE,
ShipWeight DECIMAL(10, 2),
ShipCharge DECIMAL(10, 2),
Carrier VARCHAR(50),
TrackingNum VARCHAR(50)
);
-- Create CATEGORY Table
CREATE TABLE CATEGORY (
CatID INT PRIMARY KEY,
CatName VARCHAR(50),
OrdNum INT,
WareHouseLocation INT
);
-- Create TRANSACTION Table
CREATE TABLE TRANSACTION (
TransactionID INT PRIMARY KEY,
TransactionDate DATE,
Quantity INT,
Email VARCHAR(50),
ContactNumber VARCHAR(20),
ShipID INT
);
-- Create ITEMS Table
CREATE TABLE ITEMS (
ItemNum INT PRIMARY KEY,
Quantity INT,
TotalPrice DECIMAL(10, 2),
ManuCode INT,
StockNum INT,
OrderNum INT
);
-- Create CATALOG Table
CREATE TABLE CATALOG (
StockNum INT,
ManuCode INT,
CatNum INT PRIMARY KEY,
CatPic VARCHAR(50)
);
-- Create CALLTYPE Table
CREATE TABLE CALLTYPE (
CusID INT,
OrdNum INT,
CallDescr VARCHAR(50),
UderID INT,
CallCode INT PRIMARY KEY
);
-- Create EMPLOYEE Table
CREATE TABLE EMPLOYEE (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Position VARCHAR(50),
Email VARCHAR(50),
ContactNumber VARCHAR(20),
CusHandledByEmp INT
);
-- Add Foreign Key Constraints
-- SUPPLIER
ALTER TABLE SUPPLIER ADD CONSTRAINT FK_SUPPLIER_PRODUCT
FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProdID);
ALTER TABLE SUPPLIER ADD CONSTRAINT FK_SUPPLIER_MANUFACT
FOREIGN KEY (ManuCode) REFERENCES MANUFACT(ManuCode);
-- CUSTOMER
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUSTOMER_ORDERS
FOREIGN KEY (OrdNum) REFERENCES ORDERS(OrdNum);
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUSTOMER_PRODUCT
FOREIGN KEY (OrderedProdID) REFERENCES PRODUCT(ProdID);
-- STOCK
ALTER TABLE STOCK ADD CONSTRAINT FK_STOCK_MANUFACT
FOREIGN KEY (ManuCode) REFERENCES MANUFACT(ManuCode);
-- ORDERS
ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_CUSTOMER
FOREIGN KEY (CusID) REFERENCES CUSTOMER(CusID);
-- LOCATION
ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_SHIPPINGINFO
FOREIGN KEY (ShipID) REFERENCES SHIPPINGINFORMATION(ShipID);
-- CATEGORY
ALTER TABLE CATEGORY ADD CONSTRAINT FK_CATEGORY_ORDERS
FOREIGN KEY (OrdNum) REFERENCES ORDERS(OrdNum);
ALTER TABLE CATEGORY ADD CONSTRAINT FK_CATEGORY_LOCATION
FOREIGN KEY (WareHouseLocation) REFERENCES LOCATION(WarehouseLocation);
-- TRANSACTION
ALTER TABLE TRANSACTION ADD CONSTRAINT FK_TRANSACTION_SHIPPINGINFO
FOREIGN KEY (ShipID) REFERENCES SHIPPINGINFORMATION(ShipID);
-- ITEMS
ALTER TABLE ITEMS ADD CONSTRAINT FK_ITEMS_MANUFACT
FOREIGN KEY (ManuCode) REFERENCES MANUFACT(ManuCode);
ALTER TABLE ITEMS ADD CONSTRAINT FK_ITEMS_STOCK
FOREIGN KEY (StockNum) REFERENCES STOCK(StockNum);
ALTER TABLE ITEMS ADD CONSTRAINT FK_ITEMS_ORDERS
FOREIGN KEY (OrderNum) REFERENCES ORDERS(OrdNum);
-- CATALOG
ALTER TABLE CATALOG ADD CONSTRAINT FK_CATALOG_STOCK
FOREIGN KEY (StockNum) REFERENCES STOCK(StockNum);
ALTER TABLE CATALOG ADD CONSTRAINT FK_CATALOG_MANUFACT
FOREIGN KEY (ManuCode) REFERENCES MANUFACT(ManuCode);
-- CALLTYPE
ALTER TABLE CALLTYPE ADD CONSTRAINT FK_CALLTYPE_CUSTOMER
FOREIGN KEY (CusID) REFERENCES CUSTOMER(CusID);
ALTER TABLE CALLTYPE ADD CONSTRAINT FK_CALLTYPE_ORDERS
FOREIGN KEY (OrdNum) REFERENCES ORDERS(OrdNum);
-- EMPLOYEE
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_CUSTOMER
FOREIGN KEY (CusHandledByEmp) REFERENCES CUSTOMER(CusID);
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started