1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please help me correct it...

Discussion in 'SQL PL/SQL' started by Richard Do, Oct 2, 2012.

  1. Richard Do

    Richard Do Guest

    Code (SQL):
    CREATE TABLE STAFF (
    Staff_ID CHAR(10) NOT NULL,
    Name CHAR(30) NOT NULL,
    Phone_Num INTEGER NOT NULL,
    Email CHAR(40) NOT NULL,
    Office_Num INTEGER NOT NULL,
    Activity_Code VARCHAR(10) NOT NULL,
    CONSTRAINT STAFF_PK PRIMARY KEY(Staff_ID)
    )

    CREATE TABLE DEPARTMENT (
    Department_Name  CHAR(10)   NOT NULL,
    Staff_ID CHAR(10) NOT NULL,
    )

    CREATE TABLE STUDENT (
    Student_ID VARCHAR(10) NOT NULL,
    Name VARCHAR(30) NOT NULL,
    Gender VARCHAR(10) NOT NULL,
    Date_Of_Birth VARCHAR(20) NOT NULL,
    Date_Amitted VARCHAR(20) NOT NULL,
    CONSTRAINT STUDENT_PK PRIMARY KEY(Student_ID)
    )

    CREATE TABLE COURSE (
    Course_ID VARCHAR(10) NOT NULL,
    Course_Title VARCHAR(30) NOT NULL,
    Course_Coordinator VARCHAR(30) NOT NULL,
    Student_ID VARCHAR(10) NOT NULL,
    Student_ID VARCHAR(10) NOT NULL,
    Unit_Code VARCHAR(10) NOT NULL,
    CONSTRAINT  STUDENT_FK FOREIGN KEY (STUDENT_ID)
    REFERENCES STUDENT(STUDENT_ID),
    CONSTRAINT  UNIT_FK FOREIGN KEY (UNIT_CODE)
    REFERENCES UNIT(UNIT_ID),
    CONSTRAINT COURSE_PK PRIMARY KEY(Course_ID)
    )

    CREATE TABLE ENROLMENT(
    Student_ID VARCHAR(10) NOT NULL,
    Course_ID VARCHAR(10) NOT NULL,
    CONSTRAINT STUDENT_PK PRIMARY KEY(Student_ID)
    REFERENCES STUDENT(Student_ID),
    CONSTRAINT COURSE_PK PRIMARY KEY(Course_ID)
    REFERENCES COURSE(Course_ID),
    CONSTRAINT ENROLMENT_PK PRIMARY KEY(Course_ID, Student_ID)
    );

    CREATE TABLE UNIT(
    Unit_Code VARCHAR(10) NOT NULL,
    Unit_Title VARCHAR(30) NOT NULL,
    Student_ID VARCHAR(10) NOT NULL,
    Activity_Code VARCHAR(10) NOT NULL,
    CONSTRAINT  ACTIVITY_FK FOREIGN KEY (ACTIVITY_CODE)
    REFERENCES ACTIVITY(ACTIVITY_CODE),
    CONSTRAINT UNIT_PK PRIMARY KEY(Unit_Code)
    )

    CREATE TABLE STUDENT_UNIT(
    Student_ID VARCHAR(10) NOT NULL,
    Unit_ID VARCHAR(10) NOT NULL,
    CONSTRAINT STUDENT_PK PRIMARY KEY(Student_ID)
    REFERENCES STUDENT(Student_ID),
    CONSTRAINT UNIT_PK PRIMARY KEY(Unit_ID)
    REFERENCES COURSE(Unit_ID),
    CONSTRAINT STUDENT-UNIT_PK PRIMARY KEY(Unit_ID, Student_ID)
    );

    CREATE TABLE ACTIVITY(
    Activity_Code VARCHAR(10) NOT NULL,
    Activity_Type VARCHAR(30) NOT NULL,
    Building_ID VARCHAR(10) NOT NULL,
    Day_And_Time VARCHAR(20) NOT NULL,
    CONSTRAINT ACTIVITY_PK PRIMARY KEY(Unit_ID)
    )

    CREATE TABLE STAFF_ACTIVE(
    Staff_ID VARCHAR(10) NOT NULL,
    Activity_Code VARCHAR(10) NOT NULL,
    CONSTRAINT STAFF_PK PRIMARY KEY(Staff_ID)
    REFERENCES STAFF(Staff_ID),
    CONSTRAINT ACTIVITY_PK PRIMARY KEY(Acivity_Code)
    REFERENCES ACTIVITY(Activity_Code),
    CONSTRAINT STAFF-ACTIVE_PK PRIMARY KEY(Staff_ID, Activity_Code)
    );
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is more wrong than missing semicolons:

    Code (SQL):
    SQL> CREATE TABLE STAFF (
      2          Staff_ID CHAR(10) NOT NULL,
      3          Name CHAR(30) NOT NULL,
      4          Phone_Num INTEGER NOT NULL,
      5          Email CHAR(40) NOT NULL,
      6          Office_Num INTEGER NOT NULL,
      7          Activity_Code VARCHAR(10) NOT NULL,
      8          CONSTRAINT STAFF_PK PRIMARY KEY(Staff_ID)
      9  );
    TABLE created.
    SQL>
    SQL> CREATE TABLE DEPARTMENT (
      2          Department_Name CHAR(10) NOT NULL,
      3          Staff_ID CHAR(10) NOT NULL,
      4  );
    )
    *
    ERROR at line 4:
    ORA-00904: : invalid identifier

    SQL>
    SQL> CREATE TABLE STUDENT (
      2          Student_ID VARCHAR(10) NOT NULL,
      3          Name VARCHAR(30) NOT NULL,
      4          Gender VARCHAR(10) NOT NULL,
      5          Date_Of_Birth VARCHAR(20) NOT NULL,
      6          Date_Amitted VARCHAR(20) NOT NULL,
      7          CONSTRAINT STUDENT_PK PRIMARY KEY(Student_ID)
      8  );
    TABLE created.
    SQL>
    SQL> CREATE TABLE COURSE (
      2          Course_ID VARCHAR(10) NOT NULL,
      3          Course_Title VARCHAR(30) NOT NULL,
      4          Course_Coordinator VARCHAR(30) NOT NULL,
      5          Student_ID VARCHAR(10) NOT NULL,
      6          Student_ID VARCHAR(10) NOT NULL,
      7          Unit_Code VARCHAR(10) NOT NULL,
      8          CONSTRAINT STUDENT_FK FOREIGN KEY (STUDENT_ID)
      9          REFERENCES STUDENT(STUDENT_ID),
     10          CONSTRAINT UNIT_FK FOREIGN KEY (UNIT_CODE)
     11          REFERENCES UNIT(UNIT_ID),
     12          CONSTRAINT COURSE_PK PRIMARY KEY(Course_ID)
     13  );
            Student_ID VARCHAR(10) NOT NULL,
            *
    ERROR at line 6:
    ORA-00957: duplicate COLUMN name

    SQL>
    SQL> CREATE TABLE ENROLMENT(
      2          Student_ID VARCHAR(10) NOT NULL,
      3          Course_ID VARCHAR(10) NOT NULL,
      4          CONSTRAINT STUDENT_PK PRIMARY KEY(Student_ID)
      5          REFERENCES STUDENT(Student_ID),
      6          CONSTRAINT COURSE_PK PRIMARY KEY(Course_ID)
      7          REFERENCES COURSE(Course_ID),
      8          CONSTRAINT ENROLMENT_PK PRIMARY KEY(Course_ID, Student_ID)
      9  );
            REFERENCES STUDENT(Student_ID),
            *
    ERROR at line 5:
    ORA-00907: missing RIGHT parenthesis

    SQL>
    SQL> CREATE TABLE UNIT(
      2          Unit_Code VARCHAR(10) NOT NULL,
      3          Unit_Title VARCHAR(30) NOT NULL,
      4          Student_ID VARCHAR(10) NOT NULL,
      5          Activity_Code VARCHAR(10) NOT NULL,
      6          CONSTRAINT ACTIVITY_FK FOREIGN KEY (ACTIVITY_CODE)
      7          REFERENCES ACTIVITY(ACTIVITY_CODE),
      8          CONSTRAINT UNIT_PK PRIMARY KEY(Unit_Code)
      9  );
            REFERENCES ACTIVITY(ACTIVITY_CODE),
                       *
    ERROR at line 7:
    ORA-00942: TABLE OR VIEW does NOT exist

    SQL>
    SQL> CREATE TABLE STUDENT_UNIT(
      2          Student_ID VARCHAR(10) NOT NULL,
      3          Unit_ID VARCHAR(10) NOT NULL,
      4          CONSTRAINT STUDENT_PK PRIMARY KEY(Student_ID)
      5          REFERENCES STUDENT(Student_ID),
      6          CONSTRAINT UNIT_PK PRIMARY KEY(Unit_ID)
      7          REFERENCES COURSE(Unit_ID),
      8          CONSTRAINT STUDENT-UNIT_PK PRIMARY KEY(Unit_ID, Student_ID)
      9  );
            REFERENCES STUDENT(Student_ID),
            *
    ERROR at line 5:
    ORA-00907: missing RIGHT parenthesis

    SQL>
    SQL> CREATE TABLE ACTIVITY(
      2          Activity_Code VARCHAR(10) NOT NULL,
      3          Activity_Type VARCHAR(30) NOT NULL,
      4          Building_ID VARCHAR(10) NOT NULL,
      5          Day_And_Time VARCHAR(20) NOT NULL,
      6          CONSTRAINT ACTIVITY_PK PRIMARY KEY(Unit_ID)
      7  );
            CONSTRAINT ACTIVITY_PK PRIMARY KEY(Unit_ID)
                                               *
    ERROR at line 6:
    ORA-00904: "UNIT_ID": invalid identifier

    SQL>
    SQL> CREATE TABLE STAFF_ACTIVE(
      2          Staff_ID VARCHAR(10) NOT NULL,
      3          Activity_Code VARCHAR(10) NOT NULL,
      4          CONSTRAINT STAFF_PK PRIMARY KEY(Staff_ID);
            CONSTRAINT STAFF_PK PRIMARY KEY(Staff_ID)
                                                    *
    ERROR at line 4:
    ORA-00907: missing RIGHT parenthesis

    SQL>         REFERENCES STAFF(Staff_ID),
    SP2-0734: UNKNOWN command beginning "REFERENCES..." - rest OF line ignored.
    SQL>         CONSTRAINT ACTIVITY_PK PRIMARY KEY(Acivity_Code)
    SP2-0734: UNKNOWN command beginning "CONSTRAINT..." - rest OF line ignored.
    SQL>         REFERENCES ACTIVITY(Activity_Code),
    SP2-0734: UNKNOWN command beginning "REFERENCES..." - rest OF line ignored.
    SQL>         CONSTRAINT STAFF-ACTIVE_PK PRIMARY KEY(Staff_ID, Activity_Code)
    SP2-0734: UNKNOWN command beginning "CONSTRAINT..." - rest OF line ignored.
    SP2-0044: FOR a list OF known commands enter HELP
    AND TO leave enter EXIT.
    SQL> );
    SP2-0042: UNKNOWN command ")" - rest OF line ignored.
    SQL>
    SQL>
     
    You have one table with a duplicate column definition, one table with a mismatched definition to the referenced table, several tables with more than one primary key, one foreign key referencing a non-unique column, a misplaced comma and multiple occurrences of duplicate constraint names. The corrected code is shown below:

    Code (SQL):
    SQL> CREATE TABLE STAFF (
      2          Staff_ID CHAR(10) NOT NULL,
      3          Name CHAR(30) NOT NULL,
      4          Phone_Num INTEGER NOT NULL,
      5          Email CHAR(40) NOT NULL,
      6          Office_Num INTEGER NOT NULL,
      7          Activity_Code VARCHAR2(10) NOT NULL,
      8          CONSTRAINT STAFF_PK PRIMARY KEY(Staff_ID)
      9  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE DEPARTMENT (
      2          Department_Name CHAR(10) NOT NULL,
      3          Staff_ID CHAR(10) NOT NULL
      4  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE ACTIVITY(
      2          Activity_Code VARCHAR2(10) NOT NULL,
      3          Activity_Type VARCHAR2(30) NOT NULL,
      4          Building_ID VARCHAR2(10) NOT NULL,
      5          Day_And_Time VARCHAR2(20) NOT NULL,
      6          CONSTRAINT ACTIVITY_PK PRIMARY KEY(Activity_code)
      7  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE UNIT(
      2          Unit_Code VARCHAR2(10) NOT NULL,
      3          Unit_Title VARCHAR2(30) NOT NULL,
      4          Student_ID VARCHAR2(10) NOT NULL,
      5          Activity_Code VARCHAR2(10) NOT NULL,
      6          CONSTRAINT ACTIVITY_FK FOREIGN KEY (ACTIVITY_CODE)
      7          REFERENCES ACTIVITY(ACTIVITY_CODE),
      8          CONSTRAINT UNIT_PK PRIMARY KEY(Unit_Code)
      9  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE STUDENT (
      2          Student_ID VARCHAR2(10) NOT NULL,
      3          Name vARCHAR2(30) NOT NULL,
      4          Gender VARCHAR2(10) NOT NULL,
      5          Date_Of_Birth VARCHAR2(20) NOT NULL,
      6          Date_Amitted VARCHAR2(20) NOT NULL,
      7          CONSTRAINT STUDENT_PK PRIMARY KEY(Student_ID)
      8  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE COURSE (
      2          Course_ID VARCHAR2(10) NOT NULL,
      3          Course_Title VARCHAR2(30) NOT NULL,
      4          Course_Coordinator VARCHAR2(30) NOT NULL,
      5          Student_ID VARCHAR2(10) NOT NULL,
      6          Unit_Code VARCHAR2(10) NOT NULL,
      7          CONSTRAINT STUDENT_FK FOREIGN KEY (STUDENT_ID)
      8          REFERENCES STUDENT(STUDENT_ID),
      9          CONSTRAINT UNIT_FK FOREIGN KEY (UNIT_CODE)
     10          REFERENCES UNIT(UNIT_code),
     11          CONSTRAINT COURSE_PK PRIMARY KEY(Course_ID),
     12          CONSTRAINT unit_cd_uq UNIQUE(unit_code)
     13  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE ENROLMENT(
      2          Student_ID VARCHAR2(10) NOT NULL,
      3          Course_ID VARCHAR2(10) NOT NULL,
      4          CONSTRAINT STUDENT_PF FOREIGN KEY(Student_ID)
      5          REFERENCES STUDENT(Student_ID),
      6          CONSTRAINT COURSE_FK FOREIGN KEY(Course_ID)
      7          REFERENCES COURSE(Course_ID),
      8          CONSTRAINT ENROLMENT_PK PRIMARY KEY(Course_ID, Student_ID)
      9  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE STUDENT_UNIT(
      2          Student_ID VARCHAR2(10) NOT NULL,
      3          Unit_ID VARCHAR2(10) NOT NULL,
      4          CONSTRAINT STUDENT_FK2 FOREIGN KEY(Student_ID)
      5          REFERENCES STUDENT(Student_ID),
      6          CONSTRAINT UNIT_FK2 FOREIGN KEY(Unit_ID)
      7          REFERENCES COURSE(Unit_code),
      8          CONSTRAINT STUDENT_UNIT_PK PRIMARY KEY(Unit_ID, Student_ID)
      9  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE STAFF_ACTIVE(
      2          Staff_ID CHAR(10) NOT NULL,
      3          Activity_Code VARCHAR2(10) NOT NULL,
      4          CONSTRAINT STAFF_fK FOREIGN KEY(Staff_ID)
      5          REFERENCES STAFF(Staff_ID),
      6          CONSTRAINT ACTIVITY_FK2 FOREIGN KEY(Activity_Code)
      7          REFERENCES ACTIVITY(Activity_Code)
      8  );
     
    TABLE created.
     
    SQL>