Rozenshtein question 10(sql cookbook)

all students only taking 2 particular courses  - a cleaner approach IMHO

select s.sno,s.sname,

sum(case  when  t.cno in('CS112', 'CS114') then 1 else 0 end)

from student s inner join take t on  t.sno = s.sno

group by s.sno,s.sname

having sum(case when  t.cno in('CS112', 'CS114') then 1 else 0 end) = 2

and  count(*) = 2

Rozenshtein question 7(sql cookbook)

any student taking 2 particular courses - a simpler approach IMHO

select * from student s

where exists

(select * from take t

where  t.sno = s.sno and t.cno = 'CS112')

and exists

(select * from take t

where  t.sno = s.sno and t.cno = 'CS114')

Rozenshtein question 4(sql cookbook)

all students taking less than 3 courses - easier solution than book

select distinct * from

(

select s.* , count(*) over(partition by s.sno)  cnt

from student s  inner join take t

on t.sno = s.sno

) main

where main.cnt <= 2

Rozenshtein question 3(sql cookbook)

alternative for question 3 - find student taking only one particular course

select * from student s  inner join take t

on t.sno = s.sno and t.cno = 'CS112'

where  exists

(

select t2.sno,count(*)

from take t2

where t2.sno = t.sno

group by t2.sno

having count(*) = 1

)

the books answers are good, though

Rozenshtein question 1 (sql cookbook)

really bizzare solutions in the book

here is the simple answer to find any student not taking a particular course

select * from student s

where not exists

(

select * from take t

where t.cno = 'CS112'

and t.sno = s.sno

)

usage of instead of triggers

in osborne's sql server developers guide - their example for instead of trigger is basically a rollback when a condition is met. I believe the correct place for that would be a regular trigger with a rollback. Instead of triggers make the most sense on views or when you want to do something completely unrelated.

Interesting phenomenon with class constructors in inherited classes

when the constructor is static first the descendant will run and then the ancestor.
when its an instance constructor its the other way around.

the reason being that there is no inheritance in static contructors