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;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;|||Originally posted by harshal_in
can u post the ddl for the base tables ?
and which view is causing the problem?|||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?
.com> wrote in message
