Use this script for the assignment.
Please follow instructions as to what to turn in.
USE MYSQL ONLY.
# orderentrydbScript.sql
# REV 3 Updated 05/15/2017 Added employee comm pct to employee name Theresa Beck
# Script to build the Order Entry Database
# Creates tables and inserts data for this assignment
# into an already open database.
#
# Drop database if exists for a clean copy
Drop database if exists orderentrydb;
# Assumes student has created a database and activated it.
# Create Database before you begin to populate data
Create Database orderentrydb;
Use orderentrydb;
# Remove tables if they already exist
# Useful if it’s not your first time trying this script
DROP TABLE if exists ordline ;
Drop TABLE if exists OrderTBL ;
DROP TABLE if exists Customer ;
DROP TABLE if exists Employee ;
DROP TABLE if exists Product ;
# Create the Product Table
CREATE TABLE Product
( ProdNo CHAR(8)
ProdName VARCHAR(50) NOT NULL
ProdMfg varchar(20) NOT NULL
ProdQOH decimal(102)
ProdPrice DECIMAL(122)
ProdNextShipDate DATE
PRIMARY KEY (ProdNo) );
# Put data into the Product Table
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P0036566”17 inch Color Monitor”ColorMeg Inc.’12’2007-02-20’169.00);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P0036577”19 inch Color Monitor”ColorMeg Inc.’10’2007-02-20’319.00);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P1114590”R3000 Color Laser Printer”Connex’5’2007-01-22’699.00);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P1412138”10 Foot Printer Cable”Ethlite’100null12.00);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P1445671”8-Outlet Surge Protector”Intersafe’33null14.99);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P1556678”CVP Ink Jet Color Printer”Connex’8 ‘2007-01-22’99.00);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P3455443”Color Ink Jet Cartridge”Connex’24’2007-01-22’38.00);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P6677900”Black Ink Jet Cartridge”Connex’44null25.69);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P9995676”Battery Back-up System”Cybercx’12’2007-02-01’89.00);
INSERT INTO product
(ProdNo ProdName ProdMfg ProdQOH ProdNextShipDate ProdPrice)
VALUES (‘P4200344”36-Bit Color Scanner”UV Components’16’2007-01-29’199.99);
# Create the Employee Table
CREATE TABLE Employee
( EmpNo varCHAR(8)
EmpFirstName varchar(20) NOT NULL
EmpLastName varchar(30) NOT NULL
EmpPhone varCHAR(15)
EmpEMail VARCHAR(50) NOT NULL
SupEmpNo varCHAR(8) REFERENCES Employee (SupEmpNo)
EmpCommRateDECIMAL(33)
PRIMARY KEY (EmpNo)
UNIQUE(EmpEMail))
;
# Put data into the Employee table
INSERT INTO employee (EmpNo EmpFirstName EmpLastName EmpPhone EmpEMail
SupEmpNo EmpCommRate)
VALUES (‘E9884325”Thomas”Johnson”(303) 556-9987””’0.05);
INSERT INTO employee
(EmpNo EmpFirstName EmpLastName EmpPhone EmpEMail
SupEmpNo EmpCommRate)
VALUES (‘E8843211”Amy”Tang”(303) 556-4321””E9884325’0.04);
INSERT INTO employee
(EmpNo EmpFirstName EmpLastName EmpPhone EmpEMail
SupEmpNo EmpCommRate)
VALUES (‘E9345771”Colin”White”(303) 221-4453””E9884325’0.04);
INSERT INTO employee
(EmpNo EmpFirstName EmpLastName EmpPhone EmpEMail
SupEmpNo EmpCommRate)
VALUES (‘E1329594”Landi”Santos”(303) 789-1234””E8843211’0.02);
INSERT INTO employee
(EmpNo EmpFirstName EmpLastName EmpPhone EmpEMail
SupEmpNo EmpCommRate)
VALUES (‘E8544399”Joe”Jenkins”(303) 221-9875””E8843211’0.02);
INSERT INTO employee
(EmpNo EmpFirstName EmpLastName EmpPhone EmpEMail
SupEmpNo EmpCommRate)
VALUES (‘E9954302”Mary”Hill”(303) 556-9871””E8843211’0.02);
INSERT INTO employee
(EmpNo EmpFirstName EmpLastName EmpPhone EmpEMail
SupEmpNo)
VALUES (‘E9973110”Theresa”Beck”(720) 320-2234””E9884325′);
# update employee commission rate for assignment 5.2 – change requested 12/3/2016
Update employee set empcommrate=0.01 where empno=’E9973110′ and empfirstname=’Theresa’;
# Create the Customer Table
CREATE TABLE Customer
( CustNo varCHAR(8)
CustFirstName varchar(20) NOT NULL
CustLastName varchar(30) NOT NULL
CustStreetvarchar(50)
CustCityvarchar(30)
CustStatevarCHAR(2)
CustZipvarCHAR(10)
CustBalDECIMAL(122)
PRIMARY KEY (CustNo) );
# Put data into the Customer table
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C0954327”Sheri”Gordon”336 Hill St.”Littleton”CO”80129-5543’230.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C1010398”Jim”Glussman”1432 E. Ravenna”Denver”CO”80111-0033’200.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C2388597”Beth”Taylor”2396 Rafter Rd”Seattle”WA”98103-1121’500.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C3340959”Betty”Wise”4334 153rd NW”Seattle”WA”98178-3311’200.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C3499503”Bob”Mann”1190 Lorraine Cir.”Monroe”WA”98013-1095’0.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C8543321”Ron”Thompson”789 122nd St.”Renton”WA”98666-1289’85.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C8574932”Wally”Jones”411 Webber Ave.”Seattle”WA”98105-1093’1500.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C8654390”Candy”Kendall”456 Pine St.”Seattle”WA”98105-3345’50.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C9128574”Jerry”Wyatt”16212 123rd Ct.”Denver”CO”80222-0022’100.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C9403348”Mike”Boren”642 Crest Ave.”Englewood”CO”80113-5431’0.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C9432910”Larry”Styles”9825 S. Crest Lane”Bellevue”WA”98104-2211’250.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C9543029”Sharon”Johnson”1223 Meyer Way”Fife”WA”98222-1123’856.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C9549302”Todd”Hayes”1400 NW 88th”Lynnwood”WA”98036-2244’0.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C9857432”Homer”Wells”123 Main St.”Seattle”WA”98105-4322’500.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C9865874”Mary”Hill”206 McCaffrey”Littleton”CO”80129-5543’150.00);
INSERT INTO customer
(CustNo CustFirstName CustLastName CustStreet CustCity
CustState CustZip CustBal)
VALUES(‘C9943201”Harry”Sanders”1280 S. Hill Rd.”Fife”WA”98222-2258’1000.00);
# Create the OrderTbl table
CREATE TABLE OrderTbl
( OrdNo varchar(8)
OrdDate DATE NOT NULL
CustNo varchar(8) NOT NULL REFERENCES Customer(custNo)
EmpNo varchar(8) REFERENCES Employee (EmpNo)
OrdName varchar(50)
OrdStreet varchar(50)
OrdCity varchar(30)
OrdState varchar(2)
OrdZip varchar(10)
PRIMARY KEY (OrdNo)
);
# Put data into the OrderTbl table
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O1116324”2007-01-23”C0954327”E8544399”Sheri Gordon”336 Hill St.”Littleton”CO”80129-5543’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O1231231”2007-01-23”C9432910”E9954302”Larry Styles”9825 S. Crest Lane”Bellevue”WA”98104-2211’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O1241518”2007-02-10”C9549302””Todd Hayes”1400 NW 88th”Lynnwood”WA”98036-2244’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O1455122”2007-01-09”C8574932”E9345771”Wally Jones”411 Webber Ave.”Seattle”WA”98105-1093’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O1579999”2007-01-05”C9543029”E8544399”Tom Johnson”1632 Ocean Dr.”Des Moines”WA”98222-1123’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O1615141”2007-01-23”C8654390”E8544399”Candy Kendall”456 Pine St.”Seattle”WA”98105-3345’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O1656777”2007-02-11”C8543321””Ron Thompson”789 122nd St.”Renton”WA”98666-1289’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O2233457”2007-01-12”C2388597”E9884325”Beth Taylor”2396 Rafter Rd”Seattle”WA”98103-1121’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O2334661”2007-01-14”C0954327”E1329594”Mrs. Ruth Gordon”233 S. 166th”Seattle”WA”98011’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O3252629”2007-01-23”C9403348”E9954302”Mike Boren”642 Crest Ave.”Englewood”CO”80113-5431’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O3331222”2007-01-15”C1010398””Jim Glussman”1432 E. Ravenna”Denver”CO”80111-0033’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O3377543”2007-01-15”C9128574”E8843211”Jerry Wyatt”16212 123rd Ct.”Denver”CO”80222-0022’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O4714645”2007-01-11”C2388597”E1329594”Beth Taylor”2396 Rafter Rd”Seattle”WA”98103-1121’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O5511365”2007-01-22”C3340959”E9884325”Betty White”4334 153rd NW”Seattle”WA”98178-3311’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O6565656”2007-01-20”C9865874”E8843211”Mr. Jack Sibley”166 E. 344th”Renton”WA”98006-5543’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O7847172”2007-01-23”C9943201””Harry Sanders”1280 S. Hill Rd.”Fife”WA”98222-2258’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O7959898”2007-02-19”C8543321”E8544399”Ron Thompson”789 122nd St.”Renton”WA”98666-1289’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O7989497”2007-01-16”C3499503”E9345771”Bob Mann”1190 Lorraine Cir.”Monroe”WA”98013-1095’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O8979495”2007-01-23”C9865874””HelenSibley”206 McCaffrey”Renton”WA”98006-5543’);
INSERT INTO ordertbl
(OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity
OrdState OrdZip)
VALUES (‘O9919699”2007-02-11”C9857432”E9954302”Homer Wells”123 Main St.”Seattle”WA”98105-4322’);
# Create the OrdLine table
CREATE TABLE OrdLine
( OrdNo CHAR(8)
ProdNoCHAR(8)
QtyINTEGER DEFAULT 1
CONSTRAINT PKOrdLine PRIMARY KEY (OrdNo ProdNo)
CONSTRAINT FKOrdNo FOREIGN KEY (OrdNo) REFERENCES OrderTbl(OrdNo)
ON DELETE CASCADE
CONSTRAINT FKProdNo FOREIGN KEY (ProdNo) REFERENCES Product(ProdNo) );
# Put data into the OrdLine table
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1116324”P1445671’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1231231”P0036566’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1231231”P1445671’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1241518”P0036577’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1455122”P4200344’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1579999”P1556678’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1579999”P6677900’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1579999”P9995676’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1615141”P0036566’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1615141”P1445671’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1615141”P4200344’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1656777”P1445671’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O1656777”P1556678’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O2233457”P0036577’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O2233457”P1445671’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O2334661”P0036566’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O2334661”P1412138’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O2334661”P1556678’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O3252629”P4200344’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O3252629”P9995676’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O3331222”P1412138’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O3331222”P1556678’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O3331222”P3455443’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O3377543”P1445671’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O3377543”P9995676’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O4714645”P0036566’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O4714645”P9995676’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O5511365”P1412138’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O5511365”P1445671’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O5511365”P1556678’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O5511365”P3455443’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O5511365”P6677900’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O6565656”P0036566’10);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7847172”P1556678’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7847172”P6677900’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7959898”P1412138’5);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7959898”P1556678’5);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7959898”P3455443’5);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7959898”P6677900’5);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7989497”P1114590’2);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7989497”P1412138’2);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O7989497”P1445671’3);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O8979495”P1114590’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O8979495”P1412138’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O8979495”P1445671’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O9919699”P0036577’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O9919699”P1114590’1);
INSERT INTO ordline
(OrdNo ProdNo Qty)
VALUES(‘O9919699”P4200344’1);