In: Computer Science
Can you correct my database syntax so it works on rextester or dbfiddle.
Error(s), warning(s):
Column, parameter, or variable #2: Cannot find data type VARCHAR2.
CREATE TABLE Supplier
( SuppNo
CHAR(8),
SuppName VARCHAR2(30) CONSTRAINT
SuppNameRequired NOT NULL,
SuppEMail VARCHAR2(50),
SuppPhone CHAR(14),
SuppURL
VARCHAR2(100),
SuppDiscount DECIMAL(3,3),
CONSTRAINT PKSupplier PRIMARY KEY (SuppNo) );
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S2029929','ColorMeg,
Inc.','[email protected]','(720)444-1231','www.colormeg.com',0.10);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S3399214','Connex','[email protected]','(206)432-1142','www.connex.com',0.12);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S4290202','Ethlite','[email protected]','(303)213-2234','www.ethlite.com',0.05);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S4298800','Intersafe','[email protected]','(512)443-2215','www.intersafe.com',0.10);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S4420948','UV
Components','[email protected]','(303)321-0432','www.uvcomponents.com',0.08);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S5095332','Cybercx','[email protected]','(212)324-5683','www.cybercx.com',0.00);
CREATE TABLE Purchase
( PurchNo CHAR(8),
PurchDate DATE CONSTRAINT
PurchDateRequired NOT NULL,
SuppNo CHAR(8)
CONSTRAINT SuppNo2Required NOT NULL,
PurchPayMethod CHAR(6) DEFAULT 'PO',
PurchDelDate DATE,
CONSTRAINT PKPurchase PRIMARY KEY (PurchNo),
CONSTRAINT SuppNoFK2 FOREIGN KEY (SuppNo) REFERENCES Supplier
);
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P2224040','3-Feb-2017','S2029929','Credit','8-Feb-2017');
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P2345877','3-Feb-2017','S5095332','PO','11-Feb-2017');
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P3249952','4-Feb-2017','S3399214','PO','9-Feb-2017');
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P3854432','3-Feb-2017','S4290202','PO','8-Feb-2017');
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P9855443','7-Feb-2017','S4420948','PO','15-Feb-2017');
CREATE TABLE OrderTbl
( OrdNo CHAR(8),
OrdDate
DATE CONSTRAINT OrdDateRequired NOT NULL,
CustNo CHAR(8)
CONSTRAINT CustNoRequired NOT NULL,
EmpNo
CHAR(8),
OrdName VARCHAR2(50),
OrdStreet
VARCHAR2(50),
OrdCity VARCHAR2(30),
OrdState
CHAR(2),
OrdZip CHAR(10),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo),
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer,
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee );
INSERT INTO ordertbl
(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet,
OrdCity,
OrdState, OrdZip)
VALUES
('O1116324','23-Jan-2017','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','23-Jan-2017','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','10-Feb-2017','C9549302','','Todd
Hayes','1400 NW 88th','Lynnwood','WA','98036-2244');
INSERT INTO ordertbl
(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet,
OrdCity,
OrdState, OrdZip)
VALUES
('O1455122','9-Jan-2017','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','5-Jan-2017','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','23-Jan-2017','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','11-Feb-2017','C8543321','','Ron
Thompson','789 122nd St.','Renton','WA','98666-1289');
INSERT INTO ordertbl
(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet,
OrdCity,
OrdState, OrdZip)
VALUES
('O2233457','12-Jan-2017','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','14-Jan-2017','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','23-Jan-2017','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','13-Jan-2017','C1010398','','Jim
Glussman','1432 E. Ravenna','Denver','CO','80111-0033');
INSERT INTO ordertbl
(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet,
OrdCity,
OrdState, OrdZip)
VALUES
('O3377543','15-Jan-2017','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','11-Jan-2017','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','22-Jan-2017','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','20-Jan-2017','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','23-Jan-2017','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','19-Feb-2017','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','16-Jan-2017','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','23-Jan-2017','C9865874','','HelenSibley','206
McCaffrey','Renton','WA','98006-5543');
INSERT INTO ordertbl
(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet,
OrdCity,
OrdState, OrdZip)
VALUES
('O9919699','11-Feb-2017','C9857432','E9954302','Homer Wells','123
Main St.','Seattle','WA','98105-4322');
Below is modified queries for 2 tables : supplier & purchase
CREATE TABLE supplier
( SuppNo CHAR(8),
SuppName VARCHAR(30) NOT NULL,
SuppEMail VARCHAR(50),
SuppPhone CHAR(14),
SuppURL VARCHAR(100),
SuppDiscount DECIMAL(3,3),
CONSTRAINT PKSupplier PRIMARY KEY (SuppNo) );
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S2029929','ColorMeg, Inc.','[email protected]','(720)444-1231','www.colormeg.com',0.10);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S3399214','Connex','[email protected]','(206)432-1142','www.connex.com',0.12);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S4290202','Ethlite','[email protected]','(303)213-2234','www.ethlite.com',0.05);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S4298800','Intersafe','[email protected]','(512)443-2215','www.intersafe.com',0.10);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S4420948','UV Components','[email protected]','(303)321-0432','www.uvcomponents.com',0.08);
INSERT INTO supplier
(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)
VALUES('S5095332','Cybercx','[email protected]','(212)324-5683','www.cybercx.com',0.00);
CREATE TABLE purchase
( PurchNo CHAR(8),
PurchDate DATE NOT NULL,
SuppNo CHAR(8) NOT NULL,
PurchPayMethod CHAR(6) DEFAULT 'PO',
PurchDelDate DATE,
CONSTRAINT PKPurchase PRIMARY KEY (PurchNo),
CONSTRAINT SuppNoFK2 FOREIGN KEY (SuppNo) REFERENCES supplier(SuppNo) );
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P2224040','2017-02-03','S2029929','Credit','2017-02-11');
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P2345877','2017-02-03','S5095332','PO','2017-02-11');
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P3249952','2017-02-04','S3399214','PO','2017-02-09');
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P3854432','2017-02-03','S4290202','PO','2017-02-08');
INSERT INTO purchase
(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)
VALUES('P9855443','2017-02-07','S4420948','PO','2017-02-15');
To create OrderTbl table first create Customer and Employee table and add data to it because OrderTbl references Customer and Employee table. Then modify syntax for OrderTbl as below:
CREATE TABLE ordertbl
( OrdNo CHAR(8),
OrdDate DATE NOT NULL,
CustNo CHAR(8) NOT NULL,
EmpNo CHAR(8),
OrdName VARCHAR(50),
OrdStreet VARCHAR(50),
OrdCity VARCHAR(30),
OrdState CHAR(2),
OrdZip CHAR(10),
CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo),
CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer(CustNo),
CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee(EmpNo) );
And then add data to OrderTbl using below queries.
INSERT INTO ordertbl
(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,
OrdState, OrdZip)
VALUES ('O1116324','2017-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','2017-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','2017-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','2017-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','2017-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','2017-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','2017-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','2017-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','2017-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','2017-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','2017-01-13','C1010398','','Jim Glussman','1432 E. Ravenna','Denver','CO','80111-0033');
INSERT INTO ordertbl
(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,
OrdState, OrdZip)
VALUES ('O3377543','2017-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','2017-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','2017-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','2017-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','2017-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','2017-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','2017-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','2017-01-23','C9865874','','HelenSibley','206 McCaffrey','Renton','WA','98006-5543');
INSERT INTO ordertbl
(OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity,
OrdState, OrdZip)
VALUES ('O9919699','2017-02-11','C9857432','E9954302','Homer Wells','123 Main St.','Seattle','WA','98105-4322');