SQL - Consultas encadeadas (subqueries)

O que é consulta encadeadas?
Consulta encadeada é quando um resultado de uma consulta é utilizado por outra consulta.

Exemplo:

SELECT NOME_PRODUTO, PESO_PRODUTO
FROM PRODUTO
WHERE CODIGO_PRODUTO IN
(SELECT CODIGO_PRODUTO
FROM ITEM_PEDIDO
WHERE QUANTIDADE = 500)