Question: select all the departments having students with even roll number

```
Dept No     Roll No   Student Name 

 1         1          lee 
 1         2          scott
 2         2          scott
 2         4          smith
 1         4          smith

```

This should result in DEpt no 2 as it has only students with roll number divisible by 2

Answer: 
You can use `GROUP BY` with `HAVING` like this.

**Query**

```
SELECT [Dept No]
FROM departments
GROUP BY [Dept No]
HAVING SUM(CASE WHEN [Roll No] % 2 = 0 THEN 1 ELSE 0 END) > 1
AND SUM(CASE WHEN [Roll No] % 2 = 1 THEN 1 ELSE 0 END) = 0

```

**Explanation**

The query returns the departments if there a rollno which is even using `SUM(CASE WHEN [Roll No] % 2 = 0 THEN 1 ELSE 0 END) > 1`. If there is any rollno with odd roll no, `SUM(CASE WHEN [Roll No] % 2 = 1 THEN 1 ELSE 0 END)` will return non zero sum and that department will be excluded.
```
declare @t table (Dept int,Rno int,Student varchar(10))
insert into @t (Dept,Rno,Student)values (1,1,'lee'),(1,2,'scott'),(2,2,'scott'),(2,4,'smith'),(1,4,'smith')

SELECT Dept,Rno,Student 
FROM (SELECT ROW_NUMBER () OVER (ORDER BY Rno DESC) row_number, Dept,Rno,Student  
    FROM @t) a WHERE (row_number%2) = 0

```