help with PL/SQL

[H White]

I am having Issues with my SQL coding. Can someone have a look and let me know where Ive gone wrong

CREATE TABLE DEPARTMENT (
DEPARTMENT_ID VARCHAR2(20),
STAFF_CATEGORY VARCHAR2(50),
SUPPLIER_ID VARCHAR2(20),
CONSTRAINT DEPARTMENT_PRIMARY_KEY PRIMARY KEY (DEPARTMENT_ID),
CONSTRAINT DEPARTMENT_FOREIGN_KEY FOREIGN KEY(SUPPLIER_ID) REFERENCES SUPPLIER(SUPPLIER_ID));

CREATE TABLE SUPPLIER (
SUPPLIER_ID VARCHAR2(20),
NAME VARCHAR2(50),
ADDRESS VARCHAR2(50),
CITY VARCHAR2(50),
POST_CODE VARCHAR2(50),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT SUPPLIER_PRIMARY_KEY PRIMARY KEY (SUPPLIER_ID)));

CREATE TABLE EMPLOYEES (
EMPLOYEE_ID VARCHAR2(20),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
STREET_ADDRESS VARCHAR2(50),
CITY VARCHAR2(50),
POST_CODE VARCHAR2(50),
PHONE_NUMBER VARCHAR2(50),
WORK_NUMBER VARCHAR2(50),
NI_NUMBER VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT EMPLOYEES_PRIMARY_KEY PRIMARY KEY (EMPLOYEE_ID)));

CREATE TABLE PAYROLL (
PAYROLL_ID VARCHAR2(20),
EMPLOYEE_ID VARCHAR2(20),
PAY_SCALE VARCHAR2(20),
CONSTRAINT PAYROLL_PRIMARY_KEY PRIMARY KEY (PAYROLL_ID),
CONSTRAINT PAYROLL_FOREIGN_KEY FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEES(EMOLOYEE_ID));

CREATE TABLE ACCOMMODATION (
ACCOMMODATION_ID VARCHAR2(20),
COURSE_ID VARCHAR2(20),
VARCHAR2(50),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
TYPE VARCHAR2(50),
ADDRESS VARCHAR2(50),
CITY VARCHAR2(50),
COUNTRY VARCHAR2(50),
POST_CODE VARCHAR2(50),
PHONE_NUMBER VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT ACCOMMODATION_PRIMARY_KEY PRIMARY KEY (DEPARTMENT_ID),
CONSTRAINT ACCOMMODATION_FOREIGN_KEY FOREIGN KEY(COURSE_ID) REFERENCES COURSE (COURSE_ID));

CREATE TABLE INVOICE (
INVOICE_ID VARCHAR2(20),
EMPLOYEE_ ID VARCHAR2(20),
INVOICE_DATE DATE CONSTRAINT INVOICE_DATE_NOT_NULL NOT NULL,
INVOICE_TOTAL VARCHAR2(20),
CONSTRAINT INVOICE_PRIMARY_KEY PRIMARY KEY (INVOICE_ID),
CONSTRAINT INVOICE_FOREIGN_KEY FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEE (EMPLOYEE_ID));

CREATE TABLE ORDERS (
ORDER_ID VARCHAR2(20),
CUSTOMER_ID VARCHAR2(20),
ITEM_NAME VARCHAR2(20),
ITEM_PRICE VARCHAR2(20),
CONSTRAINT ORDERS_PRIMARY_KEY PRIMARY KEY (ORDER_ID),
CONSTRAINT ORDERS_FOREIGN_KEY FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID));

CREATE TABLE BOOKING (
BOOKING_ID VARCHAR2(20),
CUSTOMER_ID CONSTRAINT FOR_KEY_CUSTOMER_IDVARCHAR2(20),
ACCOMMODATION_ID CONSTRAINT FOR_KEY_ACCOMMODATION_IDVARCHAR2(20),
CONSTRAINT BOOKING_PK PRIMARY KEY (BOOKING_ID)));

CREATE TABLE COURSE (
COURSE_ID VARCHAR2(20),
ORDER_ID VARCHAR2(20),
COURSE_TYPE VARCHAR2(20),
ADVENTURE_TYPE VARCHAR2(20),
START_DATE VARCHAR2(20),
END_DATE VARCHAR2(20),
CONSTRAINT COURSE_PRIMARY_KEY PRIMARY KEY (COURSE_ID),
CONSTRAINT COURSE_FOREIGN_KEY FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID));

