Oracle SQL where clause evaluating two columns

Question

I have basic SQL skills but I'm stumped by this:

create table scores 
(
    name varchar2(15), 
    history_grade varchar2(1), 
    math_grade varchar2(1)
);

insert into scores (name, history_grade, math_grade) values ('Bill', 'A', 'A');
insert into scores (name, history_grade, math_grade) values ('Sue', 'F', 'F');
insert into scores (name, history_grade, math_grade) values ('Mary', 'C', 'B');
insert into scores (name, history_grade, math_grade) values ('Austin', 'C', 'A');
insert into scores (name, history_grade, math_grade) values ('Kyle', 'B', 'B');

I want to query rows where either history_grade and math_grade are both not 'A' OR both not 'F'. If either history_grade or math_grade are 'A' or 'F' but the other is not an 'A' or 'F' then I still want the row.

I'm trying this:

select * 
from scores 
where (history_grade <> 'A' and math_grade <> 'A')
  and (history_grade <> 'F' and math_grade <> 'F' )

but this only returns Mary and Kyle. I need Mary, Kyle and Austin in the result set since Austin has only one A but not two A's.

I hope this makes sense, any help is greatly appreciated!

Answer 1

You can use NOT IN:

SELECT * 
FROM   scores 
WHERE  (history_grade, math_grade) NOT IN (('A', 'A'), ('F', 'F'))

or you can use AND and NOT:

SELECT * 
FROM   scores 
WHERE  NOT ( history_grade = 'A' AND math_grade = 'A' )
AND    NOT ( history_grade = 'F' AND math_grade = 'F' )

or:

SELECT * 
FROM   scores 
WHERE  ( history_grade <> 'A' OR math_grade <> 'A' )
AND    ( history_grade <> 'F' OR math_grade <> 'F' );

Which all output:

NAME	HISTORY_GRADE	MATH_GRADE
Mary	C	B
Austin	C	A
Kyle	B	B

fiddle