3.32

Rewrite the preceding query, but also ensure that you include only instructors who have given at least one other non-null grade in some course.

---

So the question paraphrased is as follows: write an SQL query to find the ID and name of each instructor who has never given an A grade in any course she or he has taught and who has given at least one other non-null grade in some course.

SELECT id, name 
FROM instructor AS i
WHERE 'A' NOT IN (
    SELECT takes.grade
    FROM takes INNER JOIN teaches 
        ON (takes.course_id,takes.sec_id,takes.semester,takes.year) = 
           (teaches.course_id,teaches.sec_id,teaches.semester,teaches.year)
    WHERE teaches.id = i.id
) 
AND
(
    SELECT COUNT(*)
    FROM takes INNER JOIN teaches 
        ON (takes.course_id,takes.sec_id,takes.semester,takes.year) = 
           (teaches.course_id,teaches.sec_id,teaches.semester,teaches.year)
    WHERE teaches.id = i.id AND takes.grade IS NOT NULL    
) >= 1