CREATE TABLE CUSTOMER (
CUSTOMER_ID VARCHAR2(20),
ORDER_ID CONSTRAINT FOR_KEY_ORDER_IDVARCHAR2(20),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
STREET_NAME VARCHAR2(50),
CITY VARCHAR2(50),
COUNTRY VARCHAR2(50),
POST_CODE VARCHAR2(50),
DAY_PHONE VARCHAR2(50),
NIGHT_PHONE VARCHAR2(50),
DOB VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTOMER_ID),
CONSTRAINT CUSTOMER_FOREIGN_KEY FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID));

[Jeanne Boyarsky]

That looks like regular SQL (DDL to create tables), not PL/SQL (which is used for stored procedures.)

In any case, what error are you getting?

[H White]

Error starting at line : 18 in command -
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID VARCHAR2(20),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
STREET_ADDRESS VARCHAR2(50),
CITY VARCHAR2(50),
POST_CODE VARCHAR2(50),
PHONE_NUMBER VARCHAR2(50),
WORK_NUMBER VARCHAR2(50),
NI_NUMBER VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT EMPLOYEES_PRIMARY_KEY PRIMARY KEY (EMPLOYEE_ID)))
Error report -
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:   
*Action:
Error starting at line : 1 in command -
CREATE TABLE DEPARTMENT (
DEPARTMENT_ID VARCHAR2(20),
STAFF_CATEGORY VARCHAR2(50),
SUPPLIER_ID VARCHAR2(20),
CONSTRAINT DEPARTMENT_PRIMARY_KEY PRIMARY KEY (DEPARTMENT_ID),
CONSTRAINT DEPARTMENT_FOREIGN_KEY FOREIGN KEY(SUPPLIER_ID) REFERENCES SUPPLIER(SUPPLIER_ID))
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:   
*Action:

Error starting at line : 8 in command -
CREATE TABLE SUPPLIER (
SUPPLIER_ID VARCHAR2(20),
NAME VARCHAR2(50),
ADDRESS VARCHAR2(50),
CITY VARCHAR2(50),
POST_CODE VARCHAR2(50),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT SUPPLIER_PRIMARY_KEY PRIMARY KEY (SUPPLIER_ID)))
Error report -
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:   
*Action:

Error starting at line : 18 in command -
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID VARCHAR2(20),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
STREET_ADDRESS VARCHAR2(50),
CITY VARCHAR2(50),
POST_CODE VARCHAR2(50),
PHONE_NUMBER VARCHAR2(50),
WORK_NUMBER VARCHAR2(50),
NI_NUMBER VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT EMPLOYEES_PRIMARY_KEY PRIMARY KEY (EMPLOYEE_ID)))
Error report -
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:   
*Action:

Error starting at line : 31 in command -
CREATE TABLE PAYROLL (
PAYROLL_ID VARCHAR2(20),
EMPLOYEE_ID VARCHAR2(20),
PAY_SCALE VARCHAR2(20),
CONSTRAINT PAYROLL_PRIMARY_KEY PRIMARY KEY (PAYROLL_ID),
CONSTRAINT PAYROLL_FOREIGN_KEY FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEES(EMOLOYEE_ID))
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:   
*Action:

Error starting at line : 38 in command -
CREATE TABLE ACCOMMODATION (
ACCOMMODATION_ID VARCHAR2(20),
COURSE_ID VARCHAR2(20),
VARCHAR2(50),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
TYPE VARCHAR2(50),
ADDRESS VARCHAR2(50),
CITY VARCHAR2(50),
COUNTRY VARCHAR2(50),
POST_CODE VARCHAR2(50),
PHONE_NUMBER VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT ACCOMMODATION_PRIMARY_KEY PRIMARY KEY (DEPARTMENT_ID),
CONSTRAINT ACCOMMODATION_FOREIGN_KEY FOREIGN KEY(COURSE_ID) REFERENCES COURSE (COURSE_ID))
Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:   
*Action:

Error starting at line : 54 in command -
CREATE TABLE INVOICE (
INVOICE_ID VARCHAR2(20),
EMPLOYEE_ ID VARCHAR2(20),
INVOICE_DATE DATE CONSTRAINT INVOICE_DATE_NOT_NULL NOT NULL,
INVOICE_TOTAL VARCHAR2(20),
CONSTRAINT INVOICE_PRIMARY_KEY PRIMARY KEY (INVOICE_ID),
CONSTRAINT INVOICE_FOREIGN_KEY FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEE (EMPLOYEE_ID))
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:   
*Action:

