Nesting SELECT statements

Some databases, like Microsoft SQL Server, allow nested SELECT statements, also known as sub-selects.
For example:
SELECT contacts.*, 
orders = ( SELECT COUNT(*) FROM orders WHERE orders.contact_id = )
FROM contacts
This statement will select all contacts and show how many orders they have placed.
The COUNT(*) function is a so called Aggregate function. In this example COUNT(*) will count the number of records.