Need more help with Oracle SQL problems Generate an alphabetic listing containin

Question

Need more help with Oracle SQL problems

Generate an alphabetic listing containing the last names and final exam grade (FI) of students who scored above average on the final exam for section 90.

List the course number and course description of the courses with the lowest number of enrolled students. Arrange the list on course number.

STUDENT DATABASE SCHEMA GRADE CONVERSION TYPE LETTER GRADE (PK) VARCHAR2(2) NOT NULL GRADE PONT NUMBER(32 NOTNLL MAX GRADE MN GRADE CREATED BY CREATED DATE DATE MODIFIED BY MODIFIED DATE DATE GRADE TYPE CODE (PK) CHARI(2) NUMBER(3,0) NOT NULL NUMBER(3,0) NOT NULL VARCHAR2(30) NOT NULL NOT NULL CREATED BY CREATED DATE MODFED BY MODFED DATE NOT NULL VARCHAR2(50) NOT NULL VARCHAR2(30) NOT NULL NOT NULL VARCHAR2(30) NOT NULL NOT NULL DATE DATE VARCHAR2(30) NOTNULL NOT NULL GRTW GRTYP FK GRADE STUDENT D (PK)(FK) SECTION D(PK)(FK) GRADE TYPE CODE (PKFK) CHAR 2) GRADE TYPE WEIGHT NUMBER(8,0 NOT NULL NUMBER(8,0) NOT NULL SECTION ID (PKy(FK) GRADE TYPE CODE (PK)(FK) CHAR(2) NIEER SECTION PERCENT OF FINAL_GRADE NUMBER(3,0) NOT NULI DROP LOWEST CREATED BY CREATED DATE MODIFIED BY MODIFIED DATE NUMBER(8,0) NOT NULL NOT NULL NOT NULL GR GRTW NUMBER(3,0) NOT NULL GRADE CODE OCCURRENCE (PK) NUMBER(38,0) NOT NULL NUMBER(3,0) NOT NULL VARCHAR2(2000) NULL VARCHAR2(30) NOT NULL NOT NULL VARCHAR2(30) NOT NULL NOT NULL NUr RIC GRADE CHAR(1) VARCHAR2(30) NOT NULL DATE VARCHAR2(30) NOT NULL DATE NOT NULL NOT NULL NOT NULL CREATED DATE MODIFIED BY MODIFIED DATE GRTW_SECT FK GR ENR FK

Explanation / Answer

1) For this query 4 tables namely STUDENT, GRADE, ENROLLMENT, SECTION are joined based on the common attribute. Aggregate function SUM is used to get student wise grade summation, then in HAVING clause this is compared with the average grade. WHERE clause is used to filter section no 90. To sorted the result in ascending order of student last name ORDER by clause is used.

Query-

SELECT s.LAST_NAME, SUM(g.NUMERIC_GRADE)
FROM STUDENT AS s
INNER JOIN GRADE AS g
ON g.STUDENT_ID = s.STUDENT_ID
INNER JOIN ENROLLMENT AS e
ON e.STUDENT_ID = s.STUDENT_ID
INNER JOIN SECTION AS se
ON se.SECTION_ID = e.SECTION_ID
WHERE se.SECTION_NO = 90
GROUP BY s.LAST_NAME, s.FIRST_NAME
HAVING SUM(g.NUMERIC_GRADE) > AVG(g.NUMERIC_GRADE)
ORDER BY s.LAST_NAME;

2) For this 3 tables namely COURSE, SECTION and ENROLLMENT are joined on common attribute. Aggregate function COUNT is used to count the number of student in each course. In having clause this number is compared with the average enrollment of the student, if it is less then average then it is considered as minimal student enrollment.

Query-

SELECT c.COURSE_NO, c.DESCRIPTION, COUNT(e.STUDENT_ID) AS "Enrolled"
FROM COURSE AS c
INNER JOIN SECTION AS se
ON se.COURSE_NO = c.COURSE_NO
INNER JOIN ENROLLMENT AS e
ON e.SECTION_ID = se.SECTION_ID
GROUP BY c.COURSE_NO, c.DESCRIPTION
HAVING COUNT(e.STUDENT_ID) < (SELECT AVG(COUNT(STUDENT_ID)) FROM ENROLLMENT);

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.