Subqueries in SQL Server: a query inside a query

A subquery is a SELECT in brackets. In WHERE it filters. In FROM it is a derived table. In SELECT it runs per row if you are not careful.

Above average

SELECT order_id, total_amount
FROM Orders
WHERE total_amount > (SELECT AVG(total_amount) FROM Orders)
ORDER BY total_amount DESC;

The inner query runs once here. A correlated subquery that mentions the outer table can run once per row. If it is slow, rewrite as a JOIN to a grouped CTE. See CTEs.