Is there such a thing as portable SQL?

In my experience, even though there is a SQL standard, it is quite difficult to write SQL that works, unmodified, over a large number of RDBMS.

Thus, I'd like to know if there is a subset of SQL (including DDL, schemas, etc) that is known to work on all major RDBMS, including PostgreSQL, MySQL, SQL Server and, last but not least, Oracle. What kind of pitfalls should be avoided when writing portable SQL?

By the way, is there a project whose goal is to translate a valid subset of SQL into the specific dialects used by all these vendors? I know that Hibernate and other ORM systems have to do this, but I don't want ORM, I want to write straight-to-database SQL.

Thanks!