I had the occasion yesterday to have a page with a section on it where it would output a very specific set of other pages, which would need to change dynamically. What I could have done is built a special page template for this page, and inside that template run a
query_posts() to get these posts. But I wanted this page to remain editable from the admin. Besides, creating a special page template every time you need to do something like this is too cumbersome. WordPress is extensible enough to do better.
Hence, the Custom Query Shortcode!