To create a database use:

CREATE DATABASE IF NOT EXISTS <database_name_here>;



To delete a database use:

DROP DATABASE IF EXISTS <database_name_here>;



To create a table in a database use:

CREATE TABLE <table_name_here>
(
<column_name_here> INT NOT NULL AUTO_INCREMENT,
<column_name_here> INT NOT NULL,
<column_name_here> VARCHAR(50) NOT NULL,
<column_name_here> VARCHAR(40) NOT NULL,
<column_name_here> VARCHAR(100) NOT NULL,
<column_name_here> DECIMAL (10, 2),
<column_name_here> DECIMAL (10, 2),
PRIMARY KEY (<column_name_here>),
UNIQUE (<column_name_here>)
);

For this assignment specifically:

CREATE TABLE salesRep
(
salesRepKeyId INT NOT NULL AUTO_INCREMENT,
repNumber INT NOT NULL,
repFirstName VARCHAR(50) NOT NULL,
repLastName VARCHAR(40) NOT NULL,
repAddress VARCHAR(100) NOT NULL,
repComissionTotal DECIMAL (10, 2),
repComissionRate DECIMAL (10, 2),
PRIMARY KEY (salesRepKeyId),
UNIQUE (repNumber)
);

CREATE TABLE customer
(
customerId INT NOT NULL AUTO_INCREMENT,
customerNumber INT NOT NULL,
customerName VARCHAR(50) NOT NULL,
customerAddress VARCHAR(100) NOT NULL,
customerBalance DECIMAL (10, 2),
customerCreditLimit DECIMAL (10, 2),
repNumber INT NOT NULL,
PRIMARY KEY (customerId),
UNIQUE (customerNumber)
);

CREATE TABLE inventoryItem
(
itemId INT NOT NULL AUTO_INCREMENT,
itemNumber INT NOT NULL,
repFirstName VARCHAR(50) NOT NULL,
repLastName VARCHAR(40) NOT NULL,
itemDescription VARCHAR(100) NOT NULL,
itemUnitsInStock INT NOT NULL,
itemClass VARCHAR(2) NOT NULL,
itemWarehouseLocation VARCHAR(40) NOT NULL,
itemUnitPrice DECIMAL (10, 2),
PRIMARY KEY (itemId),
UNIQUE (itemNumber)
);

CREATE TABLE orderCustomerRecord
(
orderId INT NOT NULL AUTO_INCREMENT,
orderNumber INT NOT NULL,
orderDate DATETIME DEFAULT NULL,
customerNumber INT NOT NULL,
PRIMARY KEY (orderId)
);

CREATE TABLE orderInventoryRecord
(
orderInvId INT NOT NULL AUTO_INCREMENT,
orderInvNumber INT NOT NULL,
orderInvTotalUnits INT NOT NULL,
orderInvQuotedPrice DECIMAL (10, 2),
PRIMARY KEY (orderInvId)
);