SQL - Mixing AND and OR

Question

I am not an expert on SQL. I am using SQL Server 2005, and I am trying to understand how to structure a query so that it can satisfy several requirements. I have two tables that are defined as follows:

Classroom
 - ID
 - DepartmentID
 - TeacherName
 - RoomNumber

Student
 - ID
 - Name
 - ClassroomID

I'm trying to create a query that says: “Give me classrooms in the department [x] OR department [y], which has more than 30 students, and give me classrooms in the department [w] OR department [z], which have more than 40 students. I am confused about how to correctly combine AND and ORs in my SQL. Currently I'm trying to do the following:

SELECT
  c.RoomNumber,
  c.TeacherName
FROM
  Classroom c
WHERE
  c.[DepartmentID]=5 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30) OR
  c.[DepartmentID]=6 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30) OR
  c.[DepartmentID]=7 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 40) OR
  c.[DepartmentID]=8 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 40)

What am I doing wrong? Thank!

Answer 1

, AND

(...) OR
(...) 

..

Answer 2

SELECT  c.id
FROM    Classroom c
JOIN    Students s
ON      s.Classroom = c.Id
WHERE   DepartmentID IN ('X', 'Y', 'W', 'Z')
GROUP BY
        c.id, c.DepartmentID
HAVING  COUNT(*) >= CASE WHEN DepartmentID IN ('X', 'Y') THEN 30 ELSE 40 END

Answer 3

(SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID) >30

(SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30)

;]

Answer 4

Be extremely clear with your paranas.

Notice that I added 3 more sets of partners.

((c.[DepartmentID]=5) AND ((SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID) > 30)) OR