STUDENT(ID, FIRST_NAME, LAST_NAME, MAJOR) INSTRUCTOR(ID, FIRST_NAME, LAST_NAME,

Question

STUDENT(ID, FIRST_NAME, LAST_NAME, MAJOR)

INSTRUCTOR(ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY)

COURSE(ID, COURSE_NAME, DESCRIPTION)

COURSE_OFFERING(ID, COURSE_ID, INSTRUCTOR_ID, SEMESTER)

GRADE(STUDENT_ID, COURSE_OFFERING_ID, GRADE)

Can someone help me finding me those:

1. Find the names of all students who have taken every course taught by Eric Lee.

2. Find the names of all students who have never taken any course taught by Eric Lee.

3. Calculate the number and average GPA of those students who have never taken any course taught by Eric Lee. (The GPA of a student is simply the average of all his grades in TABLE GRADE. Students who have not taken any course do not have a GPA.)

4. Find the IDs of all courses which have never been taken by any student who has taken at least one course taught by Eric Lee.

5. Find the IDs of all students who have only taken courses taught by Eric Lee.

6. Find the IDs of all courses satisfying following conditions: (i) they are taught by Eric Lee; and (ii) no student who has taken the course gets a score below 60.

Explanation / Answer

SELECT s.FIRST_NAME,s.LAST_NAME FROM STUDENT s,INSTRUCTOR i,COURSE_OFFERING co,GRADE g WHERE co.INSTRUCTOR_ID=i.ID and CONCAT(i.FIRST_NAME,' ', i.LAST_NAME) ='Eric Lee' AND g.COURSE_OFFERING_ID=co.ID AND g.STUDENT_ID=s.ID; SELECT n.FIRST_NAME,n.LAST_NAME FROM STUDENT n WHERE n.ID NOT IN (SELECT s.ID FROM STUDENT s,INSTRUCTOR i,COURSE_OFFERING co,GRADE g WHERE co.INSTRUCTOR_ID=i.ID and CONCAT(i.FIRST_NAME,' ', i.LAST_NAME) ='Eric Lee' AND g.COURSE_OFFERING_ID=co.ID AND g.STUDENT_ID=s.ID) ; SELECT STUDENT_ID,AVG(GRADE) FROM GRADE GROUP BY STUDENT_ID HAVING COURSE_OFFERING_ID NOT IN (SELECT s.ID FROM STUDENT s,INSTRUCTOR i,COURSE_OFFERING co,GRADE g WHERE co.INSTRUCTOR_ID=i.ID and CONCAT(i.FIRST_NAME,' ', i.LAST_NAME) ='Eric Lee' AND g.COURSE_OFFERING_ID=co.ID AND g.STUDENT_ID=s.ID ); SELECT c.ID FROM COURSE c WHERE c.ID NOT IN ( SELECT co.COURSE_ID FROM INSTRUCTOR i,COURSE_OFFERING co,GRADE g WHERE co.INSTRUCTOR_ID=i.ID and CONCAT(i.FIRST_NAME,' ', i.LAST_NAME) ='Eric Lee' AND g.COURSE_OFFERING_ID=co.ID ); SELECT n.ID FROM STUDENT n WHERE n.ID NOT IN (SELECT g.STUDENT_ID FROM INSTRUCTOR i,COURSE_OFFERING co,GRADE g WHERE co.INSTRUCTOR_ID=i.ID and CONCAT(i.FIRST_NAME,' ', i.LAST_NAME) ='Eric Lee' AND g.COURSE_OFFERING_ID=co.ID); SELECT c.ID FROM COURSE c WHERE c.ID IN ( SELECT co.COURSE_ID FROM INSTRUCTOR i,COURSE_OFFERING co,GRADE g WHERE co.INSTRUCTOR_ID=i.ID and CONCAT(i.FIRST_NAME,' ', i.LAST_NAME) ='Eric Lee' ) AND 60 > ANY(SELECT AVG(gg.GRADE) FROM GRADE gg GROUP BY gg.STUDENT_ID HAVING gg.STUDENT_ID NOT IN (SELECT s.ID FROM STUDENT s,COURSE_OFFERING co,GRADE g WHERE g.COURSE_OFFERING_ID=c.ID AND g.STUDENT_ID=s.ID );

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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