Here are the scripts that I have created to make the tables and add the data.
CREATE TABLE EMPLOYEES
( ID INT NOT NULL,
SSN DECIMAL(9,0) NOT NULL,
BIRTHDATE DATE NOT NULL,
DEPENDANTS TINYINT NOT NULL,
MARRIED BIT NOT NULL,
FULL_PART_TIME BIT NOT NULL,
HIRE_DATE DATE NOT NULL,
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP TINYINT NOT NULL,
ZIP4 TINYINT,
COMPLETED_TRAINING BIT DEFAULT 0,
Name VARCHAR(30) NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT AK_SSN UNIQUE(SSN));
CREATE TABLE SUPPLIERS
( ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP TINYINT NOT NULL,
ZIP4 TINYINT,
EMAIL VARCHAR(50) NOT NULL,
PHONENUMBER CHAR(12) NOT NULL,
CONTACT VARCHAR(30),
TERMS VARCHAR(MAX),
NOTES VARCHAR(MAX),
PRIMARY KEY (ID));
CREATE TABLE PRODUCTS
( ID INT NOT NULL,
CATEGORY VARCHAR(12) NOT NULL,
REORDER TINYINT NOT NULL,
SIZE VARCHAR(12) ,
DISCRIPTION varchar(100) NOT NULL,
PRICE SMALLMONEY NOT NULL,
QNTY TINYINT,
FROMSUPPLIER INT REFERENCES SUPPLIERS(ID) NOT NULL,
FROMBACKUP INT REFERENCES SUPPLIERS(ID),
PRIMARY KEY (ID));
CREATE TABLE CUSTOMERS
( ID INT NOT NULL,
fNAME VARCHAR(30) NOT NULL,
lNAME VARCHAR(30) NOT NULL,
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP TINYINT NOT NULL,
ZIP4 TINYINT,
EMAIL VARCHAR(50) NOT NULL,
NOTES VARCHAR(MAX),
PHONENUMBER VARCHAR(12) NOT NULL,
PRIMARY KEY (ID));
CREATE TABLE ORDERS
( ID INT NOT NULL,
BYCUSTOMER INT NOT NULL,
ENTEREDBYEMPLOYEE INT NOT NULL,
PLACEDON DATE NOT NULL,
PRIMARY KEY (ID));
CREATE TABLE ORDERDETAILS
(ORDERID INT NOT NULL REFERENCES ORDERS(ID) on delete cascade,
PRODUCTID INT NOT NULL REFERENCES PRODUCTS(ID) on delete cascade,
QUANTITY INT NOT NULL,
);
to add test data
INSERT INTO CUSTOMERS
VALUES(1,'Kim','Gross','332 W Dewey Ave','Council','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(2,'Elaine','Gross','501 Illinois Ave','Nampa','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(3,'Joe','blow','332 W Dewey Ave','Nampa','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(4,'Fred','Savage','501 Illinois Ave','Caldwell','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(5,'John','Jingle','332 W Dewey Ave','Boise','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(6,'Jane','Doe','501 Illinois Ave','Indian Valley','ID',83602,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(7,'Tim','Tiny','332 W Dewey Ave','Denver','ID',83632,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(8,'Bud','Wiser','501 Illinois Ave','Bear','ID',83642,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(9,'Gene','Ie','332 W Dewey Ave','Nampa','ID',83692,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(10,'Patty','Peppermint','501 Illinois Ave','Seattle','ID',83682,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(11,'Tiegen','Love','332 W Dewey Ave','Portland','ID',83672,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(12,'OJ','Simpson','501 Illinois Ave','Baler','ID',83662,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(13,'Hello','There','332 W Dewey Ave','Baker','ID',83652,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(14,'Over','Here','501 Illinois Ave','McCall','ID',83642,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(15,'Bye Bye','Falicia','332 W Dewey Ave','Sun Valley','ID',83632,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(16,'Tyson','Sides','501 Illinois Ave','Casper','ID',83619,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(17,'Katie','Mechling','332 W Dewey Ave','Lewiston','ID',83610,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(18,'JR','Simplot','501 Illinois Ave','Asotin','ID',83611,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(19,'Unique','Grow','332 W Dewey Ave','Sometown','ID',83512,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(20,'Bad','Customers','501 Illinois Ave','Anytown','ID',83532,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(21,'Tired','Of Making Up Names','332 W Dewey Ave','Nampa','ID',83686,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(22,'Last','Name','501 Illinois Ave','Council','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764');
insert into EMPLOYEES
VALUES(1,518942921,'1967-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,1,'Elaine Gross'),
(2,558942921,'1967-4-16',2,1,1,'2017-1-1','332 W Dewey','Nampa','ID',83685,null,1,'Kim Gross'),
(3,518940125,'1967-5-16',1,1,1,'2016-1-1','332 W Dewey','Nampa','ID',83612,null,1,'Billy Bob'),
(4,518941234,'1972-3-16',2,1,0,'1998-1-1','332 W Dewey','Nampa','ID',83687,null,1,'Who Me?'),
(5,518944325,'1982-3-16',4,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83612,null,0,'yes you'),
(6,519942911,'1985-3-16',5,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'not me'),
(7,516942931,'1984-3-16',3,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'then who'),
(8,515942941,'1961-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'peter pan'),
(9,514942951,'1990-3-16',0,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'wendy'),
(10,513942521,'2000-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83666,null,0,'mom'),
(11,512942621,'1999-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83666,null,1,'dad'),
(12,511942721,'1998-3-16',2,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'uncle'),
(13,510942821,'1997-3-16',3,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'aunty M'),
(14,528942121,'1969-3-16',5,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'Aunt Bee'),
(15,538941921,'1970-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'Lex Luther'),
(16,548943921,'1990-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83646,null,1,'Clark Kent'),
(17,558944921,'1991-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83636,null,1,'Lois Lane'),
(18,568945921,'1992-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83626,null,1,'Robert Gross'),
(19,578946921,'1995-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83626,null,1,'Melissa Gross'),
(20,588947921,'1996-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83286,null,1,'Beyza Dursan');
INSERT INTO SUPPLIERS
VALUES (1,'wholesale supply','here','there','ID',83686,null,'orders@wholesale.com','208-354-4512','jon jingle','CC only',null),
(2,'ricks stuff','here','there','WA',83681,null,'orders@wholesale.com','208-354-0101','jon jingle','CC only',null),
(3,'whole supply','here','there','OR',8368,null,'orders@wholesale.com','208-354-4021','jon jingle','CC only',null),
(4,'sale supply','here','there','ID',83686,null,'orders@wholesale.com','208-354-3485','jon jingle','CC only',null),
(5,'wsale supply','here','there','CO',83686,null,'orders@wholesale.com','208-354-8798','jon jingle','CC only',null),
(6,'wholesale sup','here','there','DC',83686,null,'orders@wholesale.com','208-354-7848','jon jingle','CC only',null),
(7,'wholeply','here','there','FL',83686,null,'orders@wholesale.com','208-354-9748','jon jingle','CC only',null),
(8,'wsale sup','here','there','CA',83686,null,'orders@wholesale.com','208-208-4512','jon jingle','CC only',null),
(9,'PFO supply','here','there','CA',83686,null,'orders@wholesale.com','208-253-4512','jon jingle','CC only',null),
(10,'Aquamedic supply','here','there','ID',83686,null,'orders@wholesale.com','209-354-4512','jon jingle','CC only',null),
(11,'Tuniz supply','here','there','ID',83686,null,'orders@wholesale.com','207-354-4512','jon jingle','CC only',null),
(12,'Ebo Jager supply','here','there','ID',83686,null,'orders@wholesale.com','228-354-4512','jon jingle','CC only',null),
(13,'Marine Depot supply','here','there','ID',83686,null,'orders@wholesale.com','218-354-4512','jon jingle','CC only',null),
(14,'wholesale Depot','here','there','ID',83686,null,'orders@wholesale.com','238-354-4512','jon jingle','CC only',null),
(15,'Depot supply','here','there','ID',83686,null,'orders@wholesale.com','248-354-4512','jon jingle','CC only',null),
(16,'wholy','here','there','ID',83686,null,'orders@wholesale.com','258-354-4512','jon jingle','CC only',null),
(17,'who supply','here','there','ID',83686,null,'orders@wholesale.com','268-354-4512','jon jingle','CC only',null),
(18,'wholes supply','here','there','ID',83686,null,'orders@wholesale.com','278-354-4512','jon jingle','CC only',null),
(19,'wholes sup','here','there','ID',83686,null,'orders@wholesale.com','298-354-4512','jon jingle','CC only',null),
(20,'whole supper','here','there','ID',83686,null,'orders@wholesale.com','208-324-4512','jon jingle','CC only',null),
(21,'who supplies','here','there','ID',83686,null,'orders@wholesale.com','208-314-4512','jon jingle','CC only',null),
(22,'who is here','here','there','ID',83686,null,'orders@wholesale.com','208-334-4512','jon jingle','CC only',null),
(23,'who is there','here','there','ID',83686,null,'orders@wholesale.com','208-254-4512','jon jingle','CC only',null),
(24,'who is over there','here','there','ID',83686,null,'orders@wholesale.com','208-154-4512','jon jingle','CC only',null);
INSERT INTO products
VALUES (1,'hardgood',2,null,'Light',199.99,3,1,2),
(2,'livestock',2,'med','yellow tang',20.99,5,2,1),
(3,'hardgood',2,null,'prizm skimmer',49.99,3,3,4),
(4,'livestock',10,'small','blue damsel',4.99,8,5,6),
(5,'hardgood',5,'1 inch','standard bulkhead',9.99,4,6,4),
(6,'service',0,null,'drill for bulkhead',19.99,99,8,1),
(7,'service',0,null,'install overflow',39.99,99,1,12),
(8,'hardgood',16,'1.5 inch','standard bulkhead',11.99,25,18,2),
(9,'hardgood',5,'2 inch','standard bulkhead',15.99,4,12,1),
(10,'livestock',3,'small','yellow tang',17.99,4,1,12),
(11,'livestock',3,'small','blue tang',38.99,2,16,15),
(12,'livestock',12,'med','blue tang',42.99,15,12,15),
(13,'hardgood',11,'1000','Turbofloter skimmer',199.99,25,5,13),
(14,'service',0,null,'install system',199.99,99,4,5),
(15,'hardgood',12,'3','Pump',37.99,5,3,2),
(16,'livestock',13,'3','coral frag',15.99,45,1,2),
(17,'livestock',14,'med','frogfish',29.99,2,3,16),
(18,'hardgood',15,'3/4 inch','double thread bulkhead',9.99,10,1,2),
(19,'hardgood',16,'large','pump',129.99,25,1,2),
(20,'livestock',17,'extra large','naso tang',89.99,2,3,4);
INSERT INTO ORDERS
VALUES (1,1,1,'2016-4-16'),
(2,2,1,'2017-5-1'),
(3,1,20,'2017-1-2'),
(4,12,19,'2017-2-1'),
(5,3,19,'2017-3-1'),
(6,4,18,'2017-4-1'),
(7,5,17,'2017-5-1'),
(8,5,16,'2017-6-1'),
(9,6,15,'2017-7-1'),
(10,7,14,'2017-7-2'),
(11,8,13,'2017-7-3'),
(12,9,12,'2017-7-4'),
(13,10,11,'2017-7-5'),
(14,11,11,'2017-7-6'),
(15,12,12,'2017-7-7'),
(16,13,13,'2017-7-8'),
(17,14,14,'2017-7-9'),
(18,15,15,'2017-7-10'),
(19,16,16,'2017-7-11'),
(20,17,17,'2017-7-12');
INSERT INTO ORDERDETAILS
VALUES (1,1,1),
(1,2,1),
(1,1,20),
(1,12,19),
(1,3,19),
(1,4,18),
(2,5,17),
(2,5,16),
(3,6,15),
(4,7,14),
(4,8,13),
(5,9,12),
(6,10,11),
(7,11,11),
(8,12,12),
(9,13,13),
(10,14,14),
(11,15,15),
(12,16,16),
(13,17,17),
(14,2,1),
(15,3,3),
(16,5,7),
(17,6,2),
(18,6,8),
(19,20,1),
(20,19,2),
(20,18,2),
(20,17,3),
(20,15,3);