Sub queries provide a powerful means to combine data from one or more table into one result. They are sometimes called nested queries. As the name implies, they one or more queries, one inside one another other.
If you're unfamiliar them, then your first order of business should be to read our article Introduction to Subqueries. This is super place to start.
They can be used in many places throughout the SELECT statement.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters.
You can get started using these free tools using my guide Getting Started Using SQL Server