ORA-0097 error oracle sql

Question

I continue to get "ORA-0097 Missing right parenthesis" errors and I can't seem to understand where my code is going wrong. Any help is greatly appreciated! Thanks.

CREATE TABLE COURSE(
    CRS_CODE char(8) NOT NULL,
    CRS_DESCRIPTION char(35),
    CRS_CREDIT smallint(5) DEFAULT(4),
    CONSTRAINT CRS_CODE PRIMARY KEY (CRS_CODE),
);

CREATE TABLE CLASS(
    CLASS_CODE smallint(4) NOT NULL,
    CLASS_SECTION integer,
    CLASS_ROOM char(5),
    CLASS_ROOM char(25),
    CRS_CODE char FOREIGN KEY REFERENCES COURSE(CRS_CODE),
    CONSTRAINT CLASS_CODE PRIMARY KEY (CLASS_CODE),
);

CREATE TABLE STUDENT(
    STU_NUM int NOT NULL,
    STU_LNAME char(25),
    STU_FNAME char(20),
    STU_INIT char(1),
    STU_DOB date,
    STU_HRS integer(1000) DEFAULT(0) CHECK (STU_HRS >= 0 AND STU_HRS < 1000),
    STU_CLASS char(2) CHECK (STU_CLASS = "FR" OR STU_CLASS = "SO" OR STU_CLASS = "JR" OR STU_CLASS = "SR" OR STU_CLASS = "GR"),
    STU_GPA decimal(1,2) DEFAULT(0.00) CHECK (STU_GPA BETWEEN 0.00 AND 4.00),
    STU_PHONE smallint(5),
    CONSTRAINT STU_NUM PRIMARY KEY (STU_NUM),
);

CREATE TABLE ENROLL(
    ENROLL_GRADE char(1) DEFAULT "Z" CHECK (ENROLL_GRADE = "A" OR ENROLL_GRADE = "B" OR ENROLL_GRADE = "C" OR ENROLL_GRADE = "D" OR ENROLL_GRADE = "F" OR ENROLL_GRADE = "I" OR ENROLL_GRADE = "W" OR ENROLL_GRADE = "Z"),
    STU_NUM int FOREIGN KEY REFERENCES STUDENT(STU_NUM),
    CLASS_CODE smallint FOREIGN KEY REFERENCES CLASS(CLASS_CODE),
);

why do I get ORA-0097 error?

Answer 1

There are multiple errors in your code.

• There is no integer(1000) Oracle. integer does not take a "size" argument (and it's just an alias for number)
• There is no smallint in Oracle: https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF00213
• character constants need to be enclosed with single quotes in SQL, not double quotes.
  "FR" is a column name 'FR' is a character constant. See Basic elements of Oracle SQL in the manual
• the data type of a column used as a foreign key must match the data type of the referenced primary key column.
• an "inline" foreign key (one defined together with the column rather then at the end of the table) does not need the foreign key keyword.
• you also had several trailing commas at the end of the table definition - before the closing ) parentheses of the CREATE TABLE statement

Not errors, but:

• the multiple OR conditions for the check constraints can be simplified to an IN (...) condition.
• you should avoid the char data type. Use varchar (or varchar2 instead)

Putting it all together you get something like:

CREATE TABLE COURSE
(
  CRS_CODE         varchar2(8) NOT NULL,
  CRS_DESCRIPTION  varchar2(35),
  CRS_CREDIT       number(5) DEFAULT 4,
  CONSTRAINT PK_CRS_CODE PRIMARY KEY (CRS_CODE)
);

CREATE TABLE CLASS
(
  CLASS_CODE     number(4) NOT NULL,
  CLASS_SECTION  integer,
  CLASS_ROOM     varchar2(25),
  CRS_CODE       varchar2(8) REFERENCES COURSE(CRS_CODE),
  CONSTRAINT PK_CLASS_CODE PRIMARY KEY (CLASS_CODE)
);

CREATE TABLE STUDENT
(
  STU_NUM    integer NOT NULL,
  STU_LNAME  varchar2(25),
  STU_FNAME  varchar2(20),
  STU_INIT   varchar2(1),
  STU_DOB    date,
  STU_HRS    integer DEFAULT 0 CHECK (STU_HRS >= 0 AND STU_HRS < 1000),
  STU_CLASS  varchar2(2) CHECK (STU_CLASS IN ('FR', 'SO', 'JR', 'SR', 'GR')),
  STU_GPA    decimal(1,2) DEFAULT 0.00 CHECK (STU_GPA BETWEEN 0.00 AND 4.00),
  STU_PHONE  number(5),
  CONSTRAINT PK_STU_NUM PRIMARY KEY (STU_NUM)
);

CREATE TABLE ENROLL
(
  ENROLL_GRADE varchar2(1) DEFAULT 'Z' CHECK (ENROLL_GRADE IN ('A', 'B', 'C', 'D', 'F', 'I', 'W', 'Z')),
  STU_NUM      integer REFERENCES STUDENT(STU_NUM),
  CLASS_CODE   number(4) REFERENCES CLASS(CLASS_CODE)
);