Download over 3.5 million times, this module is an insane query builder. Learn it.

Omega 4.x Demonstration w/Sebastian aka fubhy, by Joel Moore

Rabbit Hole is a module that adds the ability to control what should happen when an entity is being viewed at its own page.

Also see Internal Nodes