Feature highlight: Subquery in SQL Selects
Credit: Imagenation

 Source: pubs database in SQL Server 2000

Description:
Subqueries are very powerful when doing analysis of financial and sales data. Subqueries also have a fairly easy workflow - solve each select individually then combine them together. For example, in the figure below there are two Selects. The inner Select marked in yellow finds all book titles that have total sales greater than $500 in the Sales table. This inner select has three parts:

1) join the Titles table to the Sales table using title_id as join key;
2)group by title_id and have this as the only select output column because we will use it as the subselect field;
3)Use a small trick in the Having clause - compute total sales = Sum(titles.price * sales.qty) and use that immediately as the Having filter condition.

Voila we have the first part done.
The second Select is even easier - it just involves joining the Authors table to the Titles table using Titleauthor as the link table.

So now that we have the two individual Selects working, the magic ingredient to combine them is a good cut and paste editor plus the where titles.title_id in clause as the final glue that binds the two. The Where title_id in clause matches all title_ids found having sales greater than $500 to the title_id in the set of authors and titles. We added a few spruce-ups to the output including have the constant, Criteria, print out and sorting by Title. So now we can put a title and name to the big book sellers.