Can this be done with a single query?

Please be polite enough o post DDL in the future. Is this what you
meant?
CREATE TABLE GradeBook
(student_name CHAR(20) NOT NULL,
subject_name CHAR(10) NOT NULL,
PRIMARY KEY (student_name, subject)
student_grade INTEGER NOT NULL
CHECK(student_grade BETWEEN 0 AND 100));
The term list has a certain meaning in Computer Science that you did
not mean. Try something like this:
SELECT student_name
FROM GradeBook
WHERE grade = 85
AND subject_name IN ('math', 'physics', 'chemistry')
GROUP BY student_name
HAVING COUNT(*) = 3;
Look up "relatioanl Division" for a more general approach to this kind
of problem."--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1143495213.186157.100040@.u72g2000cwu.googlegroups.com...
> Please be polite enough o post DDL in the future. Is this what you
> meant?
> CREATE TABLE GradeBook
> (student_name CHAR(20) NOT NULL,
> subject_name CHAR(10) NOT NULL,
> PRIMARY KEY (student_name, subject)
> student_grade INTEGER NOT NULL
> CHECK(student_grade BETWEEN 0 AND 100));
> The term list has a certain meaning in Computer Science that you did
> not mean. Try something like this:
> SELECT student_name
> FROM GradeBook
> WHERE grade = 85
> AND subject_name IN ('math', 'physics', 'chemistry')
> GROUP BY student_name
> HAVING COUNT(*) = 3;
> Look up "relatioanl Division" for a more general approach to this kind
> of problem.
>
Joe, you've fallen into the same trap that ALL the other posters fell into.
The original question was...
Find all the students who got a grade 85 or better in math, physics and
chemistry.
That is you need all three rows for a single student. And only of all three
rows are over 85 select the student. Hence why I did the solution which
uses a pivot.
Colin.