It's because the WHERE clause is evaluated before the SELECT. The reason that SQLite allows you to do that is because SQLite is much, much simpler than PgSQL, MySQL and SQL Server (all of which don't allow you to do this).
from people
where name = 'Joe'
select upper(name) as bigname
2. It would be easier on my head. I skip the select-clause anyway when reading SQL, then come back up to it at the end, when I know the columns' source.
3. It would show that using column aliases in the where-clause would fail (unless the database did some kind of two-pass processing).