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

    Unanswered: Could anyone help please - simple SQL code

    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

    error says:

    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
    Paris, France
    Posts
    320
    It seem to me that is not a mysql valide sql syntax but a T-sql syntaxe
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    I have no experience with MySQL in any way, but I suppose it does use ANSI SQL conventions:

    If you use an aggregate function toegether with other fields in your select you WILL NEED to specify a GROUP BY on those other fields:

    example:

    SELECT A,B,C,SUM(D),SUM(E) FROM ..........
    WHERE ......................
    GROUP BY A,B,C

    The report card view is at fault here, cause the entire GROUP BY clause is missing!!! Furthermore, using other calculations beside aggregates (SUM,COUNT) will probably get you curious results, cause data is rolled up to the dimensions (A,B,C in my example)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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