Two queries - assistance!

[cutiesugar]

Hello.

I have recently been asked to help write queries for my school database. The majority of them are allset! However there are two remaining that are difficult. Allow me to describe the nature of these:

1) I am to show students who have failed all of their classes.

2) list teachers who don't teach any classes.

For part 1:
my SQL looks like this;
SELECT STUDENTS.Name, GRADES.Grade
FROM STUDENTS INNER JOIN GRADES ON STUDENTS.StNo = GRADES.StNo
WHERE (((GRADES.Grade)<=1));

Which works partially, however it lists students who have failed SOME classes and not all. How can I eliminate those that haven't failed ALL of their classes?

Also for part 2:
I was able to display a count of the teachers and their courses. However it DOESN'T LIST the teachers with 0 courses! It lists those that have courses, which is the opposite of that I require! Here is my SQL:

SELECT FACULTY.Name, Count(CLASSES.ClassNo) AS CountOfClassNo, CLASSES.EmpNo
FROM FACULTY INNER JOIN CLASSES ON FACULTY.EmpNo = CLASSES.EmpNo
GROUP BY FACULTY.Name, CLASSES.EmpNo;


Again - I realize it may be difficult to help based upon this limited information however I'm confident an SQL guru could be helpful lol.

Any help would be appreciated!

Thanks

-Becca

 

[cutiesugar]

Update: I was able to solve the first problem. But the second one (selecting the students with 100% failure) is still unsolved!

Can anyone assist?

thanks!

 

[fneily]

students who have failed all of their classes."
"list teachers who don't teach any classes."

Wow. Don't publicize that school name.

I guess a fail = 0. So instead of <=1 change it to = 0.

Also keeping your tables normalized, all tables must be normalized, Name is not a good field description. It also should be atomized to FirstName, LastName.

There is no need to keep ClassNo. If your tables are structured correctly and the relationships are correct, you don't need this field. Counts can be calculated.

 

[jaxtell]

You want an outer join on classes in order to return teaches with no classes.

SELECT FACULTY.Name, Count(CLASSES.ClassNo) AS CountOfClassNo, CLASSES.EmpNo
FROM FACULTY LEFT OUTER JOIN CLASSES ON FACULTY.EmpNo = CLASSES.EmpNo
GROUP BY FACULTY.Name, CLASSES.EmpNo;

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/