Chapter 5: Subqueries: Using Queries Within Other Queries

Using scalar aggregate functions to guarantee a single value

Subqueries that are introduced with unmodified comparison operators often include scalar aggregate functions, because these return a single value.

For example, to find the names of books that are priced higher than the current minimum price:

select title 
from titles 
where price > 
   (select min(price) 
    from titles)

title 
---------------------------------------------------
The Busy Executive’s Database Guide 
Cooking with Computers: Surreptitious Balance
     Sheets
Straight Talk About Computers 
Silicon Valley Gastronomic Treats 
But Is It User Friendly? 
Secrets of Silicon Valley 
Computer Phobic and Non-Phobic Individuals:
     Behavior Variations 
Is Anger the Enemy? 
Life Without Fear 
Prolonged Data Deprivation: Four Case Studies 
Emotional Security: A New Algorithm 
Onions, Leeks, and Garlic: Cooking Secrets of the
     Mediterranean 
Fifty Years in Buckingham Palace Kitchens 
Sushi, Anyone? 
 
(14 rows affected)

Using group by and having in expression subqueries

View this book as PDF