I am trying to run SQL statements in the Oracle 11g Express edition, where I am to create tables. Here is my SQL code:
CREATE TABLE STORE ( StoreID INT PRIMARY KEY, StoreName VARCHAR2(30) NOT NULL, City VARCHAR2(30) NOT NULL, Country VARCHAR2(30) NOT NULL CHECK Country in ('China','Egypt','United States','Spain','New Zealand','Mexico','Africa'), Phone VARCHAR2(30) NOT NULL, Fax VARCHAR2(30), Email VARCHAR2(50) UNIQUE, Contact VARCHAR2(30) NOT NULL, UNIQUE (StoreName, City) ); CREATE TABLE PURCHASE_ITEM ( PurchaseItemID INT PRIMARY KEY, StoreID INT NOT NULL REFERENCES STORE(StoreID) ON DELETE CASCADE ON UPDATE CASCADE, "Date" DATE NOT NULL, Description VARCHAR2(30) NOT NULL, Category VARCHAR2(30), PriceUsed NUMBER(15, 2) ); CREATE SEQUENCE pur_seq START WITH 500 INCREMENT BY 5; CREATE OR REPLACE TRIGGER Purchase BEFORE INSERT ON PURCHASE_ITEM FOR EACH ROW BEGIN SELECT pur_seq.NEXTVAL INTO :new.PurchaseItemID FROM dual; END; CREATE TABLE SHIPPER ( ShipperID INT PRIMARY KEY, ShipperName VARCHAR2(30) NOT NULL, Phone VARCHAR2(30) NOT NULL, Fax VARCHAR2(30), Email VARCHAR2(50) UNIQUE, Contact VARCHAR2(30) NOT NULL ); CREATE TABLE SHIPMENT ( ShipmentID INT PRIMARY KEY Auto Increment, ShipperID INT NOT NULL REFERENCES SHIPPER(ShipperID) ON DELETE CASCADE ON UPDATE CASCADE, ShipperInvoiceNumber INT NOT NULL UNIQUE, Origin VARCHAR2(30) NOT NULL, Destination VARCHAR2(30) NOT NULL, DepartureDate DATE, ArrivalDate DATE ); ALTER TABLE SHIPMENT AUTO_INCREMENT = 100; CREATE TABLE SHIPMENT_ITEM ( ShipmentID INT NOT NULL REFERENCES SHIPMENT(ShipmentID) ON DELETE CASCADE ON UPDATE CASCADE, ShipmentItemID INT NOT NULL, PurchaseItemID INT NOT NULL REFERENCES PURCHASE_ITEM(PurchaseItemID) ON DELETE CASCADE ON UPDATE CASCADE, InsuredValue NUMBER(15, 2) NOT NULL defaut 100, PRIMARY KEY (ShipmentID, ShipmentItemID) );
It only ends up processing 4 statements and I keep getting these error messages:
CREATE TABLE STORE ( StoreID INT PRIMARY K - ORA-00906: missing left parenthesis CREATE TABLE PURCHASE_ITEM ( PurchaseItemID INT - ORA-00907: missing right parenthesis CREATE SEQUENCE pur_seq START WITH 500 INCREMENT BY 5 - ORA-00955: name is already used by an existing object CREATE OR REPLACE TRIGGER Purchase BEFORE INSERT ON PURCHASE - ORA-00942: table or view does not exist
I am wholly unfamiliar with Oracle 11g. I am unsure if I am using the correct application in it for my assignment. I am only going by these instructions:
“For this assignment you are to write scripts to create tables and insert records. In oracle 11g I don’t want you to use the tools to generate the tables, you are required to write scripts to create the tables and records and will need to include the scripts for your submission.”
Please, what am I doing wrong?
Advertisement
Answer
Let me summarize the problems with your scripts
First you have to enclose the check constraint with braces like below
CREATE TABLE STORE ( StoreID INT PRIMARY KEY, StoreName VARCHAR2(30) NOT NULL, City VARCHAR2(30) NOT NULL, Country VARCHAR2(30) NOT NULL CHECK (Country in ('China','Egypt','United States','Spain','New Zealand','Mexico','Africa')), Phone VARCHAR2(30) NOT NULL, Fax VARCHAR2(30), Email VARCHAR2(50) UNIQUE, Contact VARCHAR2(30) NOT NULL, UNIQUE (StoreName, City) );
Second, there is no ON UPDATE CASCADE
in Oracle 11g so you need to remove it from the CREATE TABLE
statement
Third, there is no Auto increment
in Oracle 11G
for columns So refer this SO for a workaround
Please let me know with these corrections whether your issue is resolved