Need help on a couple questions from another Oracle SQL assignment List the stud
ID: 3731339 • Letter: N
Question
Need help on a couple questions from another Oracle SQL assignment
List the student_id and last_name of students who received a below average grade on the third quiz in section 120.
Provide an alphabetic list containing the full names and phone numbers of students who have taken both the Systems Analysis and the Project Management courses. You must use the title of the course in your query, not the course number.
List the instructor name and course description of the Java courses that have been taught by the Instructor that has taught the most Java courses. Sort on instructor name and course description.
List the student_id and last_name of students who received an above average grade on the Final Exam in section 130.
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 FKExplanation / Answer
/*List the student_id and last_name of students who received a below average grade on the third quiz in section 120.*/
SELECT s.Student_ID,s.Last_Name FROM Student s
INNER JOIN ENROLLMENT e
ON s.Student_ID=e.student_id
INNER JOIN SECTION s
ON e.Section_ID=s.Section_ID
INNER JOIN GRADE_TYPE_WEIGHT g
ON s.Section_ID=g.Section_ID
INNER JOIN GRADE_TYPE gt
ON g.GRADE_TYPE_CODE=gt.GRADE_TYPE_CODE
WHERE gt.DESCRIPTION LIKE '%Below Average%'/*Please enter as per data in database*/
/*Assuming Grade type description contain data related to Above average and below average etc.,*/
AND s.Section_ID=120;
/*Provide an alphabetic list containing the full names and phone numbers of students
who have taken both the Systems Analysis and the Project Management courses.
You must use the title of the course in your query, not the course number.*/
SELECT s.FIRST_Name || ' ' || s.LAST_Name AS FullName, s.PHONE
FROM Student s
INNER JOIN ENROLLMENT e
ON s.Student_ID=e.student_id
INNER JOIN SECTION s
ON e.Section_ID=s.Section_ID
INNER JOIN COURSE c
ON s.COURSE_NO=c.COURSE_NO
WHERE c.DESCRIPTION='Systems Analysis' AND c.DESCRIPTION='Project Management'
ORDER BY s.FIRST_Name ASC;
/*Please check description in database and provide as per database*/
/*List the instructor name and course description of the Java courses that have been
taught by the Instructor that has taught the most Java courses.
Sort on instructor name and course description.*/
SELECT i.FIRST_Name || ' ' || i.LAST_Name AS FullName,c.DESCRIPTION,count(*) AS MostCourse
FROM INSTRUCTOR i
INNER JOIN SECTION s
ON i.INSTRUCTOR_ID=s.INSTRUCTOR_ID
INNER JOIN COURSE c
s.COURSE_NO=c.COURSE_NO
WHERE c.DESCRIPTION LIKE '%JAVA%'
GROUP BY i.FIRST_Name,i.LAST_Name,c.DESCRIPTION
HAVING count(*) = (SELECT MAX(COUNT(COURSE_NO)) FROM SECTION
GROUP BY INSTRUCTOR_ID);
/*List the student_id and last_name of students who received an above average grade on the Final Exam in section 130.*/
SELECT s.Student_ID,s.Last_Name FROM Student s
INNER JOIN ENROLLMENT e
ON s.Student_ID=e.student_id
INNER JOIN SECTION s
ON e.Section_ID=s.Section_ID
INNER JOIN GRADE_TYPE_WEIGHT g
ON s.Section_ID=g.Section_ID
INNER JOIN GRADE_TYPE gt
ON g.GRADE_TYPE_CODE=gt.GRADE_TYPE_CODE
WHERE gt.DESCRIPTION LIKE '%Above Average%'/*Please enter as per data in database*/
/*Assuming Grade type description contain data related to Above average and below average etc.,*/
AND s.Section_ID=130;
/*Please comment if you have any problem or doubt*/
/*Thanks**/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.