Rick Harker (5/21/2015)
Shouldn't the subqueries contain a TOP 1 ?
No. It would change the meaning of the queries. As posted, they implement "give me the department ID for Human Resource and make sure to throw a tantrum if there is a data quality issue". Adding TOP 1 changes this to "give me a department ID for Human Resource and if there is a data quality issue, pretend you never noticed and definitely don't tell anyone".
I know which version I prefer. (Allthough in reality, I would (a) not use a subquery for this anyway, but rather a join; and (b) have declared a UNIQUE constraint on the Name column in the Department table to prevent data quality issues.)