ORA-00907 DRIVING ME INSANE

Question

I am trying to finish up my lab and i cant find the missing right parenthesis that it is saying that i am missing. I have finished the course table and it is all correct but i am getting an error on every other table could someone please help?

CREATE TABLE COURSE(
  CRS_CODE VARCHAR(8) NOT NULL,
  CRS_DESCRIPTION VARCHAR(35) NOT NULL,
  CRS_CREDIT INTEGER DEFAULT 4 NOT NULL CHECK(CRS_CREDIT IN (1,2,3,4)),
  PRIMARY KEY (CRS_CODE));

CREATE TABLE CLASS (
  CRS_CODE VARCHAR(8) NOT NULL,
  CLASS_CODE INTEGER (5) NOT NULL,
  CHECK (CLASS_SECTION IN (0,1,2,3,4,5,6,7,8)),
  CLASS_TIME VARCHAR(25) NOT NULL,
  CLASS_ROOM CHAR(6),
  CRS_CODE REFERENCES COURSE(CRS_CODE),
  PRIMARY KEY (CLASS_CODE));

CREATE TABLE STUDENT(
  STU_NUM INTEGER NOT NULL,
  STU_LNAME VARCHAR(25) NOT NULL,
  STU_FNAME VARCHAR(20) NOT NULL,
  STU_INIT VARCHAR(1),
  STU_DOB DATE,
  STU_HRS INTEGER DEFAULT 0 CHECK (STU_HRS>=0 AND STU_HRS<1000),
  STU_CLASS VARCHAR(2) CHECK (STU_CLASS IN ('Fr', 'So', 'Jr', 'Sr', 'Gr')),
  STU_GPA DECIMAL(3,2) DEFAULT 0.00 CHECK (STU_GPA BETWEEN 0.00 AND 4.00),
  STU_PHONE INTEGER(4),
  PRIMARY KEY (STU_NUM));

CREATE TABLE ENROLL(
  ENROLL_GRADE VARCHAR(1) DEFAULT 'Z' CHECK (ENROLL_GRADE IN('A','B','C','D','F','I','W','Z')),
  STU_NUM INTEGER REFERENCES STUDENT(STU_NUM),
  CLASS_CODE INTEGER (5) REFERENCES CLASS(CLASS_CODE));

Answer 1

This is too long for a comment.

Part of the purpose of labs is so you learn how to debug your code. Trial and error in the academic environment is much more efficient than in the "real world".

To debug, simplify! The first question is: Where does the error occur? Does it occur in the first table creation? In the second? And so on. You can determine this by creating the first table. Then the first two tables, and so on.

Once you have the table, you may be able to quickly spot the error. If not, you can comment out the column definitions one at a time.

When you do this, you will find at least two errors:

• A column that is referenced in a constraint without being defined.
• A data type that is incorrectly given a length parameter.

Also, if you are using Oracle, the recommended type for strings is varchar2(), not varchar().

Answer 2

I think you forgot a column

CREATE TABLE CLASS (
  CRS_CODE VARCHAR(8) NOT NULL,
  CLASS_CODE INTEGER (5) NOT NULL,
  CHECK (CLASS_SECTION IN (0,1,2,3,4,5,6,7,8)),
  CLASS_TIME VARCHAR(25) NOT NULL,
  CLASS_ROOM CHAR(6),
  CRS_CODE REFERENCES COURSE(CRS_CODE),
  PRIMARY KEY (CLASS_CODE));

ORA-00904: "CLASS_SECTION": invalid identifier

Answer 3

Here is a table-by-table analysis of the problems with your code.

Table COURSE: ok

Table CLASS:

CLASS_CODE INTEGER(5) NOT NULL

The INTEGER datatype does not accept a length

CHECK (CLASS_SECTION IN (0,1,2,3,4,5,6,7,8))

Column CLASS_SECTION does not exists in the table. I imagine that you meant CLASS_CODE.

CRS_CODE REFERENCES COURSE(CRS_CODE)

The declaration of the foreign key constraint should be placed directly in the line that defines the referring column.

Table STUDENT:

STU_PHONE INTEGER(4)

See above

Table ENROLL:

CLASS_CODE INTEGER (5)

See above.

---

Once these issues are fixed, the 4 tables can be successfully created.

Demo on DB Fiddle:

CREATE TABLE COURSE(
  CRS_CODE VARCHAR(8) NOT NULL,
  CRS_DESCRIPTION VARCHAR(35) NOT NULL,
  CRS_CREDIT INTEGER DEFAULT 4 NOT NULL CHECK(CRS_CREDIT IN (1,2,3,4)),
  PRIMARY KEY (CRS_CODE));

CREATE TABLE CLASS (
  CRS_CODE VARCHAR(8) NOT NULL REFERENCES COURSE(CRS_CODE),
  CLASS_CODE INTEGER NOT NULL CHECK (CLASS_CODE IN (0,1,2,3,4,5,6,7,8)),
  CLASS_TIME VARCHAR(25) NOT NULL,
  CLASS_ROOM CHAR(6),
  PRIMARY KEY (CLASS_CODE)
);

CREATE TABLE STUDENT(
  STU_NUM INTEGER NOT NULL,
  STU_LNAME VARCHAR(25) NOT NULL,
  STU_FNAME VARCHAR(20) NOT NULL,
  STU_INIT VARCHAR(1),
  STU_DOB DATE,
  STU_HRS INTEGER DEFAULT 0 CHECK (STU_HRS>=0 AND STU_HRS<1000),
  STU_CLASS VARCHAR(2) CHECK (STU_CLASS IN ('Fr', 'So', 'Jr', 'Sr', 'Gr')),
  STU_GPA DECIMAL(3,2) DEFAULT 0.00 CHECK (STU_GPA BETWEEN 0.00 AND 4.00),
  STU_PHONE INTEGER,
  PRIMARY KEY (STU_NUM));

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