Get distinct result

I was asked this trick question:

Table: Student
ID       NAME
1        JOHN
2        MARY
3        ROBERT
4        DENNIS

Table: Grade
ID         GRADE
1             A
1             A
1             F
2             B
3             A

How do you write SQL query to return DISTINCT name of all students who has never received grade 'F' OR who has never taken a course (meaning, their ID not present in Grade table)?

Trick part is, you'开发者_开发知识库re not allowed to use OUTER JOIN, UNION or DISTINCT. Also, why this is a big deal?

Expected result is MARY, ROBERT, DENNIS (3 rows).

SELECT name FROM Student 
WHERE 
NOT EXISTS (SELECT * FROM Grade WHERE Grade.id = Student.id AND grade = 'F')
OR 
NOT EXISTS (SELECT * FROM Grade WHERE Grade.id = Student.id);

You may use GROUP BY in order to fake a distinct.

SELECT name FROM student
WHERE (SELECT COUNT(*) FROM grade WHERE grade = 'F'
         AND id = student.id) = 0

at least this is the shortest answer so far ...

Something like this could work, if you're allowed to use subqueries.

SELECT `NAME` 
FROM Student 
WHERE 'F' NOT IN 
    (SELECT GRADE FROM Grade WHERE ID = Student.ID)

Hmm, my homework sense is tingling... Not that my questions have never related to homework though...

You could use the GROUP BY and aggregate functions in order to fake a distinct.

You want to exclude everyone who has both taken a course and received a grade of 'F'. Something like this might work:

SELECT NAME
FROM   Student
WHERE 0 = (SELECT COUNT(*)
           FROM Student
                LEFT JOIN Grade
                USING (ID)
           WHERE GRADE='F')
GROUP BY NAME

SELECT name
FROM grade G, student S
WHERE (S.id = G.id AND 'F' NOT IN (SELECT G1.grade
                                   FROM grade G1
                                   WHERE G1.id = G.id))
      OR
      S.id NOT IN (SELECT id 
                   FROM grade)
GROUP BY name

A reason why they might not want you to use UNION, JOIN or DISTINCT is that some of those queries might be slow if you try to force an "optimized" solution.
I'm not too familiar with query optimization techniques but usually if you use some of those aggregators and JOINs, you might slow down your query rather than just letting the query optimizations run through and organize your SQL based on your table structure and contents.