Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: Please help - problem with simple SQL - SUM ?

    Hello All, after writing my simple SQL i don't know how to fix this problem, SUM problem, won't run because of it.

    I'm so stressed out, please help, need to find the problem

    this is the code:

    1. REPORT CARD

    CREATE VIEW REPORT_CARD AS
    SELECT DEPT_NAME, COURSE_NUM, COURSE_DESC, GRADE_L, COURSE_CREDIT as
    CREDITS_TAKEN, COURSE_CREDIT AS COURSE_EARNED, GRADE_N*COURSE_CREDIT AS
    GRADE_POINTS,
    SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
    SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
    TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
    SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
    SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
    TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
    CLASS.SEM_ID,
    ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE,
    S_L_ZIP,
    S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP
    FROM STUDENT, DEPARTMENT, COURSE, ENROLLMENT, CLASS, SEMESTER
    WHERE
    STUDENT.S_ID=ENROLLMENT.S_ID
    AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE
    AND CLASS.CLASS_ID=COURSE.COURSE_ID
    AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
    AND SEMESTER.SEM_ID=CLASS.SEM_ID;


    2. CLASS LIST

    CREATE VIEW CLASS_LIST AS
    SELECT COURSE.DEPT_CODE "DEPARTMENT", COURSE_NUM "COURSE", SEC_NUM
    "SECTION', CLASS.SCHED_CODE, DEPT_NAME, COURSE_DESC, COURSE_CREDIT,
    SEM_ID,
    TIME, DAY, ROOM_NUM, BLDG_CODE,
    CLASS.F_ID, F_FNAME, F_LNAME
    ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, GRADE
    FROM DEPARTMENT, CLASS, COURSE, ROOM, STUDENT, ENROLLMENT, FACULTY
    WHERE
    ROOM.ROOM_ID=CLASS.ROOM_ID
    AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
    AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
    AND ENROLLMENT S_ID=STUDENT.S_ID;

    3.GRADE VERIFICATION REPORT

    CREATE VIEW GRADE_VERIFICATION AS
    SELECT * FROM CLASS_LIST;

    4.TIME SCHEDULE


    CREATE VIEW TIME_SCHEDULE AS
    SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
    DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT, PREREQ
    FROM CLASS, COURSE, FACULTY, ROOM
    WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
    AND CLASS.F_ID=FACULTY.F_ID
    AND CLASS.ROOM_ID=ROOM.ROOM_ID
    AND SEMESTER.SEM_ID=CLASS.SEM_ID;

    6.STUDENT SCHEDULE

    CREATE VIEW STUDENT_SCHEDULE AS
    SELECT STUDENT.S_ID, SEM_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY,
    S_L_STATE, S_L_ZIP, S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
    CLASS.SCHED_CODE, CLASS_CLASS_ID, COURSE_DESC, SEC_NUM,
    COURSE_CREDIT, TIME, DAY, ROOM_NUM, BLDG_CODE, SUM(COURSE_CREDIT)
    FROM STUDENT, CLASS, COURSE, ROOM, ENROLLMENT
    WHERE STUDENT.S_ID=ENROLLMENT.S_ID
    AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
    AND CLASS.CLASS_ID=COURSE.COURSE_ID
    AND CLASS.ROOM_ID=ROOM.ROOM_ID;


    7.FULL STUDENT INFORMATION

    CREATE VIEW FULL_STUDENT_INFO AS
    SELECT STUDENT.*, SEM_ID,
    COURSE_NUM, COURSE_DES, COURSE_CREDIT,
    GRADE_L, GRADE_N*COURSE_CREDIT AS "GRADE POINTS",
    SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
    SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
    SUM(GRADE_N*COURSE_CREDIT) AS "TOTAL GRADE POINTS",
    S_GPA
    FROM STUDENT, ENROLLMENT, COURSE, CLASS
    WHERE STUDENT.S_ID=ENROLLMENT.S_ID
    AND ENROLLMENT.SCHED_ID=CLASS.SCHED_ID
    AND CLASS.CLASS_ID=COURSE_COURSE_ID;


    8. FACULTY INFORMATION REPORT

    CREATE VIEW FACULTY_INFO AS
    SELECT FACULTY.*,
    S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE, S_L_ZIP,
    S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
    DEPT_CODE, MAJOR_NAME
    FROM FACULTY, STUDENT, MAJOR
    WHERE
    FACULTY.F_ID=STUDENT.F_ID
    AND STUDENT.MAJOR_ID=MAJOR.MAJOR_ID;

    9.WORK VERSION OF THE TIME SCHEDULE

    CREATE VIEW TIME_SCHED_INTERNAL AS
    SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
    DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT,
    PREREQ,
    COUNT(ENROLL_ID), MAX_ENROLL
    FROM CLASS, COURSE, FACULTY, ROOM, ENROLLMENT
    WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
    AND CLASS.F_ID=FACULTY.F_ID
    AND CLASS.ROOM_ID=ROOM.ROOM_ID
    AND SEMESTER.SEM_ID=CLASS.SEM_ID
    AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE;


    10.CREATE VIEW COURSE_REPORT AS
    SELECT COURSE_NUM, COURSE_DESC, COURSE_CREDIT,
    DEPT_DESC, COURSE.DEPT_CODE
    FROM COURSE, DEPARTMENT
    WHERE COURSE.DEPT_CODE=DEPARTMENT.DEPT.CODE;


    11.UPDATE ENROLLMENT-NEED TO CREATE A TRIGGER TO CHECK UPON INSERT INTO
    THE ENROLLMENT TABLE WHERE COURSE.PREREQ NOT NULL AND ENROLLMENT.GRADE NOT
    NULL FOR THAT STUDENT

    SYNTAX WILL BE SIMILAR TO:
    CREATE OR REPLACE TRIGGER VERIFY_ENROLLMENT
    BEFORE INSERT ON ENROLLMENT
    BEGIN
    ******
    END


    12.POST GRADES: EXAMPLE

    UPDATE ENROLLMENT
    SET GRADE='A'
    WHERE S_ID='123';

    13. PURGE:EXAMPLE
    DELETE FROM CLASS
    WHERE CLASS < 1010;

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Please help - problem with simple SQL - SUM ?

    can u post the ddl for the base tables ?
    and which view is causing the problem?


    Originally posted by Mari9922
    Hello All, after writing my simple SQL i don't know how to fix this problem, SUM problem, won't run because of it.

    I'm so stressed out, please help, need to find the problem

    this is the code:

    1. REPORT CARD

    CREATE VIEW REPORT_CARD AS
    SELECT DEPT_NAME, COURSE_NUM, COURSE_DESC, GRADE_L, COURSE_CREDIT as
    CREDITS_TAKEN, COURSE_CREDIT AS COURSE_EARNED, GRADE_N*COURSE_CREDIT AS
    GRADE_POINTS,
    SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
    SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
    TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
    SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
    SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
    TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
    CLASS.SEM_ID,
    ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE,
    S_L_ZIP,
    S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP
    FROM STUDENT, DEPARTMENT, COURSE, ENROLLMENT, CLASS, SEMESTER
    WHERE
    STUDENT.S_ID=ENROLLMENT.S_ID
    AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE
    AND CLASS.CLASS_ID=COURSE.COURSE_ID
    AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
    AND SEMESTER.SEM_ID=CLASS.SEM_ID;


    2. CLASS LIST

    CREATE VIEW CLASS_LIST AS
    SELECT COURSE.DEPT_CODE "DEPARTMENT", COURSE_NUM "COURSE", SEC_NUM
    "SECTION', CLASS.SCHED_CODE, DEPT_NAME, COURSE_DESC, COURSE_CREDIT,
    SEM_ID,
    TIME, DAY, ROOM_NUM, BLDG_CODE,
    CLASS.F_ID, F_FNAME, F_LNAME
    ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, GRADE
    FROM DEPARTMENT, CLASS, COURSE, ROOM, STUDENT, ENROLLMENT, FACULTY
    WHERE
    ROOM.ROOM_ID=CLASS.ROOM_ID
    AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
    AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
    AND ENROLLMENT S_ID=STUDENT.S_ID;

    3.GRADE VERIFICATION REPORT

    CREATE VIEW GRADE_VERIFICATION AS
    SELECT * FROM CLASS_LIST;

    4.TIME SCHEDULE


    CREATE VIEW TIME_SCHEDULE AS
    SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
    DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT, PREREQ
    FROM CLASS, COURSE, FACULTY, ROOM
    WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
    AND CLASS.F_ID=FACULTY.F_ID
    AND CLASS.ROOM_ID=ROOM.ROOM_ID
    AND SEMESTER.SEM_ID=CLASS.SEM_ID;

    6.STUDENT SCHEDULE

    CREATE VIEW STUDENT_SCHEDULE AS
    SELECT STUDENT.S_ID, SEM_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY,
    S_L_STATE, S_L_ZIP, S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
    CLASS.SCHED_CODE, CLASS_CLASS_ID, COURSE_DESC, SEC_NUM,
    COURSE_CREDIT, TIME, DAY, ROOM_NUM, BLDG_CODE, SUM(COURSE_CREDIT)
    FROM STUDENT, CLASS, COURSE, ROOM, ENROLLMENT
    WHERE STUDENT.S_ID=ENROLLMENT.S_ID
    AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
    AND CLASS.CLASS_ID=COURSE.COURSE_ID
    AND CLASS.ROOM_ID=ROOM.ROOM_ID;


    7.FULL STUDENT INFORMATION

    CREATE VIEW FULL_STUDENT_INFO AS
    SELECT STUDENT.*, SEM_ID,
    COURSE_NUM, COURSE_DES, COURSE_CREDIT,
    GRADE_L, GRADE_N*COURSE_CREDIT AS "GRADE POINTS",
    SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
    SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
    SUM(GRADE_N*COURSE_CREDIT) AS "TOTAL GRADE POINTS",
    S_GPA
    FROM STUDENT, ENROLLMENT, COURSE, CLASS
    WHERE STUDENT.S_ID=ENROLLMENT.S_ID
    AND ENROLLMENT.SCHED_ID=CLASS.SCHED_ID
    AND CLASS.CLASS_ID=COURSE_COURSE_ID;


    8. FACULTY INFORMATION REPORT

    CREATE VIEW FACULTY_INFO AS
    SELECT FACULTY.*,
    S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE, S_L_ZIP,
    S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
    DEPT_CODE, MAJOR_NAME
    FROM FACULTY, STUDENT, MAJOR
    WHERE
    FACULTY.F_ID=STUDENT.F_ID
    AND STUDENT.MAJOR_ID=MAJOR.MAJOR_ID;

    9.WORK VERSION OF THE TIME SCHEDULE

    CREATE VIEW TIME_SCHED_INTERNAL AS
    SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
    DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT,
    PREREQ,
    COUNT(ENROLL_ID), MAX_ENROLL
    FROM CLASS, COURSE, FACULTY, ROOM, ENROLLMENT
    WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
    AND CLASS.F_ID=FACULTY.F_ID
    AND CLASS.ROOM_ID=ROOM.ROOM_ID
    AND SEMESTER.SEM_ID=CLASS.SEM_ID
    AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE;


    10.CREATE VIEW COURSE_REPORT AS
    SELECT COURSE_NUM, COURSE_DESC, COURSE_CREDIT,
    DEPT_DESC, COURSE.DEPT_CODE
    FROM COURSE, DEPARTMENT
    WHERE COURSE.DEPT_CODE=DEPARTMENT.DEPT.CODE;


    11.UPDATE ENROLLMENT-NEED TO CREATE A TRIGGER TO CHECK UPON INSERT INTO
    THE ENROLLMENT TABLE WHERE COURSE.PREREQ NOT NULL AND ENROLLMENT.GRADE NOT
    NULL FOR THAT STUDENT

    SYNTAX WILL BE SIMILAR TO:
    CREATE OR REPLACE TRIGGER VERIFY_ENROLLMENT
    BEFORE INSERT ON ENROLLMENT
    BEGIN
    ******
    END


    12.POST GRADES: EXAMPLE

    UPDATE ENROLLMENT
    SET GRADE='A'
    WHERE S_ID='123';

    13. PURGE:EXAMPLE
    DELETE FROM CLASS
    WHERE CLASS < 1010;

  3. #3
    Join Date
    Oct 2003
    Posts
    6

    Re: Please help - problem with simple SQL - SUM ?

    Originally posted by harshal_in
    can u post the ddl for the base tables ?
    and which view is causing the problem?

  4. #4
    Join Date
    Oct 2003
    Posts
    6

    Re: Please help - problem with simple SQL - SUM ?

    Thank you very much, I'll post it after work, if that's not too much of a problem.


    Originally posted by harshal_in
    can u post the ddl for the base tables ?
    and which view is causing the problem?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •