highlight: Subquery in SQL Selects
pubs database in SQL Server 2000
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
1) join the Titles table to the Sales table using title_id as join
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.
Top of Page Tutorials