Solution: Why is no alias needed?

Well, let’s first answer the supplementary question. It seems that in the SQL types can not be used directly.
As Martin Berx rightly remarked in the comment to the English blog, you must first cast to type.
In this case:

TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'

This it is a useful little trick that might help on occasions.
Now you can execute the statement. My guess was that the natural join ensures that you compelled an alias.
In fact

SELECT order_id,
SUM(quantity*unit_price)
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders o USING (customer_id)
JOIN order_items oi ON (oi.order_id=o.order_id )
WHERE TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'
GROUP BY order_id
)
GROUP BY order_id

results in:

ERROR in Line 10:
ORA-00918: column ambiguously defined


You need to define a prefix for Order ID in the group by :


SELECT order_id,
SUM(quantity*unit_price)
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders USING (customer_id)
JOIN order_items ON (order_items.order_id=orders.order_id )
WHERE TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'
GROUP BY o.order_id
)
GROUP BY order_id


In my opinion, it should always work as in the case of the natural join. Since you have already defined that the columns are equal, the optimizer should just choose any.