Error starting at line : 62 in command -
CREATE TABLE ORDERS (
ORDER_ID VARCHAR2(20),
CUSTOMER_ID VARCHAR2(20),
ITEM_NAME VARCHAR2(20),
ITEM_PRICE VARCHAR2(20),
CONSTRAINT ORDERS_PRIMARY_KEY PRIMARY KEY (ORDER_ID),
CONSTRAINT ORDERS_FOREIGN_KEY FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID))
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:   
*Action:

Error starting at line : 70 in command -
CREATE TABLE BOOKING (
BOOKING_ID VARCHAR2(20),
CUSTOMER_ID CONSTRAINT FOR_KEY_CUSTOMER_IDVARCHAR2(20),
ACCOMMODATION_ID CONSTRAINT FOR_KEY_ACCOMMODATION_IDVARCHAR2(20),
CONSTRAINT BOOKING_PK PRIMARY KEY (BOOKING_ID)))
Error report -
SQL Error: ORA-02253: constraint specification not allowed here
02253. 00000 -  "constraint specification not allowed here"
*Cause:    Constraint specification is not allowed here in the statement.
*Action:   Remove the constraint specification from the statement.

Error starting at line : 76 in command -
CREATE TABLE COURSE (
COURSE_ID VARCHAR2(20),
ORDER_ID VARCHAR2(20),
COURSE_TYPE VARCHAR2(20),
ADVENTURE_TYPE VARCHAR2(20),
START_DATE VARCHAR2(20),
END_DATE VARCHAR2(20),
CONSTRAINT COURSE_PRIMARY_KEY PRIMARY KEY (COURSE_ID),
CONSTRAINT COURSE_FOREIGN_KEY FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID))
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:   
*Action:

Error starting at line : 86 in command -
CREATE TABLE CUSTOMER (
CUSTOMER_ID VARCHAR2(20),
ORDER_ID CONSTRAINT FOR_KEY_ORDER_IDVARCHAR2(20),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
STREET_NAME VARCHAR2(50),
CITY VARCHAR2(50),
COUNTRY VARCHAR2(50),
POST_CODE VARCHAR2(50),
DAY_PHONE VARCHAR2(50),
NIGHT_PHONE VARCHAR2(50),
DOB VARCHAR2(50),
EMAIL VARCHAR2(50),
CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTOMER_ID),
CONSTRAINT CUSTOMER_FOREIGN_KEY FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID))
Error report -
SQL Error: ORA-02253: constraint specification not allowed here
02253. 00000 -  "constraint specification not allowed here"
*Cause:    Constraint specification is not allowed here in the statement.
*Action:   Remove the constraint specification from the statement.

[Jeanne Boyarsky]

The SQL in the first post and the most recent are not the same.  Try running the statements one at a time to troubleshoot. Does this work? (Make sure to include the semi-colon).

CREATE TABLE EMPLOYEES ( EMPLOYEE_ID VARCHAR2(20), FIRST_NAME VARCHAR2(50), LAST_NAME VARCHAR2(50), STREET_ADDRESS VARCHAR2(50), CITY VARCHAR2(50), POST_CODE VARCHAR2(50), PHONE_NUMBER VARCHAR2(50), WORK_NUMBER VARCHAR2(50), NI_NUMBER VARCHAR2(50), EMAIL VARCHAR2(50), CONSTRAINT EMPLOYEES_PRIMARY_KEY PRIMARY KEY (EMPLOYEE_ID))) ;

If that gives the same error, try these simpler ones (remember to run DROP TABLE EMPLOYEES in between)

CREATE TABLE EMPLOYEES ( EMPLOYEE_ID VARCHAR2(20), FIRST_NAME VARCHAR2(50), LAST_NAME VARCHAR2(50), STREET_ADDRESS VARCHAR2(50), CITY VARCHAR2(50), POST_CODE VARCHAR2(50), PHONE_NUMBER VARCHAR2(50), WORK_NUMBER VARCHAR2(50), NI_NUMBER VARCHAR2(50), EMAIL VARCHAR2(50)) ;

CREATE TABLE EMPLOYEES ( EMPLOYEE_ID VARCHAR2(20)) ;

Knowing which of these work and which don't will narrow down the problem nicely. (My guess is they will all work and you were just missing the semi-colon)

[Dave Tolls]

Your brackets don't seem to match.
At least for the Employee and Supplier ones.

After that, you seem to be attempting to create the Department table before the Supplier table, which it references, which is not allowed.

That's as far as I got.

So, check your brackets, and ensure you are creating your tables in the right